Solved

Problem Linking to Oracle Database in SQL Server 2008 Management Studio

Posted on 2011-09-29
14
275 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 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Are triggers slow? 7 23
SQL QUERY 3 33
SQL Sum of items in two tables not equal. 5 47
Parsing this XML works but the other one doesn't 9 28
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Detach & Attach 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.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

756 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