When developing SSRS reports, it’s convenient to deploy data sources and reports to the Report Server using Visual Studio. However, when it comes to production, using Visual Studio is not practical or even possible.
A common solution is to use a tool such as RSBuild, which lets you specify deployment parameters in, appropriately, an XML file.
Alternatively, you can use the SSRS Web service API directly by writing a console application in your favorite .NET language. You can also create a Visual Basic script that can be run on the command line by the RS utility that comes with SSRS.
The problem
I had to deploy a report with stored credentials. The report is deployed correctly from Visual Studio. For production, I wrote a console application to deploy the report based on the Microsoft Reporting Service samples. However, when the report is viewed, it always prompts the user to enter data source credentials, as seen below. In the report’s Data Sources properties, I found that the data source is set to a custom data source which connects using “Credentials supplied by the user running the report”.
The solution
Having to supply connection credentials every time you want to view the report obviously isn’t desirable. This is where Fiddler comes to the rescue. I used Fiddler to capture the SOAP requests and responses between Visual Studio and the Report Server. As a test, I deployed a report which prompts for data source credentials and another report with stored credentials.
The Fiddler trace was informative. Besides the expected CreateReport SOAP call, Visual Studio was also making a SetItemDataSources call to specify the data source settings. For the report which prompts for data source credentials, the SOAP request is as follows:
For the report with stored credentials, the SOAP request is as follows:
Armed with these information, I modified the code to make a call to SetItemDataSources with the same parameters and values. This time the report was deployed correctly with the stored credentials. The code is shown below.
 |
Creating the SSRS Web service proxy Before you can connect to the SSRS Web service API in a Visual Studio project, you must create a proxy class by adding a Web Reference to the Web service and providing the URL to the ASMX file for the desired SSRS instance; e.g., http://<servername>:<port>/ReportServer/ReportService2005.asmx. |
using System;using System.IO;using DeployReports.RS2005;namespace DeployReports{ class Program
{
private static ReportingService2005 service;
private static string filePath;
private static string parentFolder;
private static string parentPath;
static Program()
{
service = new ReportingService2005();
service.Credentials = System.Net.CredentialCache.DefaultCredentials;
filePath = @"c:\Projects\ReportingService\SelectableEmbeddedDataSource";
parentFolder = "PublishedByCSharpScript";
parentPath = "/" + parentFolder;
}
static void Main(string[] args)
{
// Create parent folder
try
{
service.CreateFolder(parentFolder, "/", null);
Console.WriteLine("Report folder {0} created successfully", parentFolder);
}
catch (Exception ex)
{
Console.WriteLine("Could not create report folder {0}. {1}", parentFolder, ex.Message);
}
string reportName = "SelectableEmbeddedDataSource";
CreateReport(reportName);
SetReportDataSource(reportName);
}
private static void CreateReport(string reportName)
{
FileStream stream = null;
byte[] definition = null;
try
{
stream = File.OpenRead(Path.Combine(filePath, reportName + ".rdl"));
definition = new byte[stream.Length];
stream.Read(definition, 0, (int)stream.Length);
stream.Close();
}
catch (Exception ex)
{
Console.WriteLine("Could not read RDL. {0}", ex.Message);
}
finally
{
if (stream != null)
stream.Dispose();
}
try
{
Warning[] warnings = service.CreateReport(reportName, parentPath, true, definition, null);
if (warnings != null)
foreach (Warning warning in warnings)
Console.WriteLine(warning.Message);
else
Console.WriteLine("Report: {0} published successfully with no warnings", reportName);
}
catch (Exception ex)
{
Console.WriteLine("Could not publish report {0}. {1}", reportName, ex.Message);
}
}
private static void SetReportDataSource(string reportName)
{
DataSourceDefinition dsdef = new DataSourceDefinition();
dsdef.CredentialRetrieval = CredentialRetrievalEnum.Store;
dsdef.Enabled = true;
dsdef.Extension = "SQL";
dsdef.ImpersonateUser = false;
dsdef.OriginalConnectStringExpressionBased = true;
dsdef.Password = "password";
dsdef.UserName = "testuser";
dsdef.UseOriginalConnectString = true;
dsdef.WindowsCredentials = false;
DataSource datasource = new DataSource();
datasource.Item = dsdef;
datasource.Name = "AdventureWorks";
DataSource[] datasources = new DataSource[1];
datasources[0] = datasource;
string item = parentPath + "/" + reportName;
try
{
service.SetItemDataSources(item, datasources);
Console.WriteLine("Successfully set data source for report {0}", reportName);
}
catch (Exception ex)
{
Console.WriteLine("Could not set data source for report. {0}", ex.Message);
}
}
} // class
} // ns
|
The Visual Basic script file used by the RS utility is a little simpler to create because the Web service proxy is already created for you. Nevertheless, you may still want to generate the proxy manually and write the code in Visual Studio to take advantage of Intellisense. Assuming that the script file is saved as PublishReports.rss, you can execute it with:
rs.exe -i PublishReports.rss -s http://servername:port/reportserver
|
The PublishReports.rss script is as follows:
Dim definition As [Byte]() = NothingDim warnings As Warning() = NothingDim parentFolder As String = "PublishedByScript"
Dim parentPath As String = "/" + parentFolder
Dim filePath As String = "c:\Projects\ReportingService\SelectableEmbeddedDataSource\"Public Sub Main() rs.Credentials = System.Net.CredentialCache.DefaultCredentials
'Create the parent folder
Try
rs.CreateFolder(parentFolder, "/", Nothing)
Console.WriteLine("Parent folder {0} created successfully", parentFolder)
Catch e As Exception
Console.WriteLine(e.Message)
End Try
CreateReport("SelectableEmbeddedDataSource")
SetReportDataSource("SelectableEmbeddedDataSource")
End SubPublic Sub CreateReport(ByVal reportName As String) Try
Dim stream As FileStream = File.OpenRead(filePath + reportName + ".rdl")
definition = New [Byte](stream.Length - 1) {}
stream.Read(definition, 0, CInt(stream.Length))
stream.Close()
Catch e As IOException
Console.WriteLine(e.Message)
End Try
Try
warnings = rs.CreateReport(reportName, parentPath, False, definition, Nothing)
If Not (warnings Is Nothing) Then
Dim warning As Warning
For Each warning In warnings
Console.WriteLine(warning.Message)
Next warning
Else
Console.WriteLine("Report: {0} published successfully with no warnings", reportName)
End If
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End SubPublic Sub SetReportDataSource(ByVal reportName As String) Dim dsdef As DataSourceDefinition = New DataSourceDefinition()
dsdef.CredentialRetrieval = CredentialRetrievalEnum.Store
dsdef.Enabled = True
dsdef.Extension = "SQL"
dsdef.ImpersonateUser = False
dsdef.OriginalConnectStringExpressionBased = True
dsdef.Password = "password"
dsdef.UserName = "testuser"
dsdef.UseOriginalConnectString = True
dsdef.WindowsCredentials = False
Dim datasource As DataSource = New DataSource()
datasource.Item = dsdef
datasource.Name = "AdventureWorks"
Dim datasources(0) As DataSource
datasources(0) = datasource
Dim item As String
item = parentPath + "/" + reportName
Try
rs.SetItemDataSources(item, datasources)
Console.WriteLine("Successfully set data source for report {0}", reportName)
Catch e As Exception
Console.WriteLine("Could not set data source for report. {0}", e.Message)
End Try
End Sub |
Unfortunately, the username and password for the stored credentials must be passed in the clear. If this is an issue, you can use Integrated Windows Authentication. With Fiddler, determining the necessary SOAP parameters to do so should be a cakewalk 
Share the post "Scripting Deployment of Reporting Service Reports with Stored Credentials"
Follow