Posted by Michael Burgess on 08/23/07 21:33

Hi there,

I have a problem that's beginning to do my head in and hope someone
can help!!!

I've got a SQL Server 2005 database which is partly encrypted
(certain
columns per table are encrypted).

I have two different passwords for two different symmetric keys and
these passwords are in turn stored encrypted in the registry, to be
retrieved by an app, decrypted and then passed to the relevant
symmetric key to decrypt the relevant data in a table.

I've written an application that's allows certain admins to change
these encrypted registry passwords and symmetric key passwords at
their will.

The SQL Server 2005 Reporting Services is all set up with certain
reports (one of the symmetric keys locks down a column that says who
can access reports, whilst the other protects the actual data that
the
reports show) and all works fine until I change the symmetric key
that
protects the reports access list.

I then get an error saying it can't establish a connection to the
database. (I know it's actually getting into the table to see I have
reporting access though as I've stepped through all the code)

I've tried a dummy test by encrypting a column, changing the
symmetric
key and then retrieving the data and I get back the plain text I
expect.

The error suggests that changing the symmetric key password blocks
access to the data, but my dummy tests have proved otherwise.

I'm new to reporting services.......is there something contained
within a reporting solution or the reports themselves that would
relate to the symmetric key? Am I being daft? Am I overlooking
something stupid?

To change the password I open the existing symmetric key, add
encryption with a new password, drop encryption with the existing
password and then close the key.

Sorry if some of this doesn't make sense - it's quite complicated.
Feel free to ask any more questions if you don't understand
something.
I've spent 1.5 days on this now and I'm at the end of my tether!

Thanks in advance,
Michael.

[Back to original message]