Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQLCMD -S servername\instancename Error Expand / Collapse
Author
Message
Posted Thursday, August 14, 2008 3:29 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, August 14, 2014 8:21 AM
Points: 3,084, Visits: 1,433
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
Post #553157
Posted Thursday, August 14, 2008 3:41 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Monday, August 18, 2014 8:24 AM
Points: 6,634, Visits: 1,871
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #553171
Posted Thursday, August 14, 2008 3:58 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, August 14, 2014 8:21 AM
Points: 3,084, Visits: 1,433
Thanks for your reply. I will test it and let you know.




My blog
Post #553181
Posted Thursday, August 14, 2008 4:22 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, August 14, 2014 8:21 AM
Points: 3,084, Visits: 1,433
It works! Thanks! The problem is solved!




My blog
Post #553191
Posted Thursday, August 14, 2008 4:24 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Monday, August 18, 2014 8:24 AM
Points: 6,634, Visits: 1,871
Did the SPN have to be corrected?


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #553193
Posted Thursday, August 14, 2008 4:33 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, August 14, 2014 8:21 AM
Points: 3,084, Visits: 1,433
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
Post #553197
Posted Thursday, August 14, 2008 5:40 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Monday, August 18, 2014 8:24 AM
Points: 6,634, Visits: 1,871
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #553212
Posted Monday, August 18, 2008 6:01 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, August 14, 2014 8:21 AM
Points: 3,084, Visits: 1,433
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
Post #554231
Posted Monday, August 18, 2008 1:54 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Monday, August 18, 2014 8:24 AM
Points: 6,634, Visits: 1,871
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #554569
Posted Tuesday, August 19, 2008 7:50 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, August 14, 2014 8:21 AM
Points: 3,084, Visits: 1,433
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
Post #555020
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse