locked
Authentication on local SQL Server 2008 R2 Express server fails after Lan Manager authentication level changed to "Send NTLMv2 response only\refuse LM & NTLM" RRS feed

  • Question

  • I'm upgrading my organisation's Active Directory environment and I've created a replica of our environment in a test lab.
     
    One medium-priority application uses a SQL server express installation on the same server that the application itself sits on.
     
    The application itself recently broke after I changed the following setting in group policy:
    "Send LM & NTLM - use NTLMv2 session security if negotiated"
     
    to
     
    "Send NTLMv2 response only\refuse LM & NTLM"

    The main intent was to determine which applications will break if any - I was very surprised when troubleshooting this particular application to find that the issue was actually with SQL Server express itself.
     
    The errors I get are as follows (note that there are hundreds of them, all the same two):
     
    Log Name:      Application
     Source:        MSSQL$SQLEXPRESS
     Date:          1/19/2015 2:53:28 PM
     Event ID:      18452
     Task Category: Logon
     Level:         Information
     Keywords:      Classic,Audit Failure
     User:          N/A
     Computer:      APP1.test.dev
     Description:
     Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: 127.0.0.1]
     Event Xml:
     <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
       <System>
         <Provider Name="MSSQL$SQLEXPRESS" />
         <EventID Qualifiers="49152">18452</EventID>
         <Level>0</Level>
         <Task>4</Task>
         <Keywords>0x90000000000000</Keywords>
         <TimeCreated SystemTime="2015-01-19T22:53:28.000000000Z" />
         <EventRecordID>37088</EventRecordID>
         <Channel>Application</Channel>
         <Computer>APP1.test.dev</Computer>
         <Security />
       </System>
       <EventData>
         <Data> [CLIENT: 127.0.0.1]</Data>
         <Binary>144800000E00000017000000570053004C004400430054004D00540052004D0053005C00530051004C0045005800500052004500530053000000070000006D00610073007400650072000000</Binary>
       </EventData>
     </Event>
     
    Log Name:      Application
     Source:        MSSQL$SQLEXPRESS
     Date:          1/19/2015 2:53:29 PM
     Event ID:      17806
     Task Category: Logon
     Level:         Error
     Keywords:      Classic
     User:          N/A
     Computer:      APP1.test.dev
     Description:
     SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure.  [CLIENT: 127.0.0.1].
    Event Xml:
     <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
       <System>
         <Provider Name="MSSQL$SQLEXPRESS" />
         <EventID Qualifiers="49152">17806</EventID>
         <Level>2</Level>
         <Task>4</Task>
         <Keywords>0x80000000000000</Keywords>
         <TimeCreated SystemTime="2015-01-19T22:53:29.000000000Z" />
         <EventRecordID>37089</EventRecordID>
         <Channel>Application</Channel>
         <Computer>APP1.test.dev</Computer>
         <Security />
       </System>
       <EventData>
         <Data>8009030c</Data>
         <Data>14</Data>
         <Data>AcceptSecurityContext failed. The Windows error code indicates the cause of failure.</Data>
         <Data> [CLIENT: 127.0.0.1]</Data>
         <Binary>8E4500001400000017000000570053004C004400430054004D00540052004D0053005C00530051004C004500580050005200450053005300000000000000</Binary>
       </EventData>
     </Event>
     
    All of the documentation that I have followed suggests that the errors are caused by incorrect SPN configuration- I figured that they were never correct and it has always failed over to NTLM in the test environment (I can't look at production - we couldn't replicate the setup due to special hardware and also RAM considerations), but only NTLMv2 has issues.
     
    So I spent some time troubleshooting this.  We have a 2003 forest/domain functional level, so our service accounts can't automatically register the SPN.  I delegated the write/read service principle name ACEs in Active Directory.  SQL Server confirms that it is able to register the SPN.
     
    So next I researched more into what is needed for Kerberos to work, and it seems that Kerberos is not used when authenticating with a resource on the same computer:

    http://msdn.microsoft.com/en-us/library/ms191153.aspx

    In any scenario that the correct username is supplied, "Local connections use NTLM, remote connections use Kerberos".  So the above errors are not Kerberos (since it is a local connection it will use NTLM).  It makes sense I guess - since it worked in the past when LM/NTLM were allowed, I don't see how changing the Lan Manager settings would affect Kerberos.
     
    So I guess my question is:
     
    What can I do to fix this? It looks like the SQL server is misconfigured for NTLMv2 (I really doubt it's a problem with the protocol itself...).  I have reset the SQL service or the server a number of times.  Also - all of my other SQL applications in the environment work.  This specific case where the application is authenticating to a local SQL installation is where I get the failure - works with LAN Manager authentication set to "Send LM & NTLM - use NTLMv2 session security if negotiated", but not "Send NTLMv2 response only\refuse LM & NTLM".
     
    Note also - this behaviour is identical whether I set the Lan Manager authentication level at the domain or domain controller level in Active Directory - I did initially figure I had set up some kind of mismatch where neither would agree on the authentication protocol to use but this isn't the case.


    • Edited by Gareth.T Tuesday, January 20, 2015 12:54 AM typo
    Tuesday, January 20, 2015 12:53 AM

Answers

All replies

  • Maybe your application doesn't support "Send NTLMv2 response only. Refuse LM & NTLM".
    https://support.software.dell.com/zh-cn/foglight/kb/133971
    • Marked as answer by Gareth.T Wednesday, January 28, 2015 9:10 PM
    Wednesday, January 21, 2015 2:17 AM
  • Interesting - I figured because the errors were in SQL the problem was with SQL.  Maybe that *is* the problem.  I'll contact the vendor and let you know, thanks.
    Wednesday, January 21, 2015 3:44 AM
  • The vendor has been able to replicate the issue which is great news.  In the meantime, I've changed it from Windows Authentication to SQL authentication so that I can enforce NTLMv2 across the wider test environment.
    Wednesday, January 28, 2015 9:12 PM