1. Manually Publish Sql Spn In Active Directory 2017
2. Sccm Manually Publish The Sql Server Spn In Active Directory
3. Add Spn To Active Directory
4. View Spn In Active Directory
5. Manually Publish Sql Spn In Active Directory Pdf
6. Manually Publish Sql Spn In Active Directory

This post contains the references and methodology I use when troubleshooting SQL Kerberos issues. While there are already many SQL Kerberos articles in existence, the goal here is to provide a central and up-to-date resource(including new tools + updated tool syntax) which covers not just the configuration steps, but an understanding of why this is needed as well.

We can manually publish the Terminal Server License Server, using one of the following two methods. Using 'Active Directory Sites and Services'. Microsoft SQL. SPN is automatically registered by SQL Server using the startup account of SQL Server when SQL Server starts and deregistered when SQL Server is stopped. Kerberos authentication would fail when the SPN is not registered (or) when there is duplicate SPN’s registered in Active directory (or). Option 2 - Register SPN manually. Once you've picked and implemented one of these options and if necessary restarted SQL Server you can establish a new connection and run the following TSQL to check that you are now using Kerberos authentication. Creating service principal names (SPN) in Active Directory. A service principal name (SPN) is a unique identifier of a service instance. SPNs are used during authentication to associate a service instance with a service logon account. This allows a client application to request that a service authenticate an account even if the client does not have the account name.

Common SQL errors related to failed Kerberos authentication.

Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.

Login failed for user '(null)'.

Login failed for user '.

Cannot generate SSPI context.

Access Denied. (For example, when an account was not impersonated or delegated correctly and unexpected credentials were used to access a resource, such as a failed file share access during a bulk insert operation).

What is the difference between impersonation and delegation?

Impersonation flows the original caller's identity to back-end resources on the same computer.Delegation flows the original caller's identity to back-end resources on remote computers. Delegation impersonates the client without possession of the client’s password, it is a much higher privileged operation. Impersonation can be done with NTLM or Kerberos authentication. Delegation requires Kerberos authentication. Many times the terms are used interchangeably, but it is important to understand if delegation is truly taking place to identify if the problem is in fact a double-hop issue (which requires Kerberos and Active Directory delegation settings to be enabled).

Reference:

·Impersonation/Delegation http://msdn.microsoft.com/en-us/library/ff647248.aspx

What is Kerberos?

Kerberos is an industry standard authentication protocol which provides a method of initially authenticating a user to Active Directory through the logon process and then automatically authenticating the user to other remote network services, such as database, file, and web services. This provides single sign-on to network services.

Reference:

·Kerberos Authentication Overview http://technet.microsoft.com/en-us/library/hh831553.aspx

What is an SPN?

A service principal name (SPN) is an active directory object attribute. It can be an attribute of an AD domain computer object or an attribute of an AD domain user object. Every service that will use Kerberos authentication needs to have an SPN set for it so that clients can identify the service on the network. If the service is running under the LOCALSYSTEM account, the SPN attribute should be added to the AD domain computer object. If the service is running under a domain user account, the SPN attribute should be added to the AD domain user object.

The format of an SPN is

<service_class>/<host_name>:<service_listening_port|service_name>

For a default instance of SQL Server, listening on port 1433, the SPN format is as follows:

MSSQLSvc/serverxyz.your_domain.com:1433

MSSQLSvc/serverxyz:1433

Reference:

·Register a Service Principal Name for Kerberos Connections http://technet.microsoft.com/en-us/library/ms191153(v=sql.110).aspx

When do we need an SPN for SQL Server?

Kerberos is not typically required for a direct (one hop - simple client to server) connection to SQL. The default behavior for negotiated authentication in Microsoft Windows is to use Kerberos first and then failback to NTLM.Kerberos is used if it has been configured correctly, but if it is not configured the authentication will automatically use NTLM.However, a direct connection to SQL may still fail if an SPN is not present and only Kerberos is being used or if the failback to NTLM is not working correctly.

On the other hand, Kerberos is always needed when a double hop is done. For example, if you log on from LAPTOP1 to SQL2 and within SQL2 you execute a SQL linked server query to SQL3. If the SQL linked server is configured to impersonate your Windows credentials, this will require your Windows credentials to be delegated from SQL2 to SQL3, and hence requires Kerberos delegation to be configured.

Reference:

·Double-hop authentication: Why NTLM fails and Kerberos Workshttp://blogs.msdn.com/b/besidethepoint/archive/2010/05/09/double-hop-authentication-why-ntlm-fails-and-kerberos-works.aspx

How can you tell if an established SQL connection is using Kerberos or NTLM?

Run the following SQL DMV query:

The auth_scheme column will indicate 'KERBEROS' or “NTLM” depending on how your connection was established.

Reference:

·How to make sure that you are using Kerberos authentication when you create a remote connection to an instance of SQL Server 2005http://support.microsoft.com/kb/909801

I’ve heard the term double-hop very often. Are more than two hops allowed?

Yes, we often see multi-hop implementations with SharePoint and Reporting Services deployments.

How is an SPN used?

Please note, this is a very high-level and over-simplified explanation of the Kerberos protocol, omitting many details. For full details please refer to the referenced links.

Kerberos uses “tickets” to identify users and provide a method to authenticate to services.

1.Steps (1) and (2) in the figure 2 below show the initial client to Active Directory KDC (Key Distribution Center) Authentication Service (AS) exchange.

a.The client connects to the KDC Authentication Service.

b.The KDC Authentication Service returns a ticket granting ticket (TGT) and a session key which the client can use to encrypt and authenticate communication with the KDC for ticket-granting service (TGS) requests.

2.Steps (3) and (4) in the figure below show the client to Active Directory KDC Ticket-Granting Service (TGS) exchange.

a.The client sends a request to the KDC for a ticket for the [SQL] server. To do this it sends the previously received ticket granting ticket (TGT) and the SPN. Think of the SPN as a unique name for the [SQL] service which is being connected to. When a client requests a service ticket to a network resource, the SPN host identifier will be based on how the client resolves the resource and must match an existing SPN within Active Directory. Some possible options:

i.FQDN of server running the service

ii.NetBIOS name of server running the service

b.The KDC returns a service ticket and a session key (which is used to encrypt communication with the [SQL] server). The client extracts the ticket and its own copy of the session key. Both are cached by the client allowing them to be used for the lifetime of the ticket without the need to constantly communicate with AD(for more information see Kerberos Technical Supplement for Windows http://msdn.microsoft.com/en-us/library/ff649429.aspx).How long a ticket is valid depends on Kerberos policy for the realm. Typically, tickets are good for no longer than eight hours, about the length of a normal logon session. It is each client's responsibility to manage the ticket for the server in its ticket cache and to present that ticket each time it accesses the [SQL] server. This provides a significant performance increase when connecting to a service.

3.Steps (5) and (6) in the figure below show the client to [SQL] server exchange.

a.The client requests access to the [SQL] server and presents the previously received service ticket. The [SQL] server decrypts the service ticket.

Reference:

·Ticket Granting Tickets http://msdn.microsoft.com/en-us/library/aa380510.aspx.

·Session Tickets http://msdn.microsoft.com/en-us/library/aa380136.aspx.

·How the Kerberos Version 5 Authentication Protocol Works http://technet.microsoft.com/en-us/library/4a1daa3e-b45c-44ea-a0b6-fe8910f92f28

Delegation

Kerberos allows the use of delegation where the frontend service (for ex. Web App) can connect to a remote backend service (for ex. SQL Server) using the identity of the windows user who was authenticated to the frontend service.The user can authenticate to the frontend service using Kerberos and then the frontend service can authenticate using Kerberos to a backend service using the identity of the user. Kerberos also supports multi-hop scenarios where delegation is passed down to other servers.

Two main types of delegation can be configured:

·Unconstrained delegation - Allows the service identity to delegate another user to any destination computer, service, or application.

·Constrained delegation - Allows the service identity to delegate another user only to those destination services explicitly defined. As a security best practice, Microsoft recommends constrained delegation over unconstrained delegation. Constrained delegation has two configurations; one that requires Kerberos authentication and another that works with any initial authentication protocol and protocol transition. Protocol transition enables applications to support different authentication mechanisms at the user authentication tier. They can then switch to the Kerberos protocol for security features, such as mutual authentication and constrained delegation, in the subsequent application tiers. For more information please see Delegated Authentication and Trust Relationshipshttp://technet.microsoft.com/en-us/library/dn169022(v=ws.10).aspx.

Manually Publish Sql Spn In Active Directory 2017

Delegation is enabled by configuring the properties of the Active Directory account running the service which will be delegating credentials. The delegation tab is only available after an SPN attribute has been added to the active directory object.

Reference:

·Kerberos Constrained Delegation Overview http://technet.microsoft.com/en-us/library/jj553400.aspx

·Configure Analysis Services for Kerberos constrained delegation http://technet.microsoft.com/en-us/library/dn194199.aspx

·How to Implement Kerberos Constrained Delegation with SQL Server 2008 http://msdn.microsoft.com/en-us/library/gg723715.aspx

Troubleshooting and Configuration Steps

Configuration

1.Understand your topology.

a.Identify the machines, network services (such as SQL, IIS, Sharepoint, etc), Active Directory domains, number of hops, and user and service accounts involved.

2.Configure/Verify Active Directory SPN requirements.

a.Determine which services you will be connecting to via Kerberos and ensure proper SPNs exist for those services.

b.By default SQL will try to automatically register an SPN. You can either grant the SQL Service permissions to create its own SPNs or you can opt to manually register an SPN for SQL Server.

Sccm Manually Publish The Sql Server Spn In Active Directory

i.See Step 3: Configure the SQL Server service to create SPNs dynamicallyof How to use Kerberos authentication in SQL Serverhttps://support.microsoft.com/kb/319723

c.Using the example topology above, we will need to connect to the SQL2 and SQL3 services via the Kerberos protocol. Therefore, proper SPN(s) need to be registered for both the SQL2 and SQL3 service accounts. We can manually register these SPNs via the setspn.exe tool.

Add Spn To Active Directory

3.Configure/verify local impersonation user rights.

a.Determine which service(s) will be impersonating credentials and ensure those services have impersonation rights. The impersonating service account should have the local user right (local security policy) “Impersonate a client after authentication” on the machine where the impersonation will take place.

b.In the example above, if the SQL service running on SQL2 will be impersonating the credentials of User1, you should grant it the “Impersonate a client after authentication” user right.

4.Configure/Verify Active Directory delegation requirements.

a.Determine which service(s) will be delegating credentials and ensure those services have delegation settings enabled within Active Directory.

i.The delegating service account should have unconstrained or constrained delegation enabled in Active Directory. See Figure 4 above for an example of how to enable unconstrained delegation.

b.In the example above, the SQL service running on SQL2 will be delegating the credentials of User1. Therefore, the Active Directory delegation settings for SQL2’s service account should be enabled. SQL3 will not be delegating credentials so you do not need to enable AD delegation settings for SQL3’s service account.

5.Verify the user account which will be delegated is allowed to be delegated (it should be by default).

a.Ensure the account being delegated does not have any delegation limitation set. The “Account is sensitive and cannot be delegated” setting should be unchecked.

b.In the example above, the ContosoUser1 account should have the “Account is sensitive and cannot be delegated setting” option unchecked.

Troubleshooting

If the above settings are configured and Kerberos authentication is still failing, then check for common issues which can cause Kerberos authentication to fail.

1.Verify there are no duplicate SPNs. When moving or adding SPN’s it is important that a duplicate is not created. SPN’s must be unique.

a.From any server in the domain:

2.Verify client to domain controller network connectivity and name resolution:

a.From Laptop1 and SQL2:

3.Verify remote service connectivity and host name resolution at each hop.

a.From Laptop1:

b.From SQL2:

c.From SQL3:

4.Verify you can make Kerberos connections at each hop

a.From LAPTOP1:

a.From SQL2:

5.Use the KLIST command to view cached SPNs at each hop. Check for any issues with the cached spn (wrong domain, port, spn name, etc)

a.From LAPTOP1 and SQL2:

6.Use the KLIST command to test acquiring the necessary SPN at each hop.

a.From LAPTOP1:

b.From SQL2:

7.Enable Kerberos logging and examine the System event log for errors

a.How to enable Kerberos event logging http://support.microsoft.com/kb/262177

b.Security Auditing http://technet.microsoft.com/en-us/library/cc771395.aspx

8.Collect network traces at each hop. You may need to clear the locally cached SPN’s to force the client query the domain controller (and not use the cached SPN). This can be done via the KLIST purge command.

a.Kerberos errors in network captures http://blogs.technet.com/b/askds/archive/2012/07/27/kerberos-errors-in-network-captures.aspx

b.Consult whitepaper for kerberos error code reference - Troubleshooting Kerberos Errors White Paper http://www.microsoft.com/downloads/en/details.aspx?familyid=7dfeb015-6043-47db-8238-dc7af89c93f1&displaylang=en

Reference:

·Nslookup http://technet.microsoft.com/en-us/library/cc725991.aspx

·Nltesthttp://technet.microsoft.com/en-us/library/cc731935.aspx

·Klist http://technet.microsoft.com/en-US/library/hh134826.aspx

·Network tracing (packet sniffing) built-in to Windows Server 2008 R2 and Windows Server 2012. http://blogs.technet.com/b/yongrhee/archive/2012/12/01/network-tracing-packet-sniffing-built-in-to-windows-server-2008-r2-and-windows-server-2012.aspx

··Audit Filtering Platform Packet Drop http://technet.microsoft.com/en-us/library/dd941625(v=WS.10).aspx

View Spn In Active Directory

·Kerberos Authentication problems – Service Principal Name (SPN) issues - Part 1 http://blogs.technet.com/b/askds/archive/2008/05/29/kerberos-authentication-problems-service-principal-name-spn-issues-part-1.aspx

Tool: Kerberos Configuration Manager for SQL Server

The Kerberos Configuration Manager for SQL Server is a diagnostic tool that helps troubleshoot Kerberos related connectivity issues with SQL Server, SQL Server Reporting Services, and SQL Server Analysis Services. It can perform the following functions:

·Gather information on OS and Microsoft SQL Server instances installed on a server.

·Report on all SPN and delegation configurations on the server.

·Identify potential problems in SPNs and delegations.

·Fix potential SPN problems.

Reference:

·New tool: 'Microsoft Kerberos Configuration Manager for SQL Server' is ready to resolve your Kerberos/Connectivity issueshttp://blogs.msdn.com/b/farukcelik/archive/2013/05/21/new-tool-quot-microsoft-kerberos-configuration-manager-for-sql-server-quot-is-ready-to-resolve-your-kerberos-connectivity-issues.aspx

·Kerberos Configuration Manager for SQL Server is available http://support.microsoft.com/kb/2985455

·Download location http://www.microsoft.com/en-us/download/details.aspx?id=39046

Tool: SETSPN

Starting with Windows 2008, setspn.exe is now part of the OS. You should see the setspn.exe tool in the c:windowssystem32 directory.Be sure to use a version of Setspn.exe that is included with Windows Server 2008 or later because it includes the -S parameter for adding an SPN. Setspn -S will verify that there are no duplicate SPNs. SETSPN -A with Windows Server 2012 also does a duplicate check upfront.

Reference:

·Setspn http://technet.microsoft.com/en-us/library/cc731241.aspx

·Service Principal Names (SPNs) SetSPN Syntax (Setspn.exe) http://social.technet.microsoft.com/wiki/contents/articles/717.service-principal-names-spns-setspn-syntax-setspn-exe.aspx

Additional References

Manually Publish Sql Spn In Active Directory Pdf

·Configuring Linked Servers for Delegation http://technet.microsoft.com/en-us/library/ms189580(v=SQL.105).aspx

Manually Publish Sql Spn In Active Directory

·You may experience connectivity issues to SQL Server if SPNs are misconfigured http://support.microsoft.com/kb/2443457

·You may experience connectivity issues if SQL Server service account delegation settings are misconfigured http://support.microsoft.com/kb/2443455

·All About Kerberos “The Three Headed Dog” with respect to IIS and Sql http://blogs.msdn.com/b/chiranth/archive/2013/09/20/all-about-kerberos-the-three-headed-dog-with-respect-to-iis-and-sql.aspx

·Kerberos Survival Guide http://social.technet.microsoft.com/wiki/contents/articles/4209.kerberos-survival-guide.aspx

·Kerberos for the Busy Admin http://blogs.technet.com/b/askds/archive/2008/03/06/kerberos-for-the-busy-admin.aspx

·Kerberos Troubleshooting http://blogs.msdn.com/b/canberrapfe/archive/2012/01/02/kerberos-troubleshooting.aspx

Fany Carolina Vargas | SQL Dedicated Premier Field Engineer | Microsoft Services