I wanted to query some Oracle data from the SQL Server Management Studio today and issue some updates as well. After trying many, many things I finally was able to get the connection to work. I wanted to document the exact steps here, mostly for myself.
You have to add a linked server into SQL Server instance. You can use sp_addlinkedserver stored procedure. Unfortunately, the article does not document well how to pass parameters. I was not able to get the process to work using tns name. I had to use explicit data source string. Here is what it looks like
sp_addlinkedserver 'OracleLatest', 'Oracle', 'OraOLEDB.Oracle', '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = Orcl)))'
OracleLatest in the above example is just linked server name, it can be anything. Oracle is the product name, which can technically be anything as well. I like “Oracle” name. The next parameter is the provider name, and it has to be OraOLEDB.Oracle. The last parameter is the actual Oracle connection string.
Now, I need to create login. I am going to use sp_addlinkedsrvlogin procedure. Parameters as well enough documented for this one.
sp_addlinkedsrvlogin 'OracleLatest', 'FALSE', null, 'user', 'password'
First parameter is the linked server name from the previous step. Second parameter indicates that login information is not passed from SQL Server into Oracle. As a result second parameter is null to signify that no login is passed across. FIanly, the last two parameters are Oracle credentials, user and password.