Unable to register SPN in windows 2008R2 to create SPN for SQL Server 2012 service account

Question

I have a client that I can't use SQL Server client tools to remote connect to his database He is running SQL Server 2012 installed in instance name INTERNAL

I narrowed down the problem

I run

execute XP_READERRORLOG

and get the error:

The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/SERVERNAME.domainname.com:INTERNAL ] for the SQL Server service. Windows return code: 0xffffffff, state: 63. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

So I checked the sql service is running under the default virtual account "NT Service\MSSQL$INTERNAL"

and I follow the advice here http://msdn.microsoft.com/en-us/library/ms191153.aspx

setspn -A MSSQLSvc/ServerName.domainname.com:1433 "NT Service\MSSQL$INTERNAL"

however it tells me:

Unable to locate account MSSQL$INTERNAL

What am I doing wrong in my command prompt command?

I read a blog here.

If the instance of SQL Server is running under the LocalSystem account, you do not have to run the SETSPN utility.

but I don't understand then why I get the error?

Answer 1

Since the service runs as network service, I believe the account name will need to be the machine name.

Tristan shows something similar in his blog at http://blogs.technet.com/b/tristank/archive/2006/05/08/spns-r-fn.aspx.

His example is for HTTP but you should be able to change it to MSSQLSvc:

For an App Pool running as NetworkService on the default port:

SETSPN -A HTTP/intranetweb syd-inet-web01 (that last part's the machine name)

Answer 2

As you can see from abatishchev's message the SQL service is running under a virtual account (common scenario in Windows7/Server2008R2) not Network Service. I have the same issue and my conclusion is that it is not possible to register manually an SPN for a Virtual (and possibly MSA) account...

Other than MS191153 you can read ms143504(SQL.110) where it states: "If the virtual account fails to register the Service Principal Name (SPN), register the SPN manually". And sends you to MS191153 which doesn't provide any useful info for doing that specifically other than saying that both VA and MSA have permissions to register an SPN by default. If you dig the article "Service Accounts Step-by-Step Guide" you can read that Domains at the Windows Server 2008 R2 functional level provide native support for both automatic password management and SPN management for both MSA and VA. For Windows 2003/8 additional configuration is required. What is your domain FL?

In my case after the service restarted the SPN registered automatically without any further issue. Thanks MS once again for "Simplifying" things...