I need to create a linked server to an Oracle database but there is no 'MSDAORA' provider. How do I add the provider?
asked Mar 21 '12 at 04:59 PM in Default
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):
answered Mar 21 '12 at 05:47 PM
+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:
After installation some adjustments in registry are necessary. Under registry Keys
You have to change Values of entries:
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.
answered Mar 21 '12 at 07:16 PM