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 ««123»»

Changed SQL Services Acct - "Cannot Generate SSPI Context" Expand / Collapse
Author
Message
Posted Monday, February 21, 2011 12:18 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:22 AM
Points: 2,832, Visits: 8,514
Thanks for the help ... (Big Edit because I think I found the problem... didn't realize there was already a reply)

Here's a follow-up for anyone else with a similar problem

I logged onto a 3rd server, thinking I could now change the SQL services account to the new account (I'm local admin on the box). It accepts the change, but I get an error of "Access Denied" after chaning the account, and SQL Services won't start. Event Log says: Server local connection provider failed to listen on [ \\.\pipe\SQLLocal\MSSQLSERVER ]. Error: 0x5

Worse than before !

I search the error and found this, suggesting a reboot should fix it http://blogs.msdn.com/b/sql_protocols/archive/2006/03/09/546655.aspx

" ... snip ... If the listening named-pipes are not closed properly during the last shutdown of SQL Server, there will be orphan named-pipe handles in the windows kernel file system. Since the listening pipes are opened ACLing to the current user, if you happen to switch SQL Server to run under different account, you will get error 0x05(ERROR_ACCESS_DENIED). ... snip ...

The solution is to either switch back to previous account and make a clean shutdown of SQL Server, or reboot the machine. In most cases, I feel the later is faster.... snip"

... so far, so good:



Post #1067256
Posted Monday, February 21, 2011 12:40 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, October 19, 2014 10:29 AM
Points: 1,880, Visits: 3,460
Did you use SQL Server Configuration Manager to change the account? Is the new account member of any local Windows security groups? What errors are logged for SQL Server during startup in the event log and SQL Error log (<SQL Server installation path>\Data\Log\)?
Post #1067271
Posted Monday, February 21, 2011 12:58 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:22 AM
Points: 2,832, Visits: 8,514
Nils, Sorry for the confusion and thanks for all your help ... see my corrected & edited post above.


Post #1067280
Posted Monday, February 21, 2011 1:29 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, October 19, 2014 10:29 AM
Points: 1,880, Visits: 3,460
Hope the reboot will fix the problem
Post #1067300
Posted Thursday, January 17, 2013 7:05 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, August 8, 2014 7:13 AM
Points: 662, Visits: 464
I am currently sitting with the same issue on a SQL 2008 R2 server running on a Server 2003 R2 SP2 OS, I had to install the setspn.exe from http://www.microsoft.com/en-us/download/details.aspx?id=4461
I got the server registered by running setspn -R MYSERVERNAME in command prompt.
I will let you know if this resolved it after the server gets restarted later.
Post #1408407
Posted Wednesday, March 20, 2013 11:12 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 17, 2014 12:20 PM
Points: 373, Visits: 909
The user account used for SQL Services has to have local admin privileges and the Log on as a Service right.
Post #1433354
Posted Thursday, March 21, 2013 8:43 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 11:56 AM
Points: 880, Visits: 2,435
I usually have to use:
setspn -A MSSQLSvc/Servername.Domain.TopLevelInternalDomain:PortUsually1433 Domain\ADServiceAccount
Post #1433827
Posted Thursday, July 10, 2014 10:30 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 8:20 AM
Points: 58, Visits: 330
I have tried all these fixes an none worked....any other suggestions?

changed service account
drop server name/re-added.

Any other suggestions. In our environment this happened out of the blue....a user called and could not connect, i am able to connect with SQL authentication.

Post #1591239
Posted Thursday, July 10, 2014 12:23 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 11:56 AM
Points: 880, Visits: 2,435
MMartin1 (3/20/2013)
The user account used for SQL Services has to have local admin privileges


Not true - the user account used for SQL Services does not (and should not, in any high security installation) have local admin rights, much less domain admin rights.

The user account does need permissions to a variety of directories for SQL Server files (sometimes it's easier to use
icacls * /reset /t

to reset security on entire subdirectory trees).

There are some Group Policy permissions that are required or recommended; the set I use includes some for proxy users:
gpedit.msc
Computer Configuration
Windows Settings
Security Settings
Local Policies
User Rights Assignments
Act as part of the operating system
Adjust memory quotas for a process
Bypass traverse checking -- proxy user use, I think
Lock pages in memory -- a subject of some debate
Log on as a service
Perform volume maintenance tasks -- required for instant file initialization
Replace a process level token -- proxy user use, I think

Post #1591280
Posted Thursday, July 10, 2014 12:26 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 8:20 AM
Points: 58, Visits: 330
Giving the Service account 'domain admin' privileges for a brief time allowed the SPN error to correct itself or register properly.

Thank you
Post #1591282
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse