Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

starting db instance under a non-Administrator Account Expand / Collapse
Author
Message
Posted Monday, September 8, 2008 9:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 29, 2009 3:10 PM
Points: 10, Visits: 30
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.
Post #565486
Posted Monday, September 8, 2008 9:11 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 40,615, Visits: 37,080
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 2008, MVP
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

Post #565489
Posted Monday, September 8, 2008 1:22 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 29, 2009 3:10 PM
Points: 10, Visits: 30
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.
Post #565706
Posted Monday, September 8, 2008 3:17 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:55 AM
Points: 6,752, Visits: 14,397
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"
Post #565785
Posted Tuesday, September 9, 2008 8:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 29, 2009 3:10 PM
Points: 10, Visits: 30
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
Post #566197
Posted Wednesday, September 10, 2008 6:08 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 19, 2014 11:18 AM
Points: 904, Visits: 676
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.
Post #566793
Posted Thursday, September 11, 2008 2:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:03 AM
Points: 2,900, Visits: 3,301
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: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 18 October 2014: now over 31,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #567542
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse