Recently, one of our clients migrated from an 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 a 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 PostgreSQL.
- Download: https://github.com/npgsql/npgsql/releases/tag/v3.2.7[Note: Latest version(v4+) does not work.] EDIT (Jan 2021): Microsoft has now recommended using v4.0.10. Please check the Official MS blog here.
- Make sure you select the “Entire Feature to be installed on local drive” option while installing the PostgreSQL.
(This is the most important step, else it will not work.)
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 the above AWS key go to this link –> https://www.sslshopper.com/ssl-converter.html
- In the Type of Current Certificatedropdown select Standard PEM
- In the Type To Convert Todropdown select P7B/PKCS#7
- Browse to the certificate you downloaded before in Certificate File to Convert
- Click on Convert Certificate and download it.
(Generate you certificate at sslshopper.com)
Step 2 & 3: Its already Done for you
- I have already done the above two steps for you.
- Download the P7B/PKCS#7certificate 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 >.
(Select Certificates and Click on Add >)
- In the pop-up, select Computer account and click on Next and then Finish
- Press OKto close the Add or Remove Snap-ins popup
(After you are done with the popup, you will get to this screen)
- 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
- Click on Next and then Finish
(Final Certificate Screen)
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 also have to follow these steps on the PC which will host your Data Gateway/Personal Gateway, as refreshes will not work without the installation.
