Cannot Generate SSPI Context with AD account

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » Security (SS2K8) » Cannot Generate SSPI Context with AD account

Cannot Generate SSPI Context with AD account

Rate Topic

Display Mode

Topic Options

Author

Message

ben.bizzell

Posted Friday, October 19, 2012 10:21 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:23 AM
Points: 378, Visits: 484

I have several SQL 2K8R2 servers in "the cloud"; most work correctly. One is giving me problems.

On that one (which I will call [ProblemServer]):
I am able to RDP to [ProblemServer] using my AD account and everything works as expected.
I am able to connect to that server using integrated authentication from other servers in the cloud
From my local system, I can connect to [ProblemServer] using SQL authentication
When I try to connect to [ProblemServer] using SSMS from my local system, I get an error "Cannot generate SSPI context"
When I run SQLCMD on [ProblemServer] from my local system, I get a response of
"SQL Network Interfaces: The target principal name is incorrect.
Sqlcmd: Error: Microsoft SQL Native Client : Cannot generate SSPI context."

SetSPN showed everything appearing to be correct; eventually we removed and re-created all SPN entries; did not fix the problem
We had network watch [ProblemServer], it saw my connections from my local system coming through the firewalls and hitting [ProblemServer]
We checked the basics: Remote connections are enabled, mixed mode authentication.

I am having difficulty trying to localize the issue;
Windows authentication works on the system: I can RDP to it and I can run SSMS locally on the system (when connected via RDP) using domain authentication
Remote integrated authentication works: I can use windows authentication from another cloud server
Network connections work: I can connect from my local system using SQL authentication
the ONLY thing that is not working is Domain Authentication when crossing from office network to cloud. Of course, that is the most critical part of the whole setup.

Any information you can provide will be appreciated.

Thanks!

Post #1374908

opc.three

Posted Sunday, October 21, 2012 10:58 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 5:41 PM
Points: 6,724, Visits: 11,769

You should see an entry in the SQL Server Error Log when an integrated connection fails (as long as you're still logging failed login attempts). The error number, severity and state may explain the issue.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1375183

ben.bizzell

Posted Sunday, October 21, 2012 7:25 PM

Old Hand

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:23 AM
Points: 378, Visits: 484

That was another strange thing, SQL server log did not show any attempt to connect, but the firewall said the attempt was getting through. We never DID determine what was causing the specific issue. After several SQL Service restarts and rebooting the server itself multiple times, we eventually resolved the issue by removing the server from the domain, then re-adding it.

Not sure what CAUSED the problem, but that is how we RESOLVED the problem.

From here on out, simply a matter of curiousity and future avoidance.

Thanks for your interest and reply.

Post #1375229

Joie Andrew

Posted Monday, October 22, 2012 2:36 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 3:14 AM
Points: 548, Visits: 1,026

I see this at times with servers that I manage that are in another AD forest than where I am connecting from. I have researched but have been unable to find a fix for it yet.

In my environment as a workaround I connect to the server first through a network drive (ex: \\<servername>\<sharename>). Once that network connection comes up showing me the shared folder I connected to I have confirmation that an authentication attempt succeeded. I then connect to SQL using SSMS, works fine.

Joie Andrew
"Since 1982"

Post #1375305

Superdoc

Posted Wednesday, October 31, 2012 11:00 PM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 8:39 PM
Points: 21, Visits: 563

I have run into this problem when the users had changed date/time while testing application and started getting "Cannot Generate SSPI context" using AD account, would this be the same case with you?

Post #1379616

ben.bizzell

Posted Thursday, November 01, 2012 2:06 PM

Old Hand

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:23 AM
Points: 378, Visits: 484

Good thought, but the time was good, and both systems were synching with the same time server

Post #1380023

« Prev Topic | Next Topic »

Permissions