cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
BBIUser Member
Member

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

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. 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

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

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.


Did I answer your question? Mark my post as a solution!
"Proud to be a Datanaut!"
Power BI Blog

View solution in original post

Super User
Super User

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

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


Did I answer your question? Mark my post as a solution!
"Proud to be a Datanaut!"
Power BI Blog

View solution in original post

4 REPLIES 4
Super User
Super User

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

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.


Did I answer your question? Mark my post as a solution!
"Proud to be a Datanaut!"
Power BI Blog

View solution in original post

BBIUser Member
Member

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

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!

Super User
Super User

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

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


Did I answer your question? Mark my post as a solution!
"Proud to be a Datanaut!"
Power BI Blog

View solution in original post

BBIUser Member
Member

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

Thanks @GilbertQ! Your insights were really helpful.

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 146 members 1,719 guests
Please welcome our newest community members: