I have one Azure Managed stance database which has multiple tenant stored in it. Now one of my customer is asking for data source to run report using power BI. I am looking for best approach how do i share database with him as i have all the customers(tenant) in single database.
Is there way i can share database with him but will be only able to access his information from azure database?.
On
You can implement Row-level Security (RLS) at the SQL Server level or at the Power BI level. With SQL Server RLS the data will be filtered with by tenant-ID assuming each tenant gets connected with a different login. With RLS you will map on a table all logins with their associated tenant ID and logins that do not exist on that table, will have queries returning no rows. RLS is more effective when INNER joins are involved when considering Tenant IDs.
You may have to configure a Data Gateway or a Private Endpoint to connect Power BI to Azure SQL Managed Instance. Please visit here for more information.
Create a Power BI dataset that is filtered to only that tenant and share that. If each tenant has a separate Power BI dataset there’s no need to implement row level security.