SSRS: History of report execution and email (subscribe)

Quick one today:

On SSRS, during one of the troubleshooting efforts, some ReportServer tables came up to surface that seemed interesting. Here is the question:

Is there a place where history of each SSRS report execution is stored? When the SSRS report is emailed or just generated using API or web browser?

Obviously, the answer is yes. Below is the query that could look it up:

--
-- History of a report execution on SSRS
--
SELECT C.name
    , CASE(E.RequestType)
		WHEN 0 THEN 'Interactive'
		WHEN 1 THEN 'Subscription'
		WHEN 2 THEN 'Refresh Cache'
		ELSE 'Unknown'
	  END AS RequestType
	, E.InstanceName
	, E.ReportID
	, E.UserName
	, E.[Format]
	, E.TimeStart
	, E.TimeEnd
	, E.TimeDataRetrieval
	, E.TimeProcessing
	, E.TimeRendering
	, E.[Source]
	, E.[Status]
	, E.ByteCount
	, E.[RowCount]

FROM ExecutionLog AS E
INNER JOIN dbo.catalog AS C
	ON E.reportid = C.itemid

WHERE c.name = N'Replication Latency Common'
ORDER BY E.TimeEnd DESC
GO

Result would look something like this:

SSRS Report Execution History

Most of the columns are self-explanatory; But for source column, details are added below.

Source

1: Live
2: Cache
3: Snapshot
4: History
5: Ad Hoc (Report Builder, etc)
6: Session (some session calling this session, etc)
7: RDCE – Report Definition Customization Extension

;nbsp;

Hope this helps,
_Sqltimes

Read Full Post »

SSRS Error Message : The dataset `DataSource1′ refers to the shared data source `DataSource1′, which is not published on the report server. The shared data source `DataSource1′ must be published before this report can run.

Quick one today:

Earlier today, in our SSRS lab, after deploying reports using PowerShell, we ran into this error:

The dataset `DataSource1' refers to the shared data source `DataSource1', 
which is not published on the report server. The shared data source `DataSource1' 
must be published before this report can run.

Obviously, since we are still in the process of figuring out the correct “SSRS Deployment Steps” for automation, we may have missed a step or two. In this case, we missed/forgot the “data source association to each RDL” step.

Resolution:

Since we are still figuring out an automated step to it, for now, this is what we did to resolve this issue manually.

1. Go to Reports Manager URL on IE browser (with Administrator rights)
2. Go to the individual report/RDL and hover mouse over it.
3. Click on the right side yellow drop down and go to ‘Manage’
4. Go to ‘Data Sources’ tab
5. Assign the correct shared data source (see below image for clarity)

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server Reporting Services : SSRS : How to send reporting errors to client (or web page) – EnableRemoteErrors

Quick one day:

Lately, while working on SSRS, we’ve uncovered some tidbits that have been helping us identify the root cause. Here is one of them:

In our environment, we pull SSRS reports from a client application (web page); For the most part, things run smooth. On occasion, when we run into issues, we need to be able to pull up the underlying error message. In SSRS, by default, error reporting back to calling application (client webpage) is disabled. But it is easy to enable it as and when needed and then turn it off, once done.

Go to SSMS and connect to Reporting Service. Right click on the root and go to properties. Under Advanced tab, under Security section, look for EnableRemoteErrors and set the value as True from the drop down. This sends error back to the client, so we can see what is going on. See the image below for clarity.

 

Read Full Post »

SSRS : rsAccessDenied : The permissions granted to user are insufficient for performing this operation (rsAccessDenied)

Interesting one today:

Setting up a new SSRS machine on one of the virtual machines in our lab, we ran into this interesting error after walking through the initial steps of install & configuration. Turns out, this is a common error after fresh installs. Following is the error message:

The permissions granted to user are insufficient for performing this operation. 
(rsAccessDenied) Get Online Help

Following steps have helped resolve it.

Resolution:

SSRS service runs under the security of a user account; My guess is that this account needs permissions to access the content on the SSRS site & folders. So we need to carry out two steps to assign proper permissions.

1. Folder Permissions
2. Site Permissions
3. Trusted Site Settings

Folder Permissions

Step 1:

Open browser with Administrative Privileges and point it to Reports Manager URL. Go to ‘Folder Settings’.

 Step 2:

In the properties page, go to the Security tab and click on ‘New Role Assignment‘

Step 3:

In the new page, add the user account for ‘Group or User Name’ field. Then assign ‘Content Manager‘ permissions to the user account. And hit okay to save the settings.

Site Settings

Step 1:

Use the same browse (with administrative permissions), to go to Site Settings on the right.

Step 2:

In the settings page, go to Security tab and click on New Role Assignment.

Step 3:

In the new page, add the windows user to the ‘Group or user name’; Assign System Administrator role and hit OK.

.

Trusted Site Settings

Go to Internet Explorer, Tools -> Internet Options -> Security tab > Click on Trusted Sites. In the new window, enter the URL for Report Manager and close.

Now you can open browser and open Report Manager without any security warning.

Note:

Once the permissions are set, close the browser and re-open it without administrative permissions. Going forward, it would work without needing to open browser with elevated permissions.

For more information, please refer to this BoL article.

Hope this helps,
_Sqltimes

Read Full Post »

SSRS : The report server cannot decrypt the symmetric key that is used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content

Interesting one today:

Recently, on one of the lab machines, we had to rebuild SSRS machine on a new VM. After walking through the regular install and other configuration steps, when browser is opened and pointed to the SSRS URL, we get this error.

Error Message:

The report server cannot decrypt the symmetric key that is used to access sensitive
or encrypted data in a report server database. You must either restore a backup key
or delete all encrypted content.

This error message seems cryptic and familiar at the same time. After pacing back and forth for a few minutes near my desk, it dawned on me. This VM with pre-installed SSRS must have some encryption keys set up already, that need recreation or just deletion.

Steps:

1. Open Reporting Services Configuration Manager
2. Connect to the SSRS engine on the VM
3. Open Encryption Keys tab on the left
4. Click on ‘Delete’ button to remove the keys (from previous set up)
5. Now open browser with Administration privileges
   1. Open SSRS URL

Now the error message is resolved.

Note: Since this is a lab machine (and freshly minted VM), these aggressive steps are warranted. But in a production environment, you want to restore the encryption keys with the correct backup keys.

Hope this helps,
_Sqltimes

Read Full Post »