Cloud First

SQL Server 2016 is the first “cloud first” release of SQL Server, as told to us by Remus Rusanu, former Microsoft SQL Server development team member. The features and enhancements in SQL Server 2016 have been running in the Azure SQL Database cloud for some time before they will be packaged up and released as the on-premise SQL Server product that most of us expect to work with.

There are a number of items about this worth discussing, but one stands out to me. The idea of using large numbers of feature flags and deploying code to the “cloud” for use by customers and internal Microsoft people is interesting. On one hand, it’s really just a faster way of having beta software examined by users other than developers, with no install/uninstall/upgrade for users. Speed is good, but being on the bleeding edge and having systems break isn’t necessarily what any of us want. However the use of these flags to turn features off quickly means that disruptions can be minimized for individual customers.

Some of the engineering process changes made to be cloud first were important for Microsoft to have one main branch of code for SQL Server. Having a separate branch for the cloud and on-premise versions had to be inefficient and problematic. However, that’s not something we, as customers, care about. We just want the database platform to work, wherever we run may run it.

I do think that having features come out in Azure, whether private testing, public preview, or general availability is good. The people that can test these features give feedback quickly, and the rest of us aren’t affected by the problem code. More importantly, the developers at Microsoft get the chance to learn more about how the features will be used and tune them before a very wide set of on-premise customers get code. Personally I was glad to see things like Row Level Security (RLS) appear in Azure first (Mar 2015), and come later to the on-premise version (SQL Server 2016).

I really expect in the future that almost every feature that is added to the SQL Server platform will be run in some form in Azure before it’s released for on-premise use. In fact, I’d be surprised if features are added to the on-premise without 6-12 months of testing in the cloud. That’s good, and I hope it leads to an increase in code quality for those of us that will run SQL Server on our own hardware.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.0MB) podcast or subscribe to the feed at iTunes and LibSyn.

Changing the sa Password with SQLCMD

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I wanted to make a quick note on changing the sa password, as this is a sensitive account, and the password should be changed if you ever suspect it is compromised. I’d also recommend you change this if anyone that knows the password leaves your group.

I wrote about using SSMS, but that’s not always convenient. If you need to change this remotely, perhaps in a hurry, SQLCMD is a quick way to do this.

SQLCMD is a command line tool, so open a command prompt.

2016-04-06 12_47_33-Photos

Run SQLCMD and connect to your instance as a sysadmin. If you have any doubt, you can enter the query from my previous post to check your connection.

Once you’ve connected, you can issue this code:

ALTER LOGIN [sa] with PASSWORD = N‘Sup#rAmaz!ngP@$$w0rd’

This is the code that will change the password for the login specified, even if I’ve logged in with a different account.

Once I’ve done this, test the sa login from a new session and verify it works.

SQLNewBlogger

Make sure you know how to do this. It’s a basic skill, so learn it, blog about it, and use it where appropriate. Maybe write about why you’d do this in your own post.

References

SQLCMD – https://msdn.microsoft.com/en-us/library/ms162773.aspx

ALTER LOGIN – https://msdn.microsoft.com/en-us/library/ms189828.aspx

Guess the RTM Build

I’m going to take a page from Joey D’Antoni and start a contest. We’ve gotten quite a few releases of SQL Server 2016, including now 4 Release Candidates (RC). RC3 was released recently, and noted as being feature complete. I am assuming this might is the last RC, though certainly any big bugs might result in an RC4 (or 5).

This week, for a bit of fun, I’m going to start a pool for the final RTM Release number. If you post in the comment thread for this editorial, and guess correctly, I’ll send you a prize. Likely this will be something from Amazon, though I haven’t picked anything yet. Suggestions are welcome, though I am thinking some new SSD. We all need more fast storage.

I’ll use the Price is Right rules, so the closest person, without going over. Ties will result in the first person posting winning, based on thread order. If the final build is 13.00.9500.12, and you post 13.00.9500.13, you’re out of the running.

The RCs have been almost 100 builds apart. However there’s no guarantee that the RTM build will be follow any pattern, though it seems like previous releases have ended on an easy to remember build number like 12.00.2000, 11.00.2100, 10.0.1600, etc.

Take your guess, and if you’re right, I’ll contact you with a private message to redeem your prize. Contest ends when SQL Server 2016 RTMs.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.4MB) podcast or subscribe to the feed at iTunes and LibSyn.

Getting Started with Encryption in SQL Server 2016

The release of SQL Server 2016 gives developers a number of ways in which they can securely encrypt and protect their data. In this introductory session, you will learn about the encryption options in SQL Server 2016, watching Always Encrypted, TDE, Row Level Security, Dynamic Data Masking, and server side column encryption can secure and protect your data from unauthorized users.

Length: 60-75 minutes

Code: Github

Powerpoint deck: Getting Started with Encryption in SQL 2016.pptx

The Hidden Trigger

I ran across a post recently where someone asked about using a trigger for a particular issue. There were quite a few responses, but one person brought up the fact that triggers have various downsides, all of which I agreed with. However I didn’t see one mentioned, which is a big one with me.

Triggers are hidden.

It’s not that triggers are hidden from DBAs by default, after all, there’s a triggers folder under each table in the Object Explorer, but how often do you open that folder? It’s just not obvious when a trigger exists on a table. The use of triggers is too uncommon, and it’s easy to forget they exist, even if you’re the person that added them to a database.

This is really my one big complaint about triggers. I can’t easily determine when a trigger is firing, which sometimes leads to issues. I’ve troubleshot strange things in SQL Servers, only to realize hours later that a trigger is firing based on some action.

Perhaps it’s me, but do many of you think about triggers first when something strange happens in SQL Server? I think I’ve gotten better over the years at looking for them, but I still get stymied and waste time trying to debug issues. I really wish that a “triggers” tab would appear in SSMS, maybe after the Execution Plan tab, whenever a trigger fired. It could have a copy of inserted and deleted, or even just the trigger code. That alone might clue me in quicker.

I think triggers can be valuable in database systems, but because they seem to be so rarely used, I’ve grown to distrust and dislike them over the years.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 1.9MB) podcast or subscribe to the feed at iTunes and LibSyn.

Basic SQLCMD–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I had the need to connect from the command line recently, and decided to make a quick post on using SQLCMD, as I had an issue.

SQLCMD is a command line utility that comes with SQL Server. I know many people don’t use command lines, but they are handy at times. I recently opened a command prompt.

2016-04-06 12_47_33-Photos

I then typed SQLCMD. After a delay, I got this:

2016-04-06 12_51_23-Photos

The issue here is that I don’t have a default instance on this machine. All of mine are named. I need to provide a –S parameter, with a server name (and possibly instance name).

2016-04-06 13_01_33-Photos

I do that and I’m connected. By default, SQLCMD (and osql) try to use Windows Auth. The 1> indicates that the utility is ready for T-SQL queries. You need to know your language here as there’s no help.

I can enter code, and check my user name. I do this, and get a 2>. The end of a batch is indicated with “GO” and this will execute the batch. You can see how this works below:

2016-04-06 13_01_47-Photos

I can use this to make my code easier to read. I can format code as I would in an editor, though be aware you can’t go back and edit previous lines.

2016-04-06 13_07_03-Photos

If I enter go, I’ll get this:

2016-04-06 13_07_15-Photos

Not so easy to read. I have to scroll up to even figure out what the display is:

2016-04-06 13_07_26-Photos

As you can see, using SELECT *, or retrieving too many columns make results hard to read. You would to wise to pick only those columns you need to return.

To leave SQLCMD, you can type exit, which will return you to the command prompt.

2016-04-06 14_00_07-Start

This is a short look at SQLCMD. The older, osql, utility functions the same way, and both are good, lightweight ways to connect to your SQL Server instance.

Changing the sa Password with SSMS–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I wanted to make a quick note on changing the sa password, as this is a sensitive account, and the password should be changed if you ever suspect it is compromised. I’d also recommend you change this if anyone that knows the password leaves your group.

Changing sa with SSMS is really simple. Follow these steps:

Connect to SSMS as a sysadmin. You can check this for your login. Then expand the Security folder and the Logins folder. Right click the “sa” account and choose properties.

2016-04-06 12_40_31-Photos

Once you do this, you’ll get the login properties dialog, and see the Password text field at the top in the General tab.

2016-04-06 12_42_22-Photos

You can type a new password in the Password box, and confirm this in the Confirm Password box. Password policies checks are up to you, though I’d recommend you use them.

Click OK and the password is changed. You can then connect a new query window as “sa” and verify your password.

SQLNewBlogger

Make sure you know how to do this. It’s a basic skill, so learn it, blog about it, and use it where appropriate. Maybe write about why you’d do this in your own post.

MDF File Password Confusion

I had never seen this, but I ran across a blog that mentioned an MDF File password here. The post really looks at ways to reset the administrator password for the “sa” account in SQL Server. However it has some mistakes and issues. I tried leaving a comment, but comments are disabled.

With that in mind, I decided to respond to a few things and clear up confusions.

With regards to the post, I think it’s confusing in that the text notes an MDF file password, but all the instructions are really about resetting the “sa” account password. sa is the built in sysadmin account in SQL Server, which isn’t related to the MDF file. The MDF file is the extension of the main data file for a database. You can change this, but there isn’t a good reason to do so. Note, the .ndf files are the same format, though by convention, these are the 2nd, 3rd, and other files added to a database.

There also isn’t a password on these files. I can open them in notepad (not recommended) or xvi32, and there isn’t any requirement if I have read access in NTFS to the file. It doesn’t matter if this is the master database or any user database. If you have NTFS permissions, you can read the file.

Now interpreting is different. SQL Server interprets this, and it requires permissions itself to access the server process, either sysadmin, or normal login. However, you can use ORCAMDF or MDF Viewer, or some other tool to read the files. The information contained in an mdf/ndf file is just formatted in a certain way. If you spend a lot of time, you will understand how to interpret the format.

Changing the sa password requires that the SQL Server service be running and you connect in some way. The post gets the methods right, but says that you must stop the service, which is only needed if you access the file some other way (ORCAMDF, xvi32, etc.). If you want to change the sa password, there are a few choices:

  1. USE SSMS
  2. Use SQLCMD
  3. Use osql
  4. Use one of the above methods with SQL Server restarted in single user mode
  5. Use a third party utility.

Any of these first four will work, and feel free to use whichever fits your situation. The last one is one I do not recommend as I can’t be sure any third party products will work correctly here.

Ultimately I’m a little embarrassed by this post, as it appeared through our syndication process on SQLServerCentral. We don’t review these posts, so there is no quality control. Most of the posts on this blog are good ones, but this one appears to be by a guest author and it’s one I’d ignore.

Quick Encryption with Always Encrypted

What do you need to do in order to access data in a SQL Server that’s encrypted with Always Encrypted? It’s not much, and it’s really simple.

  1. The certificate used for encryption
  2. A parameter in the connection string

That’s it. It’s a small list of things.

I was experimenting with this, and I set up encryption on a VM, then copied the certificate backup to another VM and installed it in the Certificate Store.

2016-03-28 17_56_00-Settings

This is all I changed on my C# application to enable encryption.

strConnstring += “; Column Encryption Setting = Enabled”

I had a connection string built, and I added this one little option to the end and when I queried my encrypted table, I could read the data.

There are certainly more caveats and more to learn about encryption, but this shows how easy it can be to change your application. Just alter the connection string.

Locking sa

When I started working with SQL Server, the sa account was “the” account used for admin operations. This was the default account for many DBAs and as a result, it couldn’t be locked out.

This changed in SQL Server 2005, which is a good thing. We don’t want unlimited attacks on the sa account with brute force password guesses. I wasn’t aware of this, as I haven’t had an issue with attacks in a long time. However Jeff Moden pointed out to me recently that we can lock out sa.

I decided to test.

First, I went to the local policy on my desktop and checked the security policy. No lockout was set, which probably makes sense for consumer OSes. I took a minute to then set my lockout to 5 attempts with a 30 minute timeout.

2016-03-28 17_46_26-Settings

I then restarted my SQL instance. I couldn’t get this to lock me out at first, so I decided to ensure the policy applied.

I then tried logging in with the sa account 6 times with the the wrong password. Each time I got this message. Including the 7th time with the correct password.

2016-03-28 17_39_02-SQLQuery1.sql - JOLLYGREENGIANT_SQL2014.master (sa (51)) - Microsoft SQL Server

No note about being locked out. However when I check the properties for sa, I find the login is locked out.

2016-03-28 17_39_25-Settings

I could uncheck the box, but I can easily use T-SQL as well.

ALTER LOGIN sa  WITH PASSWORD=’test’ UNLOCK

Please don’t use a password like this. I actually ran this to test and then reset the password to something more complex.

Reference

ALTER LOGIN – https://msdn.microsoft.com/en-us/library/ms189828.aspx

SQL Authority – http://blog.sqlauthority.com/2009/04/23/sql-server-fix-error-18486-login-failed-for-user-sa-because-the-account-is-currently-locked-out-the-system-administrator-can-unlock-it-unlock-sa-login/