Can't connect to SQL Server in different domain using SSMS and Windows 7 Credential Manager

Question

I need to connect to a SQL Server 2008 instance in another Windows domain to manage it. We only use Windows Authentication.

In Windows XP, I could use the "Manage Network Passwords" feature to store on my local machine my username and password for the remote machine. Doing so would cause SQL Server Management Studio to use those credentials, instead of my local credentials, when connecting to that server. This worked great, and prevented my having to enter a username or password every time I connected with SSMS.

But, Holy Cow, after upgrading to Windows 7, I can no longer do this. Adding my remote domain credentials in Credential Manager fails to produce the desired behavior. SMSS 2008 R2 ignores the stored credentials, and instead, always sends my local credentials, causing the login to fail every time. There's not even a way, within SMSS, to enter alternate credentials, so the upshot is that I simply cannot access the remote server!

The login fails with the following message:

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (Microsoft SQL Server, Error: 18452)

How can I get SMSS on Windows 7 to work the same way it did on Windows XP?

Solution

It looks like there is a bug in the Windows 7 Credential Manager that causes this behavior. After lots of Internet research, I found some ugly workarounds, but also found a great one:

In Credential Manager, add an entry for the remote server, but instead of adding just the server name, like database.contoso.com, add the server name with the SQL Server port, like so:

database.contoso.com:1433

This results in the desired behavior, with no pain!

Source: http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/c05a90e4-cb16-46f6-9072-37083c65696d/

Also remember, of course, that you need to prefix the remote username in Credential Manager with the remote domain name, like so:

database\administrator

Comments

[Greg Bray]

Note that if you are trying to connect to a named instance the port will NOT be 1433. You can find the current dynamic port or change it to a fixed port using the instructions at msdn.microsoft.com/en-us/library/ms345327(v=sql.100).aspx

[Chris]

To expand on Greg's comment, you will be making a credential manager entry that is like database.contoso.com:12345 (or whatever weird port your named instance is on). However, when you connect with SSMS you will use database.contoso.com,12345 (note the comma instead of a colon).