CREATE PROXY – STEP BY STEP – IN DETAIL

How to create proxy in SQL Server?

Berore reading this post if you want to understand what a proxy is then you can go through the following post here

So now coming to creation of proxy there are basically two ways:

First method would be using the SSMS(SQL SERVER MANAGEMENT STUDIO) – Object Explorer
Second method would be using CREATE PROXY Query.

First I would explain how to create proxy using Create proxy query and later in the post I would show how to create proxy using SSMS Object Explorer.

Create Proxy using SQL QUERY:

Syntax for Creating proxy:
[sql]
sp_add_proxy @proxy_name = <<name_of_the_proxy>> ,
@description = <<description_of_proxy>> ,
@credential_name = <<name_of_the_credential>> ,
@credential_id = <<credential_id>> ,
@enabled = <<1 or 0>> ,
@proxy_id = <<proxy_id >> OUTPUT
[/sql]

So in the steps to create proxy here first we need to create a credential.
Further information about creating credentials can be found here

First let us create a credential,
lets call it cred_jhon,
So let us suppose the username and passowrd be jhon and pass000

Query to create credential:
[sql]
USE
[master]
GO
CREATE CREDENTIAL [cred_jhon] WITH IDENTITY = N’jhon’, SECRET = N’pass000′
GO
[/sql]

Now that credential is created we can create an agent proxy now.

Query to create proxy:

[sql]
declare @proxy_id int
exec sp_add_proxy @proxy_name = ‘proxy1’,
@description = ‘simple proxy’,
@credential_name = ‘cred_jhon’, –The credential that we created above.
@enabled = 1, –its enabled
@proxy_id = @proxy_id OUTPUT

SELECT @proxy_id
[/sql]

procedure sp_add_proxy has to be run on msdb database.
execute permissions on sp_add_proxy are given to the members of sysadmin role.

The above query creates a proxy using credential cred_jhon.
Now that the proxy is created but this will be un assigned as we did not map it to any subsystem.
More info about subsystems can be found here

To map this proxy to a subsystem the procedure sp_grant_proxy_to_subsystem has to be used.

[sql]

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’proxy1′, @subsystem_id=11
GO

[/sql]

The above command maps this proxy to SSIS Subsystem.

Following are the subsystemid’s:

subsystem_id Sub System
2 Microsoft ActiveX Script
3 Operating System
4 Replication Snapshot Agent
5 Replication Log Reader Agent
6 Replication Distribution Agent
7 Replication Merge Agent
8 Replication Queue Reader Agent
9 Analysis Services Command
10 Analysis Services Query
11 SSIS package execution
12 PowerShell Script

Creating Proxy Using Object Explorer – SSMS:

Following are the steps to create proxy using SSMS:
Open SQL SERVER MANAGEMENT STUDIO

Connect to SQL server in object explorer

Connect to sql server in object explorer
Connect to sql server in object explorer

In object explorer expand SQL Server Agent >> Proxies
Note: If SQL Server Agent is disabled then check if the agent service is started in services.msc

Expand SQL Server Agent and Proxies
Expand SQL Server Agent and Proxies

RightClick on Proxies and click on New Proxy

Right Click On Proxies And Click On New Proxy
Right Click On Proxies And Click On New Proxy

Now Enter the proxy name

Enter Proxy Name
Enter Proxy Name

Then cllick on the button beside “Credential Name” box (highlighted button in above image)

Then Select Credential window appears

Select Credential Window
Select Credential Window

Now click on Browse and select the credential that you want to use for proxy and click on OK

Browse for Credentials
Browse for Credentials

Information about creating credentials can be found here

Now click on Ok in Select Credential window
In the “Active to the following subsystems” tab select the subsystems for the proxy

Select Sub Systems
Select Sub System

Then click on OK to create the Proxy

In order to view the newly created proxy, expand the subsystem that is mapped to the Proxy.

expand the subsystem that is mapped to the Proxy
expand the subsystem that is mapped to the Proxy

Now you have created a proxy in sql server in two ways.

Hope this post helped you!!!!

One thought on “CREATE PROXY – STEP BY STEP – IN DETAIL

Leave a Reply

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