Solved

Problem Linking to Oracle Database in SQL Server 2008 Management Studio

Posted on 2011-09-29
14
278 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
14 Comments
 
LVL 143

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 143

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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 143

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
 
LVL 143

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 143

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 143

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

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…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

738 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