• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2104
  • Last Modified:

SQL 2005 SQLCMD and SSPI context

Hi

Environment:
Windows 2003 (native) AD
Windows 2003 R2 ENT, x64 SP2
SQL 2005 ENT x64

The issue I have just recently been getting is the following when running an SQLCMD via SSMS:

HResult 0x80090322, Level 16, State 1
SQL Network Interfaces: The target principal name is incorrect.
Sqlcmd: Error: Microsoft SQL Native Client : Cannot generate SSPI context.

I have run the command also connected as "SA" (which should bypass the use of win integrated auth and SSPI?) and I still get the same issue...

I can also run the command fine on the target server and also via command line???

I have checked the SPN on the domain controller's and the host and FQDN of the server seems fine.
I have also run nslookup and checked overall DNS and all seems fine...

I do get an error in the event log:

The kerberos client received a KRB_AP_ERR_MODIFIED error from the server host/DIFFERENT_MACHINENAME.DOMAIN.COM.  The target name used was MSSQLSvc/DIFFERENT_MACHINENAME.DOMAIN.COM:1433. This indicates that the password used to encrypt the kerberos service ticket is different than that on the target server. Commonly, this is due to identically named  machine accounts in the target realm (DOMAIN.COM), and the client realm.   Please contact your system administrator.


I am thinking the best way is to unjoin from domain, delete AD machine account and then re-join domain, or use NETDOM to reset machine account???

Otherwise, SQL seems to be happy with no other problems (so far that I have noticed anyhow..!)
Any pointers greatly appreciated.

Cheers
Bry

0
bryan oakley-wiggins
Asked:
bryan oakley-wiggins
  • 5
  • 3
2 Solutions
 
St3veMaxCommented:
Bry,

It would appear the SPN (Service Principal Name) for SQL Server has been deleted/corrupt.

In a DOS prompt, run "SETSPN" and take a look at the options; this should allow you to regenerate without the need to disjoin/rejoin the domain.

HTH
0
 
St3veMaxCommented:
Ignore my above comment; Didnt read the text properly!

Just wondering - Did you try re-setting the SPN with the Service and Port?
0
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

 
bryan oakley-wigginsSenior Cloud EngineerAuthor Commented:
Hi all - thanks so much for your quicke responses

@St3veMax:
This is the SPN listed for the service account that SQL server is using:

setspn -l SQLSERVICEACCOUNT
Registered ServicePrincipalNames for CN=SQLSERVICEACCOUNT,OU=SQL Agents OU,OU=System Agent OU,O
U=COMPANY OU,OU=Old_DOMAIN,DC=DOMAIN,DC=COM:
    MSSQLSvc/MACHINENAME.DOMAIN.com:1433
    MSSQLSvc/MACHINENAME.DOMAIN.com:1433
    MSSQLSvc/MACHINENAME
    MSSQLSvc/MACHINENAME.DOMAIN.com

Should I try deleting this and then re-creating the SPN for this service account?


@rrjegan17:
I shall have a read through the info and see if this helps the issue.

Again, thanks both for your responses.

Cheers
Bry
0
 
bryan oakley-wigginsSenior Cloud EngineerAuthor Commented:
also, if I check the AUTH method in SQL I get NTLM returned (when using via Windows integrated auth)
***select auth_scheme from sys.dm_exec_connections where session_id=@@spid***
When run as SA, I get SQL (as expected) but still get the SSPI error?

I have the green light to remove from domain and then re-join - Is this quicker to rectify, or would I still possibly be left with the same issue?

Cheers
Bry
0
 
bryan oakley-wigginsSenior Cloud EngineerAuthor Commented:
another addendum:

When I run the statement via <S command line in a separate AD Forest, it runs with no issues...
It's almost definately something within my production AD Forest where the SQL servers reside that is causing this issue.

I will schedule to un-join / re-join to server to Domain and see if this resolves the issue.
I will update the results of this when completed (next Monday now) or indeed, if I manage to fix the issue in the meant ime.

If there are anoy other ideas/pointers, I would be most appreciative of these :-)

Cheers
Bry
0
 
St3veMaxCommented:
Bry,

Looking at your SPN's they look OK to me. Could be worth deleting and re-creating unless you've resolved by remove/add to the domain.

Do you use Kerberos on your domain anywhere?

Steve
0
 
bryan oakley-wigginsSenior Cloud EngineerAuthor Commented:
Hi St3veMax:

Thanks.
I managed to get all working fine in the end. I did initially try deleting and then recreating the SPN's but this didn't fix the issue per se.

What I did was the following (worked for me but may work for others also)

Backed up my SQL DB's
Changed SQL svc account to local systems
rebooted the machine (for graceful SQL shutdown)
deleted any SPN references with domain user account provisioned for SQL service
removed server from domain
deleted machine account from AD and allowed replication to ocurr
purged any kerberos tickets for this machine
re-joined machine to domain
changed SQL service account back to domain user account
checked / created SPN's for this account against MSSQLSvc/HOST.DOMAIN:1433
tested and et viola, it was working...


I am happy to split the points in the following manner as both responses were in the right area.

@ St3veMax: 300 (as I had more responses from St3veMax: )
@ rrjegan17: 200

If no objections, I will accept multiple solutions.
Thanks again for all responses, very much appreciated :-)

Cheers
Bry
0
 
bryan oakley-wigginsSenior Cloud EngineerAuthor Commented:
@ St3veMax: Thanks for your responses, much appreciated.
@ rrjegan17: Thanks for your pointer, again much appreciated.

Cheers
Bry
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell┬« is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now