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)
Initial SQL service permission

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 ‘contosoMyUser’. Download the Powershell Access Control from hereand put under C: and run the following two commands. See Fig-2.
Import-Module C:PowershellAccessControl
get-service mssqlserver | add-AccessControlEntry -Principal contosomyuser  -ServiceAccessRights start,stop
Assign start/stop permission of SQL service to 'MyUser'

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.
SDDL is changed after SQL service's permission is changed

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 1st slot (ace-type) is ‘A’ which is for Access Allow.
The 2nd 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 3rd  slot (access-rights), RP is for Read Property and WP is for Write Property
The 4thand 5th slots are about object-guid and inherit-object-guid. This is usually blank.
The 6thslot (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
This entry was posted in Security, Windows. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *