8

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.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
Zachary Scott
  • 20,968
  • 35
  • 123
  • 205
  • We got this to work. The trick is to add the server's name in the local host file so that the name resolves to an IP rather than use the IP address. After that, a local account maps to the foreign server, same user / password but different workgroup/domain names. – Zachary Scott Feb 02 '16 at 06:50

2 Answers2

7

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?

Fammy
  • 513
  • 3
  • 14
  • I installed VS Express in my work machine for a pinch, but I rather prefer my VS2010 with Resharper on my notebook. – Zachary Scott Jun 15 '11 at 02:20
  • Let me know if it works for you and what your network situation is to SQL Server. VPN or open on the internet, etc. Also +1 for Resharper. – Fammy Jun 15 '11 at 02:22
  • 192.168.1.2 on DB server, 172.16.1.2 on local machine through a VPN, however, I can attach my notebook to the local network and it doesn't work either way. – Zachary Scott Jun 15 '11 at 02:25
6

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.

WCRC
  • 405
  • 2
  • 5
  • 1
    Just as a data-point, I got it working by using the built-in "Run as administrator" (I'm sure I don't understand why this would work :-) – thebjorn Jul 08 '13 at 19:49