Link to home
Start Free TrialLog in
Avatar of PVHS
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.
Avatar of anantshah
anantshah

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
Avatar of Daniel_PL
Daniel_PL
Flag of Poland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Alpesh Patel
Please use Domeian account or Windows authentication.
Please make sure use have permission of File structure, to where you are creating backup file.
Avatar of PVHS

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).
Avatar of PVHS

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
Avatar of PVHS

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.