Category Archives: SQL Reporting

The report server cannot decrypt the symmetric key – Reporting Servie, SSRS, SharePoint

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.

 

Solution:

As the error shows, it is an access denied on a registry entry. A Process Monitor http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx trace can show which registry key needs permissions for the service account of the SSRS service.

Usually, it could be this –

HKLM\Software\Microsoft\Microsoft SQL Server\Reporting Services\Service Applications

A brute force change to permissions at HKLM\Software\Microsoft\Microsoft SQL Server by adding the desired user with Read (propagated to child folders) could show if that’s all that is required.

Assign the service account full permission or if it doesn’t work assign everyone full permission on the desired key.

There may be multiple keys which is giving access denied in the process monitor.

try to fix all of them one by one.

unable to connect to sql server using local sql authentication

Reason 1:

SQL Authentication not enabled: If you use SQL Login for the first time on SQL Server instance than very often error 18456 occurs because Windows Authentication (only) is set in Server properties (security section).

To Access Server Properties, Open SQL Server Management Studio, go to Object Explorer pane (use view if you can’t see it). Use the connect button to connect to database engine for your server. Once connected you will see it in object explorer. Right click server and click properties. Server Properties window will appear.

See below screenshot that might be causing SQL login to fail

You should set Server Authentication to SQL Server Windows Authentication 

SQL Authentication not enabled: If you use SQL Login for the first time on SQL Server instance than very often error 18456 occurs because Windows Authentication (only) is set in Server properties (security section).

To Access Server Properties, Open SQL Server Management Studio, go to Object Explorer pane (use view if you can’t see it). Use the connect button to connect to database engine for your server. Once connected you will see it in object explorer. Right click server and click properties. Server Properties window will appear.

See below screenshot that might be causing SQL login to fail

You should set Server Authentication to SQL Server Windows Authentication 

 

Reason 2: Create the user with the SQL Script:

— Create a new SQL Server Login

exec sp_addlogin @loginame = ‘MyUser’, @passwd = ‘P@ssw0rd’ — Grant the SQL login access to your database. — Create a database user called WebAppUser to which the login is associated Use YourDatabase GO exec sp_grantdbaccess ‘MyUser’ — Create a user-defined database role. exec sp_addrole ‘WebAppUserRole’ — Add the database user to the new database role. exec sp_addrolemember ‘WebAppUserRole’, ‘MyUser’ — Grant the role execute permissions on the stored procedure called sprocname grant execute on sprocname to WebAppUserRole