Search code examples
ssisssis-2012

SSIS Package variables required for user name and password?

We are creating SSIS packages for our data feeds. Our connection manager has our individual usernames and passwords. Is there a way we can create environmental variables?

Also when we deploy our packages to dev or test or prod environments, do we need service accounts?

Solution

  • Yes, you can protect credentials.

    1. Create Parameter variable and then select sensitive. This will block out the credential with astericks '*'
    2. Set both the project and package Protection Level as either EncryptSensitiveWithPassord, or EncryptSensitiveWithUserKey. Note: failure to set both will result in a compiler failure that says the consistency check of the Protection Level failed - this is required.
    3. Deploy the package to the server. Note: if the package is encrypted then anyone else who tries to open the package will be unable to do so. This is an important point for deployment purposes. It's not a big deal if you are handing off the *.ispac file, but if your company deploys via the DBA opening the solution and deploying from there then they will be unable to do so. I think you can do this if you EncryptWithPassword and then share the password, but EncryptWithUserKey will not work.
    4. Set the Environment variable value on the server and then mark it as sensitive as well.

    I don't recommend storing user credentials in protected parameters, but SSIS has been designed with this in mind if necessary. I much prefer doing everything through Windows Authentication and I highly recommend you to do the same if that is available to you.

    Regarding service accounts. Yes, you can use those. In fact, it is highly recommended that all production deployments use service accounts where the concept of least privilege is implemented. So, the service account should only be granted the bare minimum level of privileges necessary. Said differently: DO NOT GRANT THE SERVICE ACCOUNT ADMINISTRATOR PRIVILEGES. This means specifying each individual privilege on each object. For example, SELECT only on dimension tables and SELECT/INSERT/UPDATE/DELETE on fact tables.

    The best way for determining what privileges to grant is to go through the package and identify all tables that are touched as well as the command that is used when touching them. So, you will need to look at the following: Execute SQL task, OLE DB Source, OLE DB Destination, OLE DB Command, Lookup, etc.