Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In some cases, a database that resides on SQL Server may require the data to be retrieved from the Oracle database.
Requirements
First, a related user is created on the oracle side.
If the Oracle Client did not installed on the server where your SQL Server is located,, the installation is necessary.
The tns information to be used by the user created on the Oracle side is saved in the tnsnames.ora file in the path where the client is installed (usually this happens -> C: \ app \ product \ x.y.z \ client_1 \ network \ admin).
If you can not save to the Tnsnames.ora file, open the notepad with run as adminintrator and then open the file “tnsnames.ora” which I mentioned above with Path-> Open.
Create a Linked Server
Then you can do this with the help of the following script.
| myUser | Sql login to use linked server |
| Xsource | The name you give to TNS that you have saved in the “Tnsnames.ora” file |
| XServer | Linked server’s name |
| oracleuser | The user created on the Oracle side |
| oracleuserşifre | The password of the user created on the Oracle side |
| serverName\InstanceName | SQL Intance Name |
1 2 3 4 5 | EXEC master.dbo.sp_addlinkedserver @server = 'XServer', @srvproduct = 'Oracle', @provider = 'OraOLEDB.Oracle', @datasrc = 'Xsource' ,@provstr=N'SERVER=serverName\InstanceName;User ID=myUser' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'XServer', @locallogin = 'myUser' , @useself = N'False', @rmtuser = N'oracleuser', @rmtpassword = N'oracleuserşifre' |
To run the part of the above script that is related to “addlinkedsrvlogin” you need to know the user and password that has the privilege on the oracle side.
It is not appropriate for an MS SQL database expert to know a user password defined on oracle.
So you can make this operation to the developer that will use this linked server without ever learning this password.
The details of how to do this can be found in the article titled “How To Change The Remote Login Password For The Linked Server“.
1 2 3 | USE MASTER GO GRANT EXECUTE ON sys.xp_prop_oledb_provider TO myLogin |
In some cases, you may experience difficulty with linked server after installing Oracle Client.
If you make the following settings by right clicking on the OraOLEDB.Oracle tab under ServerObjects-> LinkedServers-> Providers in the SQL Server Instance, you will fix the problem.
