passing key decryption password to Stored procedure

I try to pass the key used to decrypt symmetric key to a stored procedure as a parameter like this:

OPEN SYMMETRIC KEY MyKey
DECRYPTION BY PASSWORD=@. keypassword;

I get an error message saying "Incorrect syntax near "@.keypassword".
Is there something that I am missing?

Unfortunately the syntax doesn't allow a variable as a password. You will have to either use dynamic SQL to use the password (make sure you escape the user input properly to prevent SQL injection) or, if it is possible in your particular scenario, consider using the key hierarchy available in SQL Server 2005.

-Raul Garcia

SDE/T

SQL Server Engine

Pass in encrypted password parameter somehow?

I have multiple databases, one for each client. I created a master report. Then I created links to this report for each client database in separate client-specific folders. I parameterized the connection string so that the server name and the database name are parameters of the linked report. The connection string is: ="data source=" & Parameters!SrvrName.Value & ";initial catalog=" & Parameters!DbName.Value.

The problem is that the security for now is Windows Authentication. (We are in the testing phase). However, my users will be both internal (company) and external (client) users. We do not want to set up database user IDs for each client user. I could create a single SQL account that has read access to all databases, relying on the Reporting Services security. But I would prefer to create separate SQL accounts for each database as the information is sensitive. Then I would probably pass that information in as a parameter to the report as well. Either way, I would have to include the password somehow. But, if I do that, I'm not sure how to include the password parameter. I would like it to be secure and passing it in as a parameter is not very secure.

I would really appreciate some suggestions on how to proceed.

Consider storing the database credentials in the Report Server web.config file instead of passing them as parameters. The ExpressionBasedConnection report in this download shows how this could be done.