How To Create a Linked Server To Connect To PostgreSQL From SQL Server

Faruk Erdem

5 years ago

You may want to transfer data to MSSQL from PostgreSQL or send query to PostgreSQL from MSSQL. There are different methods to perform this. One of method is linked server.

First, to create a linked server to connect to PostgreSQL from MSSQL, you need to download the .msi package from the following site.

https://www.pgoledb.com/index.php/component/filecabinet/?view=files&id=1

After downloading and installing from the above link, we can create a linked server by following the steps below. You need to see if you have the required provider in the instance by go to the “Server Objects> Linked Servers> Providers” tab.

Create Linked Server

First, right-click the Linked Server tab under the Server Objects tab and click the New Linked Server.

After you click New Linked Server, you have to fill in the fields on the General tab as I will explain.

Linked server -> Give any name
Server Type -> Chose Other data source.
Provider -> Choose Intellisoft OLE DB Provider for PostgreSQL.
Product Name -> Give any name.
Data Source -> Write PostgreSQL IP
Provider -> SERVER=POSTGRESQL_IP,port;USER ID=POSTGRESQL_USER

Example: SERVER=127.0.5.5,5432;USER ID=postgres

After you fill the necessary fields on the General tab, go to the Security tab.

On the Security tab, we select “Be made using this security context” at the bottom and write the PostgreSQL user name and password. After completing these steps, you will see the Linked Server created under the Linked Server tab.

You can check linked server is created successfully by right-clicking on the created linked server and clicking test connection.

In the Message Box above, you can see that we have successfully connected from MSSQL to PostgreSQL via a linked server.

If you want to see the databases in the PostgreSQL you are connected to, you can see the databases by clicking the Catalog tab in the Linked Server you created. We can query PostgreSQL via the linked server as follows.

SELECT *  FROM [POSTGRESQL].[postgres].[public].[Person]

1	SELECT * FROM [POSTGRESQL].[postgres].[public].[Person]

if you want to connect to SQL Server or Oracle From PostgreSQL you may want to read the below articles.

“Foreing Data Wrapper Between PostgreSQL and MSSQL“,

“Foreign Data Wrapper Between PostgreSQL and Oracle“