Today I had a client contact me asking to help them figure out why they could not connect remotely to an instance of SQL Server 2008 R2 using SQL Server Management Studio (SSMS) on a Windows 7 workstation. The error being displayed when trying to connect is:
Access is available via SSMS locally on the server. No errors in event viewer on the workstation or the server. Hmm needless to say the client was not sure where to start and look to rectify this issue. This error message does not give much for you go on to determine how to resolve the issue.
A good point of call is the SQL Server Error log located on the server. This does not show anything out of the ordinary at first glance. If you currently have a job that re-cycles the error logs then find the first error log from the last time the Instance of SQL Server 2008 R2 was restarted. Upon start up there are many messages that inform you on how the instance of SQL Server has started up. Normally this will be without any errors. However in terms of this issue and this post this message stood out
The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
This error message is a problem for you and stopping you connecting remotely to your instance of SQL Server 2008 R2 as TCP/IP is the protocol that is enabled.
In this case this issue was caused by the SPN was not created by a Domain Admin account. Now we need to check the SPN’s that have been created for the instance. This is able to be done by running the following command
- Setspn –L [ServerName]
Now that we have the list of SPN’s associated with the server to resolve the issue we need to delete the SPN’s associated with the instance of SQL Server 2008 R2. This can be done using the following commands:
- Setspn –D MSSQLSvc/[FQN – ServerName]: [SQL Port]
- Setspn –D MSSQLSvc/[FQN – ServerName]
Now that these have been removed we need to re-create the SPN’s for the instance with a Domain Admin account. This can be done using the following commands:
- Setspn –A MSSQLSvc/[FQN – ServerName]: [SQL Port] [FQN – Service Account]
- Setspn –A MSSQLSvc/[FQN – ServerName]: [FQN – Service Account]
The last thing to do is to now restart your instance of SQL Server and you can now connect remotely.
I hope this helps.
Leave a Reply