[Tip] Use RUNAS to set your Windows Auth domain for database connections

Ever run into problems connecting to a database using Windows Authentication when you're not on that domain? I sure have - I was connecting over VPN, wanting to use SQL Server Management Studio, but my VPN account wasn't in the correct domain to authenticate. I ended up just using Remote Desktop whenever I needed to connect to that database for the length of that project... which dragged out more than year. Johnny Coder ran into it, too, but he's got the solution:

Please consider the following scenarios: 

  1. An instance of SQL Server is setup in the Development Environment and it isn’t running in Mixed Mode.  This implies that one needs to be a member of the appropriate domain and have appropriate permissions in order to access the SQL Server.  Not a big deal, right?  Well, I would agree if I were a developer working onsite and the Dev Network was readily available to me. But I happen to work remotely on occasion and there isn’t an entrance point into this particular domain through VPN.  Boy, SQL Authentication sure would come in handy in this case. 
  2. Let’s say your environments (perhaps Dev, QA and Production) are hosted in separate domains.  It would be painful to switch between domains in order to access each SQL Servers, wouldn’t it?

...

an even better solution in the appropriately named RunAs command which allows a user to run specific tools and programs with different permissions than the user’s current logon provides. The following are a few of  my favorite commands which I’ve wrapped up neatly in their own .cmd file for quick execution (you will need to update the domain and user values accordingly):

  • runas /user:domain\user “C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\ssmsee.exe”
  • runas /user:domain\user “C:\WINDOWS\system32\mmc.exe /s \”C:\Program Files\Microsoft SQL Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC\”"
  • runas /user:domain\user isqlw

[via Johnny Coder - Coping with Windows Auth]

That's one of those solutions that's not obvious, but makes you slap your head when you hear it. Of course! I'll be sure to keep RUNAS in mind for more than installing software on my kids' computer.

4 Comments

  • Nice tip Jon (and Johnny). I've had this issue a couple of times over the last few months and resorted to the RDP option as well.

    [slaps head]!

  • how to using windows authentification to access a program ??
    i mean ..if user not login to window's user so he cannot running a program ??
    how can i do that ??

  • Hi I have been trying to login to SQL Server 2008 Management Studio using VPN

    However, when I try to login it says 'There are currently no logon server available' to service the logon request.' It points out to Ssms.exe file under ..\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE folder

    Could you plase point out what can be the issue?

  • Thanks Jon!
    Lucky you. I get an error saying that i can't logon like this on my PC

    "
    1787: The security database on the server does not have a computer account for this workstation trust relationship.
    "

Comments have been disabled for this content.