How to use integrated security in report viewer from C# winforms

[Former Member]

My c# winforms use a report viewer to access crystal reports. Basically I have a Crystal Reports ReportViewer placed on a winforms. I pass to it the report i want to run. I load the report. I load various parameters from the apps config file to set up the connection info. I update the data source to ensure it is pointing to the correct database. I set the logon object to the users proper credentials and set my criteria and call the asmx file which in turn runs the report and sends the data back to the viewer form.

Everything works fine as long as i use sql server authentication. Here is my connection properties being set.

      public void RetrieveAppSettings()

        {

            connectionInfo.ServerName = ConfigurationManager.AppSettings["DatabaseServer"];

            connectionInfo.DatabaseName = ConfigurationManager.AppSettings["DatabaseName"];

            connectionInfo.UserID = ConfigurationManager.AppSettings["DatabaseUserName"];

            connectionInfo.Password = ConfigurationManager.AppSettings["DatabasePassword"];

            reportServer = ConfigurationManager.AppSettings["UAReportServer"];

            reportSource = reportServer + "/" + reportName + "Service.asmx";

        }

Now i want to switch to Windows Authentication for sql server. So i change the connection string in the apps config file and set Integrated Security=True. I run the app and the app starts up and correctly connects to the database. I click on a report from the menu.I loads the viewer and tries to run the report but it is unable to log in. I get the database login dailog box from Crystal Reports being displayed waiting for the proper login information..

Here is how i changed the code.

    public void RetrieveAppSettings()

        {

            connectionInfo.ServerName = ConfigurationManager.AppSettings["DatabaseServer"];

            connectionInfo.DatabaseName = ConfigurationManager.AppSettings["DatabaseName"];

            connectionInfo.IntegratedSecurity = true;

//            connectionInfo.UserID = ConfigurationManager.AppSettings["DatabaseUserName"];

//            connectionInfo.Password = ConfigurationManager.AppSettings["DatabasePassword"];

            reportServer = ConfigurationManager.AppSettings["UAReportServer"];

            reportSource = reportServer + "/" + reportName + "Service.asmx";

        }

What am i missing here? What datasource type do i have to use, ADO,Net, SQLOLEDB, SQL Native client?

Bill

[former_member183750 · Accepted solution]

Hi Bill

Please see if the following will help:

Using Integrated and SQL Authentication in .NET... | SCN

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow us on Twitter

[Former Member · Accepted solution]

First of all thank you for the article it was great. So i have gone through it. there is one problem our application is not running on the same computer as the database in all clients.

i call UpdateDataSource and loop through all tables and change the connectionInfo to the correct values for the client using the app. I set the properties of ConnectionInfo object right before i call this routine. I read those values from the apps config file

     public void RetrieveAppSettings()

        {

            connectionInfo.ServerName = ConfigurationManager.AppSettings["DatabaseServer"];

            connectionInfo.DatabaseName = ConfigurationManager.AppSettings["DatabaseName"];

            connectionInfo.IntegratedSecurity = true;

    //        connectionInfo.UserID = ConfigurationManager.AppSettings["DatabaseUserName"];

    //        connectionInfo.Password = ConfigurationManager.AppSettings["DatabasePassword"];

            reportServer = ConfigurationManager.AppSettings["UAReportServer"];

            reportSource = reportServer + "/" + reportName + "Service.asmx";

        }

       public void UpdateDataSource()

        {

            LoadReport();

            reportDocument.DataSourceConnections[0].IntegratedSecurity = connectionInfo.IntegratedSecurity;

            reportDocument.DataSourceConnections[0].SetConnection(connectionInfo.ServerName,

                connectionInfo.DatabaseName, connectionInfo.IntegratedSecurity);

            MessageBox.Show(reportDocument.DataSourceConnections[0].DatabaseName);

            foreach (Table table in reportDocument.Database.Tables)

            {

                table.LogOnInfo.ConnectionInfo = connectionInfo;

                table.ApplyLogOnInfo(table.LogOnInfo);

            }

            foreach (ReportDocument subrep in reportDocument.Subreports)

            {

                foreach (Table table in subrep.Database.Tables)

                {

                    table.LogOnInfo.ConnectionInfo = connectionInfo;

                    table.ApplyLogOnInfo(table.LogOnInfo);

                }

            }

            //Assign data source details to the report viewer

            if (this.ReportViewer.LogOnInfo != null)

            {

                TableLogOnInfos tlInfo = this.ReportViewer.LogOnInfo;

                foreach (TableLogOnInfo tbloginfo in tlInfo)

                {

                    tbloginfo.ConnectionInfo = connectionInfo;

                }

            }

//           MessageBox.Show(reportDocument.DataSourceConnections[0].DatabaseName);

  //          MessageBox.Show("Integrated Security = " +

  //                          reportDocument.DataSourceConnections[0].IntegratedSecurity.ToString());

            try

            {

                reportDocument.SaveAs(GetReportName());

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

        }

When this is run and it tries to run the report i still get the database logon screen. It has the proper servername and database name filled in but the login id and password are empty and the integrated security checkbox is checked true.

What data source do i need to be using?  ADO.NET, SQLOLEDB, SQL Native Client etc?

Bill