{"id":738,"date":"2018-07-12T20:14:36","date_gmt":"2018-07-12T20:14:36","guid":{"rendered":"http:\/\/dbtut.com\/?p=738"},"modified":"2018-11-08T13:11:04","modified_gmt":"2018-11-08T13:11:04","slug":"how-to-create-a-linked-server-to-connect-to-oracle","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2018\/07\/12\/how-to-create-a-linked-server-to-connect-to-oracle\/","title":{"rendered":"How To Create a Linked Server To Connect To Oracle"},"content":{"rendered":"

 <\/p>\n

In some cases, a database that resides on SQL Server may require the data to be retrieved from the Oracle database.<\/p>\n

 <\/p>\n

Requirements<\/h3>\n

First, a related user is created on the oracle side.<\/p>\n

If the Oracle Client did not installed on the server where your SQL Server is located,, the installation is necessary.<\/p>\n

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).<\/p>\n

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.<\/p>\n

 <\/p>\n

Create a Linked Server<\/h3>\n

Then you can do this with the help of the following script.<\/p>\n\n\n\n\n\n\n\n\n
myUser<\/td>\nSql login to use linked server<\/td>\n<\/tr>\n
Xsource<\/td>\nThe name you give to TNS that you have saved in the “Tnsnames.ora” file<\/td>\n<\/tr>\n
XServer<\/td>\nLinked server’s name<\/td>\n<\/tr>\n
oracleuser<\/td>\nThe user created on the Oracle side<\/td>\n<\/tr>\n
oracleuser\u015fifre<\/td>\nThe password of the user created on the Oracle side<\/td>\n<\/tr>\n
serverName\\InstanceName<\/td>\nSQL Intance Name<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
EXEC master.dbo.sp_addlinkedserver @server = 'XServer', @srvproduct = 'Oracle', @provider = 'OraOLEDB.Oracle',\r\n@datasrc = 'Xsource' ,@provstr=N'SERVER=serverName\\InstanceName;User ID=myUser'\r\n\r\nEXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'XServer', @locallogin = 'myUser' , @useself = N'False',\r\n@rmtuser = N'oracleuser', @rmtpassword = N'oracleuser\u015fifre'<\/pre>\n
 <\/p>\n
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.<\/p>\n
It is not appropriate for an MS SQL database expert to know a user password defined on oracle.<\/p>\n
So you can make this operation to the developer that will use this linked server without ever learning this password.<\/p>\n
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<\/a>“.<\/p>\n
USE MASTER\r\nGO\r\nGRANT EXECUTE ON sys.xp_prop_oledb_provider TO myLogin<\/pre>\n
 <\/p>\n
In some cases, you may experience difficulty with linked server after installing Oracle Client.<\/p>\n
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.<\/p>\n\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
  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 …<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[3],"tags":[829,821,831,830,832,835,836,833,834],"class_list":["post-738","post","type-post","status-publish","format-standard","","category-mssql","tag-create-a-linked-server","tag-linked-server","tag-oraoledb","tag-oraoledb-oracle","tag-providers","tag-sp_addlinkedserver","tag-sp_addlinkedsrvlogin","tag-sys-xp_prop_oledb_provider","tag-xp_prop_oledb_provider"],"aioseo_notices":[],"yoast_head":"\nHow To Create a Linked Server To Connect To Oracle - Database Tutorials<\/title>\n<meta name=\"description\" content=\"How To Create a Linked Server To Connect To Oracle\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2018\/07\/12\/how-to-create-a-linked-server-to-connect-to-oracle\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How To Create a Linked Server To Connect To Oracle - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"How To Create a Linked Server To Connect To Oracle\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2018\/07\/12\/how-to-create-a-linked-server-to-connect-to-oracle\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2018-07-12T20:14:36+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-11-08T13:11:04+00:00\" \/>\n<meta name=\"author\" content=\"dbtut\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"dbtut\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/07\/12\/how-to-create-a-linked-server-to-connect-to-oracle\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/07\/12\/how-to-create-a-linked-server-to-connect-to-oracle\/\"},\"author\":{\"name\":\"dbtut\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\"},\"headline\":\"How To Create a Linked Server To Connect To Oracle\",\"datePublished\":\"2018-07-12T20:14:36+00:00\",\"dateModified\":\"2018-11-08T13:11:04+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/07\/12\/how-to-create-a-linked-server-to-connect-to-oracle\/\"},\"wordCount\":349,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"keywords\":[\"Create a Linked Server\",\"linked server\",\"OraOLEDB\",\"OraOLEDB.Oracle\",\"Providers\",\"sp_addlinkedserver\",\"sp_addlinkedsrvlogin\",\"sys.xp_prop_oledb_provider\",\"xp_prop_oledb_provider\"],\"articleSection\":[\"MSSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2018\/07\/12\/how-to-create-a-linked-server-to-connect-to-oracle\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/07\/12\/how-to-create-a-linked-server-to-connect-to-oracle\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2018\/07\/12\/how-to-create-a-linked-server-to-connect-to-oracle\/\",\"name\":\"How To Create a Linked Server To Connect To Oracle - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"datePublished\":\"2018-07-12T20:14:36+00:00\",\"dateModified\":\"2018-11-08T13:11:04+00:00\",\"description\":\"How To Create a Linked Server To Connect To Oracle\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/07\/12\/how-to-create-a-linked-server-to-connect-to-oracle\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2018\/07\/12\/how-to-create-a-linked-server-to-connect-to-oracle\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/07\/12\/how-to-create-a-linked-server-to-connect-to-oracle\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How To Create a Linked Server To Connect To Oracle\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\",\"name\":\"dbtut\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"caption\":\"dbtut\"},\"description\":\"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.\",\"sameAs\":[\"http:\/\/NurullahCAKIR\"],\"url\":\"https:\/\/dbtut.com\/index.php\/author\/dbtut\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How To Create a Linked Server To Connect To Oracle - Database Tutorials","description":"How To Create a Linked Server To Connect To Oracle","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/dbtut.com\/index.php\/2018\/07\/12\/how-to-create-a-linked-server-to-connect-to-oracle\/","og_locale":"en_US","og_type":"article","og_title":"How To Create a Linked Server To Connect To Oracle - Database Tutorials","og_description":"How To Create a Linked Server To Connect To Oracle","og_url":"https:\/\/dbtut.com\/index.php\/2018\/07\/12\/how-to-create-a-linked-server-to-connect-to-oracle\/","og_site_name":"Database Tutorials","article_published_time":"2018-07-12T20:14:36+00:00","article_modified_time":"2018-11-08T13:11:04+00:00","author":"dbtut","twitter_card":"summary_large_image","twitter_misc":{"Written by":"dbtut","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2018\/07\/12\/how-to-create-a-linked-server-to-connect-to-oracle\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/07\/12\/how-to-create-a-linked-server-to-connect-to-oracle\/"},"author":{"name":"dbtut","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408"},"headline":"How To Create a Linked Server To Connect To Oracle","datePublished":"2018-07-12T20:14:36+00:00","dateModified":"2018-11-08T13:11:04+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/07\/12\/how-to-create-a-linked-server-to-connect-to-oracle\/"},"wordCount":349,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"keywords":["Create a Linked Server","linked server","OraOLEDB","OraOLEDB.Oracle","Providers","sp_addlinkedserver","sp_addlinkedsrvlogin","sys.xp_prop_oledb_provider","xp_prop_oledb_provider"],"articleSection":["MSSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2018\/07\/12\/how-to-create-a-linked-server-to-connect-to-oracle\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2018\/07\/12\/how-to-create-a-linked-server-to-connect-to-oracle\/","url":"https:\/\/dbtut.com\/index.php\/2018\/07\/12\/how-to-create-a-linked-server-to-connect-to-oracle\/","name":"How To Create a Linked Server To Connect To Oracle - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"datePublished":"2018-07-12T20:14:36+00:00","dateModified":"2018-11-08T13:11:04+00:00","description":"How To Create a Linked Server To Connect To Oracle","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/07\/12\/how-to-create-a-linked-server-to-connect-to-oracle\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2018\/07\/12\/how-to-create-a-linked-server-to-connect-to-oracle\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2018\/07\/12\/how-to-create-a-linked-server-to-connect-to-oracle\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"How To Create a Linked Server To Connect To Oracle"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408","name":"dbtut","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","caption":"dbtut"},"description":"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.","sameAs":["http:\/\/NurullahCAKIR"],"url":"https:\/\/dbtut.com\/index.php\/author\/dbtut\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/738","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=738"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/738\/revisions"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=738"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=738"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=738"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}