Recently, one of our client migrated from On-Premise SQL Server to AWS RDS PostgreSQL. With this came the task of pointing our Power BI dashboards to PostgreSQL. What we thought was a 2 line change in Advanced Queries, ended up being a long list of tasks(It was still a 2 line change once the below tasks were done
We did find multiple articles on the Power BI community to help us around it, but found few of the links broken. So finally, I decided to write an article with all the steps we undertook. This article will help you install all the dependencies needed to successfully connect to AWS RDS PostgreSQL from Power BI.
Step 1: Install Npgsql which allows .NET access to PostgresSQL.
- Download: https://github.com/npgsql/npgsql/releases/tag/v3.2.7 [Note: Latest version(v4+) does not work.]
- Make sure you select “Entire Feature to be installed on local drive” option while installing the PostgreSQL.
Step 2: Download the AWS public key
- Download AWS key from here –> https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem
Step 3: Convert the key to certificate
- To convert above AWS key go to this link –> https://www.sslshopper.com/ssl-converter.html
- In the Type of Current Certificate dropdown select Standard PEM
- In the Type To Convert To dropdown select P7B/PKCS#7
- Browse to the certificate you downloaded before in Certificate File to Convert
- Click on Convert Certificate and download it.
Step 2 & 3: Its already Done for you
- I have already done the above two steps for you.
- Download the P7B/PKCS#7 certificate directly here –> https://saurabha.com/pbirepo/aws-rds-cert.p7b
Step 4: Apply certificate to your Laptop
- Note: You will not be able to connect to RDS unless you apply the certificate.
- We will now use Microsoft Management Console (MMC) to import our certificate into the Windows Certificate Store.
- Click on Start Menu and search mmc, click on it to open
- In MMC, from the menu item, click on File followed by Add/Remove Snap-in…
- Select Certificates from the list of Available snap-ins and click Add >.
- In the pop-up, select Computer account and click on Next and then Finish
- Press OK to close the Add or Remove Snap-ins popup
- Right click on the Trusted Root Certification Authorities folder and select All Tasks then click on Import… to open up the Certificate Import Wizard.
- Click on Next, browse for the certificate file you downloaded (aws-rds-cert.p7b). It will probably be located in your Downloads folders.
- Click on Next and then Finish
Congratulations !!! You have successfully added the AWS RDS certificate to your PC. You can now access AWS RDS PostgreSQL from Microsoft Power BI.
Important Note: You will have to follow these steps on the PC which will host your Data Gateway/Personal Gateway, as refreshes will not work without the installation.