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
Steve Novoselac's Blog 
14 replies on “How To: Connect to SQL Server, VS TFS, etc using Windows Authentication when computer is not on Active Directory Domain (XP and Vista!)”
Thank you, that made life sooo much easier
LikeLike
You’re my hero.
LikeLike
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.
LikeLike
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!
LikeLike
Xcellent stuff. I am in exactly this position using windows 7 and the above works just great.Much appreciatedHans
LikeLike
Xcellent stuff. I am in exactly this position using windows 7 and the above works just great.Much appreciatedHans
LikeLike
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!
LikeLike
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!
LikeLike
Awesome tip, just what I was looking for. Thanks!
LikeLike
Awesome tip, just what I was looking for. Thanks!
LikeLike
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.)
LikeLike
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.)
LikeLike
Works like a charm with Windows 7. Thanks!
LikeLike
Sir, u rock !!!
LikeLike