September 30, 2006 at 8:42 am
More important, we have an item of concern regarding accounts with least permissions and SQL server. We are now running SQL with an account with low/least permissions. Everything seems to work just fine and we've solved the problem with Full Text Indexing we ran into last Spring. However we have problems with SQL and Active Directory. When we try to refresh Active Directory data we get the following error.
"Error 14303: Stored procedure 'sp_ActiveDirectory_SCP' failed to access registry key."
With some tinkering we eventually got to this error...
"SQL Server Enterprise Manager could not refresh the attributes of the server '(LOCAL)' in Active Directory.
Error 22039: xpadsi.exe failed."
Currently we are back to and stuck at the first error. The problem goes away if we revet to the adminsitrator account.
We have not deleted the active directory infotmation and readded it. Given that we can't refresh it we were concerned we wouldn't be able to recreate the active directory entry.
We did manually recheck all of the permissions. We used SQL Enterprise Manager to change service accounts, which should have done automatically set all needed permissions, but we verified it anyway just to be safe.
For now we are leaving this alone and staying with the new service account with least permissions, but we are concerned, particularly when it comes to applying patches to SQL server or upgrading to 2005. We've spent a few hours searching the web for a solution and although there are many related articles we could not find a fix.
When we are ready to upgrade to SQL 2005, we may revert to an administrative account just to be safe, as we are not sure what the implications of the current error will be.
Do you have any thoughts or suggestions?
Many thanks,
Ernie
October 3, 2006 at 1:01 pm
I've made some progress, but my problem is not solved. I found a message thread that suggested adding Full control rights to the following registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\SQLServerSCP
I'm not sure what this key does, but I tried it and it got me past the first error. Now, I'm seeing the second error:
SQL Server Enterprise Manager could not refresh the attributes of the server '(LOCAL)' in Active Directory.
Error 22039: xpadsi.exe failed.
So I'm still stuck. I'm just starting to focus on the second error.
Any help would be greatly appreciated.
thanks,
Ernie Noa
October 3, 2006 at 1:38 pm
I've got it fixed, but I'm not sure why it works. I have some thoughts, but I am unclear. I would love to have a better understanding of why this worked.
Most of the information is here: http://support.microsoft.com/kb/q299362/
and some of it is here: http://www.developersdex.com/sql/message.asp?p=1870&ID=%3CfklPf.19205%24NS6.14560%40newssvr30.news.prodigy.com%3E
In short, the latter article suggests giving permissions to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\SQLServerSCP for the service account. I did, and it fixed the first error: Error 14303: Stored procedure 'sp_ActiveDirectory_SCP' failed to access registry key.
but generated a second error: SQL Server Enterprise Manager could not refresh the attributes of the server '(LOCAL)' in Active Directory.
Error 22039: xpadsi.exe failed.
The second error was resolved by changing MSSQLActiveDirectory helper to run under either the Local System account or a domain admin account. I've got it running under a domain admin account for now.
So... I have deduced/assumed, I didn't have proper permissions to access the necessary registrty key for the service account, then the MSSQLAD needed to have elevated permissions beyond what I had given the service account.
I followed Microsofts recommendations for changing the service account with in Enterprise manager and double check the procedure with there guide explaining how to manually make the switch.
So why were these other steps needed? Are my assumptions correct?
I'd love to hear your thoughts on this.
Many thanks,
Ernie Noa
IT Manager
ISA
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply