Crystal Reports Generating SQL Server Error 18456 Severity 8 Error Logs

Crystal Reports XI R2 and Crystal Reports 9 incorrectly generate non-critical connection error logs when set to run reports from SQL Server 2008.

Specifically, when Crystal Reports is connected to a SQL Server 2008 database it will serve the report correctly, but invisibly it will also generate an error log in SQL Server.

Although insignificant for just one report, this clutters up the error logs for no reason when there are several hundred Crystal Reports all doing the same thing.

Background:

As part of modernizing our company database infrastructure we updated our database server from SQL Server 2000 to SQL Server 2008.

To make this integration as seamless as possible we made sure to re-point traffic directed to the old database server name to the new server name. This is useful since we have several hundred actively used Crystal Reports in which the connection information is embedded in the report.

Also, we are using two different versions of Crystal Reports. We have some reports written in Crystal Reports 9, and some written in Crystal Reports XI R2. I would not expect problems with this since Crystal Reports XI R2 is a 2006 version that is still sold today.

As I mentioned above, the report connection information is held in each report as OLE DB connections (ie: Microsoft OLE DB Provider for SQL Server). To connect to the database server the reports use MS SQL user accounts and the user and password are set as part of this connection that are kept within the report.

Problem:

As soon as we updated our database server from SQL Server 2000 to SQL Server 2008 our Crystal Reports started generating error logs although they would serve the data as they were expected to.

When I looked at the SQL Server error logs I would see Error 18456 with a Severity of 8. Basically this error just means that Crystal Reports was not passing correct login information to SQL Server.

What was interesting, though, was using the SQL Server Profiler tool to see what Crystal Reports was trying to do.

Basically Crystal Reports would try to connect to the SQL Server master database with the SQL Server user account and would pass a blank password, which would of course result in an error log. Then, regardless of the error, Crystal Reports would send a second connection request to SQL Server to connect to the correct database, and it would pass the user and password that it was supposed to.

I checked through the Crystal Report settings and help files and could not see any information on how to correct this issue through Crystal Reports, although many people seem to have been reporting the same error. This is obviously a bug that many people are experiencing, but most help requests end with the person being told to make sure their connection information is correct, which is quite unhelpful and not the problem.

Solution: 

Once I had identified what Crystal Reports was trying to do, I was able to start experimenting with possible solutions. However I came up against many dead ends the solution would have to ensure that Crystal Reports would be forced to connect with a user and password.

Although at first moving from an ADO OLE DB provider to an ODBC connection seemed promising, this presented several problems and in the end was not a viable solution.

However, I was able to force Crystal Reports to work correctly by using a UDL file type. I have previously not used this file type much, but in this case the option of the UDL file to save the SQL user and password information proved to be the solution.

Conclusion:

I was able to circumvent the bug in Crystal Reports by setting it to use a UDL file in which the user name and password have been permanently set. In our Intranet this is not a security issue since we are quite isolated from the outside Internet, and the UDL files have specific security set on them.

The majority of the work at this point is simply to update the Crystal Report connections from embedded OLE DB connections to ADO OLE DB  connections via UDL files. Please see my article on UDL files or take a look at my post on creating UDL files for more information.

I am quite happy with this solution since it takes away the requirement to save database connection information with each report and instead centralizes it in a secure UDL file. If in future we need to change the account user or password we will be able to do so in a centralized location rather than having to update several hundred Crystal Reports.

References:

• Understanding “login failed” (Error 18456) error messages in SQL Server 2005
• Forums – SQLTeam.com – facing a problem with SQL Server and Crystal Reports 9.2.
• MSDN – Login failure issue with Crystal reports connecting to SQL Server 2005
• What are credentials in sql server 2005
• Era Of Data Blog: Identifying the source of SQL Server login failures (18456 errors)