Monday, August 30, 2010

Programmatic Deployment of SSRS reports with strored credentials

When you face the necessity of adding reporting to your application, you have to choose the way how to transform your data into some readable format. And SQL Server Reporting Services (SSRS) might be the case. In this post we’ll show the way how to programmatically deploy SSRS reports.

Suppose you’ve properly configured SSRS (in SharePoint Integrated mode in our case) and developed one report in Visual Studio; all report’s related data sources (either Shared or Custom) run through stored credentials. Now you have to:

  • deploy report definition;
  • adjust and/or deploy related Data Sources (regardless of which type of Data Source you chose, you must change connection strings and user login/password for accessing data from the database).

To run the code below you have to create appropriate application (depending on your needs) and add reference to ReportService2010.asmx web service. Here are two methods used from deploying reports:

public void DeployReport(
            string serverUrl,
            string docLibraryPath,
            string reportFilePath,
            Dictionary<string, DataSourceDefinition> dsDefinitions)
            //create report service instance
            var _rsService = new ReportingService2010
                                     Url = string.Format("{0}/_vti_bin/ReportServer/ReportService2010.asmx", serverUrl),
                                     Credentials = System.Net.CredentialCache.DefaultCredentials
            //read content from report file
            byte[] reportContent;
            using (var reader = new StreamReader(reportFilePath))
                using (var br = new BinaryReader(reader.BaseStream))
                    reportContent = br.ReadBytes((int) reader.BaseStream.Length);

            //change connections strings in report definition
            reportContent = ChangeConnectionStrings(reportContent, dsDefinitions);

            Warning[] warnings;
            var report = _rsService.CreateCatalogItem(
                new FileInfo(reportFilePath).Name,
                out warnings);

            //fix datasource definitions
            var dataSources = _rsService.GetItemDataSources(report.Path);
                .Where(_ => dsDefinitions.ContainsKey(_.Name))
                .ForEach(ds =>
                                 var templateDS = dsDefinitions[ds.Name];

                                 if (ds.Item is DataSourceDefinition)
                                     //set proper user credentials for custom report data source
                                     var dsd = (DataSourceDefinition) ds.Item;
                                     dsd.CredentialRetrieval = templateDS.CredentialRetrieval;
                                     dsd.UserName = templateDS.UserName;
                                     dsd.Password = templateDS.Password;
                                     //create shared datasource and fix reference in report
                                     var newDS=_rsService.CreateDataSource(

                                     ds.Item = new DataSourceReference { Reference = newDS.Path };
                                 _rsService.SetItemDataSources(report.Path, dataSources);

        private byte[] ChangeConnectionStrings(
            byte[] reportContent,
            IDictionary<string, DataSourceDefinition> dsDefinitions)
            var doc = XDocument.Load(XmlReader.Create(new MemoryStream(reportContent)));

            //find all connection strings in custom data sources
            //and replace connection strings with proper values
            doc.Descendants(XName.Get("ConnectString", doc.Root.Attributes().First(_ => _.IsNamespaceDeclaration && _.Name.Namespace == XNamespace.None).Value))
                .ForEach(_ =>
                    var dsName = _.Parent.Parent.Attribute("Name").Value;
                    if (dsDefinitions.ContainsKey(dsName))

            var newStream = new MemoryStream();
            var xmlWriter = XmlWriter.Create(newStream);
            return newStream.ToArray();

And here is the example how to use those methods to deploy your reports:

        new Dictionary<string, DataSourceDefinition>
                    new DataSourceDefinition
                            ConnectString = "Data Source=<database_server_>>;Initial Catalog=<database_name_2>",
                            CredentialRetrieval = CredentialRetrievalEnum.Store,
                            UserName = "<user_name_1>",
                            Password = "<password_1>",
                            Extension = "SQL"
                    new DataSourceDefinition
                            ConnectString = "Data Source=<database_server_2>;Initial Catalog=<database_name_2>",
                            CredentialRetrieval = CredentialRetrievalEnum.Store,
                            UserName = "<user_name_2>",
                            Password = "<password_2>",
                            Extension = "SQL"
Replace all hardcodes in brackets to make the code working for you. Have fun! :)