[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:
- 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.
- 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.