SQL 2005 SQLCMD and SSPI context

Posted on 2009-05-01
Last Modified: 2012-05-06

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.


Question by:BryanOakley
    LVL 57

    Assisted Solution

    by:Raja Jegan R
    LVL 13

    Expert Comment


    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.

    LVL 13

    Expert Comment

    Ignore my above comment; Didnt read the text properly!

    Just wondering - Did you try re-setting the SPN with the Service and Port?

    Author Comment

    Hi all - thanks so much for your quicke responses

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

    Registered ServicePrincipalNames for CN=SQLSERVICEACCOUNT,OU=SQL Agents OU,OU=System Agent OU,O

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

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

    Again, thanks both for your responses.


    Author Comment

    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?


    Author Comment

    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 :-)

    LVL 13

    Accepted Solution


    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?


    Author Comment

    Hi St3veMax:

    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 :-)


    Author Closing Comment

    @ St3veMax: Thanks for your responses, much appreciated.
    @ rrjegan17: Thanks for your pointer, again much appreciated.


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    ADCs have gained traction within the last decade, largely due to increased demand for legacy load balancing appliances to handle more advanced application delivery requirements and improve application performance.
    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 from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…
    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…

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now