6

I want to create a Domain User that runs the SQL Service Accounts. The reason for this is that I have setup mirroring and MS recommends having the same user (a domain user account) running the services across all the the computers in the configuration to ensure mirroring will work properly. Right now in the test environment I just had them run under my user for simplicity. But now that I know what I am doing I would like to test the configuration more accurately.

I am also aware that it makes things much simpler if this user is an administrator.

My question is.

Should I just create a simple user SQLSERVICEUSER and make it an administrator? Seems a little insecure to me. Anyone have a more elegant solution?

Campo
  • 1,609
  • 17
  • 33

1 Answers1

1

Setting the sqlserviceaccount as an admin does become substantially more insecure (or perhaps loses accountability would be a better term) with the more people who know the password so you're not wrong to worry about it. Not to mention that keeping it with minimum permissions helps to ensure that the account can't do much to the actual server if the account is compromised.

You've mentioned that you have a test environment so you already have the perfect place to test out a non-admin user. Just switch the service from running on your account to a regular user. I know that SQL Server doesn't require an administrator account to run so simply go as low on the totem pole as you can for it. If you're assigning the service to a regular user and it's giving you invalid permission errors then post them here and we'll see if we can work through them. While giving out admin privileges is simpler it does add another all-powerful user to the system that needs to be monitored.

Update:

The two following articles seem to suggest ways to set up accounts for database mirroring:

How to: Allow Database Mirroring Network Access Using Windows Authentication (Transact-SQL)
Setting Up Login Accounts for Database Mirroring

Shane
  • 1,869
  • 4
  • 20
  • 34
  • Excellent response. I will try this and mark the answer correct if I am successful if not we will work through the issues and come up with a very concise answer so others can follow as I feel this could be helpful for many others. – Campo Apr 20 '10 at 19:56
  • I created an user account to run the SQL services across the mirrored servers and the witness. I had to completely redo the mirror to get it to work. I couldn't get it to work originally with SQL Service user being just a domain user as MS suggested. I then gave the User Cluster Service Account Membership this allowed it to run the services but starting mirroring still failed. I then Added the Administrator Membership to the user and specified the service accounts in the final step of the mirroring configuration wizard and it worked. Any thoughts on how I can not have the account be an admin? – Campo Apr 21 '10 at 16:27
  • @Campo - Do you know if it's failing off of Windows or SQL permissions? I found two articles that address how to set up an account for mirroring and neither mention the need for the account to be a Windows or SQL admin. I'll post the articles in my answer so you can check them out and see if they offer any insight into your issue. – Shane Apr 22 '10 at 14:27
  • OK I just removed the admin membership of the SQL service account user I created and I restarted both servers running the mirror. Everything seems to still be working. Think I am going to call this one resolved. THANKS FOR THE HELP EVERYONE! – Campo Apr 22 '10 at 18:06