SSIS and Security - need help

[katbear]

Hello,

I realize that I am confused about SSIS and security.

In BIDS, I work on and modify my packages. That part I understand. Then, I want to build my project, then deploy to SQL Server 2005. I know how to do that too (for the most part, please see below):

My confusion arises around the "Protection Level" options in the package properties. Right now I have everything set to "EncryptSensitiveWithUserKey". My understanding is that I need to change this in order to run my packages from SQL Server jobs, because only the creator of the package can currently run the job.

So my question is, since I want to deploy to SQL Server, don't I want to change the "Protection Level" to "ServerStorage"?

However, it will not let me change the protection level to ServerStorage. It says "The protection level, ServerStorage, cannot be used when saving to this destination"... presumably because it is attempting to save to the file system.

However, even when I built out my packages and saved to SQL Server, I could not change the protection level either.

Which is why I am really confused... Once you save your packages to SQL Server, how do you make changes to the packages, so that the changes are reflected in the packages stored on SQL Server?

There is some concept I am not understanding here.

Thanks for any help

 

[Catadmin]

You can't make changes to a package saved up to SQL Server. The only thing you can do is export the package back to your PC (unless you have it saved in Visual Source Safe or other versioning software), make your change and save it back up to the server.

Here's how the encryption works:

Don'tSaveSensitive (strips out the sensitive information like passwords).

EncryptSensitiveWithUserKey (encrypts the sensitive information like passwords with a specific user key. Forces that user to be the one to run the package or, if some other user, for the user running it to provide proper credentials)

EncryptSensitiveWithPassword (encrypts the sensitive information like passwords with an "external" password that is required by ALL users before the package will run)

EncryptAllWithPassword (encrypts the entire package ala DTS with an "external" password that is required by ALL users before you can run it, open it, edit it, etc.)

EncryptAllWithUserKey (encrypts the entire package with a specific User key which means only that user can open it, run it, edit it, etc.)

ServerStorage (encrypts the package using SQL Server Storage Encryption).

<RE: ServerStorage>

This option is supported only when a package is saved to the SQL Server msdb database. It is not supported when a package is saved to the file system from Business Intelligence Development Studio.

Hope that helps you out.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Catadmin]

Opps. I forgot to mention something.

By default, the folder that saves under an Integration Services component is File System. Usually, it is set up to point to a specific location on the server. You can change this behavior by editing the MSDtsSrvr.ini.xml file usually found under Program Files\Microsoft SQL Server\90\DTS\Binn.

The usual information is: 
<Folder xsi:type="FileSystemFolder">
      <Name>File System</Name>
      <StorePath>..\Packages</StorePath>
    </Folder>

Add:
    <Folder xsi:type="SqlServerFolder">
      <Name>MSDB</Name>
      <ServerName>Myserver\MyInstance</ServerName>
    </Folder>

Now, you can comment out the File System information (which I recommend if you're not going to use it), or leave it there to give your people the change to do either MSDB saving or FileSystem saving. I don't recommend over-writing or deleting the FileSystem stuff, though, because you may need it later on.

Just don't forget to change the MyServer\MyInstance part of the above code. And you only need the "\MyInstance" if you actually use Instance names.

Hope this helps.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[katbear]

Thanks for the info.

Here's what I ended up doing. Instead of using the deployment tool to save the packages to SQL Server, I just did a SAVE COPY AS ... for each package (I only had a few anyways).

When I do it this way, I have the option of setting the encyption level to use SQL Server storage.

Now, I see all my packages in the msdb database, and everything works fine now. No more permissions problems when running my packages through jobs.

Does this sound like I did the right thing?

Thanks