What is the use of SPN in SQL Server

SPNs are used by the authentication protocol to determine the account in which a SQL Server instance runs. If the instance account is known, Kerberos authentication can be used to provide mutual authentication by the client and server.

What is SPN in Active Directory?

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.

How do I set up SPN?

1. On the Domain Controller machine, start Active Directory Users and Computers.
2. Select View > Advanced.
3. Under Computers, locate one of the Network Controller machine accounts, and then right-click and select Properties.
4. Select the Security tab and click Advanced.

What is SPN issue?

Service Principal Name troubleshooting is usually a problem when you are setting up the application to support Kerberos. Typically once the application has been up and running for a while there are not too many SPN problems once the application is working unless the Service Principal Names are changing. Summary.

Why is SPN needed?

A service principal name (SPN) is a unique identifier of a service instance. SPNs are used by Kerberos authentication to associate a service instance with a service logon account. This allows a client application to request that the service authenticate an account even if the client does not have the account name.

How manually register SPN in SQL Server?

To register an SPN manually we can use the Microsoft provided Setspn.exe utility. To be able to run this tool and register an SPN you need to be a domain admin or have the appropriate privileges (defined above).

How do I list SPN in SQL Server?

In Command Line enter the following command: setspn -L <Domain\SQL Service Account Name> and press enter. Next, you need to look for registered ServicePrincipalName to ensure that a valid SPN has been created for the SQL Server.

Where are SPN records stored?

If the service runs under a user account, the SPNs are stored in the servicePrincipalName attribute of that account. If the service runs in the LocalSystem account, the SPNs are stored in the servicePrincipalName attribute of the account of the service’s host computer.

How do I add a SPN to my service account?

1. Assign the SPN to the Active Directory account using the setspn command.
2. Repeat this command for any number of SPN to the same account.
3. Generate a keytab file for the user account.

What is azure SPN?

An Azure SPN is a security identity used by user-created applications, services, and automation tools to access specific Azure resources. Think of it as a ‘user identity’ (username and password or certificate) with a specific role, and tightly controlled permissions.

Article first time published on

How do I remove duplicate SPN in Active Directory?

1. Click Start, point to All Programs, click Accessories, right-click Command Prompt, and then click Run as administrator.
2. If the User Account Control dialog box appears, confirm that the action it displays is what you want, and then click Continue.

How do I create an azure SPN?

1. Sign in to your Azure Account through the Azure portal.
2. Select Azure Active Directory.
3. Select App registrations.
4. Select New registration.
5. Name the application. Select a supported account type, which determines who can use the application.

How does Kerberos connect to SQL Server?

1. Create Service Principal Names (SPNs) for the Instance of SQL Server.
2. Test connections are using Kerberos and not NTLM.
3. Configure Delegation permissions for. …
4. Set the Reporting Services Service Account with Impersonate Permissions.

How do I modify SPN?

To change the SPN in ADSI Edit first browse to the user or computer object and open its properties. Find the Service Principal Name property in the list and choose edit. Here it is easy to add, edit, or delete the SPN’s for this Object.

What does Ntlm mean?

Windows New Technology LAN Manager (NTLM) is a suite of security protocols offered by Microsoft to authenticate users’ identity and protect the integrity and confidentiality of their activity.

How do I fix target principal name is incorrect?

1. Deactivate the service “Key Distribution Center”
2. Restart Domain Controller.
3. Start a command-box as administrator and enter the following command: …
4. Restart Domain Controller.
5. Reset the service “Key Distribution Center” to automatic start and start.

What is Kerberos Key?

Kerberos is a computer network security protocol that authenticates service requests between two or more trusted hosts across an untrusted network, like the internet. It uses secret-key cryptography and a trusted third party for authenticating client-server applications and verifying users’ identities.

How do I create a service account in Active Directory?

1. Open Active Directory Users and Computers.
2. Create a new user. …
3. Create a strong password for the account and clear the checkbox so a password change is not required. …
4. Save the new password in Password Boss.

What is service principal name in Azure?

An Azure Active Directory (Azure AD) service principal is the local representation of an application object in a single tenant or directory. ‎It functions as the identity of the application instance. Service principals define who can access the application, and what resources the application can access.

How do I find my Azure portal SPN?

1. Click Azure Active Directory and then click Enterprise applications.
2. Under Application Type, choose All Applications and then click Apply.
3. In the search filter box, type the name of the Azure resource that has managed identity enabled or choose it from the list presented.

What is UPN and SPN?

UPN: An entity performing client requests to some service. Entity may be human or machine. See here. SPN: An entity processing requests for a specific service, e.g., HTTP, LDAP, SSH, etc. Machine only.

How do I connect to Azure service principal?

1. Sign in to Azure AD PowerShell with an admin account.
2. Create a self signed certificate.
3. Load the certificate.
4. Create the Azure Active Directory Application.
5. Create the Service Principal and connect it to the Application.
6. Give the Service Principal Reader access to the current tenant (Get-AzureADDirectoryRole)

What causes duplicate SPN?

In the case of a duplicate SPN, what can happen is that the KDC will generate a service ticket that may be created based on the shared secret of the wrong account. Then, when the client provides that ticket to the service during authentication, the service itself cannot decrypt it and the auth fails.

How do I find duplicates in supernatural?

“SetSPN -x -f” to find duplicates in the entire forest.

How do I get a tenant ID of a service principal?

1. Sign in to the Azure portal.
2. Select Azure Active Directory.
3. Select Properties.
4. Then, scroll down to the Tenant ID field. Your tenant ID will be in the box.

Who can create service principal in Azure?

If you are the admin of your Azure Active Directory, you can grant the user Application administrator role. Then the user will be able to create service principals.