Database Logon Failed - only on one of two identical servers

[Former Member]

I have a Windows application (not web-based) that is used to run and export Crystal report files to PDF.   The application runs fine in both my dev and test environments locally, and it is also running smoothly on my client's production environment.  The client is not trying to set up a fresh Test environment, and we are encountering the "Database logon failed." error.  I don't have direct access to the servers, but for everything I can think to check, everything appears to be identical in the two environments.  I'd welcome any suggestions on what to try, as I've exhausted the things I can think to check to find the differences.

Development:

Application written in VS2010, with runtimes installed with the proper install package, not just running the msi, per instructions.   It is compiled with the platform set to "Any CPU".

The Crystal rpt's in use are older Crystal XI R2 files that were inherited from an older system.  They point to a specific ODBC name, but the database that that ODBC is set to changes at run-time (because we could be running the same report against one of a number of db's).  I found during development that I had to modify the report connection's "Use DSN Default Properties" setting to "true", but they've otherwise seemed to run smoothly up until now.

Deployment Environment:

Both the working and not-working environments are running the application on a Windows 2012 R2 server (64-bit) and hitting a SQL Server 2008 R2 database.  The Crystal 64-bit runtimes are installed.  Both ODBC's are pointing to a SQL Server driver version 6.03.9600.17415.  They were set up in the 32-bit ODBC manager, but show as 32/64-bit so I presume there isn't a clash there (or there isn't in production, anyway).

The basic code used to generate the report:

reportDocument.Load(rptFilePath);

reportDocument.SetDatabaseLogon(strUser, strPwd);

CrystalDecisions.CrystalReports.Engine.Tables tables = reportDocument.Database.Tables;

connectionInfo.ServerName = "MyODBCName";

connectionInfo.DatabaseName = strDB;

connectionInfo.UserID = strUser;

connectionInfo.Password = strPwd;

foreach (CrystalDecisions.CrystalReports.Engine.Table table in tables)

{

    TableLogOnInfo tableLogonInfo = table.LogOnInfo;

    tableLogonInfo.ConnectionInfo = connectionInfo;

}

reportDocument.SetParameterValue(0, myParameterValue);

reportDocument.ExportToDisk(ExportFormatType.PortableDocFormat, pdfFilePath);

[Former Member · Accepted solution]

We resolved the problem - logging here for posterity.

I didn't specify in my original post, but my Windows app was running as a service.  The client had a specific user set as the account the service ran under.

We had found in setting up their production environment that the reports only seemed to work if we created the ODBC entry as a "User DSN", and she happened to be logged into the server as that Service Owner user when she created it.  In her test environment, she created the User DSN when logged in as the wrong user.    Once we logged in as the "Service Owner" user and created the User DSN under that account, it worked fine.

I'd be curious if anyone has any thoughts why a Service DSN wouldn't work in this scenario, but otherwise, the issue is resolved.

[former_member11696]

SQL 2008 should be using the SQL Native 10 client dll. Try updating the ODBC drivers to use that one.

MS doesn't fully support the MDAC drivers when connecting to 2008 or 2012 SQL Servers, or above.

Can you ping the Server?

Don

[Former Member]

I had them try changing to the Native 10 client while I was waiting for a response, and it didn't help.   (I had them install the 64-bit install for that.)

The application connects successfully to the database in other spots where I just use a regular SQL Connection via code (if that's what you were looking for with the "ping" question... that the sql server is accessible to the box running the application?)