How to add linked server provider 'MSDAORA'

You just need to install Oracle Client on your SQL Server. This way the Provider you need will become available in the providers dropdown when you setup your linked server. I should note that the provider should be OraOleDB.Oracle not MSDAORA. After you install Oracle Client and add your linked server, please do one more step to check the properties of your OraOleDB.Oracle provider: it needs to have the option Allow inprocess enabled, like this:

Also, you might want to make sure that your connection settings to your Oracle database are setup properly (under Security tab of your linked server property pages):

Oleg

• List item

+1 to @Oleg. I only add, that there are issues with x64 machines. I mean, you need to do the installation of the Oracle Drivers in appropriate way to be able to access oracle from x64 version of SQL Server as well as from x32 tools like BIDS (SSDT).

This steps are for the 11g client installation (you can access earlier versions of oracle as well using the 11g client).

You need to install first x32 version of Oracle Drivers followed by x64 version of drivers.

During installation you have to ensure, you are installing:

• Oracle Call Interface
• Oracle Net Client
• Oracle Provider for OLE DB

After installation some adjustments in registry are necessary. Under registry Keys

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSDTC\MTxOCI 

You have to change Values of entries:

OracleOciLib = oci.dll 
OracleSqlLib = orasql11.dll
OracleXaLib = oraclient11.dll 

After Rebot and following @Oleg answer you should be able to create the Linked Server and use it. You will probably be able to create it even without following @Oleg's answer, but in that case you will be able to query the Linked Server, but you will be unable to browse the linked server views, tables etc. in SSMS.