September 22, 2011 at 10:25 am
Hi,
I have a SQL 2005 stored procedure that calls .Net clr procedure that read that call a SSRS report, generate a PDF and save it under a directory.
The procedure runs fine under user credidential userA which has necessary rights on the folder.
The challenge is to call the stored proceedure in a scheduled job to run successfully.
I have
1)modfied the procedure to 'execure as 'userA'
2)I have also configured the step in the job to run as 'userA'
3) execute as user = 'userA; exec the_procedure
without any success.
Grateful if any of you could please advise on the solution.
Thank you very much in advance
September 22, 2011 at 10:29 am
Hmm, I might debug this by having the procedure run one of those steps and see if it works. It sounds almost like you have some delegation or impersonation issue, but I'd think if they job runs as "UserA", it would work.
Can you run just the CLR proc and have that work in the job? Or can you have a call to the report only? I'd try to narrow down if you have an SSRS permissions issue or a folder permission issue.
September 23, 2011 at 2:57 am
The clr procedure is itself wrapped in another stored procedure, as follows:
create procedure the_procedure
as
begin
exec the_clr_procedure
end
1) Logging on SSMS as 'userA' and exec the_procedure does create the pdf file.
2) Logging on SSMS as 'sa' and doing "execute as user = 'userA; exec the_procedure" does not generate the file. This would confirm this does not make the_clr_procedure run under userA. But surely 'the_procedure' must be running under context of 'userA' ?!!
3) Finally the objective is to make everything run as a scheduled sql server agent job.
Up to now all my various atttempts have failed.
Is above possible ? If yes, can you please point me to concrete command/configuration examples.
Thank you.
September 23, 2011 at 9:14 am
Yes and no. You have the right idea, but the problem is that UserA is inside SQL Server. When you execute the CLR, you should still be inside SQL Server, but if you call out to an external service, then SQL Server has two choices. It can impersonate the user's credentials for calls to SSRS and the file system, or it can use it's own. However if you are doing execute as, the SQL Server engine doesn't store those credentials, esp if they are Windows ones. It depends on the user having credentials.
I would think that setting the UserA credentials in the SQL Agent job and just having
exec the_procedure
in the job step would work. If you do that, and trace it, do you see UserA logging into SQL Server?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply