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