How To: Connect to SQL Server, VS TFS, etc using Windows Authentication when computer is not on Active Directory Domain (XP and Vista!)

Whew, long title, amazing results!

Problem: You have a laptop or computer and you are working remotely for a company. You VPN in. Your computer is not on their Active Directory (AD) domain. You try to connect to SQL Server using SSMS or Analysis Services using Excel, but it doesn’t work because it is using your user, not a domain user. How do you get around this?

Answer: Well, this is what I have found (tested on XP only) – start->run: \\computername\c$ – then it prompts you to login. Use your AD username and password, so

domain\username and password, and check the box to save password.

Seems that XP will save that in your authentication list somewhere, and then you can use SSMS or Excel to connect to the SQL Server via Windows Authentication!

This trick also works for TFS Build Servers/Team Explorer (tested with VS2005 Team Explorer) ..

Now for the fun part – Vista. The tricks above don’t work on Vista, but you can still get it to work. Here is what you do…create some shortcuts…

C:\Windows\System32\runas.exe /netonly /user:domain\username “C:\Program Files\Microsoft Office\Office12\excel.exe”

C:\Windows\System32\runas.exe /netonly /user:domain\username “C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\devenv.exe”

C:\Windows\System32\runas.exe /netonly /user:domain\username “C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe”

 

Replace “domain\username” with your info. So if your domain is mycompany then it would be mycompany\steve.novoselac for example.

What happens is that then when you run those apps from those shortcuts it will prompt you for your domain password, you put it in, and it runs the app in the context of your domain user. You can then change the icon for each of these pretty easy, just browse to the exe in the second part when clicking the change icon button on the shortcut properties (the shortcuts are actually links to runas.exe which is a generic icon)

In Vista, for instance, if you are testing SQL (SSMS), you might get this error:

Login failed for user ''. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)

The shortcuts above will get you around it in the situation where your computer is not not on the domain or you are not logged in as a domain user..

These tricks above are especially good if you need to connect to SSAS (Analysis Services) since it is only Windows Authentication. And also, the IT department doesn’t really need to have consultant machines on the domain, or VM’s set up, etc, instead they can use these workarounds



  • David

    Thank you, that made life sooo much easier

  • http://garylarock.com Gary

    You’re my hero.

  • Adnan

    That trick worked fine for Vista and Windows 7 as well. I was getting TFS31004 error before and running the application as the domain user fixed the problem. Thanks.

  • Piet

    Brilliant, just what i needed to know!

    The XP-trick using \\\C$ doesn’t work with MSSQL 2008 anymore it seems, but the ‘Vista’ approach works very nicely.
    Thanks a lot!

  • Hans Hilzinger

    Xcellent stuff. I am in exactly this position using windows 7 and the above works just great.

    Much appreciated
    Hans

  • Hans Hilzinger

    Xcellent stuff. I am in exactly this position using windows 7 and the above works just great.

    Much appreciated
    Hans

  • Seb

    Thanks alot for this, i was able to launch MS SQL Server Management Studio just like if I was on the domain…

    When the Mgt Studio loads it still shows my local credentials in the grayed out box, but if I click ok it connects without any problem. Funny thing is that if I load the app with usual runas procedure (right click run as), that doesn't work at all…

    Now I just created an icon with the following command:
    C:WindowsSystem32runas.exe /netonly /user:domainusername “C:Program FilesMicrosoft SQL Server90ToolsBinnVSShellCommon7IDESqlWb.exe”

    I get prompted for a password in the dos window and that's it.

    Thanks again!

  • Seb

    Thanks alot for this, i was able to launch MS SQL Server Management Studio just like if I was on the domain…

    When the Mgt Studio loads it still shows my local credentials in the grayed out box, but if I click ok it connects without any problem. Funny thing is that if I load the app with usual runas procedure (right click run as), that doesn't work at all…

    Now I just created an icon with the following command:
    C:WindowsSystem32runas.exe /netonly /user:domainusername “C:Program FilesMicrosoft SQL Server90ToolsBinnVSShellCommon7IDESqlWb.exe”

    I get prompted for a password in the dos window and that's it.

    Thanks again!

  • Jonathan Despain

    Awesome tip, just what I was looking for. Thanks!

  • Jonathan Despain

    Awesome tip, just what I was looking for. Thanks!

  • http://www.leafdigital.com/ sam

    Awesome. Thanks. The 'runas' version, at least, also works in Windows 2000. (Yes I know nobody uses that any more… but it's the only Windows OS I have set up in my virtualbox, the 'real' OS is a different platform.)

  • http://www.leafdigital.com/ sam

    Awesome. Thanks. The 'runas' version, at least, also works in Windows 2000. (Yes I know nobody uses that any more… but it's the only Windows OS I have set up in my virtualbox, the 'real' OS is a different platform.)

  • Luis

    Works like a charm with Windows 7. Thanks!

  • Vicky Karmakar

    Sir, u rock !!!