mandag den 12. august 2013

Connecting to SQL Server from another domain

Have you ever had to connect to an SQL Server using SSMS or perhaps from an ASP.NET application, where the SQL Server is set up to only use Windows Authentication (not Mixed Authentication) from your client PC, web server or whatever, and that machine was not part of the same domain as the SQL Server?

Eg. if I want to connect from my laptop (OHTXPS13, not a member of any domain) to the server "SQL-B-462" (part of the Contoso domain) then it appears I am out of luck.

Notice that the "User name" and "Password" fields are disabled. Even though I have the username "Contoso\Kim_Akers" and password "Pa$$w0rd" there seems to be no way to provide it.

Trying SQLCMD.exe does not improve the situation. There are -U and -P switches to allow you to provide username and password, but those requires the SQL Server to allow mixed mode authentications.


The recommended solution I think is to establish a domain trust relationship between the SQL Server domain and the "client" domain. However, since this must involve a Domain Administrator it might not be a practical solution in all circumstances.

A very simple quick 'n' dirty solution is this:

That is, simple use the command (from powershell or plain cmd.exe):
runas /netonly /user:<domain>\<username> ssms.exe

Enter the password of the domain user and SQL Server Management Studio will start up running under the specified user account. Now simply connect to the desired SQL Server with Windows Authentication.

Ingen kommentarer:

Send en kommentar