locked
Windows Auth using SQL 2012 across a different domain RRS feed

  • Question

  • HI 

    When I setup everything for ease I fell to the easy way to connect remotely  using SQL Logon.  However with things growing I really want to be using window auth which I know is far more secure.

    So I have an account as in the Domain Admin group which is a a local admin to the development server which has SA.

    domain\RobertD

    Me I have full admin to my laptop but on a different domain and put the following in credential manager

    ipaddress:1433

    Domain\Robertd

    Password:BlahBlah

    unfortunately when I try to connect through SQL Server Management studio I get the following 

    TITLE: Connect to Server
    ------------------------------

    Cannot connect to IPAddress
    ------------------------------
    ADDITIONAL INFORMATION:

    Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (Microsoft SQL Server, Error: 18452)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=18452&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    Ideally I would like to use management studio win auth to connect to various domains so long as they are trusted without having to resort to using SQL logons.

    Many Thanks

    Robert

    Monday, December 23, 2013 10:59 AM

Answers

  • I can't see that:

    1) that it can work out.
    2) and if it did, that it would be safer than SQL Authentication.

    I am not very familiar with Credential Manager, but I found
    http://windows.microsoft.com/en-us/windows7/what-is-credential-manager
    which says:  By storing your credentials, Windows can automatically log you on to websites or other computers.

    So by storing your credentials in Credential Manager, you should be able to log in to Remote Desktop automatically without having to specify username and password. And indeed, this appears to work; I just tested it.

    But when you connect to SQL Server with Windows authentication, you are not logging in anywhere. The whole point with Windows authentication is that you have already logged into Windows, and then Windows vouches for you.

    Let's for a moment assume that you could log into to the remote domain at the same time as you logged into SQL Server. Would that be safer than SQL authentication? Username and password would cross the wire in both cases...

    Here are some options you can consider:

    1) Join your laptop to the domain.
    2) Set up trust between domain the SQL Server is in and the domain of your laptop.
    3) Set up a VPN connection.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Sofiya Li Tuesday, December 24, 2013 7:08 AM
    • Marked as answer by Sofiya Li Tuesday, December 31, 2013 8:46 AM
    Monday, December 23, 2013 10:43 PM

All replies

  • I'm not entirely sure of your setup, but I suspect you might be trying to rely upon your membership in an administrator's group. But Windows isn't passing admin credentials.

    Can you go to your SQL Server on Domain A, and create a login specifically for your Domain B Windows user account? That's what I would expect you to be able to do. If you can't, perhaps the trust relationship is not supporting this. Or you might get an interesting error message. Note that with a SQL Server Windows Authentication login for your Domain B Windows user account, Windows won't need to pass any administrator group memberships.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Monday, December 23, 2013 4:38 PM
  • I can't see that:

    1) that it can work out.
    2) and if it did, that it would be safer than SQL Authentication.

    I am not very familiar with Credential Manager, but I found
    http://windows.microsoft.com/en-us/windows7/what-is-credential-manager
    which says:  By storing your credentials, Windows can automatically log you on to websites or other computers.

    So by storing your credentials in Credential Manager, you should be able to log in to Remote Desktop automatically without having to specify username and password. And indeed, this appears to work; I just tested it.

    But when you connect to SQL Server with Windows authentication, you are not logging in anywhere. The whole point with Windows authentication is that you have already logged into Windows, and then Windows vouches for you.

    Let's for a moment assume that you could log into to the remote domain at the same time as you logged into SQL Server. Would that be safer than SQL authentication? Username and password would cross the wire in both cases...

    Here are some options you can consider:

    1) Join your laptop to the domain.
    2) Set up trust between domain the SQL Server is in and the domain of your laptop.
    3) Set up a VPN connection.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Sofiya Li Tuesday, December 24, 2013 7:08 AM
    • Marked as answer by Sofiya Li Tuesday, December 31, 2013 8:46 AM
    Monday, December 23, 2013 10:43 PM