Questions About Kerberos and SQL Server That You Were Too Shy to Ask

The Questions

1. What is Kerberos?
2. Why is Kerberos needed for SQL Server?
3. Can’t I just avoid using Kerberos?
4. What is a Service Principal Name (SPN)?
5. Is there a way to see SPNs in AD?
6. How are SPNs created or changed?
7. What happens if I change the service account of my SQL Server?
8. How can I tell if I’m connecting with Kerberos authentication?
9. I’m connecting from another server, but it’s using NTLM. How did that happen?
10. Kerberos authentication is working for the instance, but why aren’t the SSRS reports running?
11. How do I set up Kerberos for linked servers?
12. Any other surprises I need to know about?

1. What is Kerberos?

Kerberos is an authentication protocol used in networks, including Active Directory (AD), that is based on the use of encrypted tickets for access to network resources.

In a situation in an AD network when Kerberos can’t be used, then the older and less secure NTLM authentication protocol is used instead. There are many situations where the end user will not be able to access the resources they need with NTLM. This is especially true when more than one network resource is involved with the request (double-hop), such as is often the case with SSRS (SQL Server Reporting Services) or a linked server.

For Kerberos authentication to connect to a SQL Server instance, Service Principal Names (SPNs) must be properly configured in AD. While these are not difficult to create, most DBAs will not have rights to do so. Even with network administrator privileges, it’s easy to make a mistake when creating SPNs. When DBAs understand Kerberos, they can help the network administrator troubleshoot issues.

2. Why is Kerberos needed for SQL Server?

When NTLM is used, the client, for example a user logged into a laptop, contacts a domain controller when requesting access to a resource in the network. This resource could be an SSRS report, for example. When using NTLM, the user proves their identity to the SSRS server. Unfortunately, the SSRS server cannot forward the credentials of the user along to the database server. The database server will deny the request, and the end user will see an error message. This is common with SSRS but will also be seen whenever resources are needed involving multiple servers.

When Kerberos is property configured, the SSRS server can pass along confirmation of the identity of the requester to the database server via the ticket. If the login of the original requester has permission to select the data, it’s returned to the SSRS server, and the report is delivered.

Even if you are not using SSRS, you can run into issues when Kerberos is not configured properly. For example, you will often see error messages when trying to connect to SQL Server using SSMS (SQL Server Management Studio) when logged into another server when SPNs are misconfigured.

3. Can’t I just avoid using Kerberos?

If the resources are located within the same physical server or virtual machine, then Kerberos authentication is not required. In this case, the identity of the requester is just needed on one server; nothing needs to be forwarded along. Typically, an SSRS server runs reports that need data from many servers across the network. Even if that’s not the case, SSRS is often installed on its own server for performance reasons. This is that double-hop issue I mentioned earlier.

Another way to avoid using Kerberos in any situation is by using SQL Server logins or users instead of network accounts. For example, if the SSRS report contains credentials for a SQL Server login, Kerberos will not be involved when the request is made to the database. This may or may not be a good idea in your organization depending on security policies or application requirements.

You can also save the credentials for a Windows account in an SSRS data source. This will avoid the double-hop problem since the user name and password will be used by the SSRS server when making the request to the database server. Keep in mind, however, that only one set of credentials can be saved in a data source, so this will probably not be the credentials of the person who wants to run the report. As long as the user has rights to run the report, they do not need permission to the actual data. Again, this might be something you use now, possibly for SSRS subscriptions, but it also might be something you should avoid depending on the policies in your organization.

One other option for SSRS is to save Windows credentials but try to impersonate the user running the report. Theoretically, this can be used to bypass Kerberos, but it uses the SETUSER command after connecting to the database. This command is deprecated and requires either sysadmin or db_owner membership by the account whose credentials you are saving. That is not a great idea! It may be a good choice for other sources of data, such as SSAS Tabular, but it’s probably not suited for a traditional SQL Server database.

4. What is a Service Principal Name (SPN)?

SPNs are properties of service accounts in AD. It associates the service account to the service. SPNs are in the form Service/Server Domain\ServiceAccount. Here is an example:

Fully qualified server name: SQL1.mydomain.local

Port: 1433

Instance: Default

Service account: sqlservice1

There should be two SPNs registered for the SQL Server instance:

MSSQLSvc/SQL1.mydomain.local mydomain\sqlservice1

MSSQLSvc/SQL1.mydomain.local:1433 mydomain\sqlservice1

If the SQL Server instance is using a local account instead of an AD account, the computer name will be used instead of a service account name. Here is an example:

Fully qualified server name: SQL1.mydomain.local

Port: 1433

Instance: Default

Service account: Network Service

There should be two SPNs registered:

MSSQLSvc/SQL1.mydomain.local mydomain\SQL1

MSSQLSvc/SQL1.mydomain.local:1433 mydomain\SQL1

If you have a SQL Server with a named instance, then the SPNs would look like this:

Fully qualified server name: SQL1.mydomain.local

Port: 49827

Instance: Inst1

Service account: Network Service

There should be two SPNs registered:

MSSQLSvc/SQL1.mydomain.local:inst1 mydomain\SQL1

MSSQLSvc/SQL1.mydomain.local:49827 mydomain\SQL1

5. Is there a way to see SPNs in AD?

You can see the SPNs in the Active Directory Users and Computers utility. The first step is to enable the Advanced Features view.

After finding the object, search in the Attribute Editor for servicePrincipleName and click Edit. This example shows the SPNs that are registered using the computer account because the default account was used during installation of the instance. It’s also a named instance.

 

You can also add or remove SPNs from this dialog with the appropriate rights.

6. How are SPNs created or changed?

To add or delete an SPN, use the setspn utility in a command window or PowerShell session. Keep in mind that DBAs will likely not have rights to add or delete SPNs, but it’s useful to know what needs to be changed when working with your network administrators.

Here are the commands:

Setspn -L will list all the SPNs registered for a given service account. In the case of a SQL Server using a local account, you will use the computer name.

Setspn -L mydomain\sql1
Setspn -L mydomain\sqlservice1

Setspn -D is used to delete an SPN.

Setspn -D MSSQLSvc/SQL1.mydomain.local mydomain\SQL1
Setspn -D MSSQLSvc/SQL1.mydomain.local mydomain\SQL1

Setspn -S is used to add an SPN. It avoids creating duplicate SPNs for a given service. If an SPN is already registered for a service with a different service account, it should return an error message including the account with the current SPN.

Setspn -S MSSQLSvc/SQL1.mydomain.local:1433 mydomain\sqlservice1
Setspn -S MSSQLSvc/SQL1.mydomain.local mydomain\sqlservice1

Setspn -Q is used to search for existing SPNs registered for a given service. This is helpful when troubleshooting Kerberos issues and you can’t figure out where the SPN is registered.

Setspn -Q MSSQLSvc/sql1.mydomain.local

7. What happens if I change the service account of my SQL Server?

When the service account changes, the SPNs must be switched to the new service account. Before a new SPN is added, any incorrect SPNs must be removed. The old SPN will not be removed automatically, and you cannot have multiple entries for the same service. Often, SQL Server instances are installed using the default account, which is local. Only later will the correct service account be set up. When this happens, the old SPN will be left in place, and Kerberos authentication will not work. SSRS reports that were running previously may begin erroring out instead. Often, you’ll see this error when attempting to connect via SSMS from another computer.

Someone with permission to create and remove SPNs will need to run the setspn -D command to remove the old SPNs and the setspn -S command to add the new ones. You may need to restart the SQL Server instance and wait for it to be replicated in AD before the change works.

8. How can I tell if I’m connecting with Kerberos authentication?

First, Kerberos only comes into play when connecting from a different server. If you are remote controlling the server, you will not be using Kerberos. If you have VIEW SERVER STATE permission on the instance, you can run this query:

SELECT S.login_name, C.auth_scheme, s.host_name
FROM sys.dm_exec_connections AS C
JOIN sys.dm_exec_sessions AS S ON C.session_id = S.session_id;

You can also use the klist command to view the tickets. In this example, you can see the ticket for the SQL Server in #2.

You will see this error message when Kerberos is required, but you are not able to use it.

9. I’m connecting from another server, but it’s using NTLM. How did that happen?

If no SPNs are configured for the instance, then NTLM will be used without error as long as no double-hop is involved. This could happen when the DBA installs the instance and doesn’t have permission to create SPNs, which is typically the case.

10. Kerberos authentication is working for the instance, but why aren’t the SSRS reports running?

There are five steps you must complete to get Kerberos configured for native mode SSRS connecting to a SQL Server database:

• Create an SPN for the SSRS server. The SPN should look like this for a server named SSRS1 and service account SSRSservice1:

Setspn -S http/SSRS1.mydomain.local mydomain\SSRSservice1

It might also be a good idea to set one up for the server name without the domain as well. If the SSRS service is using a local account, then no SPN needs to be created. Instead, it uses the HOST SPNs that should already be in place.

• Set the Delegation property for the SSRS service account in AD to Trust this user for delegation to any service (Kerberos only). The Delegation page will not show up in Active Directory Users and Computers until the account has an SPN filled in. Note that this is not the setting used for SharePoint integrated mode or for the new Power BI Report Server. These steps are for native mode pointing to a SQL Server database only.

• Make sure that the Account is sensitive and cannot be delegated property is disabled.

• Add a value to the SSRS RSReportServer.config file on the SSRS server.

• Once everything is complete, restart the SSRS service

Microsoft has made a tool available, Kerberos Configuration Manager, that can help troubleshoot SPNs when setting up Kerberos for SSRS.

11. How do I set up Kerberos for linked servers?

Set up SPNs for both instances. Set delegation to Trust this user for delegation to any service on the service account being used on the instance where the linked server is set up. Basically, the server that must pass the ticket along must be set up for delegation.

12. Any other surprises I need to know about?

If there are any time discrepancies on the servers, Kerberos will not work. For example, if you set up a test domain in virtual machines on your laptop, make sure that the domain controller is in the same time zone as the laptop.

Another problem could be that the SSRS server needs the .NET Framework 3.5 feature installed. I’ve seen situations where everything was configured properly, and reports using SQL Server authentication worked, but Kerberos authentication for SSRS just wouldn’t work. Installing .NET Framework 3.5 and restarting the SSRS service fixed the issue.

Instead of using dynamic ports for your instances, be sure to set static ports to make sure that the port doesn’t change and break the SPN.

When testing, you may need to clear out existing tickets with the klist purge command and log out and back in. Sometime, just waiting a few minutes is required for a change to be replicated in AD is needed. You also may need to reboot the server after adding an SPN.

This article focused on native mode SSRS connecting to a SQL Server database in the same domain. You will have to do some additional research to understand the settings required to enable Kerberos for other applications, services, and situations.

The post Questions About Kerberos and SQL Server That You Were Too Shy to Ask appeared first on Simple Talk.

from Simple Talk https://ift.tt/2PsEeUE
via