Problem Linking to Oracle Database in SQL Server 2008 Management Studio

I have been having trouble linking to an Oracle database in SQL Server 2008. I am able to set up the linked server but when I test the connection I get a error "Error 7302, Cannot create an instance of OLE DB Provider..". I have included a screen shot of this error. I have confirmed that the username and password for the connection are correct and valid. I am able to use SQL Plus and communicate to the Oracle database but once again no luck from within SQL Management Studio. I have also checked the following:
- "allow in process" is checked within the Oracle Provider for OLE DB
- The tnsnames.ora is in the correct file and the information within it is correct. I have used tnsping to verify     this information as well.
- I have installed the Oracle 11g runtime client.

I am not an expert at SQL server so I am hoping that I am missing something that you all can help me identify. Thanks in advance for your help!
 Screen shot of the error message I am currently getting
LVL 1
Mitch SwetskyBusiness AnalystAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Mitch SwetskyConnect With a Mentor Business AnalystAuthor Commented:
Interesting as this sounds, Our development server was a copy of the production virtual machine.
I uninstalled all the oracle software including removing registry entries.
When I reinstalled Oracle the linked servers worked.
So I am going to use the development server and copy all the databases and SSIS packages to that device (along with the other needed files) and then I'll just rename it.
Now my issue becomes backing up 15 dbs and 6-10 ssis packages then restoring them to a working state.
I don't know what to do with this question sisnce I could not get a suggestion to work, but as I said, I really appreciate your efforts.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this problem is not easy to tackle.
if this is done after installation of the oracle client/oledb, you need to restart the box first
after that, the only thing that I have seen that worked for people (on the net, I never had that problem after a reboot), is to play with COM permissions ...
0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
Sorry, I'm not sure I understand what you are referring to. Could you please explain or give an example of what you are suggesting? Thanks for your help.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
DCOM permissions might make that you (sql server instance) cannot create the OledB objects.
see here how it can be configured :
http://blogs.msdn.com/b/dataaccesstechnologies/archive/2010/08/19/permissions-needed-to-set-up-linked-server-with-out-of-process-provider.aspx
0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
I tried to follow the directions on the linked page with DCOMCNFG and Regedit and MA...
Basically it adds full permissions to the SQL user and then uncheck allow in process.
I stilll get the same error.

Is there some way to isolate the problem.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
unfortunately, I know that you could use some process and file access watching tools, to see where the permission denied comes from.
but I found no "quick" solution to wade through the thousands of lines generated with those tools, aka to work with them efficiently:
http://technet.microsoft.com/en-us/sysinternals/bb896653
http://technet.microsoft.com/en-us/sysinternals/bb896646
0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
angelIII             First off-  Thank You for your time and experienced effort!
I really appreciate any insight and direction.

In addition to my original posted activities, I have uninstalled SQL Server and re-installed it.
I used Process Explorer to try and identify errors in processes but were unable to identify any.
The server with this problem is a production device so I can't try things on it.
Instead I have a Virtual Machine (VM) copy of the production VM and I am trying to test sloutions on it.

I'd like to test my understanding of the situation.
If oracle is installed and works with Access databases, I can successfully run queries from Oracle\SQL Plus and I can successfully create and deploy SSIS packages to my Management
0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
I have decided to try and use our development server on which linked servers are working.
Now I want to get all the databases from the production server onto the development VM and I'll use it as the new production instance. So I am asking a new question Q_27385214
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if you get oracle to be used like you describe, the error you get is even more curious.
I have unfortunately no more input to give that might help, as of current knowledge :(
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
PAQ it
0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
What is PAQ?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Close it by accepting your own comment as answer.
a3
0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
Unable to resolve. Using another server as workaround
0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
We were unable to resolve this problem so We are using another server instead of continuing with this one.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.