October 19, 2012 at 10:21 am
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!
October 21, 2012 at 10:58 am
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
October 21, 2012 at 7:25 pm
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.
October 22, 2012 at 2:36 am
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"
October 31, 2012 at 11:00 pm
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?
November 1, 2012 at 2:06 pm
Good thought, but the time was good, and both systems were synching with the same time server
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply