3

I have done this on a server (windows server 2008) before, but can not achieve the same thing on another virtual server

I want a low level domain user to run the MSSQLSERVER service so that I can enable backup to a remote network location, this is working fine on my other SQL Server 2008R2 instance.

I have a domain user called fred@mydomain I have added this user to the server local administrator group and the group SQLServerMSASUser$**MYSERVER**$MSSQLSERVER. This is the group I believe is the one that allows members to run the MSSQLSERVER service

I have created the login in SQL Server for the user and added him to all the server roles but dbcreator and mapped it to all the instance databases as a public database role

HOWEVER when I go to SQL Server Configuration Manager to change the MSSQLSERVER service and selected the fred@mydomain and press apply I get a log on error.

Can anyone suggest what I might have missed please ? I have used Here as my reference

Improve this question

3 Answers 3

Reset to default
2

If you check the event log, does the event say something about needing "log on as service" rights?

In my experience you will need to add 'log on as service' rights to the account you select. Although the SQL Server Configuration Manager is supposed to handle this, I've found that it does not always. Try the following:

  • Log on to your domain controller and load the group policy management console (depending on your AD version)
  • add 'log on as a service' to the account you created for the SQL Server service in an existing or new group policy object. (maybe ask a sysadmin to assign this right for you)
  • Load SQL Server configuration manager and set the service account to the account you created
Improve this answer
4
  • 1
    Hi Thank you. I agree every time I try and do this through SQL configuration manager with my plain user I get Log on failure. And to be clear do you mean the server event log to check?
    – Ian W
    Commented Feb 23, 2016 at 10:00
  • @IanW yes, you will see that the error explicitly tells you to assign this right. You will see an error I think in the application log telling you about a service which cannot start due to a need for that permission. Regardless, if you have an able IT team, ask them to create a standard AD user, add that user to the group policy setting for 'log on as a service' then after perhaps a reboot of your SQL server (to ensure propagation of the group policy, 'gpupdate' in command prompt also works) you'll find that if you add your domain user as the identity of the SQL service, it will start.
    – Peter
    Commented Feb 23, 2016 at 19:48
  • Yep solve. We have had this problem for a year but sorted now. I shall mark yours as the answer which worked for me. However if you are reading this later please see references to the other bad practice involved
    – Ian W
    Commented Feb 23, 2016 at 22:37
  • An upvote for the solution would be wonderful :)
    – Peter
    Commented Feb 23, 2016 at 22:41
1

In SQL Server Configuration Manager, enter the username as MYDOMAIN\Fred, not [email protected].

You do not need to give the account any permissions at all - Configuration Manager will take care of the user rights and the group memberships and SQL logins and server roles, as well as other tasks like service master key protection.

This, however, only works if you use Configuration Manager. Do not use the Services tool to change SQL Server service accounts.

Additionally, verify that you are running Configuration Manager with elevated permissions (Run As Administrator).

I strongly, strongly suggest you take the account out of the local Administrators group. If anyone somehow compromises your SQL Server then they have just compromised your server.

TechNet: Configure Windows Service Accounts and Permissions.

Improve this answer
0

First of all you shouldn't provide instance level permissions to your SQL server service account and database level permission, this goes against all security best practises. when you assign the service account through SQL server configuration manager SQL will provide the user with the necessary permissions to run the SQL server service.

so I would first of all remove all the permissions you have given, and to the issue you are having, the service account need to be given the local security policy "Log on as a service", you will find this under local security policy under user rights assignment.

you can get to the local security policy by under windows run box write secpol.msc

P.S , also you should realise that when

added him to all the server roles but dbcreator

you have given the user sysadmin, which basically has all rights under the sun in SQL server world. I would recommend you spend some time reading BOL Fixed server role sections to understand what each fixed server role does.

Improve this answer
1
  • Ok thanks for pointing out the breach of best practice. I will work on this in the morning. Thanks
    – Ian W
    Commented Feb 21, 2016 at 21:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.