Windows Authentication - Different Domain
Question
Monday, January 29, 2007 8:02 PM
Hi:
I am trying to figure out if there is a way to connect via SQL Server Management Studio to a server sitting on a separate domain. So here is the situation. there is Server B which sits in a domain called DomainB. If I am in DomainA, I could typically remote into that server utilizing an IP address in DomainB, and even transfer files to that server. But how do I connect via SQL Server Management Studio(basically login as a different domain user) to Server B in DomainB from DomainA. Basically when I choose Windows Authentication from the dropdown list in SQL Server Management Studio, it grays out the username field, which is where I could type something like this domainB\usernameondomainB, similar to how you would connect to a share on that server. Please let me know if there is a possibly solution to this. Also, our customer doesnot allow SQL Server Auth/Logins, so that is not an option. Thanks.
All replies (22)
Monday, January 29, 2007 9:55 PM ✅Answered | 1 vote
The reason why the user name/password fields are unavailable in SQL Server Management Studio is because it will use the current Windows token (i.e. the interactive user credentials) to establish the connection.
If you want to use different credentials you will need to impersonate the different principal in Windows (i.e. RunAs) before running Management Studio.
I also recommend posting tool related questions in the tools forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=84&SiteID=1).
I hope this information will help.
-Raul Garcia
SDE/T
SQL Server Engine
Thursday, February 8, 2007 6:20 AM ✅Answered | 3 votes
This is the same thing that you do when installing software through a Remote Desktop connection. You utilize the Run As command to change your security context. If you can not utilize this command to launch Management Studio under the security credentials of a login in your secondary domain, that means that you do not have a trust relationship setup. If there is no trust relationship, the ONLY way that you can connect to SQL Servers from one domain to the other is by using either SQL Server Standard Logins or SQL Server Logins mapped to a certificate. If neither of those exist or are not allowed and you also can not create a trust relationship, then it will not be possible for you to connect to a SQL Server in another domain, because Windows security will prevent it.
Thursday, February 8, 2007 3:23 AM
| Raul Garcia - MS wrote: | |
|
Raul.
I have exactly the same problem, but the solution that you are giving doesm't solve the problem.
I can't RUN Managment Studio with RUN AS a member of a domainB if I'm in a machine that is member of the domainA.
Is there any way I can modify Managment Studio to ENABLE the login and pass to introduce the domain using the WIN AUTHENTICATION ?
Thursday, February 8, 2007 3:24 AM
Did you find any way to solve the problem... I'm having the same problem.
Wednesday, December 3, 2008 7:53 PM | 18 votes
All is not lost!!
Michael is mostly correct regarding some alternative options, but you can do what you are trying to accomplish.
use the "runas" command from the command prompt and be sure the include the /netonly switch and specify the user from your DomainB account. If you do not include the /netonly switch you will probably get a "no logon servers" type of message. When you connect to a sql server with Windows Authentication it will look like you are using your DomainA account; however, your DomainB account credentials will be passed to the sql server.
Here's and example of the command. You can create a shortcut for ease of use. NOTE: you will always be prompted for your password.
RUNAS /user:myDomainB\user /netonly "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"
Wednesday, December 24, 2008 5:10 PM
I'm hoping that someone can help me with this. I am experiencing a similar issue. When i log into the management studio using windows authentication (user is A_NT\user) I can connect to the remote SQL Server, but it appears to switch my user to the service account (A_PROD\SERVICE) allowing access that I do not want for my account (when I SELECT SYSTEM_USER it returns the service account). When I use the RUNAS with the /netonly flag for my account (A_NT\user) I connect the way I want with the limited access (SELECT SYSTEM_USER returns my account). It appears to have something to do with the /netonly flag, because when I do the command line without the /netonly flag it connects me with the extra permissions as if I am the service account.
Is there anyway to set up SQL Server so that my connection doesn't switch to the service account and instead behaves like the /netonly option of runas? I have a sinking feeling that a lot of users have more access than we would like them to have at the moment.
Wednesday, September 23, 2009 11:27 AM
I tried RUNAS using isqlw and it works
Tuesday, March 23, 2010 12:13 PM
All is not lost!!
Michael is mostly correct regarding some alternative options, but you can do what you are trying to accomplish.
use the "runas" command from the command prompt and be sure the include the /netonly switch and specify the user from your DomainB account. If you do not include the /netonly switch you will probably get a "no logon servers" type of message. When you connect to a sql server with Windows Authentication it will look like you are using your DomainA account; however, your DomainB account credentials will be passed to the sql server.
Here's and example of the command. You can create a shortcut for ease of use. NOTE: you will always be prompted for your password.
RUNAS /user:myDomainB\user /netonly "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"
the /netonly switch did the trick... thank you very much Michael Hotek and LucidObscuirty!
Friday, March 26, 2010 8:07 PM
Artiom's solution works like a champ!! I've been looking for a way to use SSMS from my machine on my domain to connect to a server on a different domain that I connect to via VPN. I had no luck until I saw his suggestion..
"RUNAS /user:myDomainB\user /netonly "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe""
THANKS Artiom!
Sunday, April 4, 2010 12:37 AM
I also wanted to post a thank you for that "RunAs" tip, works like a charm ! :-)
Tuesday, May 17, 2011 10:25 PM
Excelent!! that worked for me thank you...
Monday, June 13, 2011 4:29 PM
It did the trick for me as well.
"Walking on water and developing software from a specification are easy if both are frozen."
Wednesday, August 31, 2011 1:19 AM
LucidObscuirty: This works for me. Thank you so much!
Wednesday, May 9, 2012 8:31 PM
I also use the /savecred switch so i don't have to type in credentials each time...
C:\Windows\System32\runas.exe /user:xxxx /savecred "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe -nosplash"
Edit: Just realized you can't use /savecred with /netonly
Thursday, May 24, 2012 7:00 AM
In the same way How I can create DSN as I am not able to change the Domain in DSN(ODBC) as I need link the SQL server db tables into MS-Access. Please help me.
Thursday, May 24, 2012 7:00 AM
Hi,I able to connect the SQL Server Management Studio with the different domian and username. However, I want to link those table into my local MS-Access Database. So I need to create System or User DSN. First I try to run the DATA SOURCES (ODBC) with different user name. But I am not able to change the different Domain (From Domain A to Domain B).After that How can I link the tables to MS-Access. Please can you help me on that.
In the same way How I can create DSN as I am not able to change the Domain in DSN(ODBC) as I need link the SQL server db tables into MS-Access. Please help me.
Thursday, May 24, 2012 1:35 PM
I am able to fix my issue:
I used the following batch file and I able to create and link the SQL Server tables with MS-Access database from the different user and domain.
@echo off
cd\
c:
RUNAS /user:domain\username /netonly "C:\Windows\System32\odbcad32.exe"
@echo on
cls
Exit
=============
@echo off
cd\
c:
RUNAS /user:domain\username /netonly "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.exe"
@echo on
cls
Exit
Wednesday, March 6, 2013 3:36 PM
Excellent workaround, Thanks
Wednesday, June 19, 2013 12:43 PM
In my case it does not work using the runas command as there is no trust between the domains. The other solutions do not work either. The firewalls between the domains do allow SQL port traffic to pass through.
Due to having no other option to get this working I need to use a bad solution that gets around this but has some security concerns.
In SQL Management Studio click connect, in connection dialog select options then in additional connection parameters tab input the following
;User id=domain\user;password=*
Ofcourse changing domain\user to the domain login you wish to use and input the password for that domain account instead of *. These will be sent in plain text but will override the greyed out dropdown of your windows token domain.
Wednesday, March 26, 2014 2:42 PM
It is much easier than this and you do not need Run As.
If you are connecting from the Trusted Domain (DomainA) to the Trusting Domain (DomainB) (This indicates there is a one-way trust)
Configure the Target database (DomainB) via SQL Server Configuration Manager
Ensure under SQL Server Network Configuration that Named Pipes is "Enabled"
Then run SSMS from a computer on DomainA
Choose Server Name: <db-server-name.DomainB
Choose SQL Server Authentication
Type DomainA\your-login
Change to Windows Authentication
Click <Connect>
Tuesday, June 30, 2015 3:55 PM
This worked for me. I thought it was unsuccessful as it still displayed my local user in the SQL Studio connection dialog. However it connected and I have the permissions granted to my domain user.
Thanks Artiom!
Thursday, September 17, 2015 12:47 PM
All is not lost!!
Michael is mostly correct regarding some alternative options, but you can do what you are trying to accomplish.
use the "runas" command from the command prompt and be sure the include the /netonly switch and specify the user from your DomainB account. If you do not include the /netonly switch you will probably get a "no logon servers" type of message. When you connect to a sql server with Windows Authentication it will look like you are using your DomainA account; however, your DomainB account credentials will be passed to the sql server.
Here's and example of the command. You can create a shortcut for ease of use. NOTE: you will always be prompted for your password.
RUNAS /user:myDomainB\user /netonly "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"
Lucid-Obscurity solution worked flawlessly for me using SQL 2014 Management Studio running on Windows 10 to connect on a SQL Server 2008 R2 database under Windows Server 2012 R2, with diferent domains and credentials.