March 9, 2012 at 3:54 pm
I created a new domain account and added it to all the SQL local groups on the server (the ones that get created when installing SQL). Then I made SQL run under this new domain user in SQL configuration manager, and restarted SQL. SQL restarted ok, and is up and running, but now when I try to connect to it from SQL management studio, I get an error: Cannot generate SSPI contect
any thoughts? I'm assuming that the domain account now running SQL doesn't have permissions to do something, but I'm not sure what it's missing.
thanks again!
March 9, 2012 at 4:48 pm
You cannot use the MSSQLSERVER service account to login on SSMS unless that domain account has been added to logins on the instance.
Jared
CE - Microsoft
March 9, 2012 at 4:53 pm
Sspi context problem is due to failure to register the SPN. See my previous post for the domain rights needed to do that. Also as someone else said, computer acct needs to be trusted for delegation
March 9, 2012 at 4:54 pm
NJ-DBA (3/9/2012)
Sspi context problem is due to failure to register the SPN. See my previous post for the domain rights needed to do that. Also as someone else said, computer acct needs to be trusted for delegation
Log on as sa and check the errorlog. On startup youll see the error about failing to register the spn
March 12, 2012 at 1:53 pm
Thanks again. I added the SPN's, but when I try to log on, I get the "Cannot generate SSPI context" error on my SQL client. On the server's event mananger, there is an error "Unknown username or bad password"
To check my SPN's, I do the following for the domain acount that is now running my SQL instance: (the domain account is "domain\SQLservice"). I added 6 SPN's, mostly because I wasn't sure of the port number, it listed 2 different port numbers, so I added one for port number 2433, the standard 1433, and one for no port number, each with the FQDN. Then I added one for just the machine name, HR01, then ones for the named instance of the SQL, HR01\SQL2008. These are my SPN listings:
C:\Program Files\Support Tools>SETSPN -L domain\SQLservice
Registered ServicePrincipalNames for CN=SQLservice,OU=SQL Access,OU=Groups,DC=xxx,DC=xxx,DC=com:
MSSQLSvc/HR01.xxx.xxx.com:2433
MSSQLSvc/HR01.xxx.xxx.com:1433
MSSQLSvc/HR01.xxx.xxx.com
MSSQLSvc/HR01
MSSQLSvc/HR01\SQL2008:1433
MSSQLSvc/HR01\SQL2008:2433
MSSQLSvc/HR01\SQL2008
C:\Program Files\Support Tools>
Still can't connect as myself, have verified that my username "domain\ericb1" is listed in SQL and is the owner of at least 1 database. Is there something wrong with my SPN's Or do I need to re-start SQL after adding them? thanks again!
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply