CREATE PROXY – STEP BY STEP – IN DETAIL

Posted on : 08-07-2011 | By : Devi Prasad | In : PROXIES, SQLServerPedia Syndication

Share:

1


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:

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 

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:

USE 
[master]
GO
CREATE CREDENTIAL [cred_jhon] WITH IDENTITY = N'jhon', SECRET = N'pass000'
GO

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

Query to create proxy:

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 

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.


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

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!!!!




References : Devi Prasad (sqlserverlearner.com)

Need Help On SQL Server?

Cannot Find Solution to your problem (or) If you are looking for some help on SQL Server. Dont worry Click Here to Post your question and solve your issue.


Do you like my blog?

If you liked reading this blog, please help spread the word by sharing this blog with your friends.




Comments (1)

Your post helped me well. Thanks for aiming to the point.

Write a comment



Tags: , , , , ,