1

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!

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

BrianFinkel
  • 121
  • 1
  • 5

2 Answers2

2

You can also configure your SSMS shortcut (or a second SSMS shortcut) to simply be run under the domain account of the remote domain.

Change the shortcut to run this command instead (you may need to adjust the path slightly as I've got SQL Server "Denali" CTP3 installed).

C:\Windows\System32\runas.exe /user:Domain\YourAccount /netonly "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe"

When you run the shortcut, it prompts you for your current password (handy if you have to change your password regularly) then launches SSMS as normal just with it running under the remote domain's account.

mrdenny
  • 27,074
  • 4
  • 40
  • 68
  • Fair enough. This seems like a good alternative if you don't want to cache your password, or if your password changes often. I prefer using Credential Manager, however. – BrianFinkel Aug 05 '11 at 13:39
  • For years, I've done this since I am a consultant and often need to connect to a client's domain. However, I am now running Denali CTP3 (as you are), and this doesn't seem to be working anymore. SSMS opens, but it's not running as a the other domain user account. Any ideas? – Thiago Silva Aug 19 '11 at 14:46
  • @Thiago, my CTP3 is working fine when doing this. What error are you getting? – mrdenny Aug 19 '11 at 20:48
1

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

BrianFinkel
  • 121
  • 1
  • 5