dcsimg

Proxy Accounts in SQL Server

December 15, 2008



It would be ideal if an application connecting to SQL Server only needed to access objects and resources inside the SQL Server instance. However, oftentimes, an application needs to access external system resources, such as files, the network, environment variables, or the registry. For example, the application could need to run the xp_cmdshell extended stored procedure to invoke a Windows command shell, and execute a shell command to retrieve a list of files under a directory. Or, a SQL Server Agent job is scheduled by the application to perform maintenance tasks. This job has an Active Scripting job step or a Web Service task to call a Web Service in order to verify geographic and zip code information.

By default, in SQL Server 2000, only members of the sysadmin fixed server role can execute the xp_cmdshell extended stored procedure and Active Scripting job steps. When the xp_cmdshell extended stored procedure is executed by a member of the sysadmin fixed server role, the Windows process of the command shell runs with the security context of the SQL Server service account. When a job owned by a member of the sysadmin role runs, its Active Scripting job steps run under the security of the SQL Server Agent service account. However, in most companies, the DBA roles and the application developer roles are often separated. Due to security concerns, the application developers are not allowed to have the sysadmin permissions. To allow application developers to access external resources without giving them excessive permissions, SQL Server provides the solution of proxy accounts.

The extended stored procedure xp_sqlagent_proxy_account sets the proxy account information used by SQL Server Agent and the xp_cmdshell extended stored procedure when executing jobs or commands for users who are not members of the sysadmin fixed server role. For example, the following commands set the proxy account to a domain account PowerDomain\PowerUser, and then enable non-sysadmin logins to execute Active Scripting job steps and xp_cmdshell under the security context of the domain account.

USE master 
GO
-- Create a test login called testuser
EXEC sp_addlogin 'testuser', 'testuser'
-- Add a Windows domain account PoweDomain\PowerUser as the proxy account. 
EXECUTE xp_sqlagent_proxy_account N'SET' , N'PowerDomain' , N'PowerUser' , N'P@ssw0rd' 
-- Enable non-sysadmin logins to run Active Scripting job steps and execute xp_cmdshell. 
EXECUTE msdb..sp_set_sqlagent_properties @sysadmin_only = 0 
-- Grant database access to the SQL Server login account that you want to provide access.
EXEC sp_grantdbaccess 'testuser' 
-- Grant execute permission on xp_cmdshell to the SQL Server login account. 
GRANT exec ON xp_cmdshell TO [testuser] 
GO

Please note that only one proxy account can be specified in SQL Server 2000. This account is used to execute xp_cmdshell and Active Scripting job steps.

In SQL Server 2005 and 2008, to allow a non-sysadmin login to execute xp_cmdshell, you will need to create a special system credential ##xp_cmdshell_proxy_account## by running the extended stored procedure sp_xp_cmdshell_proxy_account and specify a Windows account. This account will be used to run xp_cmdshell by users that are not members of the sysadmin role.

USE master 
GO
-- Create a test login called testuser
CREATE LOGIN testuser WITH PASSWORD='P3h4jek@x'
-- Create a proxy credential for xp_cmdshell.
EXEC sp_xp_cmdshell_proxy_account 'PowerDomain\PowerUser', 'P@ssw0rd';
-- Grant database access to the SQL Server login account that you want to provide access.
EXEC sp_grantdbaccess 'testuser'
-- Grant execute permission on xp_cmdshell to the SQL Server login account. 
GRANT exec ON sys.xp_cmdshell TO [testuser] 
GO

To confirm that the ##xp_cmdshell_proxy_account## credential has been created, you can select the sys.credentials view.

You also have more and better control over the proxies for SQL Server Agent jobs in SQL Server 2005 and 2008. You can specify more than one proxy account. You can also specify which subsystem (job step type) you want to apply a proxy account to.

Before creating a proxy account, you need to define a Windows credential. After the credential is created, you can create a proxy account and assign the credential to it. Then you grant the proxy access to one or more subsystems. If you use the sp_grant_proxy_to_subsystem stored procedure to grant access to multiple subsystems, you will need to execute the stored procedure multiple times. After that, you can grant multiple SQL Server logins, msdb roles, and/or server roles the permissions to use the proxy account.

For example, we would like to create a proxy called SSISProxy for job steps that execute SSIS packages. This proxy will use the credentials of the domain account PowerDomain\PowerUser. We would like to allow the testUser login to execute SSIS packages with this proxy account in the SQL Server Agent jobs that it owns.

-- Create a credential containing the domain account PowerDomain\PowerUser and its password
CREATE CREDENTIAL PowerUser WITH IDENTITY = N'PowerDomain\PowerUser', SECRET = N'P@ssw0rd'
GO
USE [msdb]
GO
-- Create a new proxy called SSISProxy and assign the PowerUser credentail to it
EXEC msdb.dbo.sp_add_proxy @proxy_name=N'SSISProxy',@credential_name=N'PowerUser',@enabled=1
-- Grant SSISProxy access to the "SSIS package execution" subsystem
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'SSISProxy', @subsystem_id=11
-- Grant the login testUser the permissions to use SSISProxy
EXEC msdb.dbo.sp_grant_login_to_proxy @login_name = N'testUser', @proxy_name=N'SSISProxy'
GO

After the login testUser has granted the access to the proxy account SSISProxy, in a job owned by testUser, if a job step is to execute a SSIS package, testUser could pick the proxy SSISProxy and run the step under the proxy account.

if a job step is to execute a SSIS package, testUser could pick the proxy SSISProxy and run the step under the proxy account.

SQL Server 2005 has 11 subsystems. Their values are listed as below.

Value Description

2    Microsoft ActiveX Script

3    Operating System (CmdExec)

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


SQL Server 2008 adds one more subsystem for the integration of PowerShell.

12   PowerShell Script

Conclusion

Proxy accounts in SQL Server provide a work-around for logins in SQL Server to execute Windows shell commands and SQL Server Agent jobs without giving excessive permissions. This article describes how to set up proxies in SQL Server 2000, 2005 and 2008, and compare the differences among them.

» See All Articles by Columnist Yan Pan








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers