PostgreSQL to Microsoft Excel via Npgsql

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.

Requirements

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.

• Client
  • Operating System: Windows 10 Pro
  • Software: Microsoft Office Professional Plus 2019
• Server
  • Operating System: Ubuntu 22.04.3 LTS
  • Software: PostgreSQL 16

Let's start our setup for the Npgsql integration

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:

1. Open up Excel.
2. Go through Data > From Database > From PostgreSQL Database (see image 2).

If everything goes well, Excel will continue and ask for connection details and credentials.

Unsecured Connection

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.

Secured Connection

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:

1. Server
   1. Generate and deploy self-signed certificates on the server.
   2. Adopt the PostgreSQL configuration.
2. Client
   1. Deploy the generated certificate to the trusted root certification authorities on the client.
   2. Change the connection mode to secured.

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:

1. Right-click on root.crt and choose install certificate.
2. Choose store location: User/Local Machine.
3. Manually choose a cert store: trusted root certification authorities.
4. Trust our ca.

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 .

Conclusion to the integration of Npgsql

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.