Forum Discussion

manoj_0911's avatar
manoj_0911
Kudo Commander
2 years ago

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

Hello everyone,

 

We are currently using the Power BI Data Gateway to connect Power BI to our SQL Server on AWS. However, we want to ensure that our database is not exposed to our customers. What are the different options or best practices available to achieve this while still allowing customers to access the necessary data through Power BI?

 

Any guidance or suggestions would be greatly appreciated!

 

Thank you!

Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

1 Reply

  • rajendraongole1's avatar
    rajendraongole1
    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.