Oracle, DB2, and ASP.NET

In my ASP.NET applications, I typically use SQL Server 2000 but lately I have had the need to connect to Oracle and DB2. The Oracle and DB2 providers do not work with ASP.NET out of the box so I thought I'd share some of my experiences along with best practices for resolving the issues. Documentation for resolving these issues seems to be lacking, particularly in the area of DB2.

As you may know, ASP.NET applications execute in the user context of the ASPNET account on the server computer. By default, this account does not have access to the installation folders of the two providers. This lack of permissions will cause an ASP.NET application attempting to utilize either provider to incorrectly report that the corresponding provider has not been installed.

General Issues

Table 1: Default Folders

Client	Folder
Oracle	C:\Orant\
DB2	C:\Program Files\IBM\SQLLIB

One solution to this is to make the ASPNET account a member of the server Administrators group. This approach should be avoided. Instead, grant the ASPNET account Read & Execute permission on the folder where the provider is installed. These permissions should be inherited to lower levels of the directory structure. Refer to Table 1 for the standard installation paths for the two providers. Note that your installation may vary depending on the version installed on your server.

The DB2 Installer does simplify this process a bit. A group named DB2USERS is created during installation and the appropriate permissions have been granted to the group. To give the ASPNET account the appropriate access to the folder, add it to the DB2USERS group. This will require restarting IIS which can be easily accomplished by running IISRESET from the run dialog box.

Oracle Specific Issues

This issue seems to be specific to the Oracle 8i client and the MDAC (Microsoft ODBC driver and the Microsoft Oracle Provider) and is addressed in http://support.microsoft.com/default.aspx?scid=kb;en-us;264012. Essentially, a useless error message is displayed:

SQL State: "NA000
Native error code: 0
Driver Message: [Microsoft][ODBC Driver for Oracle][Oracle]
Do you need any suggestions to avoid the error?"

What is causing this is that some registry entries are incorrect or missing. Follow the instructions in the KB article linked above to solve the problem. Of course, the obligatory warning about incorrect modification of the registry can cause the computer to cease functioning applies.

DB2 Specific Issues

In trying to resolve the connectivity issue with DB2, a DB2 DBA suggested examining the registry to verify that the entries in HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI and HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INT for DB2Connect refer to the appropriate folder. The DBA said that the issue seems to be limited to systems where the client software was upgraded from version 7 to version 8.

My server did not fall into this category and the registry entries were referencing the correct path but I thought I should include it for completeness.

Summary

Whenever a new database connectivity provider is installed and needs to be used by ASP.NET, permissions on the installation folder should be checked to ensure that the ASPNET account can access the files. For security reasons, never set the ASPNET account as a member of the Administrators group although doing so would typically solve the problem.

If the new provider still doesn't work through ASP.NET, determine if connections to the database server can be made using the provided software (SQL+ for example) and query Google for your symptoms. As a personal preference, I typically check Google Groups before trying standard Google.