Solved

Problem Linking to Oracle Database in SQL Server 2008 Management Studio

Posted on 2011-09-29
14
266 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:Mswetsky
  • 8
  • 6
14 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36814483
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
 
LVL 1

Author Comment

by:Mswetsky
ID: 36814657
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36815776
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
 
LVL 1

Author Comment

by:Mswetsky
ID: 36817079
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36902467
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
 
LVL 1

Author Comment

by:Mswetsky
ID: 36917051
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
 
LVL 1

Author Comment

by:Mswetsky
ID: 36930357
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36930390
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
 
LVL 1

Accepted Solution

by:
Mswetsky earned 0 total points
ID: 36930422
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36930636
PAQ it
0
 
LVL 1

Author Comment

by:Mswetsky
ID: 37006198
What is PAQ?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 37006310
Close it by accepting your own comment as answer.
a3
0
 
LVL 1

Author Closing Comment

by:Mswetsky
ID: 37035272
Unable to resolve. Using another server as workaround
0
 
LVL 1

Author Comment

by:Mswetsky
ID: 37007351
We were unable to resolve this problem so We are using another server instead of continuing with this one.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now