You are here: Using a SQL Server OLAP Cube > Maintenance Configuration > SPNs in Active Directory (AD)

SPNs in Active Directory (AD)

A Service Principal Name (SPN) is a name in Active Directory that a client uses to uniquely identify an instance of a service. An SPN combines a service name with a computer and user account to form a type of service ID. For Kerberos authentication (a protocol that authenticates client and server entities on a network) to function, an SPN must be registered for each SQL Server service account in Active Directory.

ZAP CubeXpress may rely on Kerberos authentication if connection source servers are linked (refer to Link Server for Cross-Type Denormalization). In this case, the SQL Server service on each of the linked servers must have a registered SPN. Fortunately, in SQL Server (versus SSAS), the SPN is automatically assigned in SQL Server:

Register an SPN in Active Directory (AD)

  1. Determine the domain user account under which the SQL Server service is running (Identify the Service Account).

  2. Determine the service's port (Determine the Service Port).

  3. Add an MSSQLSvc registration for the SQL Server service using its port and user account. A registration must be made for both the computer's NetBIOS name and FQDN (fully qualified domain name). SPNs can be registered using one of the following:

Tip: For more information about registering Service Principle Names (SPNs), refer to: http://msdn.microsoft.com/en-us/library/ms191153.aspx

Identify the Service Account

To correctly configure the SPN, the user or account name under which the SQL Server service executes must be known and specified:

To view the account name, open the Services tool, and view the SQL Server line. For example:


Determine the Service Port

If only one instance of SQL Server is installed, the standard port is 1433. However, if an additional named instance is added, ports are dynamically assigned and the ports may vary. To look up the port:

  1. On the server on which SQL Server is running, open the SQL Server Configuration Manager.

  2. Expand the SQL Server Network Configuration, and click Protocols for <serverName>.

  3. Right-click Via in the right-hand listing and look at the Default Port value, as shown below.

Use setspn.exe

The setspn.exe utility is included with Microsoft Windows Server 2003 Support Tools, and is executed from the command line:


Syntax

Setspn -A MSSQLSvc/<SQL Server name>:<port> <domain>\<user>

Examples

The following examples include commands for DBServer01 and DBServiceUser01 in the Contoso domain.

Example Commands
List the SPN registrations for a computer account. setspn -L Contoso\DBServer01
Add MSSQLSvc registrations to DBServiceUser01 because SQL Server is running as this domain user.

setspn -A MSSQLSvc/DBServer01:1433 Contoso\DBServiceUser01

setspn –A MSSQLSvc/DBServer01.Contoso.com:1433 Contoso\DBServiceUser01

All Content © 2010-2015 ZAP — www.zapbi.comWednesday, February 03, 2016