If you understand how Reporting Services uses encryption then you will understand how to manage the encryption keys, recover from any encryption failure and more importantly, not to get in an unrecoverable situation. Although Books Online has some information about encryption keys, I feel the information is not laid out in an intuitive way.
This is the first of two articles where I will first be explaining encryption and how Reporting Services creates the keys. The second article will explain how to maintain the keys and various recovery scenarios.
Overview of Encryption
The following paragraphs explain what encryption is in general.
What is Encryption?
Encryption is used to turn some human readable piece of data into something which is not human readable to ensure the information is not compromised. We refer to this process by saying we encrypt the data. A good example would be your password. In order to turn a non-human readable piece of data back in to something that is readable, we decrypt the data.
The process that encrypts and decrypts the data uses mathematical algorithms. The algorithm will generate a random number which becomes the Key. This key will be used by the algorithm to generate the encrypted data.
While I will be explaining encryption I will only be giving you sufficient information to understand the role it plays in Reporting Services. If you would like a better understanding of Cryptography I recommend watching the following webcast at IT’S Showtime
What types of Encryption Algorithms are there?
There are numerous encryption key algorithms used today so rather than explain all the different types, I will explain what Reporting Services uses.
Reporting Services uses two algorithms, a Symmetric Key algorithm and an Asymmetric Key algorithm.
· A Symmetric Key algorithm uses the same key to encrypt and decrypt the data.
· An Asymmetric Key algorithm uses one key to encrypt the data and another key to decrypt the data.
With a Symmetric Key algorithm all parties who are involved with encrypting and decrypting data have a copy of the same key. The algorithm used by Reporting Services is the 3DES or Triple DES as it is pronounced.
With Asymmetric Key algorithms firstly; each key has a name. One key is called the Public Key and the other key is called the Private key. The Private key is never shared out whilst the Public key on the other hand is given out to anyone involved with exchanging the encrypted data. Asymmetric algorithms only allow data to be encrypted with the Public key and decrypted using the Private key. Reporting Services uses the RSA algorithm.
Why are Multiple Algorithms used?
In Cryptography it is standard practise to use one key to encrypt the data and then a second key to encrypt the first key which was used to encrypt the data. The reason for this is to prevent a malicious user from getting hold of the first key which will allow them to decrypt the data.
When it comes to deciding which key to use, there is a simple rule you use. You always encrypt data using a Symmetric key and you always encrypt a key using an Asymmetric key. The reason for this is because encrypting data using a Symmetric key is 100’s of times faster than encrypting the same data using an Asymmetric key however; an Asymmetric key is harder to crack than a Symmetric key.
By the way, if you were wondering about Certificates, these are Asymmetric keys so whilst you could encrypt data with them, the same rules apply. Certificates are not used in the context of this article however; I will be discussing Certificates in a future article on using Reporting Services over SSL.
Encryption Summary
So to summarise; Reporting Services uses two algorithms for encryption, one is the 3DES Symmetric algorithm to encrypt data and the second is an RSA Asymmetric algorithm to encrypt the Symmetric key.
A side note here. If you are considering using encrypted columns in SQL Server, follow my advice here. Use a Symmetric key to encrypt the data and use an Asymmetric key or Certificate to encrypt the Symmetric key. That’s a quick tip just for you.
Generating the Encryption Keys
The following paragraphs go into detail on how the keys are created and where they are stored in Reporting Services.
In order to fully understand how keys are created I need to explain about Scale-Out Deployment because there are additional steps involving encryption that need to be followed in order to complete the installation.
In a basic configuration there is one Reporting Services server and one SQL Server containing the ReportServer and ReportServerTempdb database.
In a Scale-Out configuration there are two or more Reporting Services all pointing at the same SQL Server containing the ReportServer and ReportServerTempdb database. This enables multiple reports to be run simultaneously on different machines for performance reasons.
How are the Symmetric and Asymmetric Keys Created
It is important to realise that all keys are managed by the Reporting Services windows service. Any encrypting or decrypting is done by the windows service. If the Web Service requires some data decrypted, it sends an RPC request to the windows service to do it on its behalf.
So this means that the windows service has to be running at all times, something which was not required on Reporting Services 2000.
The Asymmetric keys are generated by the Cryptographic Services service during Reporting Services configuration based on the account name running the windows service. This means that if you change the account name, the asymmetric keys become invalid. Also a very important point to bear in mind is the account password. If you change the password of the windows service account by specifying the old password followed by the new password, the keys remain the same and everything is fine. If you have a system administrator reset your password by not specifying the old password, the keys become invalid. I will explain some recovery situations below. Every Reporting Services installation has a different Public key
The symmetric key is also generated by the Cryptographic Services service during Reporting Services configuration however; the key is not based on the account running the windows service and in fact no change of any sort will cause it to become invalid. The Symmetric key is generated by the first server configured in a Scale-Out deployment; the other servers simply get a copy. This ensures each server is able to decrypt the data.
Configuring the first Reporting Services Instance.
The first instance is always created regardless of whether you are deploying a standard configuration or a Scale-Out deployment.
Whether you install using a Default Configuration or you are doing a Files Only installation and configuring Reporting Services using the Reporting Services Configuration Manager, the result is the same.
During the Database Setup part of the configuration we enter what is known as the Activation Process that consists of two phases.
The first phase is called the Announce phase which connects to the ReportServer database and runs the stored procedure AnnounceOrGetKey. This checks a table called Keys for a row specific to this Reporting Services installation and if none is found the following information is added:
|
Column |
Description |
|
MachineName |
This is the host name of the Reporting Services machine |
|
InstanceName |
This is the Instance name of the Reporting Services installation. You can have multiple instances of Reporting Services on the same physical machine |
|
InstallationID |
Every Reporting Services installation has a unique ID stored in its rsreportserver.config file which is created in the ReportServer virtual directory. |
|
Client |
This is always 1 |
|
PublicKey |
This is the public key created by the Reporting Services windows service and is unique for each service. |
The second phase is called the Activation phase. This phase gets the Symmetric key from the Reporting Services windows service which will be used to encrypt the data and encrypts the symmetric key with the Public key. A stored procedure called SetKeysForInstallation is run which updates the Keys table and adds the encrypted symmetric key into the SymmetricKey column. At no time during any of this process is the Symmetric key ever shown decrypted.
Configuring a Second Server in a Scale-Out Deployment
When you configure a second server as part of a Scale-Out deployment you will need to do a Files Only installation. This will require you to run the Reporting Services Configuration Manager to complete the installation. I am not going to explain the exact steps since I will be doing this in a future blog however; you complete the installation just like the first server until you get to the Database Setup part.
Normally at this stage you would be connecting to a SQL Server that does not have a previously created ReportServer or reportServerTempdb database created however; in a Scale-Out deployment you will be connecting to a SQL Server where the databases were created when you configured the first server instance.
Again during the Database Setup we enter the Activation process which contains the two phases. In phase one which is the Announce phase, the actions are completed in the same way as it was done when configuring the first server however; the difference comes when we enter the Activation phase.
In order to complete the second phase we need the decrypted Symmetric key so we can encrypt it with the second instances public key and store it in the Keys table. The problem we face is that it can only be decrypted by the first instances private key so phase two has to be completed by the first server. You run the Reporting Services Configuration Manager and connect to the first server. When you run the initialization step in the Configuration Manager, the Symmetric key is decrypted using the first server’s private key and then encrypted using the second server’s public key then stored in the Keys table. This is why the Public key for each server is also stored in the Keys table.
At this stage we have a Keys table with two rows. One row is for the first server and the other row is for the second server. Both rows contain the same symmetric key but each has been encrypted with a different public key.
So now you understand how the keys are created, the next article will explain how to maintain the keys and various recovery scenarios.