.NET导出Crystal Report为PDF时IIS部署出现Database Logon Failed错误求解

基于.NET开发的用于将Crystal Report导出为PDF的Web服务，在开发机上运行符合预期，但部署到生产服务器的IIS后就报Database logon failed错误。

导出PDF实现代码 #

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

环境规格说明 #

开发环境 #

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)

生产环境 #

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)

• 修复代码中的子报表连接配置bug：原代码中子报表遍历部分存在两处错误，一是子报表表集合的循环上限误用了主报表的表数量rptTables.Count，应该改为子报表自身的表数量rptTables1.Count；二是取子报表表对象时误用了外层循环的变量i，应该改为当前循环的变量j，修正后才能保证子报表的连接信息被正确赋值。
• 检查DSN配置是否正确：代码读取的是64位ODBC的注册表路径，需要确认生产环境的64位ODBC管理器中创建了名为mydsn的系统DSN，不要错建为32位DSN；手动测试DSN连接，确认可以正常连接到生产SQL Server 2019实例。
• 统一ODBC驱动版本：开发环境使用的是ODBC Driver for SQL Server，生产环境使用的SQL Server Native Client 10.0为SQL Server 2008配套驱动，和SQL Server 2019存在兼容性风险，建议生产环境安装与开发环境同版本的ODBC Driver for SQL Server，再重新配置DSN使用新驱动。
• 调整IIS权限配置：代码需要读取HKLM下的注册表项，默认IIS应用程序池身份（如ApplicationPoolIdentity）无该权限，可将应用程序池身份改为拥有对应权限的本地/域账户，或给对应注册表路径赋予当前应用程序池身份的读取权限；同时需要给IIS应用程序池身份开放C:\Windows\Temp目录的读写权限，Crystal Reports运行时需要在此目录生成临时文件，权限不足也会被误报为数据库登录失败。
• 验证数据库账号权限：确认生产环境中useruser账号密码正确、未被锁定/过期，拥有dbname数据库的访问权限和对应业务表的查询权限，同时确认SQL Server开启了SQL身份验证模式、允许远程连接。
• 统一Crystal Reports版本：开发环境使用的是Crystal Reports 2016 SP6，生产环境为SP4+SP9混合版本，版本差异可能导致连接逻辑异常，建议将生产环境的Crystal Reports 2016升级到与开发环境一致的SP6版本。

内容的提问来源于stack exchange，提问作者Maruthi A