Creating a Oracle Linked Server in Sql Server

1.    Make sure oracle client is installed on SQL Server machine
2.    How to test it is installed


3.    Make sure OraOLEDB.Oracle is listed under Server Objects\ Linked Servers\Provider

4.    Execute command EXEC sp_addlinkedserver ‘TEST_XYZ’, ‘Oracle’, ‘OraOLEDB.Oracle’, ‘XYZ_TCP’

a.    ‘TEST_XYZ’ – Name you give to the Linked Server
b.    ‘Oracle’ – Optional, just mention it
c.    ‘OraOLEDB.Oracle’ – Provider
d.    ‘XYZ_TCP’ is the name of the oracle database found in your tnsnames.ora  file

5.    After you create the linked server you need to associate a sql server login to a oracle login.

6.    Use EXEC sp_addlinkedsrvlogin ‘TEST_XYZ’, ‘FALSE’,’SqlServerLogin’, ‘OracleUserId’, ‘OraclePassword’
a.    ‘TEST_XYZ’ – Name of the Linked Server for which you want to create a remote login
b.    2nd Parameter I have mentioned as ‘FALSE’ since I did not want the current logged in user to be associated with the remote login, but wanted to have a different user in this case ‘SqlServerLogin’ to be associated. I had currently logged in using NT authentication which had amin privilages.
c.    ‘SqlServerLogin’ – SQL server login (Note: if you mention NULL then all sql server user will get access)
d.    ‘OracleUserId’ – Oracle UserId which is used to connect to oracle database
e.    ‘OraclePassword’ – Password for Oracle User

7.    How to query oracle database – select * from TEST_XYZ..OracleUserId.TABLE_NAME

8.    To disassociate the sql server user to a oracle user use EXEC sp_droplinkedsrvlogin ‘TEST_DDWT’, ‘SqlServerLogin’ (Local Login when you execute sp_helplinkedsrvlogin)

9.    To get a list of user access to linked server, use sp_helplinkedsrvlogin