Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’–SPN’s\Kerberos

Message
Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. [CLIENT: x.x.x.x]
Message
Error: 18456, Severity: 14, State: 11.

As far as I understand the above error happens because the CLNTSQL2 is currently running off its secondary (SQL01a) and is failing because it cant use Kerberos authentication so it is falling back onto NTLM because the Service Principal Name for sql server isn’t registered properly.

Understanding Kerberos and NTLM Authentication

As can be seen below, when the instance started up, it failed to register the Service Principal Name (SPN). This isn’t something new – in that I don’t think SPN’s have ever been set up for the SQL Servers – BUT it looks to write the login failed error to the alert log when the cluster instance isn’t on its registered node:

Date          16/03/2011 11:03:45
Source        Server
Message
The SQL 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.

When an instance of the SQL Server Database Engine starts, SQL Server tries to register the SPN for the SQL Server service. When the instance is stopped, SQL Server tries to unregister the SPN. For a TCP/IP connection the SPN is registered in the format MSSQLSvc/<FQDN>:<tcpport>.Both named instances and the default instance are registered as MSSQLSvc, relying on the <tcpport> value to differentiate the instances.

See Step 3 http://technet.microsoft.com/en-us/library/ms189585(SQL.90).aspx

To manually create a domain user Service Principle Name (SPN) for the SQL Server service account

1. Click Start, click Run, and then enter cmd in the Run dialog box.

2. From the command line, navigate to Windows Server support tools installation directory. By default, these tools are located in the C:\Program Files\Support Tools directory.

3. Enter a valid command to create the SPN. To create the SPN, you can use the NetBIOS name or the Fully Qualified Domain Name (FQDN) of the SQL Server. However, you must create an SPN for both the NetBIOS name and the FQDN.4. Verify that the command completed successfully by reviewing the command’s output for the updated object line.

Important: When you create an SPN for a clustered SQL Server, you must specify the virtual name of the SQL Server Cluster as the SQL Server computer name.

To create an SPN for the NetBIOS name of the SQL Server use the following command:

setspn –A MSSQLSvc/<SQL Server computer name>:1433 <Domain\Account>

To create an SPN for the FQDN of the SQL Server use the following command:

setspn -A MSSQLSvc/<SQL Server FQDN>:1443 <Domain\Account>

Note: The command to register an SPN for a SQL Server named instance is the same as that used when registering an SPN for a default instance except that the port number should match the port used by the named instance.

Based on this, I would assume registering vmSQL02 on SQL01a would be as follows:

setspn –A MSSQLSvc/vmSQL01.Domain.x.co.uk:1748 GROUP\SQLAPP.Service

setspn –A MSSQLSvc/vmSQL02.Domain.x.co.uk:2487 GROUP\SQLAPP.Service

And on SQL01b

setspn –A MSSQLSvc/vmSQL01.Domain.x.co.uk:1748 GROUP\SQLAPP.Service

setspn –A MSSQLSvc/vmSQL02.Domain.x.co.uk:2487 GROUP\SQLAPP.Service

SETSPN can be downloaded from here

C:\Program Files (x86)\Resource Kit>setspn -L SQL01a

Registered ServicePrincipalNames for CN=SQL01a,OU=SQL,OU=Servers,OU=Ent,DC=domain,DC=x,DC=co,DC=uk:

    HOST/SQL01a
    HOST/SQL01a.domain.x.co.uk

C:\Program Files (x86)\Resource Kit>setspn -L SQL01b
Registered ServicePrincipalNames for CN=SQL01b,OU=SQL,OU=Servers,OU=Ent,DC=domain,DC=x,DC=co,DC=uk:

    HOST/SQL01b
    HOST/SQL01b.Domain.x.co.uk

C:\Program Files (x86)\Resource Kit>setspn -L GROUP\SQL.Service
Registered ServicePrincipalNames for CN=APP SQL Service,OU=Service Accounts,OU=Ent,DC=domain,DC=x,DC=co,DC=uk:

C:\Program Files (x86)\Resource Kit>setspn -L SQL01
Cannot find account SQL01

C:\Program Files (x86)\Resource Kit>setspn -L SQL02
Cannot find account SQL02

C:\Program Files (x86)\Resource Kit>setspn -L MSSQLSvc/SQL02
Cannot find account MSSQLSvc/SQL02

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: