SQLCMD -S servername\instancename Error
-
After restarting one of the instances of my sql2005 cluster on single user mode I can't connect to the instance using "SQLCMD -S servername\instancename Error". I get the following error: "SQL Network Interfaces: The target principal name is incorrect. Sqlcmd: Error: Microsoft SQL Native Client : Cannot generate SSPI context". I don't know what can I do to restore the master database on this node. Any ideas are welcome. Thanks in advance.
-
Did an SPN get put in place in Active Directory to allow Kerberos authentication? Can you connect with a SQL Server based login like sa?
K. Brian Kelley
@kbriankelley -
Thanks for your reply. I will test it and let you know.
-
It works! Thanks! The problem is solved!
-
Did the SPN have to be corrected?
K. Brian Kelley
@kbriankelley -
I connected with the SQL account, I think that the SPN is still an issue. I will check it tomorrow first thing in the morning (It is 00:33!). You save me! Thanks a lot. When I check the SPN I will let you know.
-
If the SPN needs fixing, verify that you have set the TCP port set for static and not dynamic. If it's set for dynamic and for some reason couldn't grab the previous TCP port, it would change, since it's a named instance. This, of course, would automatically break the SPN since that keys on port.
K. Brian Kelley
@kbriankelley -
Hi Brian,
We start the SQL service with a domain account. This account does not have the "write service principalname" permission. That is why there is no SPN created in the active directory. The connections to the SQL service are made using the NTLM protocol. I have been testing on the testcluster and if I use a domain account to start the sql service then the SPN is created and I can connect using the kerberos protocol. The kerberos protocol is disabled on the production server (I don't know the reason). I have to check if the kerberos protocol is enabled on the other SQL servers.
I will keep in mind your recommendations about setting the TCP port for static. Something that is confusing me is that I thing that the SPN is automatically registered each time I restart the SQL service, so why do I have to set the tcp poort to static?
Thanks for your help.
-
Ignacio A. Salom Rangel (8/18/2008)
Hi Brian,We start the SQL service with a domain account. This account does not have the "write service principalname" permission. That is why there is no SPN created in the active directory. The connections to the SQL service are made using the NTLM protocol. I have been testing on the testcluster and if I use a domain account to start the sql service then the SPN is created and I can connect using the kerberos protocol. The kerberos protocol is disabled on the production server (I don't know the reason). I have to check if the kerberos protocol is enabled on the other SQL servers.
I will keep in mind your recommendations about setting the TCP port for static. Something that is confusing me is that I thing that the SPN is automatically registered each time I restart the SQL service, so why do I have to set the tcp poort to static?
Thank for your help.
It is only set automatically if SQL Server is running under something that comes in as the computer account (System in 2000 and Network Service in 2003) or a Domain Admin account. If it's a regular domain user account, it doesn't have rights to create the SPN. And running as either of the other two accounts is considered a violation of best practice. The first doesn't work on a cluster. The second is just an absolute security no-no.
K. Brian Kelley
@kbriankelley -
Thanks for your reply. I thought that giving the domain account the "write service principalname" permission will allow that account to create an SPN.
-
you can generate the SPN's manually using
SETSPN.EXE
syntax is
setspn -A ServiceClass/Host: Port AccountName
so for sql instance is
setspn -A MSSQLSvc/mysqlserver.domain.co.uk:staticsqlportnumber domain\sqlserviceuseraccount
use setspn -L domain\sqlserviceuseraccount
to list the SPN's
😎
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
Hi Brian,
After testing giving the "Write Service PrincipalName" permission to a normal domain user (the account that starts the MSSQL service), I found out that it generates the SPN dynamically. I am planning to implement this on the production enviroment soon. Once again thank you for your help.
-
Perry, thanks for your reply. You are right, I can use the setspn command to create the SPN manually. It is a good way to do it, but to give the "write service principal name" permission to the domain account is more flexible and that's why I decide to choose that approach.
-
Hi, just to clarify the Write/Read SPN permission on Active Directory is not recommended on clusters as for the active directory replication delay can make you have some problems as the registration need to be done before the service is brought up, and when the cluster node fail over the SPN is gonna get deleted and re-added, so for clusters this is not recommended.
-
I am currently getting the error in sql server logs:
2011-04-19 08:59:10 - ! [298] SQLServer Error: 802, Cannot generate SSPI context [SQLSTATE HY000]
2011-04-19 08:59:10 - ! [382] Logon to server '(local)' failed (JobManager)
2011-04-19 08:59:10 - ! [298] SQLServer Error: 802, SQL Network Interfaces: The target principal name is incorrect. [SQLSTATE HY000]
2011-04-19 08:59:10 - ! [298] SQLServer Error: 802, Cannot generate SSPI context [SQLSTATE HY000]
2011-04-19 08:59:10 - ! [382] Logon to server '(local)' failed (ConnUpdateJobActivity_NextScheduledRunDate)
2011-04-19 08:59:10 - ! [298] SQLServer Error: 802, SQL Network Interfaces: The target principal name is incorrect. [SQLSTATE HY000]
2011-04-19 08:59:10 - ! [298] SQLServer Error: 802, Cannot generate SSPI context [SQLSTATE HY000]
2011-04-19 08:59:10 - ! [382] Logon to server '(local)' failed (ConnAttemptCachableOp)
2011-04-19 08:59:10 - ! [298] SQLServer Error: 802, SQL Network Interfaces: The target principal name is incorrect. [SQLSTATE HY000]
2011-04-19 08:59:10 - ! [298] SQLServer Error: 802, Cannot generate SSPI context [SQLSTATE HY000]
2011-04-19 08:59:10 - ! [382] Logon to server '(local)' failed (ConnAttemptCachableOp)
2011-04-19 09:18:14 - ! [298] SQLServer Error: 802, SQL Network Interfaces: The target principal name is incorrect. [SQLSTATE HY000]
2011-04-19 09:18:14 - ! [298] SQLServer Error: 802, Cannot generate SSPI context [SQLSTATE HY000]
2011-04-19 09:18:14 - ! [382] Logon to server '(local)' failed (SaveAllSchedules)
Thanks
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply