Creating a proxy

In this recipe, we will create a SQL Server proxy.

Getting ready

In this recipe, we will map our SQL Server Agent service account (QUERYWORKS\sqlagent) to the credential we created in the previous recipe, filemanagercred. We are also going to grant this proxy rights to run the PowerShell agent steps and the Operating System (CmdExec) steps. Here's the T-SQL equivalent of what we are trying to achieve:

EXEC msdb.dbo.sp_add_proxy @proxy_name = N'filemanagerproxy', @credential_name = N'filemanagercredential', @enabled = 1, @description = N'Proxy Account for PowerShell Agent Job steps' EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name = N'filemanagerproxy', @login_name = N'QUERYWORKS\sqlagent' -- PowerShell subsystem EXEC msdb.dbo.sp_grant_proxy_to_subsystem ...

Get SQL Server 2014 with PowerShell v5 Cookbook now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.