How is security set on "Import" Datasets when it uses the SQL Server Database Security?

[BBIUser]

Hello Experts,

My apologizes if these questions are confusing, but I am trying to understand how the security works in Power BI Service when an "Import" data connection is used with On-Premise SQL Server as a source after I publish the report and dataset(s) in Power BI Service.

Steps I follow:

- Use "Import" connection in Power BI desktop to develop a report.

- Publish the report (along with the dataset) to Power BI Service.

1) Where does the data get stored after report/dataset is published? Does it store a copy of the actual data? What does "copy" means here?

2) How does the Organizations Active Directory Security group and Azure AD work when the report/datset is trying to connect to the On-Premise SQL Server source? 

3) How does the Organizations Active Directory Security group apply when trying to connect to the On-Premise SQL Server source? 

Note: My organization uses SQL Server Database Security (Active Directory Security groups) and we are using Azure. 

4) Imported datasets will need to be refreshed using the On-Premises Data Gateway. When this is done, Will the AD Security group apply through On-Premises Data Gateway and How?

I have looked into architecture diagrams, but trying to understand and dive deep into real time scenarios.

Any help is greatly appreciated. 

[GilbertQ · Accepted solution]

Hi @BBIUser

Please see my answers below

1) Where does the data get stored after report/dataset is published? Does it store a copy of the actual data? What does "copy" means here?

GQ - When you import the data into your PBIX file, all the data is copied and stored in the PBIX file. 

2) How does the Organizations Active Directory Security group and Azure AD work when the report/datset is trying to connect to the On-Premise SQL Server source? 

GQ - Because you have imported the data into your PBIX file, it does not make a connection back to the SQL Server Source, it actually makes a connection to your data that is copied/hosted in the Power BI Service.

3) How does the Organizations Active Directory Security group apply when trying to connect to the On-Premise SQL Server source? 

GQ - As explained in step 2, it is not connecting back to the On-Premise SQL Server Source, but to the imported data in the Power BI Service.

Note: My organization uses SQL Server Database Security (Active Directory Security groups) and we are using Azure. 

4) Imported datasets will need to be refreshed using the On-Premises Data Gateway. When this is done, Will the AD Security group apply through On-Premises Data Gateway and How?

GQ - When using the On-Premise Data Gateway to refresh data, it uses the Account that was set up when configuring the data source in the Manage Gateways on the Power BI Service. It will not authenticate the users, because the users connect to the copied data in the Power BI Service. The refresh actually just gets another copy of the data and updates the existing copy in the Power BI Service.

[GilbertQ · Accepted solution]

Hi @BBIUser

Please see my answers below

1) Where does the data get stored after report/dataset is published? Does it store a copy of the actual data? What does "copy" means here?

GQ - When you import the data into your PBIX file, all the data is copied and stored in the PBIX file. 

2) How does the Organizations Active Directory Security group and Azure AD work when the report/datset is trying to connect to the On-Premise SQL Server source? 

GQ - Because you have imported the data into your PBIX file, it does not make a connection back to the SQL Server Source, it actually makes a connection to your data that is copied/hosted in the Power BI Service.

3) How does the Organizations Active Directory Security group apply when trying to connect to the On-Premise SQL Server source? 

GQ - As explained in step 2, it is not connecting back to the On-Premise SQL Server Source, but to the imported data in the Power BI Service.

Note: My organization uses SQL Server Database Security (Active Directory Security groups) and we are using Azure. 

4) Imported datasets will need to be refreshed using the On-Premises Data Gateway. When this is done, Will the AD Security group apply through On-Premises Data Gateway and How?

GQ - When using the On-Premise Data Gateway to refresh data, it uses the Account that was set up when configuring the data source in the Manage Gateways on the Power BI Service. It will not authenticate the users, because the users connect to the copied data in the Power BI Service. The refresh actually just gets another copy of the data and updates the existing copy in the Power BI Service.

[BBIUser]

Thanks for your reply @GilbertQ. We do not have Power BI in our organization yet, so I dont have any idea how it works in real time. Please bear with me on these questions.

GQ - When you import the data into your PBIX file, all the data is copied and stored in the PBIX file. 

BBI - I do understand that data is copied and stored in the PBIX file, but there should be some database on cloud to store those data right? If so, what is that database and where is that located?

GQ - Because you have imported the data into your PBIX file, it does not make a connection back to the SQL Server Source, it actually makes a connection to your data that is copied/hosted in the Power BI Service.

BBI - How safe is the copied/hosted data? What security is set around the hosted data?

GQ - When using the On-Premise Data Gateway to refresh data, it uses the Account that was set up when configuring the data source in the Manage Gateways on the Power BI Service. It will not authenticate the users, because the users connect to the copied data in the Power BI Service. The refresh actually just gets another copy of the data and updates the existing copy in the Power BI Service.

BBI - I understand now how it works.

"Account" you mean Active Directory Security Account? Can I know what "account" will this be?

Thanks again!

[GilbertQ · Accepted solution]

Hi there, please see below.

The data will be stored in the tenant where your Power BI tenant is running from. You can find out when clicking About in the Power BI service. When the data is not in memory it is saved in an Azure SQL database.

All the data is encrypted at rest and in transit.

This will be a local AD account within your On-Premise domain

[BBIUser]

Thanks @GilbertQ! Your insights were really helpful.