376

Is there a way in SQL Server Management Studio 2005 (or later) to change the Windows Authentication user (as you could in SQL Server 2000 and older)?

This is the general connection properties dialog(note the greyed out UID/PWD when selecting Windows Auth):

dialog

FYI - One workaround is to use runas but I'm looking for a solution that will allow me to work with multiple Windows accounts across multiple servers (and across multiple domains).

Laurel
  • 5,965
  • 14
  • 31
  • 57
Matt P.
  • 3,761
  • 3
  • 17
  • 3
  • 2
    As far as I know, Run As... is the only way to achieve this. Why can't you use Run As... and just start a different management studio session for each user? – DCNYAM May 11 '09 at 17:46
  • 4
    RunAs only works if you're in the same domain and/or forest. If you're a consultant, you're never in the same domain and/or forest, so this would be a nice feature to have. – Eric May 11 '09 at 18:42
  • I can feel you, Eric. Sometimes I use VMs with clients so that the virtual machine can be part of the client's domain/forest and allow me to work a little more seamlessly, but if you're actively working with multiple domains, this can be tedious. – Ed Altorfer May 11 '09 at 19:01
  • 6
    Multiple Domains is one use case. Another are Windows Services which login under their own service account and a developer wishing to verify SQL connectivity, permissions, etc. Sounding like I'm outta luck . . . at least we have runas. – Matt P. May 13 '09 at 05:30

10 Answers10

461

While there's no way to connect to multiple servers as different users in a single instance of SSMS, what you're looking for is the following RUNAS syntax:

runas /netonly /user:domain\username program.exe

When you use the /netonly switch, you can log in using remote credentials on a domain that you're not currently a member of, even if there's no trust set up. It just tells runas that the credentials will be used for accessing remote resources - the application interacts with the local computer as the currently logged-in user, and interacts with remote computers as the user whose credentials you've given.

You'd still have to run multiple instances of SSMS, but at least you could connect as different windows users in each one.

For example: `runas /netonly /user:domain\username ssms.exe`
John Smith
  • 7,243
  • 6
  • 49
  • 61
SqlRyan
  • 33,116
  • 33
  • 114
  • 199
  • 32
    If you want the same domain but different user drop the /netonly switch. – jimconstable Feb 03 '11 at 00:08
  • 5
    The thing about the netonly switch is absolutely golden. So many scenarios that I needed this one. Thanks. – Jivko Petiov Mar 15 '12 at 12:57
  • 9
    +1 for the most convenient way of doing this at present. It's still beyond lame that MS can't just fold a MSTSC / remote desktop style login dialog into SSMS and configure it per-connection. – Paul Smith Jul 03 '12 at 18:42
  • 9
    Wow! netonly! Fantastic. It's kind of funny though that once it's running, when you try to connect to a remote server with windows auth, it displays your local user, not the netonly user. Makes it look like magic when it connects... – TTT Dec 08 '12 at 06:27
  • This is a solution that works. I used it with SQL Management studio 2008 R2. To get the path to management studio, find it in the start menu and select "open file location". – mortb Apr 05 '13 at 08:55
  • 3
    The answer below from user175017 worked for me, seems much easier, and allows server-specific windows connections in one SSMS session. In brief, on Win 7, run "Credential Manager", and just add Windows Credentials for :1433, where is the SQL Server machine, fully qualified (like MyServer.MyCompany.Com). – David Korn Dec 04 '13 at 00:24
  • Wonderful ! I can confirm this works on SSMS 2012, Win 8.1, when trying to login as a user from within another domain. – Diana Ionita Apr 02 '14 at 13:21
  • That netonly switch *IS* golden. It allows you to authenticate to another domain and run a program under that context without having your local machine trusted in that domain. – Crispy Ninja Mar 16 '16 at 12:16
  • 1
    How-to Run as other windows account user and as ***administrator*** ? – Kiquenet Nov 18 '16 at 12:47
  • It works. Similar to "Active Directory - Password". (AD authentication) – Ivan Chau Sep 19 '17 at 05:18
  • 1
    I used this to logon with SSMS 18.1, and although the connection shows the user that you're logged on with, it actually uses the user specified during the "runas". Thank you!! – RonaldB Jun 27 '19 at 02:00
186

Hold shift and right click on SQL Server Mangement studion icon. You can Run as other windows account user.

zo-
  • 140
  • 9
Suresh
  • 1,877
  • 1
  • 11
  • 2
138

One other way that I discovered is to go to "Start" > "Control Panel" > "Stored Usernames and passwords" (Administrative Tools > Credential Manager in Windows 7) and add the domain account that you would use with the "runas" command. In Windows 7 and up, make sure you add it under "Windows Credentials".

Then, in SQL Management Studio 2005, just select the "Windows Authentication" and input the server you wanna connect to (even though the user that you can see greyed out is still the local user)... and it works!

Don't ask me why ! :)

Edit: Make sure to include ":1433" after the server name in Credential Manager or it may not connect due to not trusting the domain.

phillhutt
  • 183
  • 1
  • 5
  • 12
    Honestly, this answer should be marked up. The runas solution didn't work for me, and created a headache in that smss doesn't like to start in console, which is what happens when you invoke in smss. And even once that stopped happening, it still didn't work. But Credential Manager > Add a windows credential worked. Enter the server name, and your DOMAIN\user username, and the password. Just like he says, it'll still SHOW the local user, but it connects anyway. – Brian Arsuaga Oct 22 '12 at 18:37
  • 17
    You may need to use sqlserver.domain.com:1433 as the network address. See http://stackoverflow.com/questions/6944933 – Greg Bray Sep 13 '13 at 18:44
  • 7
    Indeed this worked for me, seems much easier, and allows server-specific windows connections in one SSMS session. I just did Run "Credential Manager", and added Windows Credentials for :1433, where is the SQL Server machine, fully qualified (like MyServer.MyCompany.Com). – David Korn Dec 04 '13 at 00:28
  • 1
    The `runas` method didn't work for me, but this did. I initially tried without the port information in Credential Manager, but I still couldn't log in. When I added the port information in Credential Manager, it worked like a charm. Thank you so much for this! – René Jun 10 '14 at 11:23
  • 1
    This might be the most elegant solution. Works in Windows 8.1 for me connecting to a SQL 2012 standalone instance that's not even part of a domain. :1433 – Tim Lehner Jul 14 '14 at 17:06
  • This answer should get more votes! Works like a charm! – Joel Jun 25 '15 at 13:47
  • Clearly the best answer. Works in Windows 10 as well. – Dan Dec 29 '15 at 04:53
  • A very elegant solution. The first time did not work me the solution. Then he reminded himself of the default SQL server has not set the port on which to listen. :-) Once the port is working beautifully. Thanks – Piotr Knut Aug 18 '16 at 22:56
  • Best answer! Still shows your logged in username in SMSS, even though it connects with the other user's credentials. Also note that it is host:portnumber in credential manager, while in SMSS it wants host,portnumber if using the nonstandard port (1433). – wojtow Sep 23 '16 at 20:32
  • Note that connecting to different SQL server instances requires different non-standard ports. See here to find the port number: http://stackoverflow.com/questions/12297475/how-to-find-sql-server-running-port – djk Apr 16 '17 at 10:16
  • Probably one of the most useful answers ever on SO. In Windows 10 go to Control Panel and select Credentials Manager. They moved it out of Admin Tools in Windows 7. – Craig Feb 07 '18 at 16:16
22

None of these answers did what I needed: Login to a remote server using a different domain account than I was logged into on my local machine, and it's a client's domain across a vpn. I don't want to be on their domain!

Instead, on the connect to server dialog, select "Windows Authentication", click the Options button, and then on the Additional Connection Parameters tab, enter

user id=domain\user;password=password

SSMS won't remember, but it will connect with that account.

Glorfindel
  • 21,988
  • 13
  • 81
  • 109
Dave
  • 1,179
  • 12
  • 18
  • 1
    This worked for me in SQL Server Management Studio 2017 – natbob1 Feb 12 '19 at 14:28
  • 1
    Normally I use the runas /netonly command, but i just wanted to give this a try and it's amazing! This is an easy to use solution to connect from your already open ssms instance to another server with other win creds! thanks a lot for this tip, never have to switch between ssms windows again ;) – Felix Bayer Nov 18 '20 at 08:24
  • For Microsoft SQL Server SSMS, this is BY FAR the best practical solution. It even allows you to save the complicated password (we hope is difficult to guess) Regrettably, other apps need to manage via almost odious RUNAS. (a feeble attempt at imitating part of Linux's sudo.) – alejandrob Mar 26 '21 at 12:16
  • 3
    This doesn't work if the user which is currently running __ssms.exe__ is a local user. In this case you need to use runas as suggested by @SqlRyan – Bemipefe Mar 02 '22 at 12:26
14

The runas /netonly /user:domain\username program.exe command only worked for me on Windows 10

  • saving it as a batch file
  • running it as an administrator,

when running the command batch as regular user I got the wrong password issue mentioned by some users on previous comments.

zinking
  • 5,561
  • 5
  • 49
  • 81
Ricardo Mercado
  • 149
  • 1
  • 2
  • This worked for me too, no trust relationship between domains. I was able to start SSMS and connect to a SQL server using the other domains account (even though in the UI it says it will use the local domain account. – Neil Jul 27 '17 at 16:18
13

For Windows 10: Go to the Sql Management Studio Icon, or Short Cut in the menu: Right Click > Select Open File Location

enter image description here

Hold Shift and right Click the shortcut, or ssms.exe file that is in the folder. Holding shift will give you an extra option "Run as different user":

enter image description here

This will pop up a login box and you can type the credentials you would like your session to run under.

cmartin
  • 2,819
  • 1
  • 26
  • 31
8

A bit of powershell magic will do the trick:

cmdkey /add:"SERVER:1433" /user:"DOMAIN\USERNAME" /pass:"PASSWORD"

Then just select windows authentication

Stephen Rauch
  • 47,830
  • 31
  • 106
  • 135
maeneak
  • 573
  • 6
  • 10
1

There are many places where someone might want to deploy this kind of scenario, but due to the way integrated authentication works, it is not possible.

As gbn mentioned, integrated authentication uses a special token that corresponds to your Windows identity. There are coding practices called "impersonation" (probably used by the Run As... command) that allow you to effectively perform an activity as another Windows user, but there is not really a way to arbitrarily act as a different user (à la Linux) in Windows applications aside from that.

If you really need to administer multiple servers across several domains, you might consider one of the following:

  1. Set up Domain Trust between your domains so that your account can access computers in the trusting domain
  2. Configure a SQL user (using mixed authentication) across all the servers you need to administer so that you can log in that way; obviously, this might introduce some security issues and create a maintenance nightmare if you have to change all the passwords at some point.

Hopefully this helps!

Ed Altorfer
  • 4,373
  • 1
  • 24
  • 27
0

Did anybody tried "plain" runas without parameters? Those /netonly /savcecred all of them sound ambiguous and to me utter nonsense.

C:\Windows\System32\runas.exe /user:DOMAINX\OtherUser02 "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\ssms.exe"

This works just fine. No matter what, runas WILL ask you for the user password. Just type it and be security audit compliant.

alejandrob
  • 603
  • 8
  • 6
  • For security reasons (eg enterprise environments), the windows user that you need to use to connect to SQL server may not have the right to login locally. This is when you absolutely need the `/netonly` parameter – Stefan Balan Aug 16 '22 at 11:35
-2

The only way to achieve what you want is opening several instances of SSMS by right clicking on shortcut and using the 'Run-as' feature.

SQLChicken
  • 450
  • 4
  • 11