Solved

Problem Linking to Oracle Database in SQL Server 2008 Management Studio

Posted on 2011-09-29
14
279 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

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.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

628 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