{"id":3973,"date":"2022-01-10T07:00:00","date_gmt":"2022-01-10T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=3973"},"modified":"2024-06-21T01:57:19","modified_gmt":"2024-06-20T22:57:19","slug":"pos-mporoyme-na-syndesoyme-sql-server-me-oracle-database-me","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-mporoyme-na-syndesoyme-sql-server-me-oracle-database-me\/","title":{"rendered":"How can we connect SQL Server to Oracle Database using Linked Server"},"content":{"rendered":"

THE SQL Server<\/strong> it allows us to be able to connect and execute queries on remote Database Instances that are not necessarily in the same RDBMS. In this article we will see specifically how it is done from instance of SQL Server to its instance Oracle Database<\/strong> using Linked Server<\/strong>. <\/p>\n\n\n\n

In previous<\/a> article we had seen how to connect SQL Server to Oracle using PolyBase<\/strong>. The main difference is that with Linked Server we don't simply read data but we can execute Stored Procedures and modify the data with inserts, updates, deletes.<\/p>\n\n\n\n

* All the differences between Linked Server and PolyBase can be read from here<\/a>.<\/em> <\/p>\n\n\n\n

The installation<\/h2>\n\n\n\n

First we have to download him Oracle Database Client<\/strong> from here<\/a>.<\/p>\n\n\n\n

Then we will have to install it on the server that hosts SQL Server.<\/p>\n\n\n\n

When installing as installation type <\/em>we choose Administrator<\/strong>:<\/p>\n\n\n\n

\"\"
01<\/figcaption><\/figure>\n\n\n\n

In the next tab we select Windows Build-in Account<\/strong>:<\/p>\n\n\n\n

\"\"
02<\/figcaption><\/figure>\n\n\n\n

We leave the following tabs as they are and complete the installation.<\/p>\n\n\n\n

Then we go to the following folder in the path that has been created and put where user the name of the user to whom the installation was done:<\/p>\n\n\n\n

C:\\app\\client\\user\\product\\19.0.0\\client_1\\network\\admin<\/code><\/pre>\n\n\n\n
Inside this folder there will be a file named tnsnames.ora<\/em><\/strong>. We edit it and add the following entry by putting a name (e.g. ORADEV), the hostname<\/em>, the door <\/em>and as service_name<\/em> the name of the database or service we want to connect to:<\/p>\n\n\n\n
ORADEV=\n  (DESCRIPTION =\n    (ADDRESS_LIST =\n      (ADDRESS = (PROTOCOL = TCP)(HOST = oracledev1)(PORT = 1521))\n    )\n    (CONNECT_DATA =\n      (SERVICE_NAME = oradev)\n    )\n  )\n<\/code><\/pre>\n\n\n\n
To test that so far everything is playing well we can open a Command Prompt and do tnsping <\/em><\/strong>with the name of the record in tnsnames.ora:<\/p>\n\n\n\n
\"\"
03<\/figcaption><\/figure>\n\n\n\n
If it appears at the end Okay <\/em>it means that we are a blessing, otherwise we may be cut by some firewall or network issue.<\/p>\n\n\n\n
After making sure that the communication is working, we connect to SQL Server from the SQL Server Management Studio<\/strong> and we choose Server Objects<\/em> and right click Linked Server<\/em>, New Linked Server\u2026<\/strong><\/em>:<\/p>\n\n\n\n
\"\"
04<\/figcaption><\/figure>\n\n\n\n
In the tab General <\/em>we define the name we want to have Linked Server<\/em>, as Server type Provider<\/em> Oracle Provider for OLE DB<\/em><\/strong> and as Data source<\/em> the name we gave the registration to tnsnames.ora<\/strong><\/em>*:<\/p>\n\n\n\n
*Alternatively, instead of putting the document from tnsnames.ora, we can use ezconnect by putting ip, port and service, for example oracle.dataplatform.gr:1521\/orcl<\/strong><\/em><\/p>\n\n\n\n
\"\"
05<\/figcaption><\/figure>\n\n\n\n
In the tab Security <\/em>we choose Be made using this security context <\/em>and put the credentials of the Oracle user with whom we will connect:<\/p>\n\n\n\n
\"\"
06<\/figcaption><\/figure>\n\n\n\n
For our example I have created a user named \u201cStratos\u201d which has right connect<\/em>, resource<\/em>, unlimited quota on tablespace users<\/em> and I have made in it a table \u201cPelates\u201d with 3 entries:<\/p>\n\n\n\n
-- Create the user \ncreate user STRATOS identified by \"password\"\n  default tablespace USERS\n  temporary tablespace TEMP\n  profile DEFAULT\n  quota unlimited on users;\n-- Grant\/Revoke role privileges \ngrant connect to STRATOS;\ngrant resource to STRATOS;\n\n-- Create table\ncreate table stratos.pelates\n(\n  id    INTEGER,\n  onoma VARCHAR2(10)\n);\n\ninsert into pelates values(1,'Stratos');\ninsert into pelates values(2,'Nikos');\ninsert into pelates values(3,'Giorgos');\n\ncommit;\n<\/pre>\n\n\n\n
By completing the Linked Server we can with the use openquery<\/a> call the Linked Server we created and define the query we want to run to Oracle:<\/p>\n\n\n\n
select * from openquery([oracle server], 'select onoma from pelates');<\/pre>\n\n\n\n
\"\"
07<\/figcaption><\/figure>\n\n\n\n
In the event that the following error appears during execution:<\/p>\n\n\n\n
Msg 7302, Level 16, State 1, Line 1\nCannot create an instance of OLE DB provider \"OraOLEDB.Oracle\" for linked server \"ORACLE SERVER\".\n<\/code><\/pre>\n\n\n\n
Then we execute the following command which will easily fix the problem:<\/p>\n\n\n\n
EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1<\/pre>\n\n\n\n
<\/p>\n\n\n\n
Sources:<\/h2>\n\n\n\n