This article is dedicated to all the Excel heroes out there who want to integrate and use data stored in PostgreSQL. This time we’ll cover all the steps needed to do so by utilizing Npgsql, which is an open source ADO.NET Data Provider for PostgreSQL. To learn more about this versatile driver, check out its main project page or directly jump into the code base.
Table of Contents
Let me begin with the needed requirements to follow up on this quick tutorial accordingly. Apart from an accessible PostgreSQL server, an appropriate Excel version or license is required to connect to PostgreSQL via Npgsql and not through the common ODBC protocol. To verify your license, open Excel and go through the following menu entries: Data – Get Data – From Database. The resulting container should include an entry for PostgreSQL. Check out figure 1 to see how this looks in my environment.
What else is needed to continue? Please find below a summary of my environment.
To use Npgsql as a database provider for Microsoft Excel, the driver must be installed in the Global Assembly Cache first. To make this task as smooth as possible, we’ll pick one of the available but older MSI installers of Npgsql to outsource this job.
First, we download the driver from https://github.com/npgsql/npgsql/releases/download/v4.0.10/Npgsql-4.0.10.msi and install the package on Windows. The driver should now be available for Excel too. Let’s verify this:
If everything goes well, Excel will continue and ask for connection details and credentials.
Let’s start easy and connect from Excel to PostgreSQL without using SSL. I recommend testing the unsecured connection via psql first before moving on to Excel. To do so, we hand over the parameter sslmode=disabled on client the side as follows:
Great – this works out. In Excel, we step in, enter connection details and credentials, and finally try to connect. The upcoming image series documents these steps briefly:
Uuups – this does not seem to work. The error message suggests that Excel connects to PostgreSQL with SSLMODE enabled by default. Any chance to change this parameter? We try our luck in Excel’s Data Source Settings (Data Data Source Settings). From here on, we can disable the SSLMODE of our connection by unchecking Encrypt connections. Image 4 visualizes this task in detail.
Our next connection attempt (see image 5) succeeds and brings up Excel’s navigator pane designed for further data processing. Mission accomplished.
Let’s dig deeper and improve our setup by securing connections between Microsoft Excel and PostgreSQL. I expect some basic knowledge regarding PostgreSQL’s SSL capabilities.
To refresh your knowledge, I highly recommend PostgreSQL’s own documentation and a blog post from my boss, Hans Schönig.
To keep it simple, we will generate a self-signed certificate on the server and use it as a trusted root certificate on the client.
The overall process consists of the following steps:
Firstly, we generate our self-signed certificate and store it in PostgreSQL’s data directory, where the system will look after it by default. We generate the certificate and key as user postgres or use chown to change ownership accordingly.
Next, we adopt our PostgreSQL configuration in postgresql.conf, point to our files, and finally restart the database server.
Before deploying our certificate to Windows, let's quickly verify its correctness by connecting via psql.
To use our server certificate as a trusted root certificate, we copy and rename server.crt as root.crt to directory .postgresql, where psql will look after certificates by default.
Finally, we test the connection.
Amazing! The output confirms that we are connecting with SSL mode enabled. By renaming ~/.postgresql/root.crt we can cross-check our configuration.
We are still not tired and copied our certificate root.crt to Windows. The certificate can be added to the trusted root certification store as follows:
The upcoming image series documents this task.
Now it’s time to revert our PostgreSQL connection settings in Excel and enable “Encrypt connection” again. That’s it. One more time, we open Excel’s data navigator (see chapter on unsecured connection) to verify that everything works as expected.
Still not enough? As homework, force your clients to use SSL by adopting pg_hba .
Today we connected Microsoft Excel to PostgreSQL via Npgsql. This task sounds easy in the first run, but there are some stumbling blocks you might encounter. This post guides you step-by-step through this process and subsequently enables you to concentrate on your Excel magic instead of dealing with frustrating connectivity issues.
Leave a Reply