Symetric Key Issues

Jul 18, 2007

I'm starting off with some back story to help give an idea of the situation, but if you want to skip to the end, my actual questions are there.

We currently have SQL RS 2000 installed on a development machine on our production network. I've been looking into getting RS installed on an actual server that is backed up, etc. Instead of linking this new server to the existing database, our idea was to install SQL Server on the new server machine and create an all new instance of RS. Yesterday, I loaded SQL Server and RS on the new server and when I rebooted the server, I went to check what the URL was to pull up Report Manager on the old machine and it was suddenly not working. The error I receive on either Report Manager or Report Server follows.

"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. (rsReportServerDisabled)"

I'm not sure whether these two situations are related, but it seems an awful coincidence that the working server would break about the time the new server came online. In any event, here is what I've done troubleshooting so far.

In thinking it might be a conflict with the two servers (I never entered anything into the new server to relate it to the existing one, but I don't know if it possibly broadcasts something and they are on the same network), I brought the new server down completely and rebooting the existing machine. No luck there. My new install, though not working entirely, does give me the Report Server page, but I'm having what appears to be some .NET related issues with the Report Manager.

Next I started to troubleshoot the key. When we setup RS initially, we exported a key using RSKeyMgmt. I found that key, located the password and used RSKeyMgmt to apply it back to the server. The process went smoothly, services restarted, etc., but still the same error on the Report Manager or Report Server pages.

I verified, as best as I could that all the data in the database seems to be intact. At this point, I thought I might end up having to go down the road it describes in the Online Help where it says you have to use RSKeyMgmt to delete all the encrypted content, then run RSConfig to respecify the encrypted connection values, restart the service and retype stored credentials for all reports that used stored credentials. This is where I left off yesterday, though I haven't yet gone down this road.

When I arrived to work this morning, another surprise awaited. A couple subscriptions I had setup actually ran and the exported PDFs contain valid data. This is very confusing to me because if there was a key problem, how is it running the reports. Alright, enough back story. Here are my exact questions.

1. If I have to run the RSKeyMgmt to delete the encrypted content, what exactly is being deleted? We have security setup in Report Manager as well as several subscriptions and a ton of reports. If this works, what all am I going to have to set back up. It would also be helpful if someone could point me in the direction of a tutorial for this process. I was not involved in the initial setup of our RS system and those who were have left the company.

2. How is it possible that subscriptions can run, but I am getting the error I am with Report Manager and Report Server? This also begs the question whether my thought that the key is the issue is actually correct.

3. Is it possible that the new install of RS on a different server could have caused this issue?

Any help that can be provided would be great. I think I am going to hold off on wiping the encrypted data until I know exactly what that is, especially since the subscriptions seem to be running.

View 1 Replies


ADVERTISEMENT

Help Using Asymetric/Symetric Key In A Scalar UDF

Oct 26, 2006

The error message I get is as follows:
Invalid use of side-effecting or time-dependent operator in 'OPEN SYMMETRIC KEY' within a function.
&
Invalid use of side-effecting or time-dependent operator in 'CLOSE SYMMETRIC KEY' within a function.

Here is the code I am trying to implement:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER FUNCTION [dbo].[func_GetSIMSPassPhrase]
(
)
RETURNS varchar(30)
AS
BEGIN
OPEN SYMMETRIC KEY sims_sym_Key DECRYPTION BY ASYMMETRIC KEY sims_asym_key
DECLARE @GUID UNIQUEIDENTIFIER
SET @GUID = (SELECT key_guid FROM sys.symmetric_keys WHERE name = 'sims_sym_Key')
DECLARE @passphrase varchar(30)
SELECT @passphrase = (SELECT CAST(DecryptByKey(EncField) AS VARCHAR(30)) FROM tblEncryptTest)
CLOSE SYMMETRIC KEY sims_sym_Key
RETURN @passphrase
END

Anyone have any suggestions? TIA

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved