[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL 2005 SQLCMD and SSPI context

Posted on 2009-05-01
9
Medium Priority
?
2,057 Views
Last Modified: 2012-05-06
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
Comment
Question by:bryan oakley-wiggins
  • 5
  • 3
9 Comments
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 800 total points
ID: 24277154
0
 
LVL 13

Expert Comment

by:St3veMax
ID: 24277159
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
 
LVL 13

Expert Comment

by:St3veMax
ID: 24277162
Ignore my above comment; Didnt read the text properly!

Just wondering - Did you try re-setting the SPN with the Service and Port?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:bryan oakley-wiggins
ID: 24277450
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
 

Author Comment

by:bryan oakley-wiggins
ID: 24277606
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
 

Author Comment

by:bryan oakley-wiggins
ID: 24277722
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
 
LVL 13

Accepted Solution

by:
St3veMax earned 1200 total points
ID: 24279257
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
 

Author Comment

by:bryan oakley-wiggins
ID: 24302146
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
 

Author Closing Comment

by:bryan oakley-wiggins
ID: 31576792
@ St3veMax: Thanks for your responses, much appreciated.
@ rrjegan17: Thanks for your pointer, again much appreciated.

Cheers
Bry
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Uncontrolled local administrators groups within any organization pose a huge security risk. Because these groups are locally managed it becomes difficult to audit and maintain them.
In the absence of a fully-fledged GPO Management product like AGPM, the script in this article will provide you with a simple way to watch the domain (or a select OU) for GPOs changes and automatically take backups when policies are added, removed o…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …

865 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question