starting db instance under a non-Administrator Account

Question

starting db instance under a non-Administrator Account

roger.hurst

SSC Veteran

Points: 270
More actions
September 8, 2008 at 9:02 am

#124550

In the environment our product runs, there are security cops running at various times monitoring processes, and terminating applications that run with administrator identity.
Can anyone give me a method to bring up mssql$name under some non-administrator account?
If it can be done, what would be the negative side-effects?
We have a service process which also interacts with the instance. This has the same restriction. We've been able to solve this by adding a non-administrative user (SvcUser, if you will) to the Security tab on the executable properties page, giving this user full-control, and also giving this user sysadmin (via sp_addsrvrolemember) role in the database. I'm hoping to accomplish something similar with the database instance service.
Thanks in advance.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

SQL should run under a non-administrative account. It's a security best practice.

Create a domain account for SQL. I would suggest that you se the domain account so that it does not allow interactive login. Make sure that the account has full control over the directories that have your data and log files in, and also the directories that the backups go. Change the SQL service account using the SQL Server Configuration Manager. That tool will ensure that the new login has the permissions that the SQl Service needs.

Do not make it a local administrator on the server.

We've been able to solve this by adding a non-administrative user (SvcUser, if you will) to the Security tab on the executable properties page, giving this user full-control, and also giving this user sysadmin (via sp_addsrvrolemember) role in the database.

That's not what I would call a non-administrative user. By adding it to the sysadmin role, you've given that account full system administrator permissions to the SQL instance. Doe the account really need that?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

roger.hurst SSC Veteran Points: 270 More actions · Answer 2

Thanks for the tip.

We run SQL Server 2000. I was pointed to the instance Security Page under SQL Server Enterprise Manager, where I could set the Log in Acct/Password. That set the Log In Properties for my service (MSSQL$svc), and the file permissions. The service started up under the domain\acct I specified.

I need to do this "on the fly" in an InstallShield Script. Could you point me to tool(s) to accomplish that?

Thanks very much.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

GilaMonster (9/8/2008)
Make sure that the account has full control over the directories that have your data and log files in, and also the directories that the backups go.

this shouldnt actually be necessary as changing the account via sql config manager will assign the user to the appropriate sql server groups on the machine, these should already have the required permissions on the folders

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

roger.hurst SSC Veteran Points: 270 More actions · Answer 4

Follow-up for Gail:

Is there a command-line directive to SQL Enterprise Mgr that can change the Log In Properties of the Server?

My hunt so far has not been successful.

I would prefer to change identity of my mssql$inst in my installshield, rather than force the user to do it.

Any info is greatly (needed and) appreciated.

Thx

Sr SQL developer Default port Points: 1418 More actions · Answer 5

We discovered SQL 2000 full text search required the SQL instance to run as a local admin account. This is not required under SQL 2005 due to changes in the implementation of full text search under that release.

EdVassie SSC Guru Points: 60478 More actions · Answer 6

Trying to run SQL Server 2000 using a account that is not local administrator is very difficult. There is lots of functionality that does not work. My understanding is that it is best practice to use a local administrator account to run SQL Server 2000.

With SQL Server 2005 and above, there is no requirement to use an account with local administrator rights. Best practice for SQL Server 2005 is definitely do not use local administrator rights. Although there is one function within SQL Agent that requires local administrator rights if you use it (auto restart of SQL and SQL Agent), this can be achieved without using local administrator rights and far more flexibly by configuring the service restart options in the Windows service control applet.

If your security people are paranoid about not letting SQL Server run using local administrator authority, use this as a lever to move to SQL Server 2005 or 2008.

If your organisation wants to control SQL Server 2005 account rights using Group Policy Objects (GPOs), which is definitely Windows best practice, the SQL Server FineBuild Reference document has a section you can cut out that gives all the GPO details you need.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara