Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Register SPN? Expand / Collapse
Author
Message
Posted Wednesday, July 10, 2013 12:03 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 12:41 AM
Points: 1,015, Visits: 2,839
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
Post #1471954
Posted Wednesday, July 10, 2013 1:28 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:40 PM
Points: 1,077, Visits: 1,496
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.
Post #1471984
Posted Wednesday, July 10, 2013 3:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:15 PM
Points: 5,951, Visits: 12,822
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"
Post #1472022
Posted Wednesday, July 10, 2013 3:58 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 12:41 AM
Points: 1,015, Visits: 2,839
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
Post #1472034
Posted Wednesday, July 10, 2013 7:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:15 PM
Points: 5,951, Visits: 12,822
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"
Post #1472125
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse