{"id":108157,"date":"2026-01-05T15:04:00","date_gmt":"2026-01-05T15:04:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=108157"},"modified":"2026-01-15T12:50:05","modified_gmt":"2026-01-15T12:50:05","slug":"how-to-create-a-sql-server-linked-server-to-oracle-26ai-free","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/how-to-create-a-sql-server-linked-server-to-oracle-26ai-free\/","title":{"rendered":"How to Create a SQL Server Linked Server to Oracle 26ai Free"},"content":{"rendered":"\n<p><strong>Easily move data from SQL Server to Oracle 26ai Free with this step-by-step guide. Learn how to set up a linked server, configure FREEPDB1 and avoid common issues.<\/strong><\/p>\n\n\n\n<p>I recently had to migrate some data from <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/\" target=\"_blank\" rel=\"noreferrer noopener\">SQL Server<\/a> to <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/introduction-to-oracle-database-for-database-professionals\/\" target=\"_blank\" rel=\"noreferrer noopener\">Oracle<\/a> 26ai Free edition. I thought I\u2019d see if a <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/linked-servers\/linked-servers-database-engine?view=sql-server-ver17\" target=\"_blank\" rel=\"noreferrer noopener\">linked server<\/a> would help as it\u2019s often the easiest way to do this, if it works\u2026<\/p>\n\n\n\n<p>That would let me just write <code>INSERT SELECT<\/code> statements, but SQL Server linked servers to Oracle are known for being a <a href=\"https:\/\/learn.microsoft.com\/en-us\/previous-versions\/troubleshoot\/sql\/linked-servers\/set-up-troubleshoot-linked-server\" target=\"_blank\" rel=\"noreferrer noopener\">little fiddly<\/a> and often have issues with some data types, time-related settings, etc. <\/p>\n\n\n\n<p>One thing I hadn&#8217;t needed to do in the past was to create a linked server to Oracle 26ai Free, so I thought I should document how I did this so I can find it easily in the future, and it might help someone else.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-what-is-oracle-26ai-free\">What is Oracle 26ai Free?<\/h2>\n\n\n\n<p>For SQL Server readers, Oracle 26ai Free is basically the current equivalent to <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/edition-sql-server-best-development-work\/#10:~:text=editions%20are%20there%3F-,Express%20Edition,-The%20Express%20edition\" target=\"_blank\" rel=\"noreferrer noopener\">SQL Server Express<\/a> edition. There was an older Oracle Express edition but that\u2019s no longer the one to use.<\/p>\n\n\n\n<p>This latest edition has a few quirks though. When you install it, you get a service named <code>FREE<\/code>, and you get <code>SYS<\/code> and <code>SYSTEM<\/code> users. There is one <a href=\"https:\/\/www.oracle.com\/uk\/database\/container-database\/\" target=\"_blank\" rel=\"noreferrer noopener\">CDB (Container Database)<\/a>, and one <a href=\"https:\/\/docs.oracle.com\/en\/cloud\/paas\/base-database\/about-pdb\/\" target=\"_blank\" rel=\"noreferrer noopener\">PDB (Pluggable Database)<\/a> named <code>FREEPDB1<\/code>.<\/p>\n\n\n\n<p>If you have an application that runs against these servers, you have one of two main choices:<\/p>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li>Create a schema for your application in <code>FREEPDB1<\/code> and put all your code and data there.<br><br><\/li>\n\n\n\n<li>Create another pluggable database.<\/li>\n<\/ul>\n<\/div>\n\n\n<p>In my case, I was using the first method. It\u2019s much easier to move schemas and their contents around on these Oracle systems than it is to move around what SQL Server people think of as databases. There isn\u2019t a simple <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/sql-server-backup-types\/\" target=\"_blank\" rel=\"noreferrer noopener\">backup\/recovery SQL command<\/a> like there is in T-SQL, but you can use the <code>expdb<\/code> utility to export a schema, and the <code>impdb<\/code> utility to import the schema again.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-installing-the-oraoledb-oracle-provider\">Installing the OraOLEDB.Oracle Provider<\/h2>\n\n\n\n<p>Before you can create the linked server, you need the OLE-DB provider. If you\u2019ve installed Oracle on the same system as the SQL Server system, you\u2019ll already have this. Otherwise, you\u2019ll need to install it.<\/p>\n\n\n\n<p>The provider you\u2019ll need is the <a href=\"https:\/\/www.oracle.com\/europe\/database\/technologies\/provider-ole-db.html\" target=\"_blank\" rel=\"noreferrer noopener\">OraOLEDB.Oracle provider<\/a>. There was an older Microsoft provider for Oracle but you shouldn\u2019t use that now:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"497\" height=\"405\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/12\/image-35.png\" alt=\"An image showing the provider you'll need: the OraOLEDB.Oracle provider.\" class=\"wp-image-108158\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/12\/image-35.png 497w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/12\/image-35-300x244.png 300w\" sizes=\"auto, (max-width: 497px) 100vw, 497px\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-configuring-tnsnames-ora-for-freepdb1\">Configuring tnsnames.ora for FREEPDB1<\/h3>\n\n\n\n<p>There are two files involved with the database engine listener, and with resolving the location of the listener. The file<strong> <\/strong><em>listener.ora<\/em> defines the port, address, and service that the listener is associated with.<\/p>\n\n\n\n<p>When SQL Server attempts to connect though, it needs to be able to resolve the service name by using a file called&nbsp;<em>tnsnames.ora<\/em>. It is generated by the Oracle configuration tools and its location depends upon where you installed Oracle. On my system, that was here: C:\\Oracle\\dbhomeFree\\NETWORK\\ADMIN\\tnsnames.ora<\/p>\n\n\n\n<p>The name <code>FREE<\/code> was already defined in there but I needed to add the following to the file:<\/p>\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \">FREEPDB1 =\n  (DESCRIPTION =\n    (ADDRESS = (PROTOCOL = TCP)(HOST = host.docker.internal)(PORT = 1521))\n    (CONNECT_DATA =\n      (SERVER = DEDICATED)\n      (SERVICE_NAME = FREEPDB1)\n    )\n  )<\/pre><\/div>\n\n\n\n<p>That allowed the name <code>FREEPDB1<\/code> to also be resolvable. There might be another way to change this file, but that worked fine for me.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-step-by-step-linked-server-setup-in-sql-server\">Step-by-Step Linked Server Setup in SQL Server<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-linked-server-security-and-authentication-settings\">Linked Server Security and Authentication Settings<\/h3>\n\n\n\n<p>On the&nbsp;<em>General<\/em>&nbsp;tab of the&nbsp;<em>New Linked Server<\/em>&nbsp;dialog, I entered&nbsp;<code>ORA26AIFREE<\/code>&nbsp;as the name of the linked server &#8211; however, the name you use is irrelevant. I choose the&nbsp;Oracle Provider for OLEDB&nbsp;that I mentioned before. For the Product name,&nbsp;<code>Oracle<\/code>&nbsp;is the appropriate value. And for the Data source, you need to put the PDB that you are connecting to. In my case, that was the default user one called&nbsp;<code>FREEPDB1<\/code>:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"851\" height=\"500\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/12\/image-36.png\" alt=\"An image showing ORA26AIFREE as the name of the linked server.\" class=\"wp-image-108159\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/12\/image-36.png 851w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/12\/image-36-300x176.png 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/12\/image-36-768x451.png 768w\" sizes=\"auto, (max-width: 851px) 100vw, 851px\" \/><\/figure>\n\n\n\n<p>On the&nbsp;<em>Security<\/em>&nbsp;tab of the dialog, I needed to use a fixed security context. I had no option to flow a context across. So, for <em>Remote login<\/em>, I entered&nbsp;<code>ppk<\/code>. That\u2019s the name of the user that the application connects as, and the name of the <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/security\/authentication-access\/create-a-database-schema?view=sql-server-ver17\" target=\"_blank\" rel=\"noreferrer noopener\">schema<\/a> that I was using. I then entered the password:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"839\" height=\"391\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/12\/image-37.png\" alt=\"An image showing 'ppk' being entered for the Remote login, and a password.\" class=\"wp-image-108160\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/12\/image-37.png 839w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/12\/image-37-300x140.png 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/12\/image-37-768x358.png 768w\" sizes=\"auto, (max-width: 839px) 100vw, 839px\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-server-options-rpc-rpc-out-and-distributed-transactions\">Server Options: RPC, RPC Out, and Distributed Transactions<\/h3>\n\n\n\n<p>On the&nbsp;<em>Server Options<\/em>&nbsp;tab, I configured RPC and RPC Out as&nbsp;<em>True<\/em>&nbsp;and I also set the option to Enable Promotion of Distributed Transactions to&nbsp;<em>False<\/em>. The latter is known to <a href=\"https:\/\/www.sqlservercentral.com\/forums\/topic\/enable-promotion-of-distributed-transactions-for-rpc-should-be-set-to-true-or-false\" target=\"_blank\" rel=\"noreferrer noopener\">cause issues<\/a> for many people.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"834\" height=\"386\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/12\/image-38.png\" alt=\"An image showing the Server Options tab with RPC and RPC Out being configured as 'True', and Enable Promotion of Distributed Transactions being configured as 'False'.\" class=\"wp-image-108161\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/12\/image-38.png 834w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/12\/image-38-300x139.png 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/12\/image-38-768x355.png 768w\" sizes=\"auto, (max-width: 834px) 100vw, 834px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-success\">Success!<\/h2>\n\n\n\n<p>And at that point, it all worked fine as shown. I hope that helps someone else.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"940\" height=\"474\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/12\/image-39.png\" alt=\"An image showing the successful outcome.\" class=\"wp-image-108162\" srcset=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/12\/image-39.png 940w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/12\/image-39-300x151.png 300w, https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2025\/12\/image-39-768x387.png 768w\" sizes=\"auto, (max-width: 940px) 100vw, 940px\" \/><\/figure>\n\n\n\n<section id=\"faq\" class=\"faq-block my-5xl\">\n    <h2>FAQs: How to Create a SQL Server Linked Server to Oracle 26ai Free<\/h2>\n\n                        <h3 class=\"mt-4xl\">1. What is Oracle 26ai Free Edition?<\/h3>\n            <div class=\"faq-answer\">\n                <p data-start=\"259\" data-end=\"415\">Oracle 26ai Free Edition is Oracle\u2019s current free database offering, similar in purpose to SQL Server Express. It replaces the older Oracle Express Edition.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">2. Can SQL Server connect to Oracle 26ai Free using a linked server?<\/h3>\n            <div class=\"faq-answer\">\n                <p data-start=\"492\" data-end=\"643\">Yes. SQL Server can connect to Oracle 26ai Free using a linked server, although Oracle linked servers can be finicky and require careful configuration.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">3. Why use a linked server for SQL Server to Oracle migration?<\/h3>\n            <div class=\"faq-answer\">\n                <p data-start=\"714\" data-end=\"848\">A linked server allows you to migrate data using simple <code data-start=\"770\" data-end=\"785\">INSERT SELECT<\/code> statements, which is often the easiest approach when it works.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">4. Which Oracle provider should be used?<\/h3>\n            <div class=\"faq-answer\">\n                <p data-start=\"897\" data-end=\"999\">You should use the OraOLEDB.Oracle provider. The old Microsoft Oracle provider should not be used.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">5. What is FREEPDB1?<\/h3>\n            <div class=\"faq-answer\">\n                <p data-start=\"1028\" data-end=\"1199\"><code>FREEPDB1<\/code> is the default pluggable database (PDB) created when Oracle 26ai Free is installed. Applications typically connect to this PDB rather than the container database.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">6. Should I use a schema or create a new pluggable database?<\/h3>\n            <div class=\"faq-answer\">\n                <p data-start=\"1268\" data-end=\"1396\">The simplest option is to create a schema in <code>FREEPDB1<\/code>. Schemas can be easily moved using Oracle Data Pump (<code data-start=\"1375\" data-end=\"1382\">expdp<\/code> and <code data-start=\"1387\" data-end=\"1394\">impdp<\/code>).<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">7. Why is editing tnsnames.ora required?<\/h3>\n            <div class=\"faq-answer\">\n                <p data-start=\"1445\" data-end=\"1588\">SQL Server uses <code data-start=\"1461\" data-end=\"1475\">tnsnames.ora<\/code> to resolve Oracle service names. Adding <code>FREEPDB1<\/code> allows SQL Server to connect to the correct pluggable database.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">8. What should be used as the linked server data source?<\/h3>\n            <div class=\"faq-answer\">\n                <p data-start=\"1822\" data-end=\"1903\">The data source should be the Oracle service name of the PDB, such as <code data-start=\"1892\" data-end=\"1902\">FREEPDB1<\/code>.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">9. Why is a fixed security context required?<\/h3>\n            <div class=\"faq-answer\">\n                <p data-start=\"1956\" data-end=\"2075\">Oracle linked servers do not support flowing Windows credentials, so a fixed Oracle username and password must be used.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">10. Which linked server options matter?<\/h3>\n            <div class=\"faq-answer\">\n                <p data-start=\"1461\" data-end=\"1535\">Enable RPC and RPC Out, and disable promotion of distributed transactions.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"<p>Easily move data from SQL Server to Oracle 26ai Free with this step-by-step guide. Learn how to set up a linked server, configure FREEPDB1 and avoid common issues.<br \/>\n&hellip;<\/p>\n","protected":false},"author":346483,"featured_media":108164,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143523,143533,143524],"tags":[4459,4150,4151],"coauthors":[159368],"class_list":["post-108157","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-databases","category-oracle-databases","category-sql-server","tag-oracle","tag-sql","tag-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/108157","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/346483"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=108157"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/108157\/revisions"}],"predecessor-version":[{"id":108220,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/108157\/revisions\/108220"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/108164"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=108157"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=108157"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=108157"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=108157"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}