Assign SQL service start,stop permission to Non-Administrator Account and SDDL explained

For full syntax of SDDL(Security Descriptor Definition Language) and ACEs (Access Control Lists), you can refer this TechNet article.

Here we will assign the start/stop permission of MSSQLSERVER to ‘MyUser’ domain user. Assume that computer has been SQL 2012 installed in domain environment. We can easily assign the necessary permission the by Powershell Access Control Module and will check which permissions are changed.Before doing this, we will run sc sdshow mssqlserver and check the initial service permission. See Fig-1.

D:(A;;CCLCSWRPWPDTLOCRRC;;;SY)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BA)(A;;CCLCSWLOCRRC;;;IU)(A;;CCLCSWLOCRRC;;;SU)S:(AU;FA;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;WD)

Fig-1: Initial SQL service permission

Before we break down the above output, let me explain a little about SDDL. It is composed as follows:

sddl = [owner-string]  [group-string]  [dacl-string]  [sacl-string]

owner-string is the prefix starting with "O:"  and is followed by sid-string

group-string is the prefix starting with  "G:"  and is followed by sid-string

dacl-string is the prefix starting with  "D:"  and is followed by [acl-flag-string]  [aces]

sacl-string is the prefix starting with  "S:" and is followed by [acl-flag-string]  [aces]

sid-string is the sid-token or sid-value (sid-token is an abbreviated form of a well-known SID. Sid-value is the unique string for each local/domain account). Check all the sid-tokens list in ref section)

First of all, let's break down the long SDDL line into the pairs of parenthesises. We find two parenthesis pairs are prefixed by D: and S:.  So here, there are only dacl and sacl strings. ( we can know this by checking D: and S: against with SDDL syntax shown previously)

D: =>  Discretionary ACL (DACL), controls permissions

S: => System Access Control List (SACL), controls auditing

For example, from the initial output, the following string is the DACL String:

D:(A;;CCLCSWRPWPDTLOCRRC;;;SY)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BA)(A;;CCLCSWLOCRRC;;;IU)(A;;CCLCSWLOCRRC;;;SU)

And the following string is the SACL String:

S:(AU;FA;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;WD)

Now you can run the powershell commands to give start/stop permission of MSSQLSERVER to ‘contoso\MyUser’. Download the Powershell Access Control from here and put under C:\ and run the following two commands. See Fig-2.

Import-Module C:\PowershellAccessControl

get-service mssqlserver | add-AccessControlEntry -Principal contoso\myuser  -ServiceAccessRights start,stop

Fig-2: Assign start/stop permission of SQL service to 'MyUser'

After giving service start/stop permission to domain user ‘MyUser’ , we need to check the service permission again with sc sdshow and it changes as shown in Fig-3.

Fig-3: SDDL is changed after SQL service's permission is changed

D:AR(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;SY)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BA)(A;;CCLCSWLOCRRC;;;IU)(A;;RPWP;;;S-1-5-21-2647241702-1957647361-952520019-1197)S:(AU;FA;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;WD)

We can see that (A;;RPWP;;;S-1-5-21-2647241702-1957647361-952520019-1197) is the extra string from initial service permission. Since it was insert just before S:, we can say that it’s for user’s permission. We will analysis this string in details. Each ACE is in the following form.

ace = "(" ace-type ";" [ace-flag-string] ";" ace-rights ";"  [object-guid] ";" [inherit-object-guid] ";" sid-string ")"

You can refer the meaning for each two-letter-pair  (such as "RP"  "WP") in the reference section.

(A;;RPWP;;;S-1-5-21-2647241702-1957647361-952520019-1197)

The 1^st slot (ace-type) is ‘A’ which is for Access Allow.

The 2^nd slot (acl-flag) is blank and I will not cover it this blogpost. It is the flags for the SECURITY_DESCRIPTOR structure, context dependent on whether a SACL or DACL is being processed. Check more about it here.

In the 3^rd  slot (access-rights), RP is for Read Property and WP is for Write Property

The 4^th and 5^th slots are about object-guid and inherit-object-guid. This is usually blank.

The 6^th slot (sid-string) is the SID of the account that you want to set permission for. Here it’s  S-1-5-21-2647241702-1957647361-952520019-1197

You can find the SID of local or domain user account by the following command.

wmic useraccount where name=”MyUser” get name,sid

So, by analysing the ACEs applied, we can see that user now has the SQL service start/stop permission.

REF Section for ACE Entries:

Syntax:

ace = "(" ace-type ";" [ace-flag-string] ";" ace-rights ";"  [object-guid] ";" [inherit-object-guid] ";" sid-string ")"

ace-type is the string that indicates the type of ACE that is being presented. ace-type can be one of the these: "A" / "D" / "OA" / "OD" / "AU" / "OU" / "ML" / "SP"

String	ACE type
"A"	Access Allowed
"D"	Access Denied
"AU"	Audit
"OA"	Object Access Allowed
"OD"	Object Access Denied
"OU"	Object Audit
"ML"	Mandatory Label
"SP"	Central Policy ID

Ace-flag-string is a set of ACE flags that define the behavior of the ACE. The string value may differ depending on objects they apply.

generic-rights-string: A set of generic user rights used to perform generic mappings to object-specific rights

String	Access right
"GR"	Generic Read
"GW"	Generic Write
"GX"	Generic Execute
"GA"	Generic All

standard-rights-string: A set of SDDL-supported standard user rights.

String	Access right
"WO"	Write Owner
"WD"	Write DAC
"RC"	Read Control
"SD"	Delete

object-specific-rights-string: A set of object-specific rights; some common ones are shown, but the it is recommended that the reader consult a specific protocol for applicable values, if any, in that protocol.

String	Object type	Access right
"FA"	File	File All Access
"FX"	File	File Execute
"FW"	File	File Write
"FR"	File	File Read
"KA"	Registry Key	Key All Access
"KR"	Registry Key	Key Read
"KX"	Registry Key	Key Execute
"KW"	Registry Key	Key Write
"CR"	Directory Object	Control Access
"LO"	Directory Object	List Object
"DT"	Directory Object	Delete Tree
"WP"	Directory Object	Write Property
"RP"	Directory Object	Read Property
"SW"	Directory Object	Self Write
"LC"	Directory Object	List Children
"DC"	Directory Object	Delete Child
"CC"	Directory Object	Create Child

sid-token is an abbreviated form of a well-known SID, per the following table.

SDDL alias	Well-Known SID name
"DA"	DOMAIN_ADMINS
"DG"	DOMAIN_GUESTS
"DU"	DOMAIN_USERS
"ED"	ENTERPRISE_DOMAIN_CONTROLLERS
"DD"	DOMAIN DOMAIN CONTROLLERS
"DC"	DOMAIN_COMPUTERS
"BA"	BUILTIN_ADMINISTRATORS
"BG"	BUILTIN_GUESTS
"BU"	BUILTIN_USERS
"LA"	ADMINISTRATOR<73>
"LG"	GUEST
"AO"	ACCOUNT_OPERATORS
"BO"	BACKUP_OPERATORS
"PO"	PRINTER_OPERATORS
"SO"	SERVER_OPERATORS
"AU"	AUTHENTICATED_USERS
"PS"	PRINCIPAL_SELF
"CO"	CREATOR_OWNER
"CG"	CREATOR_GROUP
"SY"	LOCAL_SYSTEM
"PU"	POWER_USERS
"WD"	EVERYONE
"RE"	REPLICATOR
"IU"	INTERACTIVE
"NU"	NETWORK
"SU"	SERVICE
"RC"	RESTRICTED_CODE
"WR"	WRITE_RESTRICTED_CODE
"AN"	ANONYMOUS
"SA"	SCHEMA_ADMINISTRATORS
"CA"	CERT_PUBLISHERS
"RS"	RAS_SERVERS
"EA"	ENTERPRISE_ADMINS
"PA"	GROUP_POLICY_CREATOR_OWNER
"RU"	ALIAS_PREW2KCOMPACC
"LS"	LOCAL_SERVICE
"NS"	NETWORK_SERVICE
"RD"	REMOTE_DESKTOP
"NO"	NETWORK_CONFIGURATION_OPS
"MU"	PERFMON USERS
"LU"	PERFLOG USERS
"IS"	IIS USERS
"CY"	CRYPTO OPERATORS
"OW"	OWNER_RIGHTS
"ER"	EVENT LOG READERS
"RO"	ENTERPRISE RO DCS
"CD"	CERTSVC DCOM ACCESS
"AC"	ALL APP PACKAGES
"RA"	REMOTE ACCESS SERVERS
"ES"	RDS ENDPOINT SERVERS
"MS"	RDS MANAGEMENT SERVERS
"UD"	USER MODE DRIVERS
"HA"	HYPER V ADMINS
"CN"	CLONEABLE CONTROLLERS
"AA"	ACCESS CONTROL ASSISTANCE OPS
"RM"	REMOTE_MANAGEMENT_USERS
"LW"	ML_LOW
"ME"	ML_MEDIUM
"MP"	ML MEDIUM PLUS
"HI"	ML_HIGH
"SI"	ML_SYSTEM