How can I securely connect Power BI Desktop to a SQL Server database in a private network via a Powe

[manoj_0911]

**Question:**

How can I set up a connection for customers using Power BI Desktop on their laptops to connect to a SQL Server database hosted on AWS (92 box) through a Power BI data gateway on an EC2 instance (27 box)? What are the necessary AWS and network configurations?

**Current Setup:**
- Power BI Data Gateway on EC2 instance (27 box).
- SQL Server database hosted in a private network on AWS (92 box).
- Customers will use their own laptops with Power BI Desktop, located in a separate network box.

**Objective:**
Enable customers to securely connect their Power BI Desktop to the SQL Server database on AWS using the Power BI data gateway.

**Steps to Implement the Setup:**

### 1. **Set Up AWS EC2 Instances**
- **Power BI Data Gateway:**
- Launch an EC2 instance (27 box) to host the Power BI Data Gateway.
- Ensure the instance has the necessary specifications and security group settings.

- **SQL Server Database:**
- Ensure the SQL Server database is hosted on an EC2 instance (92 box) in a private subnet within a VPC.
- Set up the security group to allow inbound traffic on SQL Server ports (default 1433) from the gateway instance.

### 2. **Configure the VPC and Network**
- **Private Subnet:**
- Place the SQL Server (92 box) in a private subnet to ensure it's not accessible directly from the internet.
- **Public Subnet:**
- Place the Power BI Gateway (27 box) in a public subnet if it needs to communicate directly with Power BI service.

- **Route Tables:**
- Configure route tables to allow communication between the private and public subnets.

### 3. **Install and Configure Power BI Data Gateway**
- **Installation:**
- Install the Power BI Data Gateway on the EC2 instance (27 box).
- **Configuration:**
- Sign in to the gateway with your Power BI account.
- Configure the gateway to connect to the SQL Server database hosted in the private subnet (92 box).

### 4. **Set Up Security Groups and Firewall Rules**
- **Security Groups:**
- Allow inbound traffic on required ports for the Power BI Gateway and SQL Server.
- Restrict access to only trusted IP ranges or VPN connections.
- **Firewall Rules:**
- Ensure that network ACLs and firewall rules allow traffic from the EC2 instance with the Power BI Gateway to the SQL Server database.

### 5. **Configure VPN for Customer Access**
- **VPN Setup:**
- Set up a VPN to allow customer laptops to securely connect to the VPC.
- Provide VPN credentials and configuration details to customers.

### 6. **Register the Gateway in Power BI Service**
- **Power BI Service:**
- In the Power BI service (app.powerbi.com), navigate to "Manage gateways."
- Add the gateway installed on the 27 box and create a data source for the SQL Server database, providing the necessary connection details and credentials.

### 7. **Configure Power BI Desktop on Customer Laptops**
- **Connection Setup:**
- Install Power BI Desktop on customer laptops.
- In Power BI Desktop, connect to the SQL Server database through the configured gateway:
- Go to "Get Data" -> "SQL Server."
- Enter the server name (92 box) and database name.
- Specify the gateway connection and use the credentials configured in the Power BI service.

### 8. **Test and Validate the Setup**
- **Report Creation:**
- Create a simple report in Power BI Desktop using the SQL Server data.
- Publish the report to the Power BI service and verify data refresh through the gateway.

### Summary:
By following these steps, customers should be able to securely connect their Power BI Desktop to a SQL Server database hosted on AWS through a Power BI data gateway on an EC2 instance. Ensure all AWS and network configurations are properly set up to allow seamless and secure data access.

**Can anyone provide additional insights or best practices for setting up this configuration?**

Thank you!

====================================================================================

**Revised Question:**

Hi everyone,

I'm working on a project where our customers need to connect their Power BI Service to a SQL Server database hosted on AWS through a Power BI Data Gateway. The SQL Server is in a private network (92 box), and the Power BI Gateway is on an EC2 instance (27 box). Customers will be using Power BI Desktop to publish reports to the Power BI Service.

**Objective:**
Enable customers to securely refresh their datasets in the Power BI Service using the Power BI Data Gateway.

**Current Setup:**
- **Power BI Data Gateway:** Installed and configured on an EC2 instance (27 box).
- **SQL Server Database:** Hosted on a private network in AWS (92 box).
- **Customer Laptops:** Power BI Desktop installed, connected via VPN to our network.

**Steps Taken:**
1. Installed and configured the Power BI Data Gateway on the EC2 instance (27 box).
2. Ensured network and security configurations allow the gateway to access the SQL Server database (92 box).
3. Set up the necessary VPN for secure access.

**Questions:**
1. What are the best practices for configuring the Power BI Data Gateway to ensure secure and efficient data refreshes from the SQL Server database hosted in a private network on AWS?
2. Are there specific AWS setups or configurations we need to be aware of to facilitate this connection?
3. Any insights on ensuring the gateway and SQL Server are properly secured while allowing customer access through the Power BI Service?

Thanks in advance for your help!

[manoj_0911]

I received the above reply from ChatGPT, but I'm looking for input's from experienced people who have dealt with this situation. Please share your valuable insights.

[Greg_Deckler]

@manoj_0911 Well, first of all, you don't connect Power BI Desktop to a data source using a gateway. You connect from Power BI Desktop directly to the data source. The gateway is there only for the Power BI Service to be able to access on-premises data. So, your premise, I think, is not correct or I am not understanding what you are saying.

[manoj_0911]

You are right, Sorry for the confusion. I need to edit my question properly.

[v-linyulu-msft · Accepted solution]

Thanks for the reply from Greg_Deckler  please allow me to provide another insight:
Hi,@manoj_0911

Regarding the issue you raised, my solution is as follows:
 

Regarding your first question, the gateway configuration:

1.First, there can only be one gateway per report, even if there are multiple data sources, they need to go through the same gateway. If your dashboard is based on multiple reports, you can use a dedicated gateway for each report to distribute the load.

2.Second, you can choose DirectQuery or Import, depending on the data source. Try to separate the DirectQuery data source from the scheduled refresh data source to avoid the gateway processing a large number of requests at the same time.

3.Finally, you can keep the gateway, data source, and Power BI tenant as close together as possible to reduce network latency and improve query performance.
 

Here are the relevant documents:
Guidance for deploying a data gateway for the Power BI service - Power BI | Microsoft Learn
Power BI implementation planning: Data gateways - Power BI | Microsoft Learn
 

On your second question:

1.First, we recommend that you install the Microsoft on-premises data gateway on an Amazon EC2 instance in a private subnet that contains the data source. The subnet should be configured to route requests to the internet through an Amazon VPC NAT gateway in a public subnet.
 

2.Second, if high availability is required, gateways on multiple EC2 instances can be installed in multiple AWS Availability Zones to form a gateway cluster.
 

Here are links to the relevant documentation:

Connecting the Microsoft Power BI service to AWS data sources - Using Microsoft Power BI with the AW...

 

On your third question:
 

1.First, you can take advantage of row-level security.

Row-level security (RLS) guidance in Power BI Desktop - Power BI | Microsoft Learn
 

2.Second, you can use sensitivity labels.
How to apply sensitivity labels in Power BI - Power BI | Microsoft Learn

3.Finally, update your gateway software regularly to take advantage of the latest security enhancements.

Ensuring Data Security with Power BI: Tips and Tricks (datalogz.io)

Of course, if you have any new ideas, you are welcome to contact us.
 

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.