July 26, 2011 at 7:21 pm
One of our process access resources on shared network. We use xp_cmdshell through stored proc to access it. Stored proc is executed through a SQL job which runs under service account but once in a while it fails with the message "Access is denied"
I checked the SQL service configuration Manager and found out that MSSQLSERVER is set to LogOn as Local System Account. When I try to change this to service account, the account gets changed but SQL SERVICE AGENT stops.
Any help on this would be much appeicated.
SQL Surfer
July 27, 2011 at 6:28 am
Is there a problem with restarting SQL Agent? (which is required when you change accounts).
July 27, 2011 at 6:39 am
Yes. This is error logged in windows when I reattempt to start agent.
SQLServerAgent could not be started (reason: SQLServerAgent must be able to connect to SQLServer as SysAdmin,
but '(Unknown)' is not a member of the SysAdmin role).
July 27, 2011 at 6:42 am
If you change the SQL Server service account, change that to Agent as well and restart the services.
It should come up and running fine.
Is this SQL Service account, a windows account ?
Cheers,
- Win.
" Have a great day "
July 27, 2011 at 6:46 am
If you are on Win 2003 box, you can give a try ,
Administrative Tools -> Local Security Policy -> New window - >Local Policies -> User Rights Assignment - > Lock pages in memory Properties.
Add your AD user here.
Note : It doesn't work until a reboot
Hope this helps you..
- Win.
Cheers,
- Win.
" Have a great day "
July 27, 2011 at 7:15 am
SQL_Surfer (7/27/2011)
SQLServerAgent could not be started (reason: SQLServerAgent must be able to connect to SQLServer as SysAdmin,but '(Unknown)' is not a member of the SysAdmin role).
Does the new account have permissions on SQL Server at all? What kind of permissions? What server & db roles is it a member of?
As Win asked, it needs to be a windows domain account. You can't use a SQL account to access off-server resources.
July 27, 2011 at 7:16 am
- Win. (7/27/2011)
If you are on Win 2003 box, you can give a try ,Administrative Tools -> Local Security Policy -> New window - >Local Policies -> User Rights Assignment - > Lock pages in memory Properties.
Add your AD user here.
Note : It doesn't work until a reboot
In my experience, an actual reboot is not necessary. However, a restart of the services is.
July 27, 2011 at 7:00 pm
SSC veteran, that worked. Thanks for your help. BTW, it didn't require a reboot.
Thanks again!!!!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply