The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded
It’s TSQL Tuesday!!! The topic was picked by Sebastian Meine over at SQLIty.net who is hosting this month’s edition of the rotating monthly blog party known as TSQL Tuesday. Sebastian asks participants this month to write about passwords.
There is a saying in circles with high operational security (opsec). “Three people can keep a secret, as long as two of them are dead”. I try to be careful when sharing things like passwords. I get suspicious when software offers to store it for me.
I just finished writing a series on some of the edges, corners, and shadow places of SSMS. When I looked up this months topic I thought I’d share something I discovered while researching Registered Servers and stored passwords.
Among many other useful features offered by Registered Servers, SSMS allows you to import and/or export your registered servers list. When exporting you are prompted if you’d like to store usernames and passwords. It sounds convenient at first but as stated I become suspicious of anything “storing my password”. This goes for websites, browsers, applications, and Nigerian Princes.
Once upon a time SQL Server stored these passwords in the registry. They were encrypted but it wasn’t too big a deal to find out where they were and break it. If you had access to DMO and a basic understanding of programming you could even just ask SQL Server what the password was directly. Some of these old approaches can still be found on code project today.
The modern product appears to be a little more secure. The registered server list is now persisted to disk in the following location with encryption unique to the user/box.
C:\Users\<username>\AppData\Roaming\Microsoft\Microsoft SQL Server\120\Tools\Shell The file in this folderr named regsrvr.xml contains the full list of registered servers and groups. Among some of the SSMS specific information like connection color etc. is the connection string associated with each registered server. SQL accounts you’ve added that require a password (such as sa – he says with a scowl) do in fact have the username and password stored within this file using some method of encryption. My guesses on what kind below.
While earlier versions of SQL Server made discovering the password from this encrypted value more of a nuisance than an obstacle – today’s approach utilizes a type of “salt” unique to the current installation/windows user. When exporting your registered servers list, SSMS generates a very similarly structured version of this file with a regsrvr extension. While similar, there are a variety of differences. In my testing for example, it generated a slightly different encrypted value on the exported version versus the saved version of the connection string.
MSDN doesn’t go into detail about how these passwords are encrypted – it only says that they are and that you should exercise control over these files as they do contain sensitive information. Good to see no one making “unbreakable” claims here. Also understandable why Microsoft would not overly document how they are keeping something secret.
My worry here isn’t only about someone discovering a sensitive password, what if they simply got ahold of my registered servers list (exported or original) with stored passwords. Could they just move or import them to another box and gain access to any server I had registered and saved the password to?
In practice, no, in fact in my own attempts to import a registered server list when passwords were included caused SSMS to generate an error on those servers that had associated password stored with them. I couldn’t use the export at all until those passwords were removed. See link for similar experiences of others.
We see then that importing a file generated by an SSMS Registered Server export doesn’t work when passwords are included. What about simply moving the original xml file into the shell? It claims the encryption is based on the local user so could I move it to a box where I was still the local windows user and get it to work?
In practice, that works even less well. When I attempted this – SSMS just started crashing. Wouldn’t work at all. I had to delete the shell folder entirely so SSMS could generate a new blank registered server list.
Frankly I’m glad neither of these worked. I don’t like the idea of my stored passwords being movable to other boxes and/or users. I’m glad I can share my registered servers but do not mind at all having to re-enter associated passwords. Just like MSDN says.
I am still curious as to the manner of encryption. My hunch is that it’s the same or similar to what is used by SSIS for securing sensitive details in packages under the SaveAllWithUserKey option? The error message above is similar to the message you can get with SSIS. That is just a guess however. If so, per MSDN this is inline with the DPAPI standards. However stored – it’s a safe guess that it can’t be a one-way hash. If you think about it – SSMS has to be able to obtain the original password from this encrypted string to pass on to the registered server for authentication.
Question? Why does SSMS allow you to include the encrypted password in the export file if importing it on another box doesn’t work? The answer is that this is for one purpose only. You can export a copy of your registered servers as a backup for your own box. In other words you could export, then do all kinds of mucking around with the lists, decide you liked it the old way, and import the old settings again – passwords intact and everything.
Outside of this lone example, just say no when asked to save passwords. The encryption used here may be more secure than it used to be, but if you run in opsec crowds you need to start being paranoid about things like passwords and discoverable remnants of them. Local box salt or no. Best practice – just stick with windows authentication.