Microsoft ODBC For Oracle Driver Connectivity Problems

Well it has been a long time since any new posts were posted here on this wonderful blog. It is not because I have run out of problems to write about, far from it. It is because I have not had enough time to write because of the so many problems.

Anyway, what more wonderful of a topic to write about than that of the connectivity problems associated with Microsoft's ODBC drivers for Oracle?

.NET developers will probably be accustomed to using ODBC drivers for connecting to data sources. Consider the following code:

1. // Create Connection String.

2. OdbcConnectionStringBuilder CString = new OdbcConnectionStringBuilder( );

3. CString.Add( "Dsn" , "MY_SYSTEM_DSN" ); // As Registered With Windows.

4. CString.Add( "Uid" , "MY_USER_NAME" );

5. String.Add( "Pwd" , "MY_PASSWORD" );

Above I am creating a new ODBC connection string which I will eventually pass to a new ODBC connection object. It is important to note that the ODBC DSN on line 3 should be exactly the same as the one registered with Windows or else an exception will be thrown.

So, what is the problem with Microsoft's ODBC driver for Oracle? To put simply, permission problems. When attempting to connect to an Oracle database using Microsoft's ODBC driver for Oracle, especially from a Web Application hosted on IIS, your going to run into problems.

The biggest problem is that of Windows not able to locate the Oracle Client software that is installed. You might receive the now famous following message:

The Oracle(tm) client and networking components were not found.

These components are supplied by Oracle Corporation and are part

of the Oracle Version 7.3(or greater) client software

installation.

You will be unable to use this driver until these components 

have been installed.

But you do have the Oracle Client software installed you say? Oh my god, so did I, yet I still received this error message. So what is the solution? Follow the following steps and you should be on the way to victory:

1. If you have more than one copy of the Oracle Client software installed on the same machine, whether for the same or different versions of Oracle, un-install them completely and then reinstall only one of them. For some reasons Windows will complain if more than one version exists. If you have different versions to support multiple different versions of Oracle, then the one version you install should be the latest version since the latest version will be backwards compatible with older versions of Oracle and not vice verse.
   
2. Examine the PATH environmental variable on the machine and check to see if there is more than one location reference to different Oracle components. If so, remove them and leave only those entries set by the installment you did in Step 1.
   
3. Examine the PATH environmental variable on the machine and ensure that the \Bin sub directory of the Oracle Client software installation directory is listed included. If not, add it.
   
4. Disable SQL*Net Authentication in the SQLNET.ora file. To do this, navigate to the SQLNET.ora file in the \NETWORK\ADMIN sub directory of the Oracle Client software installation directory and either edit or add the following entries:
   
   

   SQLNET.AUTHENTICATION_SERVICES = (none)
   SQLNET.AUTHENTICATION = (none)

   
   
5. If your connecting from a Web Application hosted on IIS, navigate to the Oracle Client software installation directory and add both the IUSR_machinename and IWAM_machinename accounts to it. Give both accounts the Full Control permission. You can do this by right clicking on the directory folder and choosing the tab named Security.
   
6. Restart the machine to allow all changes to take effect and voila.
   

Don't you wish all things in life could be this easy? I do and I conclude this post by thanking you for reading.