February 23, 2009 at 1:26 pm
We are having an issue regarding the functionality of an SSIS package.
We have a SQL task that uses the OPENQUERY statement utilizing a linked server (DataSource = IBM teradata).
When we test it in BIDS it runs without a hitch - because it is using the credentials associated with the user executing the package. When we SCHEDULE the process it fails because it executes the package with the credentials of the domain user account of the SQL server.
Because we are a financial institution, our information security division is rightfully hesitant to assign rights to the domain service account. Their thought process is that they need a way to control access on a per user basis for the server because the many different users creating scheduled packages have varied access rights. I agree.
Is there a way to get a scheduled job to "emulate" or 'impersonate" a particular user, or pass users credentials with and OPENQUERY statement?
Any ideas to help circumvent this issue are welcome.
February 23, 2009 at 1:37 pm
You could run it with a proxy account for the Agent using Admin level permissions but in IBM or other RDBMS the owner of the package must also be admin. Check the thread below for a solution for DB2 AS400.
Kind regards,
Gift Peddie
February 23, 2009 at 2:13 pm
A SSIS package does not have to be scheduled through SQL Agent, it can run on it's own entirely outside of SQL Server.
As such you can schedule it using whatever Enterprise-wide scheduler your information security division has approved for executing Jobs in the Enterprise with the approved accounts/owners/identities/permissions, etc. This is not a problem in any way unique to SSIS.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 23, 2009 at 2:19 pm
I prefer the Agent because I have used a package that runs for five hours a day five days a week moving data from DB2 AS400 to SQL Server 2000 at almost real time.
Kind regards,
Gift Peddie
February 23, 2009 at 4:32 pm
Gift Peddie (2/23/2009)
I prefer the Agent because I have used a package that runs for five hours a day five days a week moving data from DB2 AS400 to SQL Server 2000 at almost real time.
Not sure what you mean, Gift. There is nothing about SQL Agent that I know of that would necessarily be inherently better for a Package like this?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 23, 2009 at 4:48 pm
I have run it in a bank and it works without any other cost, these are some of the reasons SSIS is not redistributable as DTS and the Agent was one of the first 64bits process in SQL Server. And there are many new SSIS roles defined in SQL Server 2008 MSDB. I also know the Windows scheduler I am not aware it can run one operation for five hours a day five days a week.
On a side note those third party tools are doing what I can do with DMO/SMO code in a GUI and extra cost.
Kind regards,
Gift Peddie
February 23, 2009 at 4:57 pm
I do not understand what "other costs" you are talking about. And, I was talking about an Enterprise scheduler. I don't know anyone who would consider the Windows scheduler as an Enterprise scheduler.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 23, 2009 at 5:07 pm
The Enterprise schedulers used with ETL that I know comes with cost if you know a free one let know so I can take a look at it. The reason companies I work for don't pay for Enterprise scheduler.
Kind regards,
Gift Peddie
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply