Encryption Keys (Reporting Services Configuration)

Ok I am playing around with SQL Server 2005 got it all setup. Then I decided to change the admin password and run dcpromo. Now I got all the issues resolved except this one. It had a key from what the server use to be called. I deleted it to make a new key for the state of the server now. Well big mistake I am finding out. Below are the only real directions I have found to recreate the key and it is not a lot of help. Does anyone know how to do this? Not like SQL Server 2000 to many choices and I got click happy on over load. Thanks in advance...

SQL Server Setup Help

Encryption Keys (Reporting Services Configuration)
Delete
Deletes the symmetric key and all encrypted content, including connection strings and stored credentials. You should only delete the symmetric key if you cannot restore it.

Once you delete the symmetric key, you must re-enter the missing connection strings and stored credentials in the reports and shared data sources that no longer have these values. You must also update all subscriptions that use delivery extensions that store encrypted data. This includes the file share delivery extension and any third-party delivery extension that use encrypted value.

There is no automated way to update this information. Each report, subscription, and shared data source that uses stored credentials and connection strings must be updated one at a time.

Initialization (Reporting Services Configuration)
Remove
Click Remove to remove the encryption keys of the selected report server instance from the report server database. You can remove keys to remove a report server from a scale-out deployment. With this option, only the encryption keys for the specified report server instance are removed. Encrypted data in the report server database is not affected.

As a precaution, be sure to create a backup copy of the symmetric key before you remove it. Once you remove the encryption keys of the last report server in the list, you introduce new requirements for any subsequent report server initialization for that database. The new requirement is that after you initialize a report server you must restore a backup copy of the symmetric key. Restoring the symmetric key is necessary if you want to access the encrypted data that is currently in the report server database.

If you no longer need the encrypted data or if you do not have a backup copy of the key, you must delete the encrypted data. For more information, see Encryption Keys (Reporting Services Configuration).

Event Viewer Log

Event Type: Error
Event Source: Report Server Windows Service (MSSQLSERVER)
Event Category: Management
Event ID: 107
Date: 2/9/2006
Time: 11:13:32 PM
User: N/A
Computer: VM-IPDG3
Description:
Report Server Windows Service (MSSQLSERVER) cannot connect to the report server database.

If you don't have a backup of your key and you've deleted it, then the only thing you could do is to create a new key. From the "Encryption keys" panel in the config tool, click "change". By now you've lost all secure information, so you should reenter them. You need to redo the "Database setup" panel, and the credentials for data sources.|||

James Wu

Thanks for the reply I will try this when I get home. On the "Encyption Keys" panel in the configure reporting sections if memory serves me. The Change was disabled and the only two buttons out of the four I could click were Restore and Delete. The Change and Backup buttons were disabled. Can you supply a step by step to get where you are talking about.

I know if you go into

Microsoft SQL Server 2005|||

Yes your are right. "Change" and "Backup" will be disabled if your report server is not initialized. Here is a more detailed article on how to delete and recreate encryption keys :) http://msdn2.microsoft.com/en-us/library/ms156010.aspx

|||

James Wu

Thanks for the link I will research it now. I am using vmware so I reverted back a snapshot and started over. Made a few dumb errors that I should have been smarter not to do. Thanks Again

Encryption in SSIS package - symmetric keys

1) We have OLTP DB on 2000 & OLAP DB on 2005.

2) We have decided to use SSIS for migration and interface is BIDS to develop, build, deploy & install SSIS packages.

3) We even have to encrypt some tables colmns data while reading from source so that it is secure in network and gets into target database as such requiring decryption for reports and front end display.

4) For database Security, we are going to use AES 256 Symmetric Encryption. We will be using RSA for Asymmetric Key Encryption, 1024 Bits.
Can you please give links on these 2 encryption mechanisms.

5) Where can we use Symmetric Encryption & Asymmetric Key Encryption i.e on columns of tables i.e. data or password & based on what do we decide which to use?

6) Please post links/code to generate these keys. Where do we fit this code in SSIS package & where do we use the keys. Can you give me code for keys fitted into
following query:
insert into EmpDest
select e.empno, e.ename, d.deptno, d.dname
from emp e, dept d
where e.deptno = d.deptno
go

Thanks in adavnce.

I've created a feature request to load symmetric keys into SSIS. As it is now, I issue a batch SQL statement to encrypt the data outside of SSIS.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=256770

Encryption in SSIS package

1) We have OLTP DB on 2000 & OLAP DB on 2005.

2) We have decided to use SSIS for migration and interface is BIDS to develop, build, deploy & install SSIS packages.

3) We even have to encrypt some tables colmns data while reading from source so that it is secure in network and gets into target database as such requiring decryption for reports and front end display.

4) For database Security, we are going to use AES 256 Symmetric Encryption. We will be using RSA for Asymmetric Key Encryption, 1024 Bits.
Can you please give links on these 2 encryption mechanisms.

5) Where can we use Symmetric Encryption & Asymmetric Key Encryption i.e on columns of tables i.e. data or password & based on what do we decide which to use?

6) Please post links/code to generate these keys. Where do we fit this code in SSIS package & where do we use the keys. Can you give me code for keys fitted into
following query:
insert into EmpDest
select e.empno, e.ename, d.deptno, d.dname
from emp e, dept d
where e.deptno = d.deptno
go

Thanks in adavnce.

I've created a feature request to load symmetric keys into SSIS. As it is now, I issue a batch SQL statement to encrypt the data outside of SSIS.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=256770sql