Last week, someone from the other team contacted me to help him out regarding an issue he was facing while creating a Linked Server to an Oracle database. He was trying to create a Linked Server to a Oracle Database from a SQL Server 2005 instance. He had the Oracle Client components installed on the server hosting the SQL Server instance. After giving all the details in the Linked Server properties when clicking on Ok, he would receive the following error message.
Since the linked server is Oracle, the first thing that I tried was to check if the Server hosting the SQL Server instance, was able to connect to the listener on the Oracle Database Server using tnsping.
tnsping successfully connected to the Oracle database from the SQL Server box. Then I moved on to check the Provider details while creating the Linked Server.
The Linked Server provider was Oracle Provider for OLE DB. This is the driver that gets installed along with the Oracle Client Components. This KB article states that Error 7302 can be a result of MSDAORA.dll not being registered correctly. In this case it had to be an issue with the dll related to Oracle Provider for OLE DB i.e.OraOleDB.dll. Before checking the dll related issues, I wanted to check if something was missing in the registry. The list of Providers in the Linked Server is populated from the Providers key in the registry. This key is located under the instance name under the below key.
The entries in the registry looked like this.
I could not find anything unusual here. Now it was confirmed that something must be wrong with the OraOleDB.dll. This dll is located under the bin directory of the folder where Oracle Client Components are installed (usually called oracle_home). When I navigated to that folder, I could not find any such files. To confirm this, I checked that directory on a *working* instance, the OraOleDB.dll was listed there.
Now my job was easy. On the server which was having issues with the Linked Server, the Oracle Client Components were either not installed correctly or it was corrupt. I copied the dll from the *working* server and pasted it under <oracle_home>\bin directory. Then I registered the OraOLEDB10.dll using regsvr32.exe.
Issue was not yet resolved. While creating the Linked Server, the same error message was displayed but the error number was different, 7303.
Error 7303 means that the username and password combination is not correct, hence the login failed for the user specified in the Linked Server. When the correct login credentials for the Oracle database were entered, the Linked Server got created successfully!