Error creating SSRS Report subscriptions

Author: Preeti Saklani, 15 November 2017


Many a times we come across the situation wherein we are trying to create a subscription for a SSRS report, but it fails with a message as below.  


  

                     






Now, this problem looks like the report expects the user to provide credentials when it is viewed. When you create a subscription, it does not have the credentials to execute the report. SSRS requires a Login to connect to the datasource to process the report when subscription will be occurring at its scheduled time. It was required to store the credential securely into report server. There are two approaches basically for storing the credential securely into report server. Either you can change the datasource properties at the report level or at the datasource level itself.  

I came across such a scenario recently. I created a new datasource on my report manager and was able to make a successful connection with the database. I created a SSRS report and pointed it to my new datasource. The report ran good as well. But when I came to create the subscription I got the error. Here it was:

A PPR0ACH 1:STORING THE CREDENTIALS AT THE REPORT LEVEL

If you are having this issue for any report follow the steps below:
Step 1: Go to Report -> Properties ->  Click on the Data Sources tab 
Step 2: Click on A custom data source and choose option credentias stored securely in the report server and provide credential information and further you can use option Use as Windows credentials when connecting to the data source.  
Step 3: Click on apply button.  





                                                                         Now you are good to create a subscription.

APPR0ACH 2:STORING  THE CRENDENTIALS AT THE DATASOURCE LEVEL

We can even change the same at the datasource level.
Step 1: Open the datasource from the Data Sources at the report manager.  
Step 2: Fill in the connection string 
Step 3:Choose to connect using: Credentials stored securely in the report server. Put the credentials and use option Use as Windows credentials when connecting to the data source.  

 


    

Now can you can to the desired report and choose the option of ‘a shared data source. You are good to go with creating a new subscription now.    





My Two Cents

 ·If you are worried about security, I would suggest changing the datasource properites on the report level rather than the data source level itself. (i.e approach  1 as mentioned above)         

 · If your report uses sub-reports also ensure that the sub-reports are using the data source with saved credentials, otherwise it won't let you create a subscription on the parent report.

CREATE CLUSTERED INDEX idx_ci ON FactFinance(DateKey)
GO

-- "Swap" the Clustered Index through a Clustered ColumnStore Index  
CREATE CLUSTERED COLUMNSTORE INDEX idx_ci ON FactFinance 
WITH (DROP_EXISTING = ON) 
GO    


You should have now sorted data in the Clustered ColumnStore Index, and Segment Elimination should work quite well:  

-- Segment Elimination works better than previously, but still not perfectly
SELECT  
             DateKey,
             SUM(Amount)
FROM FactFinance  
WHERE 
             DateKey >= '20101229' 
             AND DateKey <= '20131228'
GROUP BY 
DateKey  
GO     

But when you look again at the output of STATISTICS IO, SQL Server still has to read a lot of segments, and only skips a few of them:  

But why can’t SQL Server skip all segments other than the one we are interested in? The problem lies in the creation of the Clustered ColumnStore Index. Execution Plan, the ColumnStore Index Insert (Clustered) operator was running in parallel – across multiple worker threads. And these worker threads are again destroying the order of your data in the Clustered ColumnStore Index! You read your data pre-sorted from the Clustered RowStore Index, and then the parallel creation of the Clustered ColumnStore Index reshuffles your data… That hurts – again!

You can only solve that problem by creating the Clustered ColumnStore Index with a MAXDOP of 1: 

CREATE CLUSTERED COLUMNSTORE INDEX idx_ci ON FactFinance 
WITH  (DROP_EXISTING = ON, MAXDOP = 1) 
GO   

This sounds terrible, and it is terrible!!! But it’s the only solution to let you preserve the ordering of your data in the ColumnStore Index. When you later read from the Clustered ColumnStore Index, you will then see that SQL Server was finally able to skip all Segments other than the one you are interested   in:   

 

Summary

ClusteredColumnStore Indexes are great. But by default, the Segment Elimination can’t be performed very well, because you have no predefined sorting order in your Clustered ColumnStore. Therefore, you should always make sure that the Segment Elimination works well, when you tune your ColumnStore queries. And sometimes you even have to work against SQL Server by using a MAXDOP 1 to preserve the ordering of your data.