Monday, June 18, 2007

Making SQL Server Management Server work on Windows Vista

Under Windows XP, I could define a "Stored User Name and Password" that would use a domain wildcard. That is, I could say "DOMAINX\*" and then the provided user/password would work for any computer that was a member of DOMAINX.

Note: The "Stored User Name and Password" interface is available from the User Accounts control panel, select a user (under XP; the current user is auto selected under Vista), and then click "Manage my Network Passwords" on the left.

This feature was particularly useful to me, as a consultant with a laptop that is not part of my client's domain. I log onto the laptop as a local user, yet I had the ability to seamlessly access any domain resource without being prompted for credentials. This mechanism also allowed me to override individual servers, just in case I needed to use a different user/password for specific situations.

It seems that Vista, either by design or due to a bug, does not allow this concept of domain wildcarding - at least in respect to NetBIOS or LM or any related technology. This particularly crippled me in that I could not connect to any of the SQL Servers at this location using SQL Management Studio.

Using a network packet sniffer (Ethereal), I could see that the SQL Server was challenging my client for domain credentials, but my client would reply with local credentials instead. The end result:

Login failed for user '(null)'. Not associated with a trusted SQL Server connection (18452).

I sent a few emails off to a few Microsoft folks that I knew, and then began to play around while waiting for any sort of response.

The first signs of success started when I mapped a network drive to any fileshare on the domain, just to kick-off a domain-specific connection. Then, I created one password rule per SQL Server that I wanted to connect to, and finally forced the SSMS connection to use Named Pipes. Cumbersome, but it seemed to work (albeit, a little flaky on boot-up, because the mapped drive would not automatically reconnect for some reason, requiring me to remap it before things started to work).

I then stepped back, and remembered the "RunAs" command. By creating a new shortcut to SQL Server Management Studio using my network credentials, I am able to consistantly force SSMS to authenticate with the SQL Servers using my domain user every time, including over TCP/IP (instead of Named Pipes).

The shortcut runs the following (all one command, broken up for readability):

C:\Windows\System32\runas.exe /netonly /user:domainx\myusername
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe"

This is the workaround that I'll be using until MS fixes the "Stored User Names and Passwords" feature (or explains why the Vista behavior is different than the XP behavior as far as domain wildcards go).