Running a TSQL type job (not SSIS) through Proxy

Question

Running a TSQL type job (not SSIS) through Proxy

balasach82

SSCrazy Eights

Points: 9653
More actions
August 24, 2012 at 10:57 am

#260545

I have an job which has SQL queries and Stored procedures. Can this step be run through an proxy account. I created credential/proxy, but it didnt list down in "Run As". Can proxy be give only for SSIS, ActiveX, Replication etc and not for general TSQL queries and SP's?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

balasach82 (8/24/2012)
I have an job which has SQL queries and Stored procedures. Can this step be run through an proxy account. I created credential/proxy, but it didnt list down in "Run As". Can proxy be give only for SSIS, ActiveX, Replication etc and not for general TSQL queries and SP's?

I could be wrong but I believe you have to create a login for the proxy for that to work.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

belvdr SSC Veteran Points: 271 More actions · Answer 2

You cannot assign a proxy directly for TSQL steps.

You have two options:

1. Create an operating system proxy, and use sqlcmd to execute the TSQL.

2. Create a Powershell proxy and use Powershell to execute your TSQL.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

Since it's an SQL Server Agent job, I have to ask... why do you need a proxy to begin with?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 4

I am trying to run a query in multiple server. In a SQL server A, I have created linked servers. When i run the query in QA, it runs,because my account has admin access in those servers. But when i try to schedule it, SQL agent account is different and in some servers, this account many not exist. Hence, I am looking at running the job under a proxy (like my account).

I would be more than welcome to share more information if you need it.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

balasach82 (8/26/2012)
I am trying to run a query in multiple server. In a SQL server A, I have created linked servers. When i run the query in QA, it runs,because my account has admin access in those servers. But when i try to schedule it, SQL agent account is different and in some servers, this account many not exist. Hence, I am looking at running the job under a proxy (like my account).
I would be more than welcome to share more information if you need it.

It would be far better to set things up right so that SQL Server has privs on those other machines so you don't have to use a proxy, especially your login name.

That, not withstanding, have you tried a simple EXECUTE AS?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 6

Yes jeff, I tried to use EXECUTE AS, but it was a bit confusing, since I dont know whether I should give Agent account or my account in EXECUTE AS.

belvdr SSC Veteran Points: 271 More actions · Answer 7

While I agree you shouldn't create a proxy for a standard user account, I see no problem with creating a proxy for a service account. I have done this on all my servers to automate the backup of the SSRS key.