March 27, 2007 at 9:02 am
I get access denied or server doesn't exists error message when try to connect to a SQL Server 2005 named instance remotely. I have sysadmin permissions on SQL Server. I checked in SQL Server Surface Area Configuration tool to make sure Local and Remote connections are allowed. I can connect to this instance fine when I am RDP'ed in to the box. I just can't connect to it remotely. I have a SQL Server 2005 Default instance on the same box and I can remotely connect to that just fine. When I looked at the error log I found the following error message in all SQL Server instances' error logs:
The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098. 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.
I did some research online and removed the Service Principal Name (SPN) for SQL Server service ID from LDAP to no avail. I was trying to use Setspn.exe command and for some reasons it's not working either. Here is what i was trying to run:
setspn.exe -A MSSQLSvc/<Fully Qualified Machine Name>:<Port#> <Service Account Name>
I don't exactly know what the error is about. I just tried couple of suggestions from online and they don't seem to work. Can someone help please??
Thanks in advance,
- Malik
May 9, 2008 at 11:20 am
I'd need more info about your error you receive. If I understand correctly your trying to set a SPN for the MSSQLService account?
If you type:
setspn -L "ServerName"
You should see atlest two entries
1. HOST/"ServerName"
2. HOST/"ServerName.Domain" FQDN
Then type:
setspn -L "DomainUserAccountYourRunningMSSQLServiceUnder"
If you have a SPN registerd in Active Directory for that user running SQL it should be listed as:
MSSQLSVC/"FQDN":"PortNumber"
Do you have those entries?
May 9, 2008 at 2:24 pm
It happens quite often when changing proxy account to another in Services. We had better modify it in GUI.
May 10, 2008 at 6:40 am
The Service Principal Name (SPN) is for Kerberos authentication (the default authentication mechanism in Active Directory). Basically, if the client attempts a connection using a Windows account and all the systems are within the same forest, Kerberos authentication will be attempted first. The SPN is a record within Active Directory that allows the client to verify the server. So the SPN check is important from that regard. However, in most cases if an SPN does not exist with respect to SQL Server, the authentication will drop back to NTLM.
As far as creating an SPN, technically only two sets of accounts can. The first is Domain Admins. The second is the machine account. So if SQL Server is running under a domain user account and the domain user isn't a member of the domain admins group (and it shouldn't be), you'll get that error. That's not a problem, the SPN can be added manually by a domain admin and that's the right path to take.
Now, if an SPN is set but it is incorrect or if there are duplicate SPNs, you can run into problems with Kerberos authentication. Here's how you fix that:
Service Logons Fail Due to Incorrectly Set SPNs
K. Brian Kelley
@kbriankelley
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply