How to Securely Connect Power BI to AWS SQL Server Database from Outside the Network

[manoj_0911]

Hi everyone,

I need help setting up secure external access for Power BI Desktop to connect to a SQL Server database hosted on AWS. Here’s the current setup:

1. I connect to a VPN to join my company's network from home.
2. I access a (77 box) within the company network.
3. From there, I connect to another jump server (90 box).
4. Inside the 90 box, there are two more servers:
- **27 box**: Hosts Power BI Desktop and Power BI Data Gateway.
- **92 box**: Hosts the SQL Server database (private network on AWS).
5. I use Power BI Desktop on the 27 box to connect to the SQL Server database on the 92 box using the import method.
6. I publish the reports to Power BI Service and set up a scheduled refresh.

Objective:
Configure a setup that allows secure access to the SQL Server database (92 box) from outside the company network using the internet, without relying on the current internal VPN setup.

Specific Questions:
1. What AWS configurations are needed to securely expose the SQL Server database for external access?
2. How should I configure the Power BI Data Gateway to securely connect from Power BI Service to the SQL Server database hosted on AWS?
3. What are the best practices for ensuring security and efficiency in this new setup?

Thanks in advance for your help!

[aduguid · Accepted solution]

1. AWS Configurations for Secure External Access
   • Create a VPC Endpoint: Ensure that your SQL Server database is hosted in an Amazon VPC (Virtual Private Cloud). Create a VPC endpoint if you want to restrict access to your database through AWS PrivateLink, which allows private connectivity between your VPC and supported AWS services.
   • Configure Security Groups:
   • Database Security Group: Modify the security group associated with the SQL Server instance (92 box) to allow inbound traffic only from the IP address ranges you plan to use for accessing it. This should be a static IP address or range that your Power BI Data Gateway uses.
   • Allow Specific Ports: Ensure that the SQL Server port (usually 1433 for default instances) is open to the necessary IP addresses.
   • Set Up a Publicly Accessible Endpoint (If Necessary): If you need direct access from the internet, you can create a Network Load Balancer (NLB) or Application Load Balancer (ALB) in front of your SQL Server database, and expose the required ports securely. Ensure that proper firewall rules and security groups are configured to restrict access to only trusted IP addresses.
     • Use IAM Roles: Implement AWS IAM (Identity and Access Management) roles to manage permissions securely for your SQL Server instance if you’re using AWS services like RDS.

2.     Configuring Power BI Data Gateway

• Install and Configure Data Gateway: Ensure that the Power BI Data Gateway is installed on the 27 box (or another secure server). Configure the gateway to connect to your SQL Server database. The gateway will securely manage data refreshes and queries between Power BI Service and your SQL Server.
• Register the Gateway: Register the gateway with your Power BI Service account. Ensure that the gateway is online and connected.
• Set Up Data Source Connection: In Power BI Service, configure a data source in the Power BI Gateway to connect to the SQL Server database. Provide the necessary connection details, including the database server name, database name, and authentication method.
• Test Connectivity: Ensure that the Power BI Data Gateway can connect to the SQL Server database by performing a test connection. This verifies that the setup is correct and the connection is secure.

3. Best Practices for Security and Efficiency

• Use Encryption: Enable encryption both in transit and at rest for your SQL Server database. This ensures that your data is secure during transmission and when stored.
• Regularly Update and Patch: Keep the SQL Server, Power BI Desktop, and Power BI Data Gateway updated with the latest patches and security updates.
• Access Controls: Implement strict access controls. Use least privilege principles for accessing your SQL Server database and Power BI Service.
• Monitoring and Logging: Enable logging and monitoring for both SQL Server and AWS resources. This helps in tracking access and identifying any potential security issues.
• Backup and Disaster Recovery: Ensure that regular backups are taken for your SQL Server database and have a disaster recovery plan in place.