locked
Connecting to SQL Servers with SSMS in Windows 7 x64 RRS feed

  • Question

  • Hello,

    I am the database administrator for my company. Recently I upgraded my workstation to Windows 7 x64 Enterprise. I have SQL Server 2008 Management Studio is installed on my computer. Windows credentials used to connect to other Windows Server 2003 computers are stored in my Control Panel\Credential Manager.

    Whenever I'm trying to connect to other SQL Server Database Engines using SSMS and the TCP/IP protocol, I get the following error:

    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+SQL+Server&EvtSrc=MSSQLServer&EvtID=18452&LinkId=20476

    Two events are generated on the server side, also:

    SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. 
    Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

    Although i'm able to connect SUCCESSFULLY to the same servers' Analysis Services and Reporting Services. I am also be able to connect by using the Named Pipes network protocol.

    I created a Virtual PC in this non-domain network, installed Windows Server 2003 and SQL 2008 on it. Added the necessary credentials in the Stored User Names and Passwords dialog box (control userpasswords2). From here I am able to connect to other servers' Database Engines, Analysis Services and Reporting Services in my network using Management Studio and the TCP/IP network protocol.

    I am not willing to use SQL Authentication, nor Named Pipes. I need to create custom TFS Reports using BIDS and Report Designer.

    This behavior can't be by design so this must be an annoying bug.
    Wednesday, January 27, 2010 12:06 PM

Answers

  • Hi levidos,

    With SQL 2008, manifestation changes, and you may get error message like “The login is from an un-trusted domain” . To resolve this problem, set the DisableLoopbackCheck registry entry to 1.

    To set the DisableLoopbackCheck registry entry to 1, follow these steps on the client computer:
    1. Click Start, click Run, type regedit, and then click OK.
    2. Locate and then click the following registry subkey:
    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa
    3. Right-click Lsa, point to New, and then click DWORD Value.
    4. Type DisableLoopbackCheck, and then press ENTER.
    5. Right-click DisableLoopbackCheck, and then click Modify.
    6. In the Value data box, type 1, and then click OK.
    7. Exit Registry Editor.
    8. Restart the computer.

    This is one the KB articles http://support.microsoft.com/kb/926642  which speak about similar scenario where when you try to access a server locally by using its CNAME alias, connection fails.

    If you have any more questions, please feel free to ask.

    Thanks,
    Jin Chen



    Jin Chen - MSFT
    • Marked as answer by Levente Rög Tuesday, February 9, 2010 2:04 PM
    Friday, January 29, 2010 11:09 AM

All replies

  • Is the SQL Server running in a domain or is it stand alone like your VPC?  I can't tell from your post.  Also is your Windows 7 machine a part of the domain or not?  It sounds to me like you are hitting a kerberos failure, most often caused by failed SPN creation at SQL Server Service Start, but I'd need to know the answer about the domain or not to be sure.  If you check your startup ErrorLog for the SQL Server it would have an error like:

    The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.

    If it is a standalone server that isn't a part of a domain that would be expected.  If it is part of a domain, that is the likely culprit and you would need to troubleshoot the SPN failure or manually set the SPN for the service account and restart the SQL Services for kerberos delegation to work properly.


    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Friday, January 29, 2010 4:27 AM
  • Hi levidos,

    With SQL 2008, manifestation changes, and you may get error message like “The login is from an un-trusted domain” . To resolve this problem, set the DisableLoopbackCheck registry entry to 1.

    To set the DisableLoopbackCheck registry entry to 1, follow these steps on the client computer:
    1. Click Start, click Run, type regedit, and then click OK.
    2. Locate and then click the following registry subkey:
    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa
    3. Right-click Lsa, point to New, and then click DWORD Value.
    4. Type DisableLoopbackCheck, and then press ENTER.
    5. Right-click DisableLoopbackCheck, and then click Modify.
    6. In the Value data box, type 1, and then click OK.
    7. Exit Registry Editor.
    8. Restart the computer.

    This is one the KB articles http://support.microsoft.com/kb/926642  which speak about similar scenario where when you try to access a server locally by using its CNAME alias, connection fails.

    If you have any more questions, please feel free to ask.

    Thanks,
    Jin Chen



    Jin Chen - MSFT
    • Marked as answer by Levente Rög Tuesday, February 9, 2010 2:04 PM
    Friday, January 29, 2010 11:09 AM
  • Excelent Post Jin Chen,

    Thank you, it's working.
    Tuesday, February 9, 2010 2:03 PM