Introduction
In his “Troubleshooting Database Login Errors using Crystal Reports with .NET,” Eric Landes points out that he sees questions regarding the “Exception Details: CrystalDecisions.CrystalReports.Engine.LogOnException: Logon failed” message quite often in the newsgroups. That's an understatement. Since I use the “push” method (generating an ADO.NET dataset first, then setting the report's DataSource), I never saw this error, but I decided to try and replicate the problem to see why this was happening--it seems to be a popular error. I had a suspicion that impersonation and the “double hop“ (where the ASP.NET application cannot pass the user's credentials) may play a role in this problem, since the methods in Eric's article don't solve the problem for every user it should.
To replicate the problem, I used an ASP.NET project connecting to either a remote production SQL Server database or a copy of the same database on my development machine (SQL Server Developer Edition). I created a stored procedure that was just a simple SELECT statement, and used this stored procedure as my report source (“pull“ method). I probably created this report 20 different ways, testing various options, and here's what I found.
Lesson 1: Integrated or SQL Server Security
When you begin to design your report, you are prompted to connect to the SQL Server database when you choose an OLEDB connection (CR.NET doesn't use ADO.NET, and ODBC requires a system DSN). Your choices are to use Integrated Security or to enter a user ID and password. If you choose Integrated Security, Visual Studio will use your credentials to connect to the SQL Server database via Windows Authentication.
If you choose to enter a user ID and password, VS will connect using SQL Server authentication. You cannot enter another domain account in the user ID and password boxes and connect using those credentials via Windows Authentication. This information in these textboxes can only be used for SQL Server authentication. Even though you must enter a password at this stage, the password information is not stored within the report file for security reasons. The password is only used to establish the database connection. This means you must apply logon information to each table at runtime as shown in Eric's article.
The security method you choose at this point is the security method you must use when you generate your report, as we'll see in Lesson 2.
Lesson 2: Only One Security Model Allowed
The Crystal Reports engine cannot switch from Integrated Security to SQL Server security. This means that if you design your report using Integrated Security, you must supply a domain user's credentials in code to log on to the SQL Server (you can change the user, but it must be a domain account). If you will be using a SQL Server account in production, it is very important that you use this account's credentials to connect to the database during the design phase.
The CR designer caches database connection information, so I have found it best to completely delete all current connections and then exit and reopen Visual Studio before designing my report. I could then make a fresh connection to the database using the credentials I wanted. You can check by right-clicking the connection in Server Explorer and choosing “properties.” If you're using a SQL Server account, you'll see a user name; if using Integrated Security, the user name will not appear.
When using SQL Server authentication, I found it was also best if there was a SQL Server login account with the same user name on my dev database as well as the production database. You should be able to use a different SQL Server account in production than in development, but I had occasional problems I couldn't explain when I tried to do so.
Lesson 3: Credential Delegation/Impersonation ("Double Hop")
Integrated Security can only be used in two contexts: when inside Visual Studio (which, since it is a Windows Application, can directly use your network token to connect to the remote database), or when IIS and SQL Server are on the same production machine. If you try to use Integrated Security on a production IIS Server with a remote SQL Server, you will get the “Logon Failed” error. This is because the application cannot impersonate your credentials from the IIS Server to the SQL Server (the “double hop”) without Kerberos or some sort of delegation set up.
Kerberos is running on the network I tested, and normal ASP.NET apps impersonate my credentials to the SQL Server, so it appears that the Crystal Reports engine cannot take advantage of Kerberos. Not every network has Kerberos running, so if in doubt, ask the network admins.
When testing whether or not the Crystal engine could use impersonation, I tried both specifying my user name and password in the web.config file as well as leaving the information blank (and the ASP.NET worker process would use the current login credentials). In both cases, the report was unable to connect to a remote SQL Server database. The CR engine could connect to a database hosted on the same machine.
If you are using the pull method, this means your only option to connect to a remote SQL Server is to create a new SQL Server login with minimum permissions (note: do not use 'sa' or some other administrative login--this is a bad security practice), and use that login's credentials in your code. This is actually not a bad thing at all, but I could not find mention of this in any Crystal documentation.
Lesson 4: Design Changes Sometime Cause Login Failure
Sometimes, after making small changes (such as removing a field from the report), I would get the “logon failed” error. I found that after verifying the database (right-click on report, choose Database >> Verify Database) and recompiling the project, the problem went away.
Conclusion
This article is not an all-encompassing list of causes for the "login failed" error. This error is pretty general and may be thrown for reasons other than login credentials. In these cases, knowing some of the causes for this error may save you some debugging time.
Before starting a Crystal Reports application in ASP.NET, you need to give some thought as to the security context your application will be running under. In addition to data security and application security, you must also consider how the report will connect to the database.
Personally, I still prefer the push method. Since this method uses ADO.NET datasets, it can take advantage of the optimzed System.Data.SqlServer class for better performance, and security is handled in the connection string or by application impersonation as specified in the web.config file.