[스크랩] How to Configure an SPN for SQL Server Site Database Servers

 

http://technet.microsoft.com/en-us/library/bb735885.aspx

 

 

Updated: January 1, 2011

Applies To: System Center Configuration Manager 2007, System Center Configuration Manager 2007 R2, System Center Configuration Manager 2007 R3, System Center Configuration Manager 2007 SP1, System Center Configuration Manager 2007 SP2

Running the SQL Server service using the local system account of the SQL Server computer is not a SQL Server best practice. For the most secure operation of SQL Server site database servers, a low rights domain user account should be configured to run the SQL Server service.

A Service Principal Name (SPN) must be registered for the SQL Server service account (when the local system account will not be used) to allow clients to identify and authenticate the service using Kerberos authentication. The SetSPN utility can be used to register an SPN for the site database server SQL Server service account. The SetSPN utility must be run on a computer that resides in the SQL Server's domain and it must be run using Domain Administrator credentials. To properly configure an SPN for the SQL Server service account using the SetSPN utility, follow the steps in these procedures.

Note
To use the SetSPN utility, or to open an ADSIEdit MMC console, you must first install the Microsoft Windows Server support tools. These tools are included in the support tools folder on both Windows 2000 Server and Windows Server 2003 CDs. To install the Windows Server support tools, navigate to \SUPPORT\TOOLS\ on the server's installation CD and run suptools.msi.

 

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.

   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>:1433 <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.

    

4. Verify that the command completed successfully by reviewing the command’s output for the updated object line.

To verify the domain user SPN is registered correctly using the SetSPN command

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

2. From the command line enter the following command: setspn –L <domain\SQL Service Account>

3. Review the registered ServicePrincipalName to ensure that a valid SPN has been created for the SQL Server.

To verify the domain user SPN is registered correctly using the ADSIEdit MMC console

1. Click Start, click Run, and then enter adsiedit.msc to launch the ADSIEdit MMC console.

2. If necessary, connect to the site server's domain.

3. In the console pane, expand the site server's domain, expand DC=<server distinguished name>, expand CN=Users, and then right-click CN=<Service Account User>. on the context menu, click Properties.

4. In the CN=<Service Account User> Properties dialog box, review the servicePrincipalName value to ensure that a valid SPN has been created and associated with the correct SQL Server.

To change the SQL Server service account from local system to a domain user account

1. Create or select a domain or local system user account that will be used as the SQL Server service account.

2. Open SQL Server Configuration Manager.

3. Click SQL Server 2005 Services, and then double click SQL Server<INSTANCE NAME>.

4. On the Log on tab, select This account, and then enter the user name and password for the domain user account created in step 1 or click Browse to find the user account in Active Directory and then click Apply.

5. Click Yes on the Confirm Account Change dialog box to confirm the service account change and restart the SQL Server Service.

6. Click OK after the service account has been successfully changed.

See Also

Concepts

Configuration Manager Service Principal Name Requirements

For additional information, see Configuration Manager 2007 Information and Support.
To contact the documentation team, email SMSdocs@microsoft.com.

Was this page helpful?

Yes

No

Not accurate

Not enough depth

Need more code examples

Tell us more...

(1500 characters remaining)

Thank you for your feedback

Community Additions

ADD

Configure the SQL service to be able to register its own SPN

Rather than using SetSPN to create the ServicePrincipalName for the SQL Server service account, use ADSIEdit or DSACLS to grant the account the right to register its own SPNs. Now the account can take care of itself.

ADSIEdit Method: Kevin Homan blogged about this in his OpsMgr blog, but it is applicable here as well. See:
http://blogs.technet.com/b/kevinholman/archive/2007/12/13/system-center-operations-manager-sdk-service-failed-to-register-an-spn.aspx

DSACLS Method: This is from a SQL Data Access Technologies blog for all you command line fans out there:
http://blogs.msdn.com/b/dataaccesstechnologies/archive/2010/01/06/how-to-grant-readserviceprincipalname-and-writeserviceprincipalname-rights-to-sql-server-service-start-up-account-without-using-adsdiedit-tool.aspx

Bruce Hethcote

11/10/2011

Do not copy and paste syntax from this article as it has invalid characters

You will get "Unknown parameter" error when you copy paste from this article. The best way is to type the entire command. This could be caused by wrong ASCII characters in the examples above. Alternatively copy the command from below and see if that works.

setspn -a mssqlsvc/SQLServer:1433 domain\SQLAccount
setspn -a mssqlsvc/SQLServer.yourdomain.net:1433 domain\SQLAccount

ASIF SHAFI

10/20/2011

You shouldn't need 1433

Unless the service name and port are not standard, you do not have to enter them when you use Setspn. Source: http://technet.microsoft.com/en-us/library/cc773257(WS.10).aspx

Mike Crowley

5/3/2011

Typo: should be port # 1433

Syntax for creation of SPN with FQDN should use port # 1433, _not_ 1443.