Nisarg Upadhyay
General Page in New Linked Server

Configure a Linked Server between SQL Server and PostgreSQL using ODBC drivers

December 6, 2021 by

This article explains configuring a linked server between SQL Server 2019 and PostgreSQL. In my previous article named Configure ODBC drivers for PostgreSQL, we got introduced to the ODBC driver of PostgreSQL. We also learned how we could download, install, and configure it. In this article, we will explore how we can utilize the ODBC driver to access the data of the PostgreSQL from SQL Server 2019.

Environment Setup

For demonstration, I have installed and configured an instance of SQL Server 2019 and PostgreSQL 13 on my workstation. I have created a database named DVDShop on PostgreSQL 13. In the previous article, I have configured the ODBC driver that points to the DemoDatabase of PostgreSQL. In this article, we are going to connect to the DVDShop database, so we must change the database name in the pSQLODBC driver. To do that, Open Control Panel 🡪 Open Administrative tools 🡪 Open ODBC Data Source (64 bit) 🡪 open System DSN tab 🡪 select pgadmin13 and click on Configure.

ODBC data source administrator

The dialog box named PostgreSQL Unicode ODBC Driver (pSQLODBC) Setup opens. We have already specified all required parameters, so we are changing the name of the database in the Database text box only. We are connecting to the DVDShop database so enter DVDShop in the Database textbox.

PSQLODBC seetup

To test the connectivity, click on Test. The output of the Test connection command is as following.

Connection to new database successful

As you can see, the connection was established successfully. Click on Save to update the changes in System DSN and close the dialog box. Now let us create the linked server in SQL Server 2019.

Create Linked Server

We will create a linked server using SQL Server management studio (SSMS). Open SQL Server management studio 🡪 Connect to SQL Server instance 🡪 In object explorer, expand Server Objects 🡪 Right-click on Linked Server and select New Linked Server.

New linked seerver

A dialog box New Linked Server opens. To configure the linked server, specify the following details on the General page.

  1. Linked Server: Enter the desired name of the linked Server. This name will be used by the queries to populate the data from PostgreSQL. In our demo, the name is DVDShop
  2. Server Type: Specify the server type. We are using ODBC driver to connect to a data source, therefore select Other Data source
  3. Provider: Specify the name of the provider. We are using Microsoft OLEDB Provider for ODBC Driver. So select it from the drop-down box
  4. Product Name: Specify the product name. Specify any relevant name. In our demo, the name is PostgreSQL DB
  5. Data Source: Specify the data source name that you have specified in the pSQLODBC driver setting. We have created an ODBC data source named pgadmin13, so specify pgadmin13 in it
  6. Provide string: If you have not configured the data source, you can specify the entire provider string. We have created a data source, so we are not entering the provider string
  7. Catalog: Specify the database name. It is optional, so we are not entering the database name

Following is the screenshot of the general page of the New Linked Server dialog box.

General Page in New Linked Server

To connect to the PostgreSQL database, we must provide the login details. So, open the security page in the New Linked Server dialog box. We are using the PostgreSQL credential to connect to the PostgreSQL database, so select Be made using this security context option and specify the username and password of the Postgres user.

Security Page in New Linked Server

We are not changing the server options, so click OK to create a linked server named DVDShop and close the dialog box.

Test the connectivity of Linked Server

Now, let us test whether we can connect to PostgreSQL or not. To do that, right-click on DVDSHOP and select Test Connection.

Test connection to linked server

The output of the Test connection command:

Connection to linked server is successful

As you can see, the connection to the linked server succeeded.

View the objects of the DVDShop database

We can view the object created in the DVDShop database using DVDSHOP linked server. To do that, expand DVDSHOP 🡪 expand Catalogs.

Expand DVDSHOP linked server

Under Catalogs, you can view only DVDShop database because we have specified it in the ODBC data source configuration.

  • Note: If you do not specify the database, you won’t see any database of PostgreSQL under catalog

Now, Expand DVDShop database 🡪 Expand Tables. Under tables, you can view the list of tables that have been created in DVDShop database.

Expand Tables

Under Views, you can see the list of views that have been created in DVDShop database.

Expand Views

Access the data of DVDShop database using Linked Server

First, let us run the SELECT statement; we want to populate the data from the public.address table. Run the following query:

 

Output of address table

Summary

In this article, we learned how we can utilize the PostgreSQL ODBC Driver to create a linked server. We have learned the step-by-step configuration process of creating a linked server between SQL Server 2019 and PostgreSQL 13.

Nisarg Upadhyay
788 Views