Setting Up TLS Between Power BI Report Server and PostgreSQL Using Npgsql

[valeriospennato]

Hello everyone,

I'm working on establishing a secure TLS connection between Power BI Report Server and a PostgreSQL database using Npgsql. I've installed Npgsql, but I'm uncertain if additional configurations are necessary to ensure a secure connection. Could anyone provide a step-by-step guide or share their experiences on setting up TLS certificates in this scenario? Any advice would be greatly appreciated.

Thank you in advance!

[Shruti_D]

Hi @valeriospennato ,
Hi @mch98 ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[Shruti_D]

Hi @valeriospennato ,
Following up to check whether you got a chance to review the suggestions given. If the issue still persists please let us know.
If our response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it.
Thank you

[v-csrikanth]

Hi @valeriospennato

You can use the above mentioned steps to set up a secure TLS connection between Power BI Report Server and PostgreSQL using Npgsql.

In addition to @AmiraBedh,

If you want to enforce client-side SSL verification:

You can use-

***********************************
ssl_verify_client = require

***********************************

If you don’t have valid SSL certificates for your PostgreSQL server, you can generate self-signed certificates.

To generate a self-signed certificate and private key (for testing purposes):

***********************************
# Create a private key
openssl genpkey -algorithm RSA -out server.key

# Create a self-signed certificate
openssl req -new -x509 -key server.key -out server.crt -days 365

***********************************

Ensure the SSL certificates are valid, properly formatted, and correctly located in the postgresql.conf file.

By following these steps, you should be able to set up a secure TLS connection between Power BI Report Server and PostgreSQL using Npgsql.

Hope this helps!

The ones that are in bold are commands and it world be better if they are screenshots.

I couldnt edit it in word thats why used notepad.

But you can use these in place of them-

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

[AmiraBedh]

Hi Valerio, great question !
Prerequisites :
- Power BI Report Server installed and configured
- PostgreSQL server with TLS (SSL) enabled
- Npgsql ADO.NET provider installed (should be version-compatible with Report Server and PostgreSQL)
- TLS certificate(s) on the PostgreSQL server
- Access to the client machine where Power BI Report Server is running

On your PostgreSQL server, ensure that TLS is enabled. This is typically done in the postgresql.conf and pg_hba.conf files:

**postgresql.conf**
conf
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt' -- (optional but recommended for client-side validation)


**pg_hba.conf**
conf
hostssl all all 0.0.0.0/0 cert

Then, restart PostgreSQL.

Then you need to verify taht the PostgreSQL certificates (server.crt, server.key, and optionally root.crt) are:
- In the PostgreSQL data directory (or specify absolute paths)
- server.key must be 600 permission (chmod 600 server.key)

In Power BI Report Server, when setting up a data source with Npgsql, use a connection string like:

Host=your_postgres_server;Port=5432;Username=your_user;Password=your_password;Database=your_db;SSL Mode=Require;Trust Server Certificate=false;Root Certificate=c:\\certs\\root.crt