Nisarg Upadhyay
Data has been imported

Configure ODBC drivers for Azure Database for PostgreSQL

January 24, 2022 by

This article helps you learn how to configure an ODBC driver to connect the Azure Database for PostgreSQL. In my previous article, Configure ODBC drivers for PostgreSQL, you learned the step-by-step process to download, install and configure the ODBC driver for PostgreSQL. We also learned the process of creating a DSN used to connect PostgreSQL and populate data from it.

Environment Setup

For demonstration, I have created an Azure Database for PostgreSQL on my Azure Portal. You can read the article Getting started with Azure Database for PostgreSQL to learn the step-by-step deployment process of configuring the Azure Database for PostgreSQL. I have installed and configured the PostgreSQL ODBC driver named psqlODBC in my workstation. I have created a database named EltechHR on my Azure PostgreSQL Server. The database has a table name tblEmployees. I have added a few dummy records.

Now, let us create a data source using ODBC Data Source Administrator.

Create Data Source Name (DSN)

First, let us create a data source name using Microsoft ODBC Data Source 64-bit. Launch ODBC Data source 64-bit 🡪 Click on System DSN 🡪 Click Add.

System DSN

A dialog box named Crete New Data Source opens. In the dialog box, you can see the list of the drivers installed on the computer. Select PostgreSQL ANSI from the list and click on Finish.

Create New Data source

Another dialog box named PostgreSQL ANSI ODBC Driver Setup opens. In the dialog box, specify the following parameters

  1. Data source: Specify the desired name of the data source
  2. Database: Specify the appropriate database name. I have created a database named postgre*****test in Azure Database for PostgreSQL, so I have entered postgre*****test in database textbox
  3. SSL Mode: If you are using the SSL to securely authenticate your server, you can select the appropriate option from the SSL Mode drop-down box. In our demo, I am not using SSL, so I have selected Disabled
  4. Server: Specify the name of the server. You can view the server name on the Azure resource page. In our demo, the server name is postgres******.postgres.database.azure.com
  5. Port: Specify the PostgreSQL port that is used by the server to connect to the Azure PostgreSQL server
  6. Username: Specify the administrator username that is used to connect to the Azure Database for PostgreSQL. The username must be in <username>@<databasename>. In our demo, the username is nisarg******@postgresqlodbctest
  7. Password: Specify the appropriate password to connect to the PostgreSQL database

The ODBC configuration dialog box looks like the following image:

Configure psql ODBC driver

To connect to the Azure PostgreSQL database, we must allow the IP address to connect to the. To do that, click on the Connection Security link from a left pan of the Azure Database for the PostgreSQL resource page.

Connection Security

In the connection security webpage, under the Firewall Rules section, click on Add Current client IP Address and specify the appropriate name of the firewall rule. We are not using the SSL, so disable the SSL connection by clicking the Enforce SSL connection button.

Add firewall rules

Save the firewall rules. Once rules are applied, let us test the connectivity. To do that, click on the Test button on the ODBC configuration dialog box.

Connection Successful

As you can see in the above image, the connection has been established successfully. Back to the ODBC Data source administrator screen, you can see that the AzurePostgreSQL data source has been created.

PostgreSQL DSN created

Now, let us consume the DBC DSN to access the data from the Azure Database for PostgreSQL.

Access data from Azure Database for PostgreSQL

In this demonstration, I will show how we can directly access the PostgreSQL Azure database from Microsoft Excel. First, open the excel file 🡪 Click on Data 🡪 Click on Get Data. In the pan, you can view the various data sources that can be integrated with the excel file and can access data via excel files. The ODBC data source will be in the Other Sources section. Hover on From Other Source 🡪 Select From ODBC.

Select ODBC from excel data source

A dialog box From ODBC opens. The list of configured DSNs is listed in the Data source name (DSN) drop-down box. Select AzurePostgreSQL and Click OK.

Select ODBC DSN

A dialog box ODBC driver opens. You must specify the username and password of the user which is used to connect to the data source. Click Connect.

Add user name and password

In the navigator dialog box, you can view the database named created in the Azure PostgreSQL. In our case, you can view the EltechHR database.

In PostgreSQL, the user tables are created in the public schema. The tables Expand EltechHR 🡪 Expand Public 🡪 Click on tblEmployees. When you select the tblEmployees table, you can view the preview of the data of the tblEmployees table. Click on Load.

Data Preview

The data of tblEmployees will be loaded in the excel worksheet, which indicates that the ODBC data source is working correctly.

Data has been imported

Summary

In this article, we understood how we could configure the ODBC driver of PostgreSQL to connect to the Azure Database for PostgreSQL.

Nisarg Upadhyay
Latest posts by Nisarg Upadhyay (see all)
Azure, ETL, PostgreSQL

About Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

490 Views