Options for Connecting Power BI to SQL Server on AWS without Exposing DB to Customers

Super User

2 years ago

Hi manoj_0911 -you can ensure that your SQL Server on AWS remains secure and inaccessible to unauthorized users, while still allowing Power BI to access the necessary data for reporting. The specific combination will depend on your organization's security requirements and infrastructure setup.Place your SQL Server within a VPC on AWS and use Security Groups to restrict access to the database. Only allow traffic from the Power BI Data Gateway and other trusted sources.

Solution: Configure Security Groups to allow only specific IP addresses or subnets (such as those used by the Power BI Gateway) to connect to your SQL Server.

Instead of connecting Power BI directly to the SQL Server, consider creating a data access layer using RESTful APIs or an intermediate service. This layer can control access to the database and apply additional security measures.

Solution: Develop a web service that interfaces with the SQL Server and exposes only the necessary data to Power BI via APIs. Power BI would connect to this service instead of directly to the SQL Server.

Use dynamic data masking in SQL Server to obfuscate sensitive data. This ensures that even if users gain access to the data, they can only see masked values for sensitive information.

Solution: Configure dynamic data masking rules in your SQL Server for columns containing sensitive data.

Store database credentials securely using AWS Secrets Manager and ensure that the Power BI Gateway retrieves credentials from there, reducing the risk of exposure.

Solution: Integrate AWS Secrets Manager with your SQL Server and Power BI Gateway.

reference links:

SQL Server security best practices - SQL Server | Microsoft Learn

https://docs.aws.amazon.com/vpc/latest/userguide/what-is-amazon-vpc.html

What is IAM? - AWS Identity and Access Management (amazon.com)

Connect Powerbi with AWS RDS with no public access... - Microsoft Fabric Community

SQL Server encryption - SQL Server | Microsoft Learn

Hope it helps.