PVHS
asked on
How to get SQL Server and Agent to start without being a local administrator on machine
We are needing to secure some SQL servers in our environment and want to move towards getting the SQL server and agent from using a username that is a local administrator on the server. We have a mix of SQL 2000, 2005 and 2008. We would like to use a local user for the SQL server and then a domain account for the Agent so we can create our backups on a network share where the backup server can pick them up.
We have had mixed results getting this to work especially on SQL 2000. We have found quite a bit of documentation online from Microsoft on how to make this happen but the services keep failing to start. They give an access denied or an error 1053. We have looked at the local security policies, registry, and permissions on the program and database folders as well as roles on the db itself. But not much luck. SQL 2005 seems to start without many modifications but when running the backup through the agent there is an error. Some think that the SQL server and agent need to use matching credentials or the users need to belong to the same AD group with matching permissions.
Has anyone had much success with this and what have you done to get this to work properly?
We would like to find some more concise documentation on what changes need to happen.
Thanks for any input on this.
We have had mixed results getting this to work especially on SQL 2000. We have found quite a bit of documentation online from Microsoft on how to make this happen but the services keep failing to start. They give an access denied or an error 1053. We have looked at the local security policies, registry, and permissions on the program and database folders as well as roles on the db itself. But not much luck. SQL 2005 seems to start without many modifications but when running the backup through the agent there is an error. Some think that the SQL server and agent need to use matching credentials or the users need to belong to the same AD group with matching permissions.
Has anyone had much success with this and what have you done to get this to work properly?
We would like to find some more concise documentation on what changes need to happen.
Thanks for any input on this.
Make sure the service has Log on As a Service permission on the server. You can use a domain GPO for the setting. For testing purposes, you can use the Local Security Policy to configure the setting.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Please use Domeian account or Windows authentication.
Please make sure use have permission of File structure, to where you are creating backup file.
ASKER
So from what I am reading here all of the configuration must be done within SQL Server Enterprise Manager (SEM) or SQL Server Configuration Manager (SSCM) and not independently of these utilities. I didn't see anywhere in the documentation to use those outside of specifying the role of the account (dbowner or sysadmin).
ASKER
Also we had the account set to logon as a service but that didn't change our issue.
I didn't see anywhere in the documentation to use those outside of specifying the role of the account (dbowner or sysadmin).Really? It's the recommended way. Read following:
How to change the SQL Server or SQL Server Agent service account without using SQL Enterprise Manager in SQL Server 2000 or SQL Server Configuration Manager in SQL Server 2005
ASKER
I didn't see that document. Thanks for that. It is working now that we changed it within the management consoles.
Thanks again for the help.
Thanks again for the help.