locked
Windows Authentication - Different Domain RRS feed

  • Question

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

    Monday, January 29, 2007 8:02 PM

Answers

  • 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 6:20 AM
  •   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

    Monday, January 29, 2007 9:55 PM

All replies

  •   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

    Monday, January 29, 2007 9:55 PM
  •  Raul Garcia - MS wrote:

      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

     

    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 ?

    • Proposed as answer by scintilationn Friday, June 22, 2012 7:54 PM
    Thursday, February 8, 2007 3:23 AM
  • Did you find any way to solve the problem... I'm having the same problem.

     

     

    Thursday, February 8, 2007 3:24 AM
  • 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 6:20 AM
  • 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 3, 2008 7:53 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, December 24, 2008 5:10 PM
  • I tried RUNAS using isqlw and it works
    Wednesday, September 23, 2009 11:27 AM
  • 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!

    Tuesday, March 23, 2010 12:13 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!

    Friday, March 26, 2010 8:07 PM
  • I also wanted to post a thank you for that "RunAs" tip, works like a charm !  :-)
    Sunday, April 4, 2010 12:37 AM
  • Excelent!! that worked for me thank you...
    Tuesday, May 17, 2011 10:25 PM
  • It did the trick for me as well.


    "Walking on water and developing software from a specification are easy if both are frozen."
    Monday, June 13, 2011 4:29 PM
  • LucidObscuirty: This works for me.  Thank you so much!
    Wednesday, August 31, 2011 1:19 AM
  • 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


    • Edited by PolishPaul Wednesday, May 9, 2012 8:35 PM can't use /netonly and /savecred together
    Wednesday, May 9, 2012 8:31 PM
  • 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 7:00 AM
  • 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

    Thursday, May 24, 2012 1:35 PM
  • Excellent workaround, Thanks
    Wednesday, March 6, 2013 3:36 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.


    • Proposed as answer by Allen Leggett Wednesday, March 26, 2014 2:33 PM
    Wednesday, June 19, 2013 12:43 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> 


    Wednesday, March 26, 2014 2:42 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!

    • Proposed as answer by no_flo Thursday, July 30, 2015 7:45 AM
    • Unproposed as answer by no_flo Thursday, July 30, 2015 7:45 AM
    Tuesday, June 30, 2015 3:55 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.
    Thursday, September 17, 2015 12:47 PM