SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQLCMD -S servername\instancename Error


SQLCMD -S servername\instancename Error

Author
Message
Ignacio A. Salom Rangel
Ignacio A. Salom Rangel
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10284 Visits: 1439
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.




My blog

K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (43K reputation)

Group: Moderators
Points: 43472 Visits: 1917
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
Ignacio A. Salom Rangel
Ignacio A. Salom Rangel
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10284 Visits: 1439
Thanks for your reply. I will test it and let you know.




My blog

Ignacio A. Salom Rangel
Ignacio A. Salom Rangel
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10284 Visits: 1439
It works! Thanks! The problem is solved!




My blog

K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (43K reputation)

Group: Moderators
Points: 43472 Visits: 1917
Did the SPN have to be corrected?

K. Brian Kelley
@‌kbriankelley
Ignacio A. Salom Rangel
Ignacio A. Salom Rangel
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10284 Visits: 1439
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.




My blog

K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (43K reputation)

Group: Moderators
Points: 43472 Visits: 1917
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
Ignacio A. Salom Rangel
Ignacio A. Salom Rangel
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10284 Visits: 1439
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.




My blog

K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (43K reputation)

Group: Moderators
Points: 43472 Visits: 1917
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
Ignacio A. Salom Rangel
Ignacio A. Salom Rangel
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10284 Visits: 1439
Thanks for your reply. I thought that giving the domain account the "write service principalname" permission will allow that account to create an SPN.




My blog

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search