The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

Those of us that write software for SQL Server or administer SQL Server instances are in the middle of the password challenges for security. We often require some sort of password for our users, storing this inside our database, but we also deal with the hassles of managing our own passwords on the platform, for administrators, applications, and services. We may handle one set of passwords differently from others, or perhaps even handle every type of password differently.

When I started working with computers, most of the systems were single user. We didn't have logins on the old Vic-20s or Apple IIs or TRS-80s. In fact, security was keeping control of the floppy disks or tapes with our code on them. Over time, I moved to multi-user systems in university, where we needed a login and password, but often we used the same random password we were assigned all semester. A few systems asked us to change periodically, which later became a requirement (and best practice) in various companies. In fact, at one company, the general password policy was 120 days for users, but 30 days for sysadmins, including service accounts.

Over the last decade, I've seen various advice and changes in how platforms and software handles passwords. We have two factor authentication (2FA) taking hold for some, longer password fields for many, complexity requirements that are all over the board, biometrics being implemented in mobile systems and certainly still many poor practices highly visible in large organizations. There are many designs and practices talked about, but I rarely see a good, comprehensive list of well thought out recommendations.

Until this week. Troy Hunt wrote a great piece on modern authentication guidance, and it's one that got me thinking. We should be both allowing users more freedom, and preventing stupidity, like reusing cracked passwords. We shouldn't be forcing changes, allowing password managers, and getting rid of specific complexity requirements. He gives reasons and provides references from large national organizations that study the issues. Some of the choices aren't ones I've made in the past, but they are ones I'd like to see implemented in the future.

I'd really like to see a shorter list of best practices for software authetnication that developers can reference easily, with supporting documentation and better examples. This is one of the issues I've had with Microsoft's Secure Coding site. If a developer wants to get started and upgrade their code, they don't have good examples. Just lots of descriptions of what is good, but not a framework of schemes that help them actually start writing code.

Certainly applications should separate their authentication schemes and be able to upgrade them, especially as our guidelines change. If we needed to implement 2FA, how easily could any of you upgrade your authentication? Perhaps more of a question, if you wanted to have limited access when there are red flags during the login, can you change your security model? To me, that's a hard one, but one that we might want in more software.

I would also argue that SQL Server should implement more flexible security schemes in its own framework. Why shouldn't we have 2FA? Or perhaps a better way of enforcing passwords outside of what Windows allows. Could we attempt to verify a sysadmin login, perhaps limiting them to read only informaiton, or system functions outside of databases until some second verification is performed? For that matter, why can't we have a sysadmin that doesn't have the ability to read from any tables in any databases? I've love to see a security model that implemented those restrictions.

Security, authentication, and controlling data access is always a challenge, but I'm hopeful that we will see more resource and options, as well as guidance and sample code, in the future. We rely more and more on data in our digital databases, so let's try to provide more security as we move forward.

Steve Jones from SQLServerCentral.com

» Join the debate, and respond to today's editorial on the forums

All the headlines and interesting SQL Server information that we've collected over the past week, and sometimes even a few repeats if we think they fit. These headlines are gathered throughout the week and are posted in real time at the website. Check there for information throughout the week or enjoy this weekly summary of the SQL Server world.

Virtualization and Containers

Automatically restarting Docker containers - One of the problems that I’ve encountered since moving my Dev/QA departments to using SQL Server within containers is that the host machine is now a single point of failure. Now there’s a whole bunch of posts I could write about this but the one point ......(more)

Vendors/3rd Party Products

How might classification and better documentation improve data safety? - SQL Atlas is the latest prototype to come from the Redgate Foundry - find out more about what the tool does and try out the interactive demo for yourself....(more)

SQL Monitor plans for the second half of 2017 - SQL Monitor’s dev team has made huge improvements to the product over the last year. In the first half of 2017 alone, they released reporting capabilities, support for collecting metrics from VMWare hosts, significant improvements to performance and scalability, improved configurability of alerts, as well as dozens of smaller enhancements. Since we’re about half way through 2017, it’s time to talk about our plans for the rest of the year....(more)

ANTS Performance Profiler for the SQL Server Dev - Sheldon Hull looks at the pros and cons of using ANTS Performance Profiler for SQL performance tuning. ...(more)

T-SQL

Passing two SQL queries to sp_execute_external_script - Recently, I got a question on one of my previous blog posts, if there is possibility to pass two queries in same run-time as an argument to external procedure sp_execute_external_script. Some of the arguments of the procedure sp_execute_external_script ......(more)

Prioritizing rows in a union - I just remembered a pretty common data challenge the other day. Suppose you have a number of tables, all with similar information in them. You want to union their contents, but you need to prioritize them, so you want to choose all the rows from table ......(more)

DevOps–Fixing Poorly Named Constraints - I was building some code the other day and kept getting problems in my deployment for a change. The deployment was having issues, and this came down to this statement. ALTER TABLE EventLogger DROP CONSTRAINT [PK__EventLog__5E548648B043C0BC] The problem ......(more)

The Lighter Side

Downloadable SQL Server Desktop Wallpapers - I’ve set things up so you can quickly download desktop wallpapers featuring cartoons and SQL Server concepts. Get ’em here Grab wallpapers at sqlworkbooks.com/wall. Want to know when I add new wallpapers? I’m working on a new query tuning wallpaper for ......(more)

HASSP-2: The Sequel - You thought we were done? We're just getting started. Here is a look at our changes for HASSP-2. ...(more)

Periscope Data | Using?Raspberry Pis to Build Beautiful Wall-Mounted Dashboards - Everyone wants a beautiful, wall-mounted dashboard for their primary KPIs—but setting one up can be challenge. Here's an easy and affordable way to display your dashboards without hiding a desktop computer under a desk. ...(more)

SQL Server Security

SQL Server 2017 SQLCLR - Whitelisting Assemblies - A little while ago I wrote a blog-post about the changes in the SQLCLR security model in SQL Server 2017. I wrote about how Microsoft has changed the relation between CAS and security boundaries, and CAS is no longer supported as a boundary...(more)

SQL Server on Linux

SQL Server on Linux Series: Additional Drives - One of the biggest differences with managing SQL Server Linux is with drive presentation. With Windows, we’d all scream if we saw a SQL Server with just one drive (C:) and everything piled on it. The same goes for any other operating system, including ......(more)

SSoL: Where are my data and log files? - I don’t think most of us need to know Linux, but if you end up managing a system, it’s good to have a little idea of how to get around. This is a short series of posts as I remember the skills I used to have back in university. This is easily documented, ......(more)

SQL Server on Linux Series: Moving TempDB - In the last blog post, we added additional drives to the SQL Server machine so that we can scale out our storage workload. Let’s move the tempdb objects to one of these new drives! By default, our tempdb files are in the system database folder at /var/opt/mssql/data. We ......(more)

SQL Server Internals

Where do SQL Server tasks come from? - In my previous post I discussed the unsung early years of a SQLOS thread. Now it’s all very well knowing that threads extend themselves with SystemThreads, don Worker outfits, and execute SOS_Tasks, but I keep glossing over where tasks come from. Gloss ......(more)

Software Development

Limit access to just Visual Studio Team Services using Azure Active Directory Premium - By leveraging Conditional Access Policies of Azure Active Directory (AAD) Premium it's possible to add external users to your AAD and limit them to just Visual Studio Team Services (VSTS). You'll need: An Azure subscription AAD Premium (you can get ......(more)

Service Broker / SOA

Message queues for the DBA: sending data out into the world - When a DBA hears the words “message” and “queues” chances are our thoughts first go out to Service Broker. If you’re talking about undervalued technologies in SQL Server, this one would darn near be at the top of my list. ...(more)

Security news and thoughts

Passwords Evolved: Authentication Guidance for the Modern Era - Sponsored by: Thanks to Raygun! See every problem in your software and how to fix it. Reproduce issues with greater speed and accuracy. Try it free today!In the beginning, things were simple: you had two strings (a username and a password) and if someone ......(more)

Alternatives to Government-Mandated Encryption Backdoors - Policy essay: "Encryption Substitutes," by Andrew Keane Woods: In this short essay, I make a few simple assumptions that bear mentioning at the outset. First, I assume that governments have good and legitimate reasons for getting access to personal data. ...(more)

Microsoft Announces Windows Bounty Program - Microsoft announced a new Windows Bounty Program that will pay researchers up to $250,000 for finding and disclosing security vulnerabilities. ...(more)

Too Many Organizations Unprepared for Cyber Attacks - CUPERTINO, Calif. -- July 20, 2017 -- Demisto, Inc., an innovator in Security Automation and Orchestration technology, today announced the results of the State of Incident Response 2017, a study investigating challenges faced by incident response (IR) ......(more)

Reporting Services

“Does that copy subscriptions too?” – Now it does! New PowerShell SSRS commands - On my last post “SSRS Report Deployment Made Easy – 700 Times Faster” I showed how you can rely on the Microsoft PowerShell module ReportingServicesTools to automate several steps that otherwise would be a big time consumer. After a couple of shares ......(more)

R Language

R’s tidytext turns messy text into valuable insight - Authors Julia Silge and David Robinson discuss the power of tidy data principles, sentiment lexicons, and what they're up to at Stack Overflow.“Many of us who work in analytical fields are not trained in even simple interpretation of natural language,” ......(more)

Introducing Joyplots - This is a joyplot: a series of histograms, density plots or time series for a number of data segments, all aligned to the same horizontal scale and presented with a slight overlap. The name "Joy Plot" was apparently coined by Jenny Bryan in April 2017, ......(more)

Product Upgrades and Releases

Looking for sp_whoisactive? - A while ago I quietly announced the new home for sp_whoisactive, whoisactive.com . Today I finished migrating all related content -- including the documentation consisting of a series of 28 articles posted on this blog a while back. So if you're looking ......(more)

PowerShell

offline installs of dbatools - If you work in a secure environment or your computers cannot access the Internet for any reason, you can still install dbatools with ease by downloading the zip from a workstation that does have Internet access ?? And now that our module is digitally signed, you can keep your Execution Policy set to AllSigned, too....(more)

Building Better PowerShell Parameter Validation - A good validation routine is important, and PowerShell lets us do this through parameter validation attributes. ...(more)

PowerShell for Assigning and Querying Tags in Azure - Tags in Azure are useful pieces of metadata for documenting (annotating) things such as:...(more)

PowerPivot/PowerQuery/PowerBI

Power BI Custom Visuals Class (Module 61 – Power KPI) - In this module you will learn how to use the Power KPI Custom Visual. The Power KPI displays your KPI indicator values on a helpful multi-line chart with labels. Module 61 – Power KPI Downloads Power BI Custom Visual – Power KPI Dataset – Budget vs ......(more)

Power BI Custom Connector: Connect to Any Data Sources. Hello World! - One of the recent features in Power BI Desktop is the ability to create your own custom connector and use it when connecting to the data source. This feature looks just one feature, but it opens doors to many possibilities. With this feature you can ......(more)

Optimization Problem in Power BI using R scripts: Part 3 - As I have discussed before, R is not only for doing Machine Learning (despite it is a really good tools for it), it also can be used for helping managers to so decision support. In the last two posts (Post 1 and 2), I have explained the main concepts ......(more)

Measure table madness - Measure tables in Power BI models are a fantastic way to group your calculated measures together in one place and make for a tidy model. Unlike calculated columns, a calculated measure can be placed on any table so long as the best practice of using ......(more)

Free book, Updates for SSRS and SSAS and an updated Power BI gateway - Online Book: Analytics with Power BI and R (@leila_etaati) When to Use SUM() vs SUMX() (@ExceleratorBI) Model Comparison and Merging for Analysis Services SQL Server 2017 Reporting Services Release Candidate now available Power BI On-premises data gateway ......(more)

Give external users access to your Azure Analysis Services server - In this video, Patrick looks at how you can provide access to your Azure Analysis Services server to external users. This is done by way of the Business to Business (B2B) capabilities of Azure Active Directory. He then takes it to the next level and ......(more)

Performance Tuning SQL Server

Demo: Identify and fix plan change regression in SQL Server 2017 RC1 - Plan change regression happens when SQL Database changes a plan for some T-SQL query, and the new plan has the worse performance than the previous one. SQL Server 2017 has Automatic Tuning feature that enables you to easily find plan change regressions ......(more)

ColumnStore Indexes And Recursive CTEs - Alone Together When I think about SQL Server features, I often picture a high school cafeteria. AGs are making fun of Mirroring, index rebuilds are walking around taking everyone’s lunch, dta is making a glue sandwich, and no one knows who Service Broker ......(more)

SQL Server Updates behavior - Recently a client asked me: “We want to place all the fields of the table in the SET clause of an UPDATE, no matter if the values are changed or not. How does it impact the I/O? Does SQL Server update every field, even if the old value matches the new ......(more)

Statistics and the Ascending Key Problem - I’ve mentioned previously how not having up to date statistics can cause problems in query performance. This post looks at something called the Ascending Key Problem which can badly affect your cardinality estimation in some cases and therefore your ......(more)

Performance is bad. Did you change anything recently? No. Are you sure? - Ever have this conversation? Dev: Hey, can you help me? The performance on my application is terrible all of a sudden. DBA: Sure thing. Has any code changed recently? Dev: No. Nothing’s changed at all. Everything is just suddenly slower. DBA: Are you ......(more)

SQLskills 101: The Other Bad Thing About Clearing Procedure Cache - Every time you execute a query against SQL Server, it goes through optimization and compilation and a query plan is generated. Most of the time that plan goes into cache (there’s always exceptions, which is a discussion for a separate time), which means ......(more)

Microsoft News

Microsoft is Changing The Windows 10 Servicing Model, Again - Microsoft has moved Windows to a servicing model with the release of Windows 10 which means that the company is updating the software at a much faster rate. Long gone are the three-year gaps between major updates with the company boldly moving to updating ......(more)

Microsoft Team Foundation Server 2017 Update 2 Tracks Delivery Plans - Updates include a timeline view that aids in the development team management, an enhanced Team build definition editor and new Git features. ...(more)

New Windows 10 Fall Creators Update test build adds Cortana voice commands, phone-PC linking - Microsoft is continuing to add new features to its Windows 10 Fall Creators Update test build as it enters the final development stretch. Here's what's in Build 16251. ...(more)

Using GENERATE and ROW instead of ADDCOLUMNS in DAX - This article explains how to improve DAX queries using GENERATE and ROW instead of ADDCOLUMNS when you create table expressions. A very popular DAX function to manipulate columns in a table expression is ADDCOLUMNS. You can use it to project new columns ......(more)

Hardware News

The Intel Kaby Lake-X i7 7740X and i5 7640X Review: The New Single-Threaded Champion, OC to 5GHz - Intel’s direction for the high-end desktop space has taken an interesting turn. After several years of iterative updates, slowly increasing core counts and increasing IPC, we have gotten used to being at least one generation of microarchitecture behind ......(more)

Western Digital Announces Four Bit Per Cell 64-Layer 3D NAND Flash - Western Digital's SanDisk subsidiary and Toshiba have a long history of jointly developing and manufacturing NAND flash memory. While that relationship has been strained by Toshiba's recent financial troubles and attempts to sell of their share of the ......(more)

USB 3.2 Update to Bring 20 Gbps Bandwidth: USB 3.1 Type-C Cables Compulsory - The USB 3.0 Promoters Group announced an update to the existing USB 3.1 standard in order to double the maximum possible bandwidth from 10 Gbps to 20 Gbps. This USB 3.2 specification is currently in the final draft review phase. USB 3.2 will remain backward ......(more)

Toshiba Announces TR200 Retail SATA SSDs With 3D NAND - Toshiba has announced their first retail SSDs to use 3D NAND. The new TR200 series will use Toshiba's 64-layer BiCS3 3D TLC NAND, the first generation of their 3D NAND flash technology to be suitable for mainstream mass-market use. The TR200 series is ......(more)

DevOps and Continuous Delivery (CI/CD)

DevOPs is not just about the latest & greatest tools - I’m a technical guy. Thus – I love tools. They make stuff go. Tools are great. Except – as I wrote in my other post — DevOPs and Databases — the one thing you may be doing wrong. You can’t just focus on one thing in DevOPs. You need to consider Tools, ......(more)

Database Design, Theory and Development

Acids and Databases: A look at ACID and transactions - Relational database management systems (RDBMS) such as SQL Server, Oracle, MySQL, and PostgreSQL use transactions to allow concurrent users to select, insert, update, and delete data without affecting everyone else....(more)

No, Binary Collations are not Case-Sensitive - Quite often people will use, or will recommend using, a binary Collation (one ending in “_BIN” or “_BIN2“) when wanting to do a case-sensitive operation. While in many cases it appears to behave as expected, it is best to not use a binary Collation for this purpose. The problem with using binary Collations to achieve case-sensitivity is that they have no concept of linguistic rules and cannot equate different versions of characters that should be considered equal....(more)

Data Science

The University of Virginia’s Data Science Education and Data Science Institute - The University of Virginia was founded in 1819 by Thomas Jefferson, and is now offering a Master of Science degree in Data Science (MSDS), and a new “dual degree” with a Master in Business Administration and a Master of Science in Data Science (MBA/MSDS). ...(more)

Data Privacy

Removing sensitive data from a database - Not too long ago I was assisting a client with an issue they were experiencing while running some compliance software to check for sensitive data. Sensitive data was picked up in the SQL Server data file but when the DBAs checked, the data was nowhere ......(more)

Conferences and Events

Announcing PSDay.UK – Whats a PSDay? - On Thursday evening I attended the joint London WinOps and PowerShell User Group. It was an excellent evening with two great sessions by Jaap Brasser and Filip Verloy. PSDay.UK There was also an exciting announcement about PSDay.UK https://psday.uk PSDay.UK ......(more)

Computing in the Cloud (Azure, Google , AWS)

Azure Billing Can Be Misleading - One of our customers ran across some interesting data within their Azure bill. Specifically they were looking at the billing for their SQL DW, but this same issue can show up in the billing statement for other services as well....(more)

Selecting the Best Database for Your Organization, Part 1 - The utility-based consumption model of paying for cloud services has caused a major disruption in the traditional models of technology. Read More ...(more)

Danger Averted: A DBA Horror Story - Most technical articles written explore new features of existing products or identifying a problem and providing a solution. read more ...(more)

Career Growth

One SSMS Trick That Will Make You a Faster Query Builder - Here’s the scenario: you copy and paste some code into a query you are building. A few minutes later you need that same snippet again, but you’ve already copied and pasted something else onto the clipboard....(more)

Simple Talk is hiring a full-time editor - Have experience editing and writing technical content? Redgate is currently hiring for their educational publishing site, Simple Talk. They're open to accepting remote workers, so if you're interested, there's no excuse not to go ahead and apply!...(more)

Backup and Recovery

Two Ways to Restore Database from MDF File in SQL Server - IntroductionIn todays decade Relational database Management system became the necessity of the organizations whether they are small level or large level. In such context SQL Server is the best and successful RDBMS which provides highly integrated services ......(more)

Administration of SQL Server

Downgrading SQL Server Editions - At some point in your career working with SQL Server, you will run into a situation where the wrong edition of SQL Server has been installed on a server and will need to change the edition for licensing reasons. Whether it is Enterprise Edition where Standard Edition should have been installed, Enterprise Edition where Developer Edition should have been used, or my favorite, Evaluation Edition where the 180 day trial has expired and Enterprise Edition isn’t going to be used...(more)

SQL Nexus 6.0 is released to github - With codeplex shutting down, we have moved SQL Nexus to github with a new release (6.0). Now both Pssdiag/SQLDiag manager and SQL Nexus are on github. Where to get it As you navigate to SQL Nexus, you can download code and released binary files. If ......(more)

SQL SERVER – Msg 1833 – File Cannot be Reused Until After the Next BACKUP LOG Operation - While preparing for a demo for an upcoming session, I came across an error which I didn’t see earlier. Here is the error in the UI and it is related to Backup Log Operation. I am also trying to add a file to the database using below command. USE [master] GO ALTER ......(more)

Let’s Corrupt a Database Together, Part 3: Detecting Corruption - So far in this series, I’ve shown you how to corrupt a clustered index, then how nonclustered indexes can be corrupted independently. If you haven’t read those, you should start there first. Let’s start again with our 50Ways database, but this time we’re ......(more)

Installing SQL Server: Video Tutorial - In this video I talk you through installing SQL Server 2017 RC1 on Windows Server 2016 virtual machine. It’s a nice simple process.If you need help with SQL Server check out our SQL Server Consultingpage ...(more)

Identifying Deprecated Feature Usage (Part 1) - I've wanted to do some Event Notification testing for SQL Server deprecation events for quite some time. The thought process here is that I could send myself an alert to identify usage of SQL Server features that will be removed from the next major version ......(more)

Removing sensitive data from a database - It’s fairly widely known that just because you delete data from a database, it doesn’t mean it’s gone from the data file itself, but the question is, how should you go about removing it?...(more)