locked
"The Windows Security layer always falls back to NTLM when connections are made locally" RRS feed

  • Question

  • Using Kerberos with SQL Server tells (in Comments):

    • "Il-Sung Lee 27 Nov 2006 4:12 PM
      The Windows Security layer always falls back to NTLM when connections are made locally.  This appears to be the design of SSPI when NEGOTIATE is used so what you are seeing is expected"

    And how to avoid "NEGOTIATE"?

    I am curious about 2 aspects:
    1)
    Why is "NEGOTIATE" used for local connections? What os the point in it for internal connections?

    2)
    Does it mean that I cannot force/trick into using  Kerberos developing against local SQL Sever?

    • Edited by vgv8 Thursday, September 9, 2010 7:47 AM
    Thursday, September 9, 2010 12:24 AM

All replies

  • As the connection is being performed in-memory on the server and it not going out onto the network why do you want to "force/trick into using Kerberos"? What percieved security risk are you trying to mitigate against?
    Thursday, September 9, 2010 4:17 AM
  • Kerberos is a network authentication protocol provides a highly secure method to authenticate client and server entities (security principals) on a network.

    For local connection Kerberos is not at all used.


    Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!
    Thursday, September 9, 2010 4:59 AM
  • As the connection is being performed in-memory on the server and it not going out onto the network why do you want to "force/trick into using Kerberos"? What percieved security risk are you trying to mitigate against?

    And why is SQL Server being used locally (in the same machine) in 95% of cases? Development, testing.
    Thursday, September 9, 2010 6:32 AM
  • Kerberos is a network authentication protocol provides a highly secure method to authenticate client and server entities (security principals) on a network

    And NTLM?
    NTLM is also network authentication , integrity and confidentiality suite of protocols.
    I had 3 questions and one of them was why there is a need in authentication and confidentiality accesing SQL Server on the same machine.

    BTW, [2] tells that it is possible, even more, it explains that Kerberos is used while accessing SQL Server locally and when AD is not available authentication does not fall back to NTLM:

    • "The reason that we didn’t fix this subtle issue is because the limitation is rooted in a behavior of an integrated authentication module (SPNEGO) in XP and windows 2000, i.e. whether to fallback to NTLM if KDC is not available when the target SPN points to local machine. KDC, normally, is part of your domain controller. For this specific case, SPNEGO chooses not to fallback, hence connection fail.  This issue is not a security issue though. Reader might ponder why avoiding using TCP/IP provider can solve the problem while explaining it is because certain behavior of SPNEGO in Windows. Not going too deep, the simple answer is that only TCP/IP provider, with an exception of loop-back connection, uses SPNEGO while other providers use NTLM. Be aware that only TCP/IP provider can provides the benefits of Kerberos authentication as discussed in http://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspx"

    So, the question is really who is more right.
    Is it possible or impossible otherwise when using TCP/IP for connecting locally?

    [2]
    MSDN Blogs > SQL Protocols >
    “Cannot generate SSPI context” error message, when connect to local SQL Server outside domain
    http://blogs.msdn.com/b/sql_protocols/archive/2005/10/19/482782.aspx?PageIndex=1#comments


     

    Thursday, September 9, 2010 6:47 AM
  • For authentication on the same server use Shared Memory provider.

    If you connect locally with TCP/IP by default it will use NTLM. You can test this yourself:

    SELECT
        s.session_id,
        s.login_name,
        s.[host_name],
        s.[program_name],
        c.auth_scheme
    FROM   sys.dm_exec_connections c
    INNER JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id
     

    Thursday, September 9, 2010 7:45 AM
  • If you connect locally with TCP/IP by default it will use NTLM. You can test this yourself:

    I wrote in a reply to my parallel question "how to choose between Shared Memory, Named Pipes, TCP/IP, VIA?"   that I cannot connect locally through TCP/IP at all.
    I tried it in order to understand the situation decribed in [1].

    [1] desribes how to overcome it (for ex., use 127.0.0.1 instead of localhost or to disable network connection) though in my workgroup (non-domained) Windows XP Pro SP3 with Developer Ed of SQL Server 2008 R2 those tricks do not help

    [1]
    MSDN Blogs > SQL Protocols >
    “Cannot generate SSPI context” error message, when connect to local SQL Server outside domain
    http://blogs.msdn.com/b/sql_protocols/archive/2005/10/19/482782.aspx?PageIndex=1#comments

     

    Thursday, September 9, 2010 8:03 AM
  • If you use TCP/IP with a named instance or a non-default port you'll also need to start the SQL Server Browser service. Basically, SQL and App on the same machine, use Shared Memory.
    Thursday, September 9, 2010 8:16 AM