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.

I recently had to migrate some data from SQL Server to Oracle 26ai Free edition. I thought I’d see if a linked server would help as it’s often the easiest way to do this, if it works…

That would let me just write INSERT SELECT statements, but SQL Server linked servers to Oracle are known for being a little fiddly and often have issues with some data types, time-related settings, etc.

One thing I hadn’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.

What is Oracle 26ai Free?

For SQL Server readers, Oracle 26ai Free is basically the current equivalent to SQL Server Express edition. There was an older Oracle Express edition but that’s no longer the one to use.

This latest edition has a few quirks though. When you install it, you get a service named FREE, and you get SYS and SYSTEM users. There is one CDB (Container Database), and one PDB (Pluggable Database) named FREEPDB1.

If you have an application that runs against these servers, you have one of two main choices:

In my case, I was using the first method. It’s 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’t a simple backup/recovery SQL command like there is in T-SQL, but you can use the expdb utility to export a schema, and the impdb utility to import the schema again.

Installing the OraOLEDB.Oracle Provider

Before you can create the linked server, you need the OLE-DB provider. If you’ve installed Oracle on the same system as the SQL Server system, you’ll already have this. Otherwise, you’ll need to install it.

The provider you’ll need is the OraOLEDB.Oracle provider. There was an older Microsoft provider for Oracle but you shouldn’t use that now:

Configuring tnsnames.ora for FREEPDB1

There are two files involved with the database engine listener, and with resolving the location of the listener. The file listener.ora defines the port, address, and service that the listener is associated with.

When SQL Server attempts to connect though, it needs to be able to resolve the service name by using a file called tnsnames.ora. 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

The name FREE was already defined in there but I needed to add the following to the file:

That allowed the name FREEPDB1 to also be resolvable. There might be another way to change this file, but that worked fine for me.

Step-by-Step Linked Server Setup in SQL Server

Linked Server Security and Authentication Settings

On the General tab of the New Linked Server dialog, I entered ORA26AIFREE as the name of the linked server – however, the name you use is irrelevant. I choose the Oracle Provider for OLEDB that I mentioned before. For the Product name, Oracle 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 FREEPDB1:

On the Security tab of the dialog, I needed to use a fixed security context. I had no option to flow a context across. So, for Remote login, I entered ppk. That’s the name of the user that the application connects as, and the name of the schema that I was using. I then entered the password:

Server Options: RPC, RPC Out, and Distributed Transactions

On the Server Options tab, I configured RPC and RPC Out as True and I also set the option to Enable Promotion of Distributed Transactions to False. The latter is known to cause issues for many people.

Success!

And at that point, it all worked fine as shown. I hope that helps someone else.