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
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
RightClick on Proxies and click on New Proxy
Now Enter the proxy name
Then cllick on the button beside “Credential Name” box (highlighted button in above image)
Then Select Credential window appears
Now click on Browse and select the credential that you want to use for proxy and click on OK
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
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.
Now you have created a proxy in sql server in two ways.
Hope this post helped you!!!!