EncryptSensitiveWithUserKey - question

Hi,

I have a question about the EncryptSensitiveWithUserKey package option.

Currently, this is the option on all my packages (which I then later save to SQL Server using SQL Server storage for encryption).

My question is this: When I log onto the machine where I'm developing the packages I use a common developer login. So my understanding is that if anyone else logs on using the same login as mine, they can see and run the packages in BIDS, yes?

But if someone logs onto the machine under a different login, they can also see my packages in BIDS, but they *shouldn't* be able to RUN them, correct? That's because the passwords are encrypted with MY user key, and all the connection managers that use passwords should fail, right?

Can someone please confirm this.

Thanks

That is my understanding, and based on some quick testing, that is the way it works. I use EncryptSensitiveWithUserKey very rarely, though.

It seems like it would be pretty simple for you to test, as you already have the packages set up.

|||

Yes, it would seem simple. But I don't have any other logins to the server but my own, as I am a consultant, and asking for someone else's login info for testing purposes is beyond the scope of my authority, yet I have to take other people's logins into account.

So, it would seem that since I'm not trying to protect anything in the package including passwords, I might as well set it to "DontSaveSensitive", yes?

|||That's what I would do. I usually set passwords through configurations (or use integrated security, and eliminate the need for passwords altogther).|||

Can you please explain more by what you mean by setting passwords through configurations? How would one do this?

Do you store your packages in the file system? Or in SQL Server?

I find this all terribly confusing, all the layers and security options, especially when scheduling packages in jobs. Right now I have SQL Server handling security, as it was the only way to get my jobs to run the packages, but even then, I don't really understand the implications of doing this.

Thanks

|||

As an example, if you use an XML configuration to set your connect strings, you can edit the configuration to include the password in the connection string. You have to do that outside of Visual Studio, because it will strip the password out by default. Then you just secure the folder that the configurations are stored in.

I use the file system primarily. I find it easier to deploy to, but it's mostly personal preference.

|||

Hmm, so it seems that you could have a group of similar packages all using the same configuration file?

I haven't really figured out how to do this yet, but if I'm understanding this correctly, it does seem simpler because you could just have all the packages reference ONE file, and you only have to update ONE file if a password or login changes, yes?

Then if you set up a job, you would need to reference the config file from the job itself, yes?

|||

sadie519590 wrote:

Hmm, so it seems that you could have a group of similar packages all using the same configuration file?

I haven't really figured out how to do this yet, but if I'm understanding this correctly, it does seem simpler because you could just have all the packages reference ONE file, and you only have to update ONE file if a password or login changes, yes?

Yes. That's a distinct advantage of using configurations. FYI, you can also store them in a database, rather than a file.

sadie519590 wrote:

Then if you set up a job, you would need to reference the config file from the job itself, yes?

You can do one of two things. You can keep the configuration file location in the same path, so that the package always knows where to find it. Or you can use the /CONFIG switch for DTEXEC to specify where the configuration file is located.