The following problem outlined in this entry is a relatively common situation but when searching for a solution there was not a single definitive guide for what I needed. This post is based on information in the following articles.
Jag Padda's Blog - Event Log Permissions
Washington University - Understanding SDDL Syntax
Microsoft – KB323076
Window Security – WEVTUTIL
Database Journal - Proxy Accounts in SQL Server
The following was written and tested against SQL Server 2008R2 and Windows Server 2008R2.
Background
You are in the envious position of being both the DBA and Windows administrator for a company and are given the task of integrating a third party application into your estate. The application consists of a database, a middle tier component running as a service and a client which runs on the user computers. In addition to this the application has multiple utility programs responsible for importing/exporting data, initiating summarisation routines.
This means in addition to the core service there are SQL Server Agent jobs calling out to a variety of programs, batch files and scripts. These additional programs either access the database through the middle tier or connect directly to the database, where they connect directly to the database the connection string is managed with a UDL file. In an effort to improve security windows authentication will be used rather than storing credentials in a text file.
Configuring Proxies.
For programs called by SQL Server agent we are going to use a proxy account to limit the permissions needed (SQL Server Agent is already running under a separate Managed Service Account).
First create the cmdshell proxy account which is needed to allow a non system admin account to run xp_cmdshell
EXEC sp_xp_cmdshell_proxy_account 'Domain\User', 'PASSWORD;
This results in the creation of the credential ##xp_cmdshell_proxy_account## which can now be assigned to the Operating System (CmdExec) Proxy
EXEC msdb.dbo.sp_add_proxy @proxy_name=N'CommandShell',@credential_name=N'##xp_cmdshell_proxy_account##', @enabled=1
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'CommandShell', @subsystem_id=3
EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'CommandShell', @login_name=N'DOMAIN\USER'
In this case I have named the proxy CommandShell so when calling sp_add_jobstep the parameter @proxy_name would be CommandShell
The user account used for the proxy was then removed from the users group in active directory and given access only to the directories necessary for implementation.
Finally the permissions at the database level were set to deny everything apart from those functions which were necessary.
Configuring Event Log
Some of the external programs being called through the Command Shell Proxy were attempting to write to the Application event log by calling EventLog.WriteEntry which was failing. Clearly granting many additional permissions to the proxy account would be defeating the purpose of the earlier security work so it was necessary to only allow the Application event log to be accessible.
Firstly identify the SID of the user account by using powershell
Import-Module ActiveDirectory
$user= New-Object System.Security.Principal.NTAccount("DOMAIN_NAME", "USER_NAME")
$sid= $user.Translate([System.Security.Principal.SecurityIdentifier])
$sid.Value
This will give you the security ID of the domain user (in this case S-1-5-21-96566445-2708948433-2759571698-1113)
The next step is to use wevtutil (Windows Event Utility) to get the ChannelAccess (CA) value for the Application log.
wevtutil gl Application
This gives the output
name: Application
enabled: true
type: Admin
owningPublisher:
isolation: Application
channelAccess: O:BAG:SYD:(A;;0xf0007;;;SY)(A;;0x7;;;BA)(A;;0x7;;;SO)(A;;0x3;;;IU)(A;;0x3;;;SU)(A;;0x3;;;S-1-5-3)(A;;0x3;;;S-1-5-33)(A;;0x1;;;S-1-5-32-573)
logging:
logFileName: %SystemRoot%\System32\Winevt\Logs\Application.evtx
retention: false
autoBackup: false
maxSize: 20971520
publishing:
fileMax: 1
The important bit is the channel access value
O:BAG:SYD:(A;;0xf0007;;;SY)(A;;0x7;;;BA)(A;;0x7;;;SO)(A;;0x3;;;IU)(A;;0x3;;;SU)(A;;0x3;;;S-1-5-3)(A;;0x3;;;S-1-5-33)(A;;0x1;;;S-1-5-32-573)
| Token | Meaning |
| O:BA | Owner: Built-in administrators |
| G:SY | Group: Local system |
| (A;;0x3;;;IU) | Access Allowed, Read/Write for Interactive Users |
In this case we want to add an extra block for our account
(A;;0x3;;;S-1-5-21-96566445-2708948433-2759571698-1113)
This will give read/write permission for the SID
To apply this you would execute
wevtutil sl Application /ca:O:BAG:SYD:(A;;0xf0007;;;SY)(A;;0x7;;;BA)(A;;0x7;;;SO)(A;;0x3;;;IU)(A;;0x3;;;SU)(A;;0x3;;;S-1-5-3)(A;;0x3;;;S-1-5-33)(A;;0x1;;;S-1-5-32-573)(A;;0x3;;;S-1-5-21-96566445-2708948433-2759571698-1113)
If you wanted to simplify it and make it for all Authorised Users it would be
wevtutil sl Application /ca:O:BAG:SYD:(A;;0xf0007;;;SY)(A;;0x7;;;BA)(A;;0x7;;;SO)(A;;0x3;;;IU)(A;;0x3;;;SU)(A;;0x3;;;S-1-5-3)(A;;0x3;;;S-1-5-33)(A;;0x1;;;S-1-5-32-573)(A;;0x3;;;AU)
Now under
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\eventlog\Application you have the new key
CustomerSD
