4

If I want to log into a SQL Server instance using another Windows account can I do this by simply selecting "SQL Server Authentication" as my authentication mode and entering the DOMAIN/username and password for the account I want to use?

Are there any special settings that I need to set in order to get this to work?

Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
  • I do not believe that is possible. What you can do though, is run your application or whatever is accessing the database under a different user context. – mservidio Jan 25 '12 at 18:16
  • Only way I ever got this to work was to right-click on SSMS and run as other account and provide those credentials. – billinkc Jan 25 '12 at 18:16
  • I think this may be a duplicate question, see here: http://stackoverflow.com/questions/849149/connect-different-windows-user-in-sql-server-management-studio-2005-or-later – mservidio Jan 25 '12 at 18:18

3 Answers3

8

No. 'SQL Server Authentication' works only with SQL Server logins, it won't work with a NT login. To log in as another credential have the process run as the credential you desire to run as. Use runas, most likely you want to run SSMS like this:

runas /netonly /user:domain\user "c:\program files\...\ssms.exe"

By specifying the /netonly argument your SSMS will run with the desired NT credential only for remote hosts, locally will have your local credentials. This not only preserves your SSMS environment (last used files, options etc) and doesn't require domain\user to have local priviledges, but, more importantly, it works even for a completely unrelated domain. That is if you machine is in domain foo (or not even joined to the domain) and the desired runas is in domain bar, the runas /netonly /user:bar\user ... will run just fine.

The /netonly trick works only if the SQL server host you are connecting to is remote (not on the same machine). If is local, then your runas must be locally using the desired credential, so remove the /netonly argument.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • 1
    I use this all the time. runas is a lifesaver. Also just disregard that the windows authentication box wherever you connect will display your -local- computer/user, but in reality it uses the specified domain and username. – cairnz Jan 25 '12 at 18:35
  • FYI, we use this method and it works great. However, it can cause havoc on Outlook and other domain reliant software. Not sure why, but it does. – Dustin Laine Jan 25 '12 at 22:23
0

If you have Windows 7, you can add credentials for other domains via Control Panel > Administrative Tools > Credential Manager. Map your credentials to each server you want to log into.

Then, log into additional machines using Windows Authentication. I think there is a caveat - it will try to log you in to the server using your current domain/username. IF that is rejected, it will lookup any entries you have for that server in Credential Manager, and if those are valid, you will be logged in.

PerryCollective
  • 25
  • 7
-2

If I understand the question correctly, the solution it just ship + Right click on your SSMS, that will give you "connect as different user" option and that where you want to put your windows authentication. Domain\User and password.

Melle Anzeno
  • 1
  • 1