Tip: Connecting To A Database Using Windows Authentication With Different Credentials Using SQL Server Management Studio

It is a good security practice to use Windows Authentication to connect to SQL Server because you don’t need to write the password on some configuration file or registry entry.

This practice also brings governance benefits. Managing users becomes part of domain administration and not part of each SQL Server instance’s administration.

But this becomes an hassle to users (in this context, the user is someone that needs to perform administration task of some kind – a SQL Server Management Studio user) who need to connect to databases using different credentials.

One workaround is using the runas command:

runas /user:DOMAIN\USERNAME "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

But if you are working on an environment were there are several domains and your machine does not belong to the domain of the ser account you want to use, you’ll get the folloing error:

RUNAS ERROR: Unable to run - C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe
1787: The security database on the server does not have a computer account for this workstation trust relationship.

But not everything is lost. Because you want to make a remote connection, you can use the /netonly switch, and it works just fine:

runas /netonly /user:DOMAIN\USERNAME "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

SQL Server Management Studio still shows all the databases in the server (unlike what happens if you connect from a machine logged in as the user you want to access to the SQL Server instance), but ApexSQL Edit will only show the databases that account has access to.

15 Responses to Tip: Connecting To A Database Using Windows Authentication With Different Credentials Using SQL Server Management Studio

  • Tom says:

    This doesn’t work for me. When trying to use windows auth after using runas with the /netonly flag to use different domain credentials, I’m still only given windows auth for the domain that my machine is in 🙁 So how can I get windows auth for another domain so I can log in to SQL servers? I used to be able to use runas on this particular domain before moving to vista, and my colleagies still can, so I don’t understand why I’m being restricted.

  • paulo says:

    Hi Tom,

    It looks like it doesn’t work for you, but it probably does.

    The problem is that, for the local machine, the user is still the user you are logged on. So, it’s the one displayed.

    But when you connect to the other machine, it will be the user you used to runas.

    I’m running on Vista Enterprise x64 and it works for me.

  • Vivek says:

    Thanks this worked for me. Infact, this command be used to run any other application as an impersonated user.

  • paulo says:

    Yes Vivek.

    The only caveat being that, like Tom noticed, the user that the local application sees is the user logged on.

    I tried it with an aplication that connects to a database but also uses the logged on username to other stuff and it did not work well. Although it was able to connect to the database, the usernames didn’t match.

    But for something like SQL Management Studio, it works fine.

  • Oleg M says:

    Thanks Paul for this great article – it really helped me. I didn’t know about the /netonly switch and the only way to run sqlwb / ssms under domain credentials was joining to a domain first (it means accept their crappy policies and so on). But not anymore thanks to your awesome post!

  • paulo says:

    Thanks, Oleg. Always glad to help.

  • mani thakur says:

    please simplify it!!!

  • paulo says:

    Simplify what? Who?

  • Joe says:

    I got this to work but in the dos command prompt i am not able to type in the password at the command line; however, I can copy and paste it in. I am assuming that this is either a bug or some security related setting for typing in passwords. Copying and pasting is a bit of a pain. Anyway to type in the password?

  • paulo says:

    That’s how runas works (http://technet.microsoft.com/library/bb490994.aspx).

    I don’t know of an application where you can configure this. But you could build one. 🙂

  • Shadowz says:

    can any help me please?

    im making a flyff server offical files

    but i start ssms but it dont start

    C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE

    im using 2008

  • paulo says:

    Shadowz,

    Any error message? Any entry on the Event Log?

  • Grishank says:

    Thanks Paulo worked a treat for me on Windows 7 64bit enterprise! Nice work.

  • paulo says:

    Thanks Grishank.

    You might want to consider also using /noprofile with runas or using ShellRunas (http://technet.microsoft.com/en-us/sysinternals/cc300361) from Sysinternals (http://technet.microsoft.com/en-us/sysinternals/default).

  • RG says:

    Thank you, Paulo Morgado!! This was very, very helpful!!! Much appreciated.