SQL Serevr windows console command Proxy account

For xp_cmdshell or a job runs windows console command, if the user or the job owner is a memeber of sysadmin, SQL server uses
SQL service windows account to run it. If it’s not a member of sysadmin, a proxy account must be set and SQL server uses the
proxy account to run the windows console command. However, the setup of proxy account is changed between SQL 2000 and SQL
2005.
In SQL 2000,  we use the extended stored procedure xp_sqlagent_proxy_account, e.g.:
EXEC master.dbo.xp_sqlagent_proxy_account N’SET’,
             N’NETDOMAIN’, — agent_domain_name
             N’ralph’, — agent_username
             N’Oxi&#lW8I’, – agent password
It sets the "NETDOMAIN\ralph" windows domain account as the proxy account.
In SQL 2005, We use credentials to define proxy accounts. SQL server distinguishes the proxy accounts for xp_cmdshell and for
jobs. We need to set them up separately.
For xp_cmdshell: a credential ##xp_cmdshell_proxy_account## is predefined for xp_cmdshell. We use
sp_xp_cmdshell_proxy_account to setup the windows account for the credential.
E.g.: EXEC sp_xp_cmdshell_proxy_account ‘ADVWKS\Max04’, — windows account
  ‘ds35efg##65"; — password
It sets the windows domain account "ADVWKS\Max04" as the proxy account.
For SQL Jobs, there are several steps:
1) Createa a credential:
 CREATE CREDENTIAL MyCredential WITH IDENTITY=N’emtldn\phe’,SECRET =N’Pass’
 Identity is the windows acount name nad Secret is the password;
2) In msdb, add a proxy account by menas of an existing credential:
USE [msdb]
GO
EXEC msdb.dbo.sp_add_proxy @proxy_name=N’MyProxy’,@credential_name=N’MyCredential ‘,
  @enabled=1
3) In msdb, grant the proxy access to the Operating System (CmdExec) subsystem. The same proxy account can be used by
multiple sql agent subsystems
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’MyProxy’, @subsystem_id=3 –CmdExec subsystem ID
4) In msdb, grant the SQL server logins that can use the proxy account:
EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N’MyProxy’, @login_name=N’MyLogin’
If MyLogin is not a member of sysadmin, now the SQL agent jobs owned by MyLogin can use the proxy account to run CmdExec job
steps.
 
Advertisements
This entry was posted in SQL server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s