11 Feb 2005

Reporting Services Report Viewer using SQL Authentication

I have written some code, which will create a report and deploys it to a report server. However the connection string for the report has to use SQL Authentication. My code which builds the reports uses the same approach that Microsoft does to SQL Authentication within report, which is to not store the UserID and Password, so what would happen now when the report is displayed in the report viewer is that a prompt would appear asking for a username and password. This behavior had to be suppressed, because I did not wish the users to know or need to type in a userid or a password, so I tried to resolved this by looking for a way to get the connection string stored on the server when deploying the report, like you can when you use SQL Server Business Intelligence Development Studio, with no joy. I did find a solution with the Report Viewer control, there is method to send the data source credentials to the server without the user having to type them in. Below is an example:
   1:  rvReporting.ServerReport.ReportServerUrl = new Uri("Http://localhost/reportServer");
   2:  rvReporting.PromptAreaCollapsed = true; 
   3:  rvReporting.ShowProgress = true; 
   4:  rvReporting.ServerReport.ReportPath ="DemoReport";
   5:  Microsoft.Reporting.WinForms.DataSourceCredentials dataSourceCredentials = new Microsoft.Reporting.WinForms.DataSourceCredentials(); 
   6:  dataSourceCredentials.Name = "ReportDataSource"; 
   7:  SqlConnectionStringBuilder str = new SqlConnectionStringBuilder(ConfigurationManager.ConnectionStrings["Concord"].ConnectionString); 
   8:  dataSourceCredentials.UserId = str.UserID; 
   9:  dataSourceCredentials.Password = str.Password; 
  10:  rvReporting.ServerReport.SetDataSourceCredentials(new Microsoft.Reporting.WinForms.DataSourceCredentials[1] { dataSourceCredentials }); rvReporting.RefreshReport();