Data connector fails to refresh ODBC connection to SQL Server

BA/BI SAP Crystal Reports
, , , , , , , ,
1

We’re experiencing failures with our scheduled Crystal Reports after a network reconfiguration last week. Reports that pull data via ODBC from SQL Server are consistently failing during scheduled runs, though they work fine when run manually from the designer.

The error log shows:


Error: [Microsoft][ODBC Driver Manager] Data source name not found
Connection: CRW32_SQLSERVER_PROD
Job Server: CRSERVER01

I’ve verified the ODBC DSN exists on the Crystal Reports server machine under System DSN. The firewall team mentioned they updated routing rules for database traffic. The Job Server service is running under a domain service account that should have necessary permissions. Manual runs work perfectly, but scheduled jobs fail immediately at data refresh. Any ideas what might have changed?

2

I’d also verify the service account has the necessary SQL Server permissions. Run this on your SQL Server to check:

SELECT name, type_desc FROM sys.server_principals
WHERE name = 'DOMAIN\CRJobServerAccount';

If the account isn’t listed or lacks db_datareader role on your target databases, that could explain scheduled failures even if the DSN tests fine from your account.

3

Based on your testing, here’s the complete resolution approach addressing all three areas:

ODBC DSN Configuration: Verify the DSN configuration includes the correct SQL Server instance name and authentication method. Since manual runs work, test the DSN specifically under the Job Server service account context:


runas /user:DOMAIN\CRJobServerAccount "odbcad32.exe"

Test the connection from that context. If it fails, the DSN might need SQL authentication instead of Windows authentication, or the connection string needs the fully qualified server name.

Firewall and Network Routing: Work with your network team to add explicit firewall rules:

  • Allow TCP port 1433 (SQL Server) from CRSERVER01 IP to SQL Server IP
  • Allow return traffic for established connections
  • If using named instances, also allow UDP port 1434 for SQL Browser service
  • Verify no proxy or NAT rules interfere with the service account’s Kerberos ticket exchange

Test with: Test-NetConnection -ComputerName SQLSERVER -Port 1433 from CRSERVER01

Job Server Service Account Permissions: Ensure the service account has:

  1. SQL Server login with appropriate database roles (db_datareader minimum)
  2. Windows permissions to read the System DSN registry keys (HKLM\SOFTWARE\ODBC\ODBC.INI)
  3. Network permissions to resolve and connect to the SQL Server hostname

After network rules are updated, restart the Crystal Reports Job Server service to clear any cached connection failures. The key issue is that scheduled jobs run in the service account context, which now faces network-level blocks that didn’t exist before the reconfiguration. Manual runs work because your user account likely has different network path or cached Kerberos tickets that still work through the old routes.

4

First thing to check - is the ODBC DSN configured as a System DSN or User DSN? The Job Server service runs under a specific service account context, so it needs System DSN visibility. Also verify the DSN exists on the actual server machine where Job Server runs, not just your workstation. When you run manually, you’re using your user context which might have different DSN access.