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.