Sep
24
2013

Info – SQL Server Proxy accounts and job agent execution contexts

SQL Server proxies are used in cases where impersonation is required in complex and secured environments and more control is required while executing packages, jobs, PowerShell commands etc. below links and details are collected to give more info on this topic, it requires a good amount of reading and may take up to 2-3 hours to understand it completely, so be patient if you land on this page and go through all links and articles to understand it better.

 

Links provided are in context of SQL Server 2008 R2…

 

1. SQL Server agent proxies and how to create

http://technet.microsoft.com/en-us/library/ms189064(v=sql.105).aspx

 

a) How to: Create a Credential (SQL Server Management Studio)

http://technet.microsoft.com/en-us/library/ms190703(v=sql.105).aspx

 

b). How to: Modify a Proxy (SQL Server Management Studio)

http://msdn.microsoft.com/en-us/library/ms187890(v=sql.100).aspx

 

2. How to: Configure a User to Create and Manage SQL Server Agent Jobs (SQL Server Management Studio)

http://technet.microsoft.com/en-us/library/ms187901(v=sql.105).aspx

 

3. Configure a User to Create and Manage SQL Server Agent Jobs

http://technet.microsoft.com/en-us/library/ms187901.aspx

 

4. Implementing SQL Server Agent Security

http://msdn.microsoft.com/en-us/library/ms190926(v=sql.105).aspx

 

5. SQL Server Agent Fixed Database Roles

http://msdn.microsoft.com/en-us/library/ms188283(v=sql.105).aspx

 

6. The Run As drop-down list to select PROXY ACCOUNT in the General page does not apply to T-SQL scripts; it only applies to other step types that interact with the OS, such as PowerShell scripts, CmdExec, ActiveXScript, SSIS packages, etc. For T-SQL scripts you have to use EXECUTE AS to run it under different security context, more details are available on following links,

 

Ref. for this info:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/6f507217-5594-40f5-9533-4a886f45871c/sql-job-run-as-dont-show-the-users-in-the-drop-down

Articles to read for EXECUTE AS information:

http://sqlblog.com/blogs/tibor_karaszi/archive/2009/09/19/sql-server-agent-jobs-and-user-contexts.aspx

http://technet.microsoft.com/en-us/library/ms181362(v=sql.105).aspx

 

 

Other useful References:

 

SSIS and SQL Server Agent:

http://bi-polar23.blogspot.ca/2007/12/ssis-and-sql-server-agent.html

http://stackoverflow.com/questions/6712811/how-do-i-create-a-step-in-my-sql-server-agent-job-which-will-run-my-ssis-package

 

Details about Job creation with multiple options:

http://allcomputers.us/windows_server/sql-server-2008—sql-server-agent-(part-2)—jobs—proxies.aspx

 

http://blogs.msdn.com/b/varun_sharma/archive/2007/08/30/how-to-run-sql-server-agent-and-sql-server-jobs-with-least-privilege-in-sql-server-2005.aspx

Download PDF

About the Author: Nitin G

Indian born, trekker, biker, photographer, lover of monsoons. I've been working full time with SQL Server since year 2005 and blogs to post the content aquired during my research on new topics or fixing issues faced by me as a DBA while working in different kind of projects, hope some of my posts may helps others in SQLDBA community. Everything you read on my blog is my own personal opinion and any code is provided "AS-IS" with no warranties!

Leave a comment

Subscribe to this blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 195 other subscribers

Translate this blog!

EnglishFrenchGermanItalianPortugueseRussianSpanish

Calender

August 2019
M T W T F S S
« Nov    
 1234
567891011
12131415161718
19202122232425
262728293031  

View Post by Categories

%d bloggers like this: