SQL server generate high NetLogon traffic due to missing / incorrect SPN.

I recently got an opportunity to work with one of the customer where AD team reported that they were observing high NetLogon requests on DC’s originating from SQL servers.

To make the concept simpler, SQL clients (OR front end application server) uses windows authentication either using NTLM or Kerberos through the Windows Security Support Provider Interface (SSPI) on TCP-IP/Named pipe protocol. By default, Kerberos authentication is always attempted first.

NTLM is legacy way of authentication in AD environment, Each NTLM-based authentication is unique—even if it’s a repeated authentication to the same resource by the same identity. NTLM is a more expensive authentication protocol in terms of network traffic, as well as a less secured one.

Kerberos, on the other hand, provides a reusable access-granting service ticket for the resource to that identity, and that reuse requires no interaction with an authenticating server or domain controller (DC).

Kerberos authentication require certain information from the SQL client (application OR front end server) that needs to be match with information saved in AD database for services. This unique information about the combination of service name, service account, server name & service port, is known as SPN (Service principal name). SQL client send authentication request with following information.

  1. Type of service (In this case SQL Server MSSQLSvc).
  2. The Name of the server.
  3. The Port.
  4. The service account running the service.

If above information matches with SPN stored in AD then auth scheme will use Kerberos otherwise it will fail over to NTLM (less desirable auth scheme).


Saved SPN on AD database can be seen with following two option.

  • Open command prompt in any domain joined machine with elevated command prompt and run SetSPN with following parameter.
SETSPN –L  SQLServerHostName

If SQL service running under domain account then it uses login name.

SETSPN –L myDomain\SQLsvc

Expected output:-

Registered ServicePrincipalNames for CN=sqlsvc,CN=Users,DC=myDomain,DC=local:

If SQL service running under Local System/Network Service then SQL server name needs to used.

SETSPN -l myDomain\sql1a

Expected output:-

Registered ServicePrincipalNames for CN=SQL1A,CN=localputers,DC=myDomain,DC=local:
  • Log in to the server running your Active Directory service and open Adsiedit.msc:

Expand Domain [YourDomainName], expand DC= RootDomainName, expand OU
~If your SQL service running under domain user account then select user account.
~If your SQL service running under Local System/Network Service then select computer account.

Right click on above selected object, properties and select attribute name with ‘servicePrincipalName’



Few points regarding SQL SPN registration:-

  • If SQL services running under Local system/Network Service account then SPN is registered automatically during service startup and de-registered during service stop. Event (26059,26060 ).
  • If SQL services running under domain account that is member of domain administrator group then SPN registered automatically.
  • In most of the environment, it’s recommend to run SQL services under a Domain User Account which has minimal permissions, for those situation we need to register SPN manually.


SQL client may start to use NTLM authentication when SPN is NOT registered OR It’s sending wrong set of information that cannot be validated against SPN stored in AD database.

For example:-

  • When SQL service running under local system/network service and SQL client (OR Front end application server) is contacting SQL server using host name which is not present in AD/DNS database (ex. Create a host file entry for name ‘MySQL’ points to IP of SQL server in application server and use ‘MySQL’ for SQL communication).
  • SQL service changed to domain user account but SPN is NOT registered.


To understand the root cause & possible solution, we can use the combination of following utilities & tools.

  • Netlogon log.

Enabled NetLogon logging on DC. Open command prompt & execute following command.

Nltest /DBFlag:2080FFFF

net stop netlogon
net start netlogon

Location \windows\debug\netlog.log, please don’t forget to stop logging by following command once your test complete.

Nltest /DBFlag:0x0

When NTLM is used, you may see regular pass through authentication attempts. Ex..

[LOGON] myDomain: SamLogon: Transitive Network logon of myDomain\app_account from appsrv (via SQL1A) Entered
[LOGON] myDomain: SamLogon: Transitive Network logon of myDomain\app_account from appsrv (via SQL1A) Returns 0x0
[LOGON] myDomain: SamLogon: Transitive Network logon of myDomain\app_account from appsrv (via SQL1A) Entered
[LOGON] myDomain: SamLogon: Transitive Network logon of myDomain\app_account from appsrv (via SQL1A) Returns 0x0

More details on NetLogon log

Large set of logs can be review using Netlogging Parase

  • NetMon log.

Enable on SQL server, load standard template-authentication traffic.

When Kerberos is used, you may see Kerberos ticket request in first request.


When NTLM is used, you may see regular authentication call.


  • SQL DMV.
  FROM sys.dm_exec_connections c
  INNER JOIN sys.dm_exec_sessions s
  ON c.session_id = s.session_id
  -- WHERE HOST_NAME = 'ApplicationServer_OR_RemoteServerName'

Above DMV is most useful tool to know authentication scheme, SQL client host name, login name & client IP…ETC.

Sample Output When Kerberos is used.


Sample Output When NTLM is used.


Possible output for auth_scheme column.

SQL – When SQL Server authentication is used.
NTLM- When NTLM authentication is used.
KERBEROS- When KERBEROS authentication is used.

Please note SQL Server will always use NTLM if connecting locally (like SSMS). Kerberos is only used if connecting remotely.

  • Audit Event Id 4624 Under security container.

You may see following event in regular interval indicating which authentication mode is used. Example.

Log Name:      Security
  Source:        Microsoft-Windows-Security-Auditing
  Event ID:      4624
  Task Category: Logon
  Keywords:      Audit Success
  An account was successfully logged on.

New Logon:
  Security ID:       MyDomain\app-service-account
  Account Name:      app-service-account
  Account Domain:    MyDomain
  Logon ID:         0x22ffcf
  Logon GUID:       {cbb3efa6-8801-002f-8401-8108e795a1dd}


Detailed Authentication Information:

Logon Process:                      Kerberos
  Authentication Package:           Kerberos
  Transited Services:  -
  Package Name (NTLM only):   -
  Key Length:                        0


NTLM Auth mode

Logon Process:                              NtLmSsp
Authentication Package:           NTLM
 Transited Services:  -
 Package Name (NTLM only):   NTLM V1
 Key Length:                                    128


Finally if your directory service team complaints that SQL is generating lots of authentication traffic and above information indicate NTLM is used rather than Kerberos auth scheme due incorrect / missing SPN then one of the following step can be used to fix the problem.

  • Change SQL service account either to ‘Network Service’ OR ‘Local System’ OR ‘Local Service’ from SQL server configuration manager so SPN registered itself during service startup. But it’s not recommended due to security aspect and limited functionality. Service Account Types. sscw_services


  • Assign SQL service account to be the member of the Domain Admins group so SPN can be registered for SQL service account automatically, it’s NOT ideal as the SQL account will get access to all the resources of AD environment.
  • If above fixes are NOT desirable then allow SQL service account to dynamically register it’s SPN in AD by giving by them rights ‘readServicePrincipalName’ & ‘WritePrincipalName’ using ADSIEDIT.msc.adsi2_dynamic

Unfortunately this method is NOT supported when AD environment have multiple domain controller OR SQL is installed on Active-Passive cluster due to replication & latency issues. Dynamic SQL-SPN registration

  • Manually assign SPN to SQL service account using SetSPN command line utility. (This can also be done using AdsiEdit) The syntax should be like below.
setspn -s MSSQLSvc/SQL1A.myDomain.local myDomain\sqlsvc

Checking domain DC=myDomain,DC=local
  Registering ServicePrincipalNames for CN=sqlsvc,CN=Users,DC=myDomain,DC=local
  Updated object

If you are using customized port then syntax would change slightly.

setspn -s MSSQLSvc/SQL1A.myDomain.local:1433 myDomain\sqlsvc


Last but not the least, verify front end application if it using the server name (or it’s alias) that is registered in AD & DNS server. Any alias OR host record created in host file of application server that points to SQL IP address may also cause auth schema to fail over on NTLM as SPN cannot be validate such scenarios.

Microsoft provide Kerberos Configuration Manager tool for SQL Server that can help to diagnose & fix SQL Kerberos issue in large environment.

Once you fix SPN & auth scheme issue, restart SQL service. To clear existing Kerberos tickets you can run command ‘klist purge’

Please note:-

All content provided on this blog is for informational purposes only. The reason for this blog is to document the things that I have learned reading blogs/books/self about SQL Server. We do not own any responsibility for the events that may happen because of using/applying the code/concepts mentioned in this blog.

If you like above content then feel free to share it with your colleague  and friends. If you wish to improve the content the feel free to contact me directly.