THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

Monitor your SQL Server Agent Proxies Using PowerShell

Over time it's easy to lose track of the things you set up to get work done. As a consultant, I have to discover what's set up on a client system. In either case, it's important to know what security settings are in place to know if they're appropriate and well managed.

One of the hidden gems (and potential nightmares) in SQL Server is the use of proxies to allow job steps to acquire a different set of privileges than would normally be allowed. (I blogged about setting up proxies here. I use them when I need a process to have access to WMI, for example, to gather information from the operating system on a regular basis. The service account I use for the Agent service doesn't have that privilege normally.

So, to keep track of what's been set up, I've got a PowerShell script which returns all proxies, by Job and Step. First I load the SMO assemblies and connect to the SQL Server instance.

# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
$v = [System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO')
if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | out-null
}

# Connect to the instance using SMO
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'MyServer\MyInstance'

Next, I get the ProxyAccounts collection from the instance's JobServer object and show the CredentialName and CredentialIdentity properties for each proxy created.

# Get the ProxyAccounts collection from the JobServer object
$pa = $s.JobServer.ProxyAccounts

# Return the Credential Identity for all proxies created
$pa | select Name, CredentialName, CredentialIdentity

And finally I cycle through the jobs and look at each job step, and report which step uses which proxy.

# Get the Jobs and return all proxies used by each job
$jobs = $s.JobServer.Jobs
foreach ($job in $jobs) {
[string]$jobnm = $job.Name
foreach ($step in $job.JobSteps) {
$ln = $step.ProxyName.Length
if ($ln -gt 0) {
[string]$nm = $step.Name
[string]$px = $step.ProxyName
write-output "Job: $jobnm, Step: $nm, Proxy Used: $px"
}
}
}

This gives me a quick view to see where the proxies are being used, and lets me focus my energies on where it's important.

Allen

Published Wednesday, May 25, 2011 10:40 AM by AllenMWhite

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement