SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Changed SQL Services Acct - "Cannot Generate SSPI Context"


Changed SQL Services Acct - "Cannot Generate SSPI Context"

Author
Message
homebrew01
homebrew01
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4799 Visits: 9108
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:



Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2433 Visits: 3575
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\)?
homebrew01
homebrew01
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4799 Visits: 9108
Nils, Sorry for the confusion and thanks for all your help ... see my corrected & edited post above.



Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2433 Visits: 3575
Hope the reboot will fix the problem :-)
Fox87
Fox87
Say Hey Kid
Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)

Group: General Forum Members
Points: 708 Visits: 595
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.
MMartin1
MMartin1
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2745 Visits: 2031
The user account used for SQL Services has to have local admin privileges and the Log on as a Service right.

----------------------------------------------------
How to post forum questions to get the best help
Nadrek
Nadrek
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1870 Visits: 2726
I usually have to use:
setspn -A MSSQLSvc/Servername.Domain.TopLevelInternalDomainTongueortUsually1433 Domain\ADServiceAccount
TryingToLearn
TryingToLearn
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 456
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.
Nadrek
Nadrek
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1870 Visits: 2726
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


TryingToLearn
TryingToLearn
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 456
Giving the Service account 'domain admin' privileges for a brief time allowed the SPN error to correct itself or register properly.

Thank you
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search