SQL SSMS not listing MSDAORA provider when connected to 64 bit instance

  • Article

Hello all,

Recently I had worked with the application team on a requirement of setting up Linked server for Oracle data source. There was an interesting question on MSDAORA provider not showing up in SQL management studio to setup Linked server. In this blog, we will be covering the reason why MSDAORA provider doesn’t list on latest 64 bit SQL Servers and determine an alternative approach to setup Linked server for Oracle.

The Microsoft OLE DB Provider for Oracle [MSDAORA] allows ADO to access Oracle databases. A typical connection string for this provider would be:

"Provider=MSDAORA;Data Source=serverName;User ID=MyUserID; Password=MyPassword;"

Microsoft OLEDB Provider for Oracle (msdaora.dll) is built by using Oracle Call Interface (OCI) version 7 and only 32 bit version of the provider is currently available. A simple way to test this is by launching an UDL file on 32/64 bit mode. To create a test udl file: create a test document and rename the extension as .UDL.

Launch udl file in 64 bit mode: Double click on the udl file directly.

Launch udl file in 32 bit mode: Invoke command prompt cmd.exe from C:\Windows\SysWOW64\cmd.exe and launch UDL file from command prompt.

 

64 bit:

Fig 1. 64 Bit UDL data lick Properties

 

32 bit mode:

Fig 2. 32 bit UDL data link properties

 

From the above screenshots, it’s evident that the Microsoft OLE DB Provider for Oracle gets listed only for 32 bit Windows system interface.

Also, Microsoft ODBC for oracle provider is available only on 32 bit. This can be confirmed by launching ODBC data source administrator(odbcad32.exe) in 32/64 bit mode. . The provider MSORCL32.dll is present only at C:\Windows\SysWOW64 location.

 

Fig 3. 32 bit ODBC Data Source Administrator

Similarly, we see the same behavior in SQL management studio for 32/64 bit SQL Server instances:

32 bit SQL Server Instance:

Fig 4. MSDAOra provider listed for 32 bit Instance

 

64 bit SQL Server Instance:

Fig 5. MSDAOra provider not listed for 64 bit SQL

 

Oracle no longer supports applications that use OCI version 7 calls, as these technologies are deprecated. So, MSDAORA will be removed in the future versions of Windows. So I suggest avoid using this feature in new development work, and ensure a plan to modify applications that currently use this feature. Instead, use Oracle’s OLE DB provider (ORAOLEDB.ORACLE). A typical connection string for this provider would be:

"Provider=OraOLEDB.Oracle ;Data Source=serverName;User ID=MyUserID; Password=MyPassword;"

Post installing Oracle’s OLE DB provider, SQL Server management studio lists OraOLEDB.Oracle provider and this provider can be used to setup Linked servers for Oracle data source.

Fig 6. 64 bit OraOLEDB.Oracle Provider on 64 bit SQL 

 

Please share your feedback, questions and/or suggestions.

 

Thanks,

Don Castelino | Premier Field Engineer | Microsoft

 

Disclaimer: All posts are provided AS IS with no warranties and confer no rights. Additionally, views expressed here are my own and not those of my employer, Microsoft.