Error "Database Logon Failed" when exporting a crystal report to pdf in .NET

[former_member777097]

Hi Team,

We have built a web service in .NET to export a crystal report to pdf and it's working as expected in Development machine but getting error "Database logon failed" once we deploy using IIS.

Below is the code developed to export a crystal report to pdf

  public class CryRepWebService : System.Web.Services.WebService<br>    {       <br>        [WebMethod]<br>        public string InvoiceReportCry(int Invoiceid, int docType, string Proj)<br>        {<br>            string project;<br>            int docTypes;<br>            int InvoiceIds;<br>            try<br>            {<br>                ReportDocument cryRpt = new ReportDocument();<br>                cryRpt.Load(Server.MapPath("~/Reports/myreport.rpt"));<br>                string USERNAME = "useruser";//Default user Name<br>                string PWD = "pwdpwd$";//Default pwd Name<br>                string DSN_NAME = "mydsn";<br>                string INITIAL_CATALOG = "dbname";<br>                string serverName = (string)Registry.GetValue(@"HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\" + DSN_NAME, "Server", null);<br>                cryRpt.SetDatabaseLogon(USERNAME, PWD, "serverName", INITIAL_CATALOG);<br>                // create the connection information<br>                ConnectionInfo conRpt = new ConnectionInfo();<br>                conRpt.ServerName = serverName;<br>                conRpt.DatabaseName = INITIAL_CATALOG;<br>                conRpt.UserID = USERNAME;<br>                conRpt.Password =PWD;<br>                // apply connection information to the report tables<br>                Tables rptTables = cryRpt.Database.Tables;<br>                for (int i = 0; i < rptTables.Count; i++)<br>                {<br>                    CrystalDecisions.CrystalReports.Engine.Table rptTable = rptTables[i];<br>                    TableLogOnInfo tblInfo = rptTable.LogOnInfo;<br>                    tblInfo.ConnectionInfo = conRpt;<br>                    // next table<br>                }<br>                // if the report contains sub reports, you will need to set the connection info there too<br>                if (cryRpt.Subreports.Count > 0)<br>                {<br>                    for (int i = 0; i < cryRpt.Subreports.Count; i++)<br>                    {<br>                        using (ReportDocument rptSub = cryRpt.OpenSubreport(cryRpt.Subreports[i].Name))<br>                        {<br>                            Tables rptTables1 = rptSub.Database.Tables;<br>                            for (int j = 0; j < rptTables.Count; j++)<br>                            {<br>                                CrystalDecisions.CrystalReports.Engine.Table rptTable = rptTables1[i];<br>                                TableLogOnInfo tblInfo = rptTable.LogOnInfo;<br>                                tblInfo.ConnectionInfo = conRpt;<br>                                // next table<br>                            }<br>                            rptSub.Close();<br>                        }<br>                    }<br>                }<br>                ParameterValues crParameterValues = new ParameterValues();<br>                ParameterDiscreteValue crParameterDiscreteValue = new ParameterDiscreteValue();<br>                ParameterDiscreteValue crParameterDiscreteValue1 = new ParameterDiscreteValue();<br>                ParameterDiscreteValue crParameterDiscreteValue2 = new ParameterDiscreteValue();<br>                          <br>                cryRpt.SetParameterValue("@Invoiceid", Invoiceid);<br>                cryRpt.SetParameterValue("@docType", docType);<br>                cryRpt.SetParameterValue("@Proj", Proj);<br>                project = Proj;<br>                docTypes = docType;<br>                InvoiceIds = Invoiceid;<br>                bool folderExists = Directory.Exists(@"D:\CrystalReports\Test_Pdf");<br>                if (!folderExists)<br>                    Directory.CreateDirectory(@"D:\CrystalReports\Test_Pdf");<br>                cryRpt.ExportToDisk(ExportFormatType.PortableDocFormat, @"D:\CrystalReports\Test_Pdf\test-" + Invoiceid + ".pdf");<br>                return project + InvoiceIds + docTypes;<br>            }<br>            catch (Exception ex)<br>            {<br>                ExceptionLogging.SendErrorToText(ex);<br>                return null;<br>            }<br>        }<br>    }

Below is the tech specifications of both environments:

Development System :

Windows 7 Professional (64-bit)

Visual Studio 2015

Sql Server 2012

ODBC Driver - Sql Server

SAP Crystal Reports 2016 (SP6) (version 14.2.6.2839)

SAP Crystal Reports runtime engine for .NET Framework(64-bit) (version 13.0.30.3805)

SAP Crystal Reports, version for Microsoft Visual Studio (version 13.0.30.3805)

IIS (7.5)

Production Server:

Windows Server 2012 R2 Standard (64-bit)

Visual Studio 2015

Sql Server 2019

ODBC Driver - Sql Server Native Client 10.0

SAP Crystal Reports 2016 (SP4) (version 14.2.4.2410)

SAP Crystal Reports 2016 (SP9) update (version 14.2.9.3791)

SAP Crystal Reports runtime engine for .NET Framework(64-bit) (version 13.0.30.3805)

SAP Crystal Reports, version for Microsoft Visual Studio (version 13.0.30.3805)

IIS (8.5)

Please help us with a solution to try.

Thanks in advance.

Maruthi A

[former_member751591]

Thank you for visiting SAP Community to get answers to your questions. Since you're asking a question here for the first time, I recommend that you familiarize yourself with Community Q&A , as it provides tips for preparing questions that draw responses from our members.

For example, you can:

- outline what steps you took to find answers (and why they weren't helpful)

- share screenshots of what you've seen/done

- make sure you've applied the appropriate tags

- use a more descriptive subject line.

The more details you provide, the more likely it is that members will be able to respond. Feel free to also take our Q&A tutorial Q&A tutorial

Should you wish, you can revise your question by selecting Actions, then Edit.

By adding a picture to your Profile you encourage readers to respond.

[former_member11696 · Accepted solution]

As Dell mentioned, CR will have issues using that old MDAC Database client.

Since the RPT saves the Client dll used in the file changing the DSN may or may not work.

You need to get off of Windows 7, no longer supported by Microsoft and SAP, Install the Native 10 Client, open EVERY report and update it by using the Database, Set location... function, save the report and use the updated reports in your production PC.

Just updating the DSN is not going to fix the Report. If you have TLS 1.2 enabled it is a must to update the Report because the old MDAC client does not support it and CR will never work unless the reports are updated manually.

And CR runtime is set for your application, so if the AppPool is set for x86 then the CR x86 MSI package needs to be installed, same for x64 if x86 mode is set to false.

And if IIS is running in x64 mode then you need the MS 64 bit Client installed and DSN add to the 64 bit ODBC Admin.

[DellSC]

I see a number of potential configuration issues in what you have listed.

1. The versions of the SQL Server client should be the same. You don't mention which version of SQL Server you're connecting to, but the "ODBC Driver - Sql Server" that you have listed is the version that came with Windows 7. It's old and has some issues. You need to install SQL Server Native Client 10 on the Development machine, delete the existing ODBC DSN that you're using and recreate it using the new driver. Make sure you do NOT have the connection set to use integrated security! Then you'll have to update the connection that the report is using. To do that:

- Run Crystal
- File >> New >> Blank Report
- When the Database Expert appears, right-click the ODBC connection you've been using and select "Delete Connection".
- Once the connection has been deleted, create a new connection to the same database using the new ODBC DSN so that the connection has the new driver in its properties.
- Click on Cancel to cancel out of the Database Expert. Then close the blank report without saving it.
- Finally, open the report and run it. It should pick up the new connection by name (if the name is the same). You can verify this by going to the Database Expert, right-clicking on the connection, and selecting Properties. Scroll down to find the "Provider". It should look like the image below.

2. Your production machine should not have Visual Studio, Crystal, or SAP Crystal Reports, version for Microsoft Visual Studio installed. It should only have the Crystal for VS runtime.

-Dell

[former_member777097]

Hi Dell,

Thanks for your response.

I noticed one thing which is main in this issue to find a solution. Mistakenly I mentioned as

" We have built a web service in .NET to export a crystal report to pdf and it's working as expected in Development machine but getting error "Database logon failed" once we deploy using IIS."

but it should be

"We have built a web service in .NET to export a crystal report to pdf and it's working as expected in Development machine but getting error "Database logon failed" once we deploy using IIS in Production machine ".

Can you please consider above issue scenario and help me with a solution to try.

My sincere apologies.

Regards,

Maruthi A

[DellSC]

What you're doing in terms of a web service has no bearing on what will fix the issue. See Don's comments below. The two different SQL Server drivers are NOT compatible! Because Crystal stores information about the data driver in the .rpt, you need to update your reports to use the newer SQL Server Native Client that you have installed on the server.

-Dell