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

image

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s