Register SPN?

Question

Register SPN?

SQL Galaxy

One Orange Chip

Points: 29472
More actions
July 10, 2013 at 12:03 am

#296224

Hi,
I found error msg in Error log as below.. SQL Service account not running under domain account. please guide me how to resolve this issues.
The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x5, state: 4. 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
thanks
ananda

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Andrew G SSChampion Points: 12809 More actions · Answer 1

First you need to stop SQL and change the service account to a domain account. Best practise is to use a new account with no privleges (other than Instant File Iniatialisation and Lock Pages in Memory in Windows Group Policies). The account should not be used as a service account on any other box.

Change the account using SQL Server Configuration Manager.

Then someone who has privleges to update Active Directory will need to run the commands to manually register the SPN's as outlined here:

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

(under Manual SPN registration)

Manual SPN Registration
To register the SPN manually, the administrator must use the Setspn.exe tool that is provided with the Microsoft Windows Server 2003 Support Tools. For more information, see the Windows Server 2003 Service Pack 1 Support Tools KB article.
Setspn.exe is a command line tool that enables you to read, modify, and delete the Service Principal Names (SPN) directory property. This tool also enables you to view the current SPNs, reset the account's default SPNs, and add or delete supplemental SPNs.
The following example illustrates the syntax used to register manually register an SPN for a TCP/IP connection.
setspn -A MSSQLSvc/myhost.redmond.microsoft.com:1433 accountname
Note If an SPN already exists, it must be deleted before it can be reregistered. You do this by using the setspn command together with the -D switch. The following examples illustrate how to manually register a new instance-based SPN. For a default instance, use:
setspn -A MSSQLSvc/myhost.redmond.microsoft.com accountname
For a named instance, use:
setspn -A MSSQLSvc/myhost.redmond.microsoft.com:instancename accountname

Once that's done, you can run

SELECT auth_scheme, * FROM sys.dm_exec_connections

and check to see if any connections are now using KERBEROS authentication.

Perry Whittle SSC Guru Points: 233821 More actions · Answer 2

If you're using Windows 2008, ensure you get the latest version of SETSPN.EXE which can identify any duplicates which won't usually be visible via ADSIEDIT or ealier versions of SETSPN.EXE.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 3

yes, we are using windows 2008 operating system...

SQL Service account running under windows local users.. But domain users are added into SQL logins for connection between database and application...so in this situation domain ids need to set SPN which is under login..

After done set SPN at active dirctory level what will be impact or benifit on application side? please sugesstion me.

thanks

ananda

Perry Whittle SSC Guru Points: 233821 More actions · Answer 4

ananda.murugesan (7/10/2013)
yes, we are using windows 2008 operating system...
SQL Service account running under windows local users.. But domain users are added into SQL logins for connection between database and application...so in this situation domain ids need to set SPN which is under login..
After done set SPN at active dirctory level what will be impact or benifit on application side? please sugesstion me.
thanks
ananda

You need to run the SQL Server account under a domain user account and not a local SAM account. You then register the services SPN against this domain user.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉