{"id":53,"date":"2011-07-08T13:38:55","date_gmt":"2011-07-08T13:38:55","guid":{"rendered":"https:\/\/sqlserverlearner.azurewebsites.net\/2011\/07\/08\/create-proxy-step-by-step-in-detail\/"},"modified":"2011-07-08T13:38:55","modified_gmt":"2011-07-08T13:38:55","slug":"create-proxy-step-by-step-in-detail","status":"publish","type":"post","link":"https:\/\/sqlserverlearner.com\/2011\/07\/08\/create-proxy-step-by-step-in-detail\/","title":{"rendered":"CREATE PROXY – STEP BY STEP – IN DETAIL"},"content":{"rendered":"

How to create proxy in SQL Server?<\/strong><\/p>\n

Berore reading this post if you want to understand what a proxy is then you can go through the following post here<\/a><\/p>\n

So now coming to creation of proxy there are basically two ways:<\/p>\n

First method would be using the SSMS(SQL SERVER MANAGEMENT STUDIO) – Object Explorer
\nSecond method would be using CREATE PROXY Query.<\/p>\n

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.<\/p>\n

Create Proxy using SQL QUERY:<\/strong><\/p>\n

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

So in the steps to create proxy here first we need to create a credential.
\nFurther information about creating credentials can be found here<\/a><\/p>\n

First let us create a credential,
\nlets call it cred_jhon,
\nSo let us suppose the username and passowrd be jhon and pass000<\/p>\n

Query to create credential:
\n[sql]
\nUSE
\n[master]
\nGO
\nCREATE CREDENTIAL [cred_jhon] WITH IDENTITY = N’jhon’, SECRET = N’pass000′
\nGO
\n[\/sql]<\/p>\n

Now that credential is created we can create an agent proxy now.<\/p>\n

Query to create proxy:<\/p>\n

[sql]
\ndeclare @proxy_id int
\nexec sp_add_proxy @proxy_name = ‘proxy1’,
\n@description = ‘simple proxy’,
\n@credential_name = ‘cred_jhon’, –The credential that we created above.
\n@enabled = 1, –its enabled
\n@proxy_id = @proxy_id OUTPUT<\/p>\n

SELECT @proxy_id
\n[\/sql]<\/p>\n

procedure sp_add_proxy has to be run on msdb database.
\nexecute permissions on sp_add_proxy are given to the members of sysadmin role.<\/p>\n

The above query creates a proxy using credential cred_jhon.
\nNow that the proxy is created but this will be un assigned as we did not map it to any subsystem.
\nMore info about subsystems can be found here<\/a><\/p>\n

To map this proxy to a subsystem the procedure sp_grant_proxy_to_subsystem has to be used.<\/p>\n

[sql]<\/p>\n

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’proxy1′, @subsystem_id=11
\nGO<\/p>\n

[\/sql]<\/p>\n

The above command maps this proxy to SSIS Subsystem.<\/p>\n

Following are the subsystemid’s:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
subsystem_id<\/th>\nSub System<\/th>\n<\/tr>\n
2<\/td>\n Microsoft ActiveX Script<\/td>\n<\/tr>\n
3<\/td>\n Operating System<\/td>\n<\/tr>\n
4<\/td>\n Replication Snapshot Agent<\/td>\n<\/tr>\n
5<\/td>\nReplication Log Reader Agent<\/td>\n<\/tr>\n
6<\/td>\nReplication Distribution Agent<\/td>\n<\/tr>\n
7<\/td>\nReplication Merge Agent<\/td>\n<\/tr>\n
8<\/td>\nReplication Queue Reader Agent<\/td>\n<\/tr>\n
9<\/td>\nAnalysis Services Command<\/td>\n<\/tr>\n
10<\/td>\nAnalysis Services Query<\/td>\n<\/tr>\n
11<\/td>\nSSIS package execution<\/td>\n<\/tr>\n
12<\/td>\nPowerShell Script<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Creating Proxy Using Object Explorer – SSMS:<\/strong><\/p>\n

Following are the steps to create proxy using SSMS:
\nOpen SQL SERVER MANAGEMENT STUDIO<\/p>\n

Connect to SQL server in object explorer<\/p>\n

\"Connect<\/a>
Connect to sql server in object explorer<\/figcaption><\/figure>\n

In object explorer expand SQL Server Agent >> Proxies
\nNote: If SQL Server Agent is disabled then check if the agent service is started in services.msc<\/p>\n

\"Expand<\/a>
Expand SQL Server Agent and Proxies<\/figcaption><\/figure>\n

RightClick on Proxies and click on New Proxy<\/p>\n

\"Right<\/a>
Right Click On Proxies And Click On New Proxy<\/figcaption><\/figure>\n

Now Enter the proxy name<\/p>\n

\"Enter<\/a>
Enter Proxy Name<\/figcaption><\/figure>\n

Then cllick on the button beside “Credential Name” box (highlighted button in above image)<\/p>\n

Then Select Credential window appears<\/p>\n

\"Select<\/a>
Select Credential Window<\/figcaption><\/figure>\n

Now click on Browse and select the credential that you want to use for proxy and click on OK<\/p>\n

\"Browse<\/a>
Browse for Credentials<\/figcaption><\/figure>\n

Information about creating credentials can be found here<\/a><\/p>\n

Now click on Ok in Select Credential window
\nIn the “Active to the following subsystems” tab select the subsystems for the proxy<\/p>\n

\"Select<\/a>
Select Sub System<\/figcaption><\/figure>\n

Then click on OK to create the Proxy<\/p>\n

In order to view the newly created proxy, expand the subsystem that is mapped to the Proxy.<\/p>\n

\"expand<\/a>
expand the subsystem that is mapped to the Proxy<\/figcaption><\/figure>\n

Now you have created a proxy in sql server in two ways.<\/p>\n

Hope this post helped you!!!!<\/p>\n","protected":false},"excerpt":{"rendered":"

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…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[106,3],"tags":[122,123,124,125,126,127],"_links":{"self":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/53"}],"collection":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/comments?post=53"}],"version-history":[{"count":0,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/53\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/media?parent=53"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/categories?post=53"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/tags?post=53"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}