How to connect to SQL Server with Windows Authentication when not logged in with that username?

Question

We created a local windows username on the database server and used it on a separate machine in IIS7's app pool in the same workgroup. The website app works to log in to the database server.

Now I am on my machine developing in VS 2010 trying to remotely access the database. The user and workgroup don't match on my machine to the web/database servers so I specify the username and password explicitly. When I try to connect VS 2010 to SQL Server 2008 I get the following error:

Cannot open user default database.  Login failed.
Login failed for user 'myuser'. (Microsoft SQL Server, Error: 4064)

I have the correct username and password. I am not interested in matching their workgroup and username on my local machine.

How does one log in using Windows Authentication when your local username and workgroup are different from the database server's username and workgroup?

It worked to turn on SQL authentication, enable the sa account, and log in from my home machine. It wouldn't log in with 'myuser' remotely, but does work locally.

Answer 1

The "runas" command almost works:

runas /netonly /user:domain\username "devenv.exe"

You will be prompted for the password. You can also use "cmd.exe" as the final parameter to get a Command Prompt as that other user.

As I said, it almost works for me. I've been able to connect before, but not use SSMS successfully. I was dealing with a VPN as well, so if you have a less restricted path to the server perhaps it will work better for you.

Can you remote into your work machine and use it?

Answer 2

I do this all the time with SQL Server Management Studio for SQL Server 2008 and 2005 (and VS 2010). RUNAS works perfectly, but SSMS will still display your local username in the login screen. Just ignore that and you should be able to access the database without trouble. E.g.: if your local account is mylaptop\fma1 and you log into the mycompany domain as mycompany\jsmith, SSMS will still say "mylaptop\fma1" but will log into the database as mycompany\jsmith.

Microsoft has a Sysinternals addon called "Shell Run As" that lets you do this from Explorer:

http://technet.microsoft.com/en-us/sysinternals/cc300361

Just copy the downloaded file into an appropriate directory, run the .exe with /regnetonly as the argument, and you'll be able to shift-right-click and run as a different user.