by Bill Wunder

Each time I see the "Your Password will expire in 14 days..." message when I log on to my laptop the hair on the back of my neck raises just a bit. Over time I've made enough adjustments that things aren't as bumpy on the day I finally hit the "Yes" and change my password.

It used to be that I could expect one or all of three or four kinds of problems that would slow me down within minutes of seeing the "Your password has been changed" message box.

The most painful would be the case where I had a Terminal Services session open to a SQL Server server and my account would get locked out as the Terminal Services Session would try to negotiate on the domain using my old password. The fix for this one would be to humbly ask the kind intelligent and good looking network folks to hunt down my Terminal Services session in Terminal Services Manager and Reset the offending session(s) then re-enable my logon account or - on those rare occasions when I knew why I had left the session open and I didn't want to loose that work - I might beg them to just change my account back to using the old password.

The most obscure problem would be that I had a zombie Citrix session created at some time since the last password change or last reboot of a server in the Citrix farm - which ever was more recent - that would cause much the same problem. In this case I didn't seem to have much luck with asking them to track down offending connection and would always need to roll back to the old password to keep going. I learned to solve this problem by avoiding Citrix. Certainly not what the network guys wanted, but an imperative for me since I do so much work remotely. No doubt some day we'll get some of our wrinkles ironed out with Citrix and they will force me to get back to it...

The most insipid problem would be that I had forgotten to update the passwords in the Developer Edition SQL Server service and SQL Agent service running on my laptop that were configured using my domain account. As with a Terminal Server Session or  Citrix session, the result would be a domain account lockout in short order. Over time I learned I could solve this problem by using the domain accounts that I run my other SQL Servers under for the service accounts on my laptop. The password policy is not enforced for service accounts in my shop so I don't have to mess with password changes for these accounts

Now each and every one of the above workarounds ought to raise the rankles on any serious corporate security officer or administrator. I guess to some extent there is the justification that a DBA has a unique role in the organization and as a result there are some security issues that while enforceable for the general user community are valid to relax a bit for the DBA. In reality, If I simply did a better job of  following up on Terminal Services and Citrix sessions at the time they are disconnected and paid attention to the SQL Server service accounts on my desktop at password change time all these problems are avoidable. If push came to shove I'd have to conceded to the security people that these are shortcomings in my procedures rather than deficiencies in the security model or the Windows Operating systems.

The most annoying problem, though, is legitimately beyond my control. Once I have change my password I loose all the customizations for my Enterprise Manager and Query Analyzer that are stored in the HKEY_CURRENT_USER registry hive. AARGH! In particular, I loose the registered servers in Enterprise Manager and the Options and Shortcuts in Query Analyzer. (See my sswug.org article "What's Hot in Your Query Analyzer?" for more on Query Analyzer shortcuts.) Seems that with a new password I get a new token and somehow the HKEY_CURRENT_USER hive is associated to that token and not with my domain user account per se. Now that is a Windows bug to me, but as with so many other things than I have no control over, I tend to take the path of finding a way around the problem rather than helplessly waiting for someone to come to my rescue. (OK, OK, I do a little moaning and groaning, but in the end my aim is making my life better and eventually I regain that focus most of the time.)

To solve this problem I have exported the registry keys that contain the custom configuration values I want to persist after the password change. Then all I need to do is "Import Registry File..." in regedt32 on my WINXP laptop to replace the now empty keys just after a password change and I'm all set. Much faster, easier and more accurate than re-registering 40 SQL Servers in Enterprise Manager or trying to remember what I had in my Shortcuts and all my options tweaks in Query Analyzer. I don't have to do this every time the password changes, though the more the actual settings drift from the data I have saved in the export files, the less productive is this procedure. Even so, in my experience having six month old export data to restore is better than having to manually re-create my SQL client space.

Exporting registry keys is a snap. From the "Start/Run..." command prompt just type regedit to bring up the registry editor and navigate to the key:

HKEY_CURRENT_USER/Software/Microsoft/Microsoft SQL Server/80/Tools/Client

From that level the three keys you want to export are:

SQL Query Analyzer/Customize/Shortcuts (or export one level up at "Customize" if you also have configured external tools)

SQL Query Analyzer/Options

SQLEW/Registered Servers X/SQL Server Group (Don't you wonder how they decided on the acronym SQLEW for Enterprise Manager?)

To export a key, click once on the key to give it the focus and the select the "Export Registry File..." Registry menu option as shown in the screenshot.

Importing registry keys is every bit as easy. Again simply place the focus on the key you want to replace only this time select  the "Import Registry File..." Registry menu option.

As an added bonus, you can use the exported registry keys to transfer preferred client tool configurations to other machines. The only really confusing part is that in Windows XP and Windows 2003 you can run regedt32 or regedit and get the same utility while in Windows 2000 regedt32 will give you the older multi-window based editor that won't let you import the save keys. When importing - or exporting for that matter - from a  Windows 2000 machine be sure to specify the regedit utility at the command prompt.

And if that's not enough - in the Ginsu knife tradition - as a second added bonus if you make use of alias in your Client Configuration Utility you can use this same process on the the Registry key that stored alias:

HKEY_LOCAL_MACHINE/Software/Microsoft/MSSQLServer/Client/ConnectTo

to preserve and/or move all your alias names. Note that this one is a server wide setting so use caution that you don't over write other settings during a restore. Hint: When you select the Key if the right pane is blank there is nothing currently stored at that key.

I hope it's clear that back up and restore of SQL Client tool custom configuration registry keys is a fast, easy, and effective way to keep from having to manually apply those customizations  every time you change your password or begin using the client tools on a new machine.

I should point out that all the keys we're talking about here don't appear to be part of SQL Server 2005. The good news is that the customizations as well as the ability to import and export may be integrated into the tools rather than stored in Windows Registry hives. That could even mean that the problems with disappearing configuration when you change your password won't follow you into the SQL Server 2005 world. That would be terrific!

Bill