Microsoft SQL Server requires Service Principal Name (SPN) records to be set up for authentication because of the way it interacts with Windows Authentication using Kerberos protocol. When a user attempts to connect to the SQL Server instance, the client’s operating system requests a Kerberos ticket from the server, which is then verified by checking if an SPN record has been registered for the service instance. If no valid SPN record exists or is incorrect, the Kerberos protocol fails and authentication fails, resulting in an error. This mechanism ensures that the server can verify the client’s identity and authenticate the connection securely, making SPN records a crucial component of Windows Authentication in SQL Server.
Configuring the SPN records
- Log in to the server’s console with your RT account.
- Download the tool: Microsoft Kerberos Configuration Manager for SQL Server
- Install the tool using the default options.
- Open the File Explorer and navigate to C:\Program Files\Microsoft\Kerberos Configuration Manager for SQL Server. Right-Click on KerberosConfigMgr and select Run as administrator.
- Click on Connect.
- Type localhost in the Server name field and click on Connect.
- Review the SPN records to determine if they are correct. If none exist click the Generate All button. If some exist click the Fix All button. For most new server builds, clicking Fix All is the correct option.
- After clicking Fix All you will be presented with the list of changes. Click Yes and allow the process to finish.
- The process may take 15+ minutes to syncronize across the domain.
- Reboot the SQL server.
- Test the connectivity.