August 21, 2015 at 7:44 am
In the Create Catalog Dialog it states at the bottom:
"You can manage the encryption key by creating a backup. If you migrate or move the Integration Services Catalog to another SQL Server instance, you can restore the key to regain access to encrypted content."
I referenced the link article below "Backup, Restore, and Move the SSIS Catalog" because it states how to backup the master key. The Master Key has been previously backed up on this particular server for the master database when we set up TDE for a user database on this server. On this particular server the following has already been done:
1) Service master key created by default with SQL Server Install.
2) Master key created using the Master Database.
3) Certificate created (protected by Master Key).
4) User Database Encryption Key created.
5) User Database enabled for TDE.
6) The Certificate, Master Keys and Service Master Key backed up.
My question are:
A) Do I need to execute this Backup Master Key command again? If so, should I specify to execute it against the SSISDB Database?
It does state in the linked article below (Step 2) to "Back up the master key for the SSISDB database" but it does not provide the USE SSISDB command.
B) I was under the impression that you have to 'back up the master key' only once on a server. (I am still learning about encryption.) Should I back up the master key once for the master database and/or back it up for each user database?
Linked Article:
Backup, Restore, and Move the SSIS Catalog
https://msdn.microsoft.com/en-us/library/hh213291(v=sql.120).aspx
1. Open SQL Server Management Studio and connect to an instance of SQL Server.
2. Back up the master key for the SSISDB database, by using the BACKUP MASTER KEY Transact-SQL statement. The key is stored in a file that you specify. Use a password to encrypt the master key in the file.
For more information about the statement, see BACKUP MASTER KEY (Transact-SQL).
In the following example, the master key is exported to the c:\temp directory\RCTestInstKey file. The LS2Setup! password is used to encrypt the master key.
backup master key to file = 'c:\temp\RCTestInstKey'
encryption by password = 'LS2Setup!'
August 21, 2015 at 7:47 am
Sorry, meant to state that we are running SQL Server 2014 EE SP1.
August 21, 2015 at 8:46 am
The encryption keys for the SSIS Catalog database is different and separate from your TDE keys. You should backup the SSIS encryption keys as well.
Outside the scope of this question, but the same would apply to SSRS.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply