SSIS Package Credentials

Question

Post reply

SSIS Package Credentials

RSP

SSCrazy

Points: 2614
More actions
December 9, 2009 at 8:59 pm

#218629

Comments posted to this topic are about the item SSIS Package Credentials

Viewing 12 posts - 1 through 11 (of 11 total)

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

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 1

Personally, I find using windows scheduling and invoking the SSIS package from a .bat file to be a lot more dependable.

sknox SSChampion Points: 12388 More actions · Answer 2

Samuel Vella (12/10/2009)
Personally, I find using windows scheduling and invoking the SSIS package from a .bat file to be a lot more dependable.

To each his own. I prefer using SQL Server's scheduler because it has better logging and troubleshooting features. In fact if I have a non-SQL-based scheduled job to run on a server that has a SQL server, I'll often use it's scheduler so I can track it in my SQL job tracking system.

Dave Coats SSC Eights! Points: 885 More actions · Answer 3

Which storage option are you using when deploying your SSIS packages from BIDS? (msdb or filesystem?). I have found that storing them to msdb on the server by choosing "File | Save Copy of Package AS" works best. Important: You have to choose "Rely on Server Storage and roles for access control" for Protection Level in the Save As window. By doing it this way, you don't have any problems running the package from jobs and don't have to use a proxy account. Does anyone else do it this way? However, I did find this article very useful in understanding the Security changes in 2005. Thanks!!

Dave Coats

TerryS SSCommitted Points: 1756 More actions · Answer 4

Dave Coats (12/10/2009)
I have found that storing them to msdb on the server by choosing "File | Save Copy of Package AS" works best. Important: You have to choose "Rely on Server Storage and roles for access control" for Protection Level in the Save As window. By doing it this way, you don't have any problems running the package from jobs and don't have to use a proxy account. Does anyone else do it this way?

Dave,

That is exactly how I do it. When I was in SSIS training, another attendee mentioned your described approach to getting around the credentials issue and I've done it that way ever since. So there are at least 3 of us! Unless that was you in my class. 😛

Terry

Idea Deadbeat SSC-Addicted Points: 416 More actions · Answer 5

We use proxy account / credentials and assign this account to the Run As property on a job step. The job does not have to be run under the proxy account (only the job step). I don't agree (or maybe understand) why a separate job executor account is used to create SSIS packages. We have a small team of people who create packages using VS (Pro version) and the jobs can be owned by them or any other account appropriate for the job (may need sa or SQL Agent account if CmdExec type job step is needed). The team has the appropriate SQL Agent roles.

We also use the proxy account when provisioning file share access appropriate for our job steps. I prefer the file system for storing SSIS packages (have done it both ways and think management, visibility and deployment is easier).

Dave Coats SSC Eights! Points: 885 More actions · Answer 6

TerryS - Thats funny! Glad I'm not the only one. That wasn't me in that training but sounds like he was a smart man! 😀

Dave Coats

Dave Coats SSC Eights! Points: 885 More actions · Answer 7

Brad - just a couple ideas I have on File System deployment vs. msdb is:

1) I don't really like having packages floating out there on the server - seems much more secure to have them inside the database

2) If they are in msdb, the packages can be backed up with the scheduled sys backups

3) Don't have to worry about roles/passwords/proxies etc.

Just my thoughts though. Everyone has there own preferences.

Sorry for getting a bit off track Robert....thanks for the informative article!

Dave Coats

RSP SSCrazy Points: 2614 More actions · Answer 8

Thank you, all for your comments, and reading....hope that it is helpful. I think that it's great that many of you experts chime in and add your experiences and knowledge. My goal is to offer up the experiences and solutions that I encounter to assist DBA's that may run accross similar issues.

Moreover, on the "Rely on Server Storage" option, I did extensively reseach this, and believe that one of the links I refernce in my article discusses this.

Here is one useful (didn't put this in the article) link that discusses all the options, including Rely on Server Storage....http://www.databasejournal.com/features/mssql/article.php/3619166/SQL-Server-2005-Integration-Services---Security---Part-28.htm

Nonetheless thank you for adding....

Regards,

Robert Pearl

Idea Deadbeat SSC-Addicted Points: 416 More actions · Answer 9

One of the beauties of proxy and abstracting permissions for a job step is to not tie the step's requirements to the account running the job or the service the job runs under. If you have a remote staging directory on some file share you can use that same domain account assigned to the credential object for read permissions on same staging file shares. It is a nice way to abstract permissions and if the accounts are established with some set of standards then it also provides information to others managing the remote file shares.

We use source control (TFS) for our SSIS packages in order to provide for our versioning and backup needs.

thierry.vandurme Hall of Fame Points: 3340 More actions · Answer 10

I'm missing some things here (principal, job owner, ...). I doubt this (and the link to the summary on codeproject) is the recommended way if you want it to be "totally" secure (if that's ever possible).

The articles grant sysadmin and all MSDB roles. What for? This is way too much (in most cases). Plus, you are assigning all 3 msdb roles. You don't want this login to see ALL jobs, right? They should only see their own jobs. Check BOL and see that these roles follow a hierarchy (the most privileged one includes the permission from the other two). It's like people assigning a login the sysadmin server role AND the securityadmin role. The first one already includes all privileges...

You should be assigning a principal (needs only the public server role) access to the proxy, run the job step under the proxy, set the job owner to the SQL login, grant only the SQLAgentUserRole msdb role to the SQL login, set appropriate permissions on the dtsx file and folder it resides in, ...

We did it like this and it works fine. It needs a lot of work and maintenance though.

m--S3qU3L Hall of Fame Points: 3706 More actions · Answer 11

Hi,

I have a doubt regarding the final article ('How to Schedule and Run a SSIS Package job') mentioned in this article.

Why do we need to give sysadmin server role to the 'executor login' as the whole concept of proxy account is for executing job for under priviledged logins.

Please could any one clarify?

Thanks in advance

John