Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Unable to generate SSPI context Expand / Collapse
Author
Message
Posted Thursday, February 9, 2012 7:48 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 14, 2014 7:57 AM
Points: 1,321, Visits: 729
I've setup a new SQL server and am unable to connect to it with SSMS from any machine other than the local machine. When I do, I get an "Unable to generate SSPI context" error.

Googling this tells me the problem is usually that SQL was unable to register a server principle name. However, I do not believe this is the case for a couple reasons:

1. I'm running SQL under a domain account that all our other SQL Servers use, so I know that account should have permission to register an SPN.

2. The SQL Server log contains this entry:

"The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/<machinename>.<FQDN>:<instancename> ] for the SQL Server service."

On shutdown, there is a similar message saying the SPN was successfully unregistered.

3. setspn <serviceaccount> lists:

MSSQLSvc/<machinename>.<FQDN>:<instancename>

4. There are no entries in the Windows event logs regarding SPNs.

5. If I shutdown SQL, setspn no longer lists the entry for this server. When I restart SQL, it lists it again.

So all those things tell me SPNs are being registered OK. I have never changed the account SQL runs under, so I doubt there are duplicate SPNs.

Server OS is Windows Server 2008 R2, SQL is 2008 R2 Standard, SP1. There is only a named instance of SQL installed - no default instance.

Anyone have any ideas?

Shaun
Post #1249714
Posted Thursday, February 9, 2012 7:58 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 5:56 AM
Points: 2,013, Visits: 1,584
Few Questions:
• Is it in Windows Authentication Mode or Mixed Mode?
• With which account you are trying to login? The same you used for installation or different?
• How did you configure the SQL services?
• Is Browser service running? (you said, it’s named instance so browser service should be up & running)


Dev

Devendra Shirbad | BIG Data Architect / DBA | Ex-Microsoft CSS (SQL 3T) | Open Network for Data Professionals...
LinkedIn: http://www.linkedin.com/in/devendrashirbad
Post #1249722
Posted Thursday, February 9, 2012 8:31 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 14, 2014 7:57 AM
Points: 1,321, Visits: 729
- Mixed mode

- Trying to log in with a different account than I installed with. Logging in with my network account, which is defined as sysadmin in sql server.

- Not sure what you mean. I configured everything via the install program, then used SQL Configuration manager later to add a trace flag (3226) and restarted the service for it to take effect.

- Browser service is running.

Based on your question, I tried connecting using the same account I installed it with and got the same error.
Post #1249756
Posted Thursday, February 9, 2012 8:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 5:56 AM
Points: 2,013, Visits: 1,584
Did you try ‘sa’ login? I just want to make sure that you can make one successful login. Then we can focus on login specific issue.

Dev

Devendra Shirbad | BIG Data Architect / DBA | Ex-Microsoft CSS (SQL 3T) | Open Network for Data Professionals...
LinkedIn: http://www.linkedin.com/in/devendrashirbad
Post #1249761
Posted Thursday, February 9, 2012 8:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 14, 2014 7:57 AM
Points: 1,321, Visits: 729
I can log in on the server itself via Windows authentication, no problem. Restored databases and everything. Server is up and running. Just can't connect from another PC. I AM able to connect from another PC using SQL authentication.. Just not Windows authentication.

Post #1249765
Posted Thursday, February 9, 2012 9:02 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 5:56 AM
Points: 2,013, Visits: 1,584
Are Server & PC on different domains?

Dev

Devendra Shirbad | BIG Data Architect / DBA | Ex-Microsoft CSS (SQL 3T) | Open Network for Data Professionals...
LinkedIn: http://www.linkedin.com/in/devendrashirbad
Post #1249775
Posted Thursday, February 9, 2012 9:07 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 14, 2014 7:57 AM
Points: 1,321, Visits: 729
Nope. Same domains.
Post #1249777
Posted Thursday, February 9, 2012 9:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 5:56 AM
Points: 2,013, Visits: 1,584
Search for 'Verify the domain' in following...

How to troubleshoot the "Cannot generate SSPI context" error message
http://support.microsoft.com/kb/811889


Dev

Devendra Shirbad | BIG Data Architect / DBA | Ex-Microsoft CSS (SQL 3T) | Open Network for Data Professionals...
LinkedIn: http://www.linkedin.com/in/devendrashirbad
Post #1249779
Posted Thursday, February 9, 2012 9:51 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 14, 2014 7:57 AM
Points: 1,321, Visits: 729
Yeah, been through all that:

1. Verified.
2. N/A
3. Verified.
4. N/A
5. Verified.
6. Verified.
7. N/A
Post #1249811
Posted Thursday, February 9, 2012 9:59 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 14, 2014 7:57 AM
Points: 1,321, Visits: 729
@VickyHarp on Twitter #sqlhelp found the problem - the clock on the server was wrong. It was 7 hours behind the network time. Changed it and all is working now!!

Thanks for the help!!
Post #1249820
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse