[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

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.
0
PVHS
Asked:
PVHS
  • 3
  • 2
  • 2
  • +1
1 Solution
 
anantshahCommented:
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.
0
 
Daniel_PLDB Expert/ArchitectCommented:
To be able to place backup on the network share SQL Server service account must have apropriate permission to the share. All sufficient system priviledges will be granted to each service account but it must be changed using SQL Server Configuration Manager - up from SQL Server 2005. For SQL Server 2000 you can use SQL Server Enterprise Manager (SEM) or SQL Server Configuration Manager (SSCM) to change the account, apropriate permissions and Windows user rights will be automatically added to the new startup account for you so that you do not have to do anything else. You need to have administrative rights on the server.
0
 
Alpesh PatelAssistant ConsultantCommented:
Please use Domeian account or Windows authentication.
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
Alpesh PatelAssistant ConsultantCommented:
Please make sure use have permission of File structure, to where you are creating backup file.
0
 
PVHSAuthor Commented:
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).
0
 
PVHSAuthor Commented:
Also we had the account set to logon as a service but that didn't change our issue.
0
 
Daniel_PLDB Expert/ArchitectCommented:
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
0
 
PVHSAuthor Commented:
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now