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.
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.
Another dialog box named PostgreSQL ANSI ODBC Driver Setup opens. In the dialog box, specify the following parameters
- Data source: Specify the desired name of the data source
- 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
- 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
- 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
- Port: Specify the PostgreSQL port that is used by the server to connect to the Azure PostgreSQL server
- 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
- Password: Specify the appropriate password to connect to the PostgreSQL database
The ODBC configuration dialog box looks like the following image:
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.
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.
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.
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.
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.
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.
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.
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.
The data of tblEmployees will be loaded in the excel worksheet, which indicates that the ODBC data source is working correctly.
Summary
In this article, we understood how we could configure the ODBC driver of PostgreSQL to connect to the Azure Database for PostgreSQL.
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
- Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
- Changing the location of FILESTREAM data files in SQL Database - October 14, 2022
- Manage SQL Databases in CentOS: Manage filegroups of user databases - October 5, 2022