6

We have a database using the AlwaysEncrypted feature of SQL Server 2016.

We need to create SSIS packages (deployed on SSISDB) for certain application tasks.

What options we have to store the key securely so those SSIS packages can use that key to decipher the data. Assuming the application DB and SSISDB are on same server

Alternatively, Would it be mandatory/good practice to have the application DB and SSISDB both on different servers ?

  • If you put the keys on the DB server, that's kind of like leaving the keys inside the car. If someone gets into the car, they can start it up and drive off with your data. I'd guess you don't want that, and probably want to treat SSIS like an application server and keep it separate. – AMtwo Apr 7 '17 at 15:37
1

There are a few parts to implement proper security in your environment:

  • SSIS packages are encrypted by default when referring to sensitive information, you can set with their own password
  • SSIS can also be signed by a certificate
  • There is a master key, that ideally would be in a KM outside the SQL Server
  • There is a database key
  • There is the user that you would use for running the package, which you should grant specific access and deny access to anything the package shouldn't touch

There is nothing that says you would have the keys on the same server; but even if you have them in the same server, it doesn't mean they would have the same security user. Best practice would be to have a separate KM server.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.