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: \computernamec$ – then it prompts you to login. Use your AD username and password, so

domainusername 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:WindowsSystem32runas.exe /netonly /user:domainusername “C:Program FilesMicrosoft OfficeOffice12excel.exe”

C:WindowsSystem32runas.exe /netonly /user:domainusername “C:Program FilesMicrosoft Visual Studio 8Common7IDEdevenv.exe”

C:WindowsSystem32runas.exe /netonly /user:domainusername “C:Program FilesMicrosoft SQL Server90ToolsBinnVSShellCommon7IDESqlWb.exe”

 

Replace “domainusername” with your info. So if your domain is mycompany then it would be mycompanysteve.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

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

  1. 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.

    Like

  2. 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!

    Like

  3. 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!

    Like

  4. 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!

    Like

  5. 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.)

    Like

  6. 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.)

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s