SQL Server 2000 Reporting Services Activation Process

Article
01/28/2010

By Matthew Hofacker,Brian Hartman

Summary: SQL Server 2000 Reporting Services uses cryptography to protect account data. Learn how SQL Server 2000 Reporting Services generates and stores the keys required to encrypt data.

About Cryptography

MSDN, the Microsoft Developer Network, defines cryptography as "the art and science of information security which includes information confidentiality, data integrity, entity authentication, and data origin authentication."

Cryptography centers around the use of "keys" to encrypt a message so that only the people who have the appropriate key in their possession can decipher the scrambled message. Two main types of encryption are used: symmetric and asymmetric.

Symmetric encryption is an encryption process that uses a single key to encrypt and decrypt data. This is a very fast encryption and decryption process. A symmetric key is sometimes called a session key.

Asymmetric encryption is an encryption process that uses two keys—a public key and a private key. Data encrypted with either the public or the private key can only be decrypted with the other key. This is a very slow process, roughly 1,000 times slower than the symmetric encryption process.

Cryptography and Reporting Services

Microsoft® SQL Server™ 2000 Reporting Services uses a combination of symmetric and asymmetric encryption. Symmetric encryption is used to store data in the Report Server catalog. Asymmetric encryption is used to protect the symmetric key. This is because a symmetric key can be shared when necessary to encrypt and decrypt data whereas a public and private key pair can not be shared because whatever is encrypted with one key must be decrypted with the other. In Reporting Services, two or more services may need to read and write data to a single Report Server catalog; both forms of encryption are necessary to ensure that the data is secure.

A single symmetric key is used for each Report Server catalog to encrypt and decrypt critical data that is stored in the catalog, such as connection strings, data source credentials, and file share permissions for subscriptions. This means that all computers in a Web farm scenario need to share this same symmetric key. The problem with a symmetric key is how to store it safely so that only the proper users can access it and read the data from the Report Server catalog. This is where the asymmetric encryption comes into the picture.

Each account running a Reporting Service service is assigned a public and private key pair. Each account gets the symmetric key encrypted with its public key and then both the public asymmetric key and the encrypted symmetric key are stored in the Keys table for each service.

The process of generating and storing the keys for each account makes up the activation process in Reporting Services.

Activation Process

The activation process of a Report Services installation has two phases: Announce Self and Activated.

The Announce Self phase occurs when the respective service has added a row to the Keys table. That row includes the installation ID, Client ID, and the public key of the service.

A service is activated when it gets a copy of the installation’s symmetric key. The key is encrypted with the public key of the respective service. The encrypted symmetric key is stored to the row for that service in the Keys table. The following illustration shows the Keys table of a fully activated standard installation of Reporting Services.

Next, the Report Server Windows NT Service activates itself. This functionality is built into the service. Every time the service starts, it connects to the Report Server catalog and examines the Keys table. The Report Server Windows NT Service searches for its InstanceID and Client values to determine its unique row in the table. The InstanceID value is read from the InstallationID element of the RSREPORTSERVER.CONFIG file. If it does not find a matching row and finds no other rows with a non-null symmetric key value, it initiates self activation. The self-activation process generates a symmetric key for use with the Report Server catalog installation. The self-activation process then generates an asymmetric key pair that it uses to encrypt the symmetric key and then the encrypted symmetric key is stored in its respective row in the Keys table. Only then is the Report Server Windows NT Service activated.

The Report Server Web Service connects to the Keys table at initial startup every time. It examines the Keys table for its unique row just like the Report Server Windows NT Service does at each startup. If the Report Server Web Service does not find a matching row, it generates an asymmetric key pair for itself and stores it in the Keys table along with its InstanceID and Client values. At this point the Report Server Web Service has reached the state of “Announce Self.” It then requests the Report Server Windows NT Service to encrypt the installation’s symmetric key using its newly generated public key that the Report Server Web Service just stored in the Keys table. After the Report Service Windows NT service stores the encrypted symmetric key in the Keys table, the Report Server Web Service reads it back out and it is activated.

In non-Web farm scenarios the RSACTIVATE tool is of no use in activating an installation. This an artifact left over from the beta.

Identifying Failed Activations

When an installation of Reporting Services has completed but failed to activate, it is useful to determine at what phase of the activation process the failure occurred. This is useful because the activation of the Web Service is dependant on an activated Windows NT Service. Use the following steps to determine the point at which the failure occurred.

Check the Services applet in Control Panel to verify that the Report Server Windows NT Service shows it is running.
Even if the Report Server Windows NT Service shows that it is running, this does not mean that it is activated and functioning. To check this, examine the log file for the service. This log file is located by default in the Log Files folder in the
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services directory. The log file name is ReportServerService__timestamp.log.
In the log file for a fully activated Report Server Windows NT Service, you will see entries such as the following:

"EventPolling polling service started"

"i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 0 running jobs"

The following illustration shows a successfully activated Report Server Windows NT Service.
If you do not see entries like those in the previous illustration, then the Report Server Windows NT Service is probably not activated. In that case, you will find log file entries such as the following:

ReportingServicesService!crypto!d58!4/26/2004-12:25:04:: i INFO: NT Service not activated

ReportingServicesService!library!d58!4/26/2004-12:25:04:: Attempting to start service again...

The following illustration shows a Report Service Windows NT Service that is failing to activate.
If the Report Server Windows NT Service is not activated, the log file for the Report Server Web Service will have errors similar to those in the Windows NT Services log files. This log file is located by default in the Log Files folder in the
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services directory. The name of the file to examine is ReportServer__timestamp.log. Until the Report Server Windows NT Service is successfully activated, there is no need to examine the Report Server Web Service log files.
Examine the Keys table in the Report Server catalog. You want to find data in both the PublicKey and SymmetricKey columns similar to what is shown in the following screen shot. The illustration shows the Keys table of a successfully installed Report Server Service installation.

There are two common states the installation can get into depending on the keys and activation process: rsReportServerDisabled and rsReportServerNotActivated.

The rsReportServerDisabled state yields the error, "The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content and then restart the service. Check the documentation for more information." This occurs when the service finds its appropriate row but cannot decrypt the symmetric key. This is most commonly caused when the account that the service is running under is changed. The error can come from either the Windows NT Service, the Web Service, or both.

The rsReportServerNotActivated state yields the error, "The report server installation is not activated." This occurs when the service has inserted its row in the Keys table but is waiting to get the symmetric key value. This situation is most commonly seen when the Report Server Windows NT Service has been stopped and the Report Server Web service is attempting to activate. It is also seen when the Report Server Web Service has it’s activate process initiated by a user who does not have local administrator permissions. It can also occur in a Web farm scenario, where another installation has announced itself and is awaiting activation. In this situation, use RSACTIVATE.EXE from an activated installation and point to the installation needing activation using the "-m" switch. It may be necessary to use the “-u” and “-p” switches and specify Windows credentials that have local administrator permissions on the computer needing activation.

No special permissions are required to call the APIs used to generate keys used in either asymmetric or symmetric encryption.

Private keys are stored in the following locations by default:

Documents and Settings\<username>\Application Data\Microsoft\Crypto\RSA

Documents and Settings\All Users\Application Data\Microsoft\Crypto\RSA\MachineKeys

Handling Failed Activations

The following steps show how to manually fix most installations that are failing to activate.

Shut down the Report Server Windows NT Service.
Run RSKEYMGMT using the -e, -f, and –p switches to extract the current symmetric key. If this is a new installation and no reports have been uploaded to the Report Server, you can skip this step.
Open the RSReportServer.Config file located in the C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer directory and locate the tag <InstallationID>. This value will be used in an upcoming step.
Verify that the user who is going to initiate the activation process for the Report Server is logged on to the Reporting Services/IIS machine as someone who has Local Admin credentials.
Run RSKEYMGMT using the -r switch, and pass the value that you found in the RSReportServer.Config file in step 3 of this procedure. Please note that this operation will stop and restart the Report Server Windows NT Service and IIS.
Examine the Report Service Windows NT Service log files to make sure that the service successfully activated. You can also check this by examining the Keys table to verify that there is a row where Client = 1 and the Public and Symmetric columns return non-null values.
Ask someone with Local Admin permissions on the IIS machine to browse to https://localhost/reportserver to force the Web Service to activate. This will activate the Report Server Web Service.
Examine the Report Server Web Service log files to make sure that the service successfully activated. You can also check this by examining the Keys table to verify that there is a row where Client = 0 and the Public and Symmetric columns return non-null values.
If you used step 2 of this procedure to back up the symmetric key, reapply the symmetric key using RSKEYMGMT and the -a, -f, and –p switches, where the value for –f is the file created in step 2 and –p is the same password used in step 2.

References

A Primer on Cryptography

https://research.microsoft.com/crypto/firstcrypto.aspx

Cryptography (Platform SDK)

https://msdn2.microsoft.com/library/ms950404.aspx

Public/Private Key Pairs