{"id":80438,"date":"2018-08-21T22:35:21","date_gmt":"2018-08-21T22:35:21","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=80438"},"modified":"2022-04-24T20:59:40","modified_gmt":"2022-04-24T20:59:40","slug":"questions-about-kerberos-and-sql-server-that-you-were-too-shy-to-ask","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/questions-about-kerberos-and-sql-server-that-you-were-too-shy-to-ask\/","title":{"rendered":"Questions About Kerberos and SQL Server That You Were Too Shy to Ask"},"content":{"rendered":"

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

    1. What is Kerberos?<\/h2>\n

    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.<\/p>\n

    In a situation in an AD network when Kerberos can\u2019t 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.<\/p>\n

    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\u2019s easy to make a mistake when creating SPNs. When DBAs understand Kerberos, they can help the network administrator troubleshoot issues.<\/p>\n

    2. Why is Kerberos needed for SQL Server?<\/h2>\n

    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.<\/p>\n

    When Kerberos is properly 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\u2019s returned to the SSRS server, and the report is delivered.<\/p>\n

    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.<\/p>\n

    3. Can\u2019t I just avoid using Kerberos?<\/h2>\n

    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\u2019s not the case, SSRS is often installed on its own server for performance reasons. This is that double-hop issue I mentioned earlier.<\/p>\n

    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.<\/p>\n

    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.<\/p>\n

    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 c<\/strong>ommand after connecting to the database. This command is deprecated and requires either sysadmin<\/em> or db_owner<\/em> 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\u2019s probably not suited for a traditional SQL Server database.<\/p>\n

    <\/p>\n

    4. What is a Service Principal Name (SPN)?<\/h2>\n

    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<\/em>. Here is an example:<\/p>\n

    Fully qualified server name: SQL1.mydomain.local<\/strong><\/p>\n

    Port: 1433<\/strong><\/p>\n

    Instance: Default<\/strong><\/p>\n

    Service account: sqlservice1<\/strong><\/p>\n

    There should be two SPNs registered for the SQL Server instance:<\/p>\n

    MSSQLSvc\/SQL1.mydomain.local mydomain\\sqlservice1<\/strong><\/p>\n

    MSSQLSvc\/SQL1.mydomain.local:1433 mydomain\\sqlservice1<\/strong><\/p>\n

    If the SQL Server instance is using a local account instead of an AD account, the computer name<\/em> will be used instead of a service account name. Here is an example:<\/p>\n

    Fully qualified server name: SQL1.mydomain.local<\/strong><\/p>\n

    Port: 1433<\/strong><\/p>\n

    Instance: Default<\/strong><\/p>\n

    Service account: Network Service<\/strong><\/p>\n

    There should be two SPNs registered:<\/p>\n

    MSSQLSvc\/SQL1.mydomain.local mydomain\\SQL1<\/strong><\/p>\n

    MSSQLSvc\/SQL1.mydomain.local:1433 mydomain\\SQL1<\/strong><\/p>\n

    If you have a SQL Server with a named instance, then the SPNs would look like this:<\/p>\n

    Fully qualified server name: SQL1.mydomain.local<\/strong><\/p>\n

    Port: 49827<\/strong><\/p>\n

    Instance: Inst1<\/strong><\/p>\n

    Service account: Network Service<\/strong><\/p>\n

    There should be two SPNs registered:<\/p>\n

    MSSQLSvc\/SQL1.mydomain.local:inst1 mydomain\\SQL1<\/strong><\/p>\n

    MSSQLSvc\/SQL1.mydomain.local:49827 mydomain\\SQL1<\/strong><\/p>\n

    5. Is there a way to see SPNs in AD?<\/h2>\n

    You can see the SPNs in the Active Directory Users and Computers<\/em> utility. The first step is to enable the Advanced Features<\/em> view.<\/p>\n

    <\/p>\n

    After finding the object, search in the Attribute Editor<\/em> for servicePrincipleName<\/em> and click Edit<\/em>. This example shows the SPNs that are registered using the computer account because the default account was used during installation of the instance. It\u2019s also a named instance.<\/p>\n

    \"\"<\/p>\n

     <\/p>\n

    You can also add or remove SPNs from this dialog with the appropriate rights.<\/p>\n

    6. How are SPNs created or changed?<\/h2>\n

    To add or delete an SPN, use the setspn<\/strong> 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\u2019s useful to know what needs to be changed when working with your network administrators.<\/p>\n

    Here are the commands:<\/p>\n

    Setspn -L<\/strong> 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.<\/p>\n

    Setspn -L mydomain\\sql1\r\nSetspn -L mydomain\\sqlservice1<\/pre>\n
    <\/p>\n
    Setspn -D<\/strong> is used to delete an SPN.<\/p>\n
    Setspn -D MSSQLSvc\/SQL1.mydomain.local mydomain\\SQL1\r\nSetspn -D MSSQLSvc\/SQL1.mydomain.local mydomain\\SQL1<\/pre>\n
    <\/p>\n
    Setspn -S<\/strong> 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.<\/p>\n
    Setspn -S MSSQLSvc\/SQL1.mydomain.local:1433 mydomain\\sqlservice1\r\nSetspn -S MSSQLSvc\/SQL1.mydomain.local mydomain\\sqlservice1<\/pre>\n
    <\/p>\n
    Setspn -Q <\/strong>is used to search for existing SPNs registered for a given service. This is helpful when troubleshooting Kerberos issues and you can\u2019t figure out where the SPN is registered.<\/p>\n
    Setspn -Q MSSQLSvc\/sql1.mydomain.local<\/pre>\n
    <\/p>\n
    7. What happens if I change the service account of my SQL Server?<\/h2>\n
    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\u2019ll see this error when attempting to connect via SSMS from another computer.<\/p>\n
    <\/p>\n
    Someone with permission to create and remove SPNs will need to run the setspn -D<\/strong> command to remove the old SPNs and the setspn -S<\/strong> 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.<\/p>\n
    8. How can I tell if I\u2019m connecting with Kerberos authentication?<\/h2>\n
    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<\/strong> permission on the instance, you can run this query:<\/p>\n
    SELECT S.login_name, C.auth_scheme, s.host_name\r\nFROM sys.dm_exec_connections AS C\r\nJOIN sys.dm_exec_sessions AS S ON C.session_id = S.session_id;<\/pre>\n
    <\/p>\n
    You can also use the klist<\/strong> command to view the tickets. In this example, you can see the ticket for the SQL Server in #2.<\/p>\n
    <\/p>\n
    You will see this error message when Kerberos is required, but you are not able to use it.<\/p>\n
    <\/p>\n
    9. I\u2019m connecting from another server, but it\u2019s using NTLM. How did that happen?<\/h2>\n
    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\u2019t have permission to create SPNs, which is typically the case.<\/p>\n
    10. Kerberos authentication is working for the instance, but why aren\u2019t the SSRS reports running?<\/h2>\n
    There are five steps you must complete to get Kerberos configured for native mode SSRS connecting to a SQL Server database:<\/p>\n