connect mssql server 2008 to oracle 10g on Windows 7 boxes

Yaniv Schiff
Yaniv Schiff used Ask the Experts™
on
I am trying to create a linked server on my MS SQL Server 2008 to an Oracle 10.2.0.4.0 database. They are on two different boxes but on the same domain. I have no experience with Oracle so any suggestions please be very specific. I have tried installing the Oracle 11g client on my SQL Server box but i'm kind-of lost as to what to do next. I'm unsure of the name of my Oracle "Service" and not quite sure how to find that. Thanks in advance. Also, both servers are running Windows 7 Pro.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
G GodwinDatabase Administrator

Commented:
You will need a TNS names file from your Oracle DBA.  You will also need an Oracle account name that has permissions to the objects (tables or views) you want to query.
You've said:>> I'm unsure of the name of my Oracle "Service" and not quite sure how to find that
The name will be in the TNS names file.
Once you have that, you will be able to set up the linked server.  Use SQL Server Management Studio to configure the new linked server.
-G
G GodwinDatabase Administrator

Commented:
I am assuming this will be similar to Win 2003 and Win 2008 servers.
Once you have the file, let me know.  I will tell you where to place it on the MSSQL server.
There will also be some registry entries and other configurations to check.
-G
G GodwinDatabase Administrator

Commented:
Also get the SQLNET.ORA file from the Oracle DBAs.
-G
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Yaniv SchiffDirector of Digital Forensics

Author

Commented:
Ok, I have the sqlnet.ora and the tnsnames.ora files, i also have the sys password.
G GodwinDatabase Administrator

Commented:
I would advise you not use the SYS account to access Oracle via the linked server.
A dedicated account that is restricted to only the permissions it will need is recommended for this.  You will have to make the judgment call on that one.
  • Find the location that the Oracle client software is installed, and navigate to the Network\Admin directory.
e.g. D:\oracle\product\10.2.0\client_1\NETWORK\ADMIN
Rename the existing sqlnet.ora and tnsnames.ora files that are there, and add your new files.
  • In the tnsNames.ora file are entries that state "SERVICE_NAME = "...  Find the service name you want to connect to.
  • Go to Programs>Oracle....... and find SQL Plus.  Start SQL Plus and connect to the Oracle instance (service name) with the name and password you intend to use.  This will test the connectivity via the client you installed.  Get this working before proceeding.
-G
Yaniv SchiffDirector of Digital Forensics

Author

Commented:
a couple questions:

1. I have the 11.1.0 client installed on my sql box because that is the only version i've found that will work with Windows 7. Is it ok that i have the 11.1 client but trying to connect to a 10.2 database?
2. The instructions you have above, those are all to be done on the SQL server correct? if so, when i browsed to the location you suggested there were no files in there, only a folder called "Sample" which didn't have anything in it either.
3. I would like to uninstall all of oracle off this (the sql server) machine and start over because i've installed a few different things and it's getting confusing. The programs don't show up in "Programs and Features", how do i go about removing this stuff or do i just delete the program folders?

Thanks for you patience.
Yaniv SchiffDirector of Digital Forensics

Author

Commented:
also, i will be stuck using the sys password because that is the only login i know the password for. This is oracle db is created through a 3rd party application which refuses to give access to its oracle backend due to some inane agreement it has with Oracle.
G GodwinDatabase Administrator

Commented:
1. I have the 11.1.0 client installed on my sql box because that is the only version i've found that will work with Windows 7. Is it ok that i have the 11.1 client but trying to connect to a 10.2 database?
That should not be a problem.
2. The instructions you have above, those are all to be done on the SQL server correct? if so, when i browsed to the location you suggested there were no files in there, only a folder called "Sample" which didn't have anything in it either.
Yes on the SQL Server machine.  You are looking for the location where Oracle installed its client files.  Try a directory search for *.ora.  That should show you where the client installed.
3. I would like to uninstall all of oracle off this (the sql server) machine and start over because i've installed a few different things and it's getting confusing. The programs don't show up in "Programs and Features", how do i go about removing this stuff or do i just delete the program folders?
Oracle's installer will also find installed software and allow you to uninstall it.  Get a clean install of the client.
also, i will be stuck using the sys password because that is the only login i know the password for. This is oracle db is created through a 3rd party application which refuses to give access to its oracle backend due to some inane agreement it has with Oracle.
Wow. As long as the account has access to the tables and views you need, it will work.  However, you will want to be VERY careful how you set up your linked server security, as you could expose your oracle system to high vulnerability.
-G
Yaniv SchiffDirector of Digital Forensics

Author

Commented:
I was able to uninstall everything, thanks.

When installing the client which option do i want to choose?
InstantClient
Administrator
Runtime
Custom
G GodwinDatabase Administrator

Commented:
I believe I have used InstantClient successfully in the past, Administrator should work too, but I think I tend to choose Custom.  
You want the client with all the tools.

-G

Yaniv SchiffDirector of Digital Forensics

Author

Commented:
Well, i tried doing custom and selecting everything and got almost all the way through the installation but then the installer encountered some errors and closed down. So now i'm trying to fix the installation but the installer won't show what's already been installed. Any ideas?
G GodwinDatabase Administrator

Commented:
I can't advise on that, because I can't see the server, and I don't want to advise you into a critical error.  
Rest assured, I have never seen an Oracle installation that ran smoothly.  It always seems to take multiple attempts and often some cleanup in between.
Keep trying; get help if needed.  Let me know when the client is installed.  
-G
 
Yaniv SchiffDirector of Digital Forensics

Author

Commented:
Ok, i'm trying the SQL Plus part of your instructions and i'm getting:

Error:
ORA-12560: TNS:protocol adapter error
G GodwinDatabase Administrator

Commented:
Are the new sqlnet.ora and tnsnames.ora files in place?

-G
Yaniv SchiffDirector of Digital Forensics

Author

Commented:
they are, i copied the ORAs file my oracle server to my sql server, i also copied the listener file after testing the first time. Same error.
G GodwinDatabase Administrator

Commented:
Sorry I can't help with the client software installation.  You might want to try uninstalling and reinstalling cleanly again.
Once you get the client running, we can pick up from there.  We can't do anything in SQL server until the client software is installed and working properly.
-G
Yaniv SchiffDirector of Digital Forensics

Author

Commented:
Ok, through SQL Plus I was able to get connect to my Oracle db. Is this where i need to be for you to help?
G GodwinDatabase Administrator

Commented:
Yes.  You've come a long way baby! (That's just an old expression.)
Are you comfortable with Regedit?  You will need to look at some entries; maybe change some too.  It is dangerous. Microsoft always alerts you to dire consequences if done wrong.  You could break the whole computer and have to restart from scratch.  I can't/won't be responsible for this part.
If so, check the registry for MTxOCI entries, and compare them with the names of the DLLs that were installed with your Oracle Client.
For example: in my case Oracle client installed at D:\Oracle\Product\10.2.0\client_1 so I look in D:\Oracle\Product\10.2.0\client_1\BIN for the DLLs that are similar to the ones listed in the MTxOCI entries.  The Registry entries need to point to the proper DLLs.  Older Oracle installations did not fix these entries, however I believe newer ones might be fixing them.  Check it.  Correct it if needed for all three.  See attached pictures.
Let me know what you find.
-G


Registry-MTxOCI.JPG
DLLnames.JPG
Yaniv SchiffDirector of Digital Forensics

Author

Commented:
Ok, just to be sure i'm choosing the right things, in the BIN folder i have a file called "oraclient11.dll" and "orasql11.dll", as well "oci.dll". Attached is a screenshot of the registry entries. I should change OracleSqlLib to point to "orasql11.dll" and OracleXaLib to point to "oraclient11.dll". Is this correct? And leave OracleOciLib as is?
reg.jpg
G GodwinDatabase Administrator

Commented:
Yes.
Yaniv SchiffDirector of Digital Forensics

Author

Commented:
ok, that's done.
G GodwinDatabase Administrator

Commented:
Open Management Studio, and connect to the instance you want to add the linked server to.
Navigate to Server Objects>Linked Servers>Providers.  Right-click on OraOleDB.Oracle and select properties. (See Picture.)
Make sure "ALLOW IN PROCESS" is selected (checked). (See Picture.)
-G


Provider-Properties-1.JPG
Provider-Properties.JPG
G GodwinDatabase Administrator

Commented:
Verify "Allow In Process" in the registry.
Look at each instance.  If the entry is not there you may have to add the registry key.  
(See pictures.)
 
 

Registry-AllowInProcess.JPG
G GodwinDatabase Administrator

Commented:
Let me know when you are done.
-G
Yaniv SchiffDirector of Digital Forensics

Author

Commented:
Ok, that's all set.
G GodwinDatabase Administrator

Commented:
Now Add the linked server.  
Right click on Linked servers and select New Linked Server... (see picture.)
In the window that pops up, follow the example in the picture. (see picture.)
For the security tab, I recommend adding each local user and mapping them to a remote (oracle) user, even if it is the same remote user for all of them.  If you use the bottom select (Be made using this security context), you will open the linked server up to anyone who can connect to the SQL server.  Even guests, would be able to submit queries to the linked server. (See picture.)
-G

New-Linked-Server.JPG
Yaniv SchiffDirector of Digital Forensics

Author

Commented:
What do i put for Product Name, Data source and Provider String?
Yaniv SchiffDirector of Digital Forensics

Author

Commented:
oops, never mind.
Yaniv SchiffDirector of Digital Forensics

Author

Commented:
for the remote user do i put "sys" or "sys as sysdba"?
Yaniv SchiffDirector of Digital Forensics

Author

Commented:
i'm getting this error message. Does this mean my TNS is not setup correctly?
error.jpg
G GodwinDatabase Administrator

Commented:
Just "sys"
 
-G
G GodwinDatabase Administrator

Commented:
Be sure you replaced "TNS_Service_Name_of_Oracle_instance" with your TNS name.
-G
Yaniv SchiffDirector of Digital Forensics

Author

Commented:
i did.
G GodwinDatabase Administrator
Commented:
Check the name.
Ref: http://ora-12154.ora-code.com/
Excerpt:
ORA-12154: TNS:could not resolve the connect identifier specified Cause: A connection to a database or other service was requested using a connect identifier, and the connect identifier specified could not be resolved into a connect descriptor using one of the naming methods configured. For example, if the type of connect identifier used was a net service name then the net service name could not be found in a naming method repository, or the repository could not be located or reached.
Action:
- If you are using local naming (TNSNAMES.ORA file):
- Make sure that "TNSNAMES" is listed as one of the values of the NAMES.DIRECTORY_PATH parameter in the Oracle Net profile (SQLNET.ORA)
- Verify that a TNSNAMES.ORA file exists and is in the proper directory and is accessible.
- Check that the net service name used as the connect identifier exists in the TNSNAMES.ORA file.
- Make sure there are no syntax errors anywhere in the TNSNAMES.ORA file. Look for unmatched parentheses or stray characters. Errors in a TNSNAMES.ORA file may make it unusable.
Also see: http://dbaspot.com/ora-12154-tns-could-not-resolve-service-name.html
-G
Yaniv SchiffDirector of Digital Forensics

Author

Commented:
From everything i've read in those two posts my files are configured properly. One thing though, when trying to connect thru sql plus, when i use the SYS account i get the error "Connection as SYS should be as SYSDBA or SYSOPER". When i connect as "sys as sysdba" it connects just fine. Could this be affecting my linked server config. If so, how would i go about using this connection type? I've also tried tnsping and it pings the server just fine.
G GodwinDatabase Administrator

Commented:
I'm not sure what you have going on there.
I would try "SYSDBA" in the security of the linked server.  
If that fails, you might try "sys as sysdba".  Even though I have a hard time believing it would work, I would not be too suprised at anything.
-G
G GodwinDatabase Administrator

Commented:
Please also check that the client that was installed is indeed certified by Oracle to run on your specific version of Windows.  
-G
Yaniv SchiffDirector of Digital Forensics

Author

Commented:
it is. do you know how i go about seeing what database i'm actually connecting to through sql plus?
G GodwinDatabase Administrator

Commented:
Sorry, I am a SQL Server person not an Oracle person.  So, I don't think I will be very helpful resolving the Oracle Client software problem you are having.  
I've created lots of linked servers to Oracle (in fact, I am installing a 10g client on a new server as I write this...).  
Sorry, I can't help with SQL Plus, the only thing I use it for is to verify that my client is installed and functioning.
I did read something that makes me think you may not be able to use the SYS user as you are wanting.  You may need to use SYS to create a new user that can then connect.  (that's just a thought, I don't want to send you on a wild goose chase though.)
You might need to get some help from the Oracle community in getting the Oracle client setup correctly.
-G
 
Yaniv SchiffDirector of Digital Forensics

Author

Commented:
Ok, i understand, and i am working on getting my connection to the oracle server established. I think you might be right about the SYS user dilemma. I really appreciate all your help with this. I will leave this question open for a little while longer so that if i do come across a solution i can post it. But all the steps you have given me seem to be everything necessary to connect to a standard oracle install. Unfortunately, mine isn't that standard i don't think.
Database Administrator
Commented:
O.K.,
1. Find the Oracle client software that is certified by Oracle for your OS
(e.g. 10.2.0.1 for 32 and 64 bit Win2003 [at the time of this writing]).
***Oracle recommends Client and Server be of the same generation for feature compatability, but I believe they are generally backward compatible. So, you "should" be able to connect to an 8 server with a 10 client. (i hope those are not words I will regret).
2. Get the tnsnames.ora and the sqlnet.ora files from your Oracle DBAs.
3. Install the Oracle client software (the "Runtime" option should work fine).
a. I like to reboot the server after the Oracle client installation. It just seems to help.
4. Find the path to your OracleHome. (See picture)
5. Place the tnsnames.ora and sqlnet.ora files in the proper location.
<OracleHome>\NETWORK\ADMIN\
If there are exising files there, just rename them.  
Let me know when this is done.
-G


00a-orahome-from-Registry.JPG
G GodwinDatabase Administrator

Commented:
Sorry, that last post was intended for someone else's question (similar theme)...
G GodwinDatabase Administrator

Commented:
Did you get it?
-G
Yaniv SchiffDirector of Digital Forensics

Author

Commented:
We never did get it.
G GodwinDatabase Administrator

Commented:
Until the Oracle client  connection side is figured out, I don't think we can do anything more here on this question.  
Do you want to close this question, and then open another when you have more specific questions?
This one has gotten long, and other experts will not likely look in to even see if they can help when it gets this long.  
-G
Yaniv SchiffDirector of Digital Forensics

Author

Commented:
That's probably a good idea, can i award you some points for the effort. I really appreciate your help.
G GodwinDatabase Administrator

Commented:
Sure, that would be appreciated.  
Sorry I can't help you resolve the Oracle problem also...
-G
Yaniv SchiffDirector of Digital Forensics

Author

Commented:
I was not able to complete this problem due to issues on the oracle side, but all the instructions for the microsoft issues were complete and very helpful.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial