ExpressRoute and VPNs in Azure

In this edition of Azure Every Day, I’d like to discuss networking and interacting between your data center and Azure. Two options you have are ExpressRoute and VPNs and I’d like to point out some reasons why you may choose one option over another or use a combination of both.

Let me start with a quick overview of what these components are and where they may fit strategically in your organization.

Overview of VPNs

First, let’s look at VPNs, which support a couple types of interaction and run over public internet. One VPN gateway is site to site integration, meaning you plug your data center into Azure on a VPN and have them trust each other. This is a common way to start out with Azure.

Or you can use a point to site gateway, which means you want to take your laptop and have it secured using a VPN into Azure and both these VPNs are supported across the platform. Which you choose depends on what makes sense for you. If you have a mobile workforce, you may need to do a lot of point to site work, for instance.

Overview of ExpressRoute

ExpressRoutes, unlike VPNs, run on dedicated private network fiber. To make ExpressRoute happen, you’ll need a connectivity partner (like AT&T or Verizon) to provide the private fiber connection between your assets and the Azure cloud. ExpressRoute is appealing from both a performance and security standpoint, but it’s not always something you can easily scale out due to cost implications and there’s more work involved.

Check out Azure Data Week coming in October 2018

Comparing ExpressRoute and VPN

Let’s look at some comparisons:

• SLA – VPNs and ExpressRoute run 99.95% SLA in most cases.
• Bandwidth – ExpressRoute offers a wide range of connectivity options as it ranges from 15 megabytes/second to 10 gigabytes/second. VPNs are more limited, ranging from 100 megabytes/second up to 1.25 gigabytes/second. But this is changing as Microsoft is constantly working to improve the capabilities of both.
• Security – To keep your data secure, VPNs use IPSec and IKE protocols and are typically fine for doing anything you want to do. But if you have a requirement or security that says your data can’t be pushed over the public internet, you would need to go to an ExpressRoute.
• Third Party Access – With ExpressRoute you can access some hosting organizations, like Rackspace, and take advantage of connections within their data centers.
• Office 365 – ExpressRoute allows you to take advantage of using some co-locating capabilities to do ExpressRoute connectivity to Azure and it will also cover Office 365.

Bottom line is, because of the complexity and cost of all these scenarios, it’s common to leverage multiple types of connectivity to Azure based on your needs for your business. You need to keep in mind all factors, such as security, performance and SLA capabilities. VPNs can be turned on quickly, where using ExpressRoute will take a lot more work to implement.

Choose wisely to make the best, most cost-effective choice for your organization which meets your business requirements and needs.

Azure Hybrid and Security

Data Security. These words are in the forefront of many people’s minds and just hearing them may bring on an instant tension headache. Whether you’re in Europe dealing with the GDPR (General Data Protection Regulation) that went into effect in May, or here in the US, security and compliance is a hot topic today.

The GDPR handles privacy much differently than before and, yes, if you’re in the US but have business or clients in the UK, you need to be ready as well. With these added regulations, many people are talking about the cloud or hybrid scenario.

Many of us are more comfortable with our data being handled locally, but that does not mean it’s more secure. Location is only one aspect of compliance. Today, I’d like to tell you 4 ways to take advantage of the cloud to help you become more secure.

1. Every time you provision a new service or add more business value, you need to take care of security.

Cloud providers take care of this for you. They have a vested interest in making sure the security of their platform is in place for you. As these platforms add new value-added resources, you can continue to grow your business with security taken care of.

2. Cloud facilities are physically more secure.

Cloud providers, with their multiple layers of security, processes and constant auditing and regulations, provide more security than your own data center–period.

3. Patching.

Cloud providers have very specific requirements to patch on a regular basis and to maintain their security, as well as be compliant with the numerous regulations in the industry, therefore, taking this responsibility off your team.

4. Cloud providers hire the best in the business.

They invest money to have the best and brightest specialists to ensure your environment is secure. They want to be sure you have everything you need to be secure and compliant.

If you are serious about security in your environment and your data, you should be considering the cloud. They are likely doing it better than you can and they are constantly improving.

Power BI Data Security – Power BI Report Server

Power BI Report Server was released as a way to host reports on premises. It was one of the highest requested features for Power BI. Power BI Report Server offers a subset of Power BI Service capabilities and as such does not have the rich collaborative or security options as seen in the service (online). I have a number of other thoughts on this tool, but that is not the purpose of this post. I am often asked in sessions on Power BI Data Security about Power BI Report Server. The problem is that Power BI Report Server is not what Power BI was intended to be and as such security is very different.

Power BI Report Server Uses Files and Folder Security Model

Unlike Power BI Service which leverages the Office 365 security model with workspaces and apps, Power BI Report Server only supports deploying Power BI Desktop files as Power BI Reports in SQL Server Reporting Services. Each file is uploaded to the Report Server and it is viewed by opening the report.

You have essentially three layers of access to the report file security in Power BI Report Server.

1. The portal itself can be secured. You can and should limit access to the reports by only allowing specific users or groups access to the report portal.
2. Folders can be used to provide more granular security over a group of assets in the report portal. In the image above, I created a folder called PBI Secure Reports. A specific AD group has access to this folder. If a user does not have permissions to the folder, the folder does not show up in the portal and they cannot access the folder or the assets, including Power BI reports, stored in this folder.
3. Individual reports can be secured as well. I never recommend this option as it becomes administratively difficult to manage. However, the capability is there is a single asset needs to be secured in this fashion.

These options work for any asset stored in the Report Portal and are not limited to Power BI reports.

Power BI Report Server Report Nuances

If you have read many of my posts around Power BI Data Security, I have discussed gateways, workspaces, and even Office 365 groups. The following items are uniquely related to Power BI reports stored in the report server.

Data Sources

Because it is not possible to use a Reporting Services Shared Data Source with your Power BI reports at this time (not sure if this will change), the data source information will need to be managed by report. This is not dissimilar to the Power BI Service. However, for on premises data we use the gateway with the service. There is not a common or shared data access feature in Power BI Report Server. Data sources are included in the report and can be managed in the deployed report by clicking the ellipses on the icon and selecting manage.

Only the report creator, Content Managers, and Publishers have permissions to see and modify this information.

Protecting Data Sources

One concern raised is whether data sources with credentials are downloaded when the Power BI file is downloaded. First key idea is that only users with the Publisher and Content Manager roles can download the files

When a Power BI report file is downloaded, you are prompted for credentials when refreshing data. Passwords are encrypted so they must be reentered when the file is opened or data refreshed in the desktop.

Mapping SSRS Roles to Power BI Functions

The portal, folders, and files are secured using SSRS roles. Here is a high level summary of the roles and how it impacts Power BI Reports

• Browser – This is similar to a read only function or if you would deliver the report as a Power BI App in the service. This should be used by anyone who needs access to the report, but does not create content.
• Content Manager – This is for a content creator with admin privileges. This role can manage content and user access.
• Publisher – This is for content creators who do not need to manage users. Most content creators fall into this category.
• Report Builder – does not apply to Power BI Reports which use Power BI Desktop to develop reports.

Avoid Deploying to the Portal Home Page

I would recommend you not allow Power BI reports to be deployed to the primary portal, but create folders to manage the group of content creators and managers based on the department or group level needs.

Reports deployed to the home page of the portal should be managed as enterprise assets. Use the folders and related security groups to manage who can deploy at this  level.

Wrap Up

I would like to extend a special thanks to Kathy Vick, Principal Consultant at Pragmatic Works. She provided guidance on implementing Power BI Report Server. Thanks Kathy for the help on this topic. Check out Kathy’s Twitter (@MsKathyV)  and LinkedIn account to learn more.

Check out more about Power BI Data Security in my blog series.

Power BI and Data Security – Audit Logs, Powershell, Power BI and @AngryAnalytics

The following has been reposted with permission from Steve Howard a.k.a. @AngryAnalytics. I have made some formatting changes but the content is unchanged. Thanks again to Steve for allowing me to repost this content. You can find the original post and the rest of Steve’s great work on his blog.

Power BI Audit Log Analytics Solution

As Power BI adoption in your organization grows, it becomes more and more important to be able to track the activity in the environment.

When you start to think about deploying a Power BI Audit Log solution that is repeatable there are a few challenges that you will face.

• Going to the O365 Audit Logs portal each time you want to extract log events is a manual process and doesn’t scale
• When automating this process through API or PowerShell, there is a limit to how much data you can pull, therefore examples that are currently available also don’t scale very well
• The AuditData field is a JSON format by default and although Power BI can parse JSON beautifully, when doing this over several thousand record entries may result in data load errors

Based on these factors, i have put together a PowerShell script that can be scheduled on a nightly basis that can iterate MORE than 5000 records so that no data is lost. Also, the screenshot below is of an initial template that you can use to start with to analyze your audit logs for your organization.

TL;DR

• The required files can be found on my GitHub
• Update the PowerShell script with a UserID and Password that has O365 audit log privileges
• Use Task Scheduler to schedule the PowerShell script to run each night at midnight (run as admin).
• At the end of the script, specify the directory you would like the script to generate CSV files in
• In the PBIX file, it was challenging to get a parameter to work for the file location that the CSVs are in, so in the Query Editor the script for the AuditLog table needs to be manually modified to include your file path.
• Enjoy

Quick look at the PowerShell

First, there is a PowerShell script.

Set-ExecutionPolicy RemoteSigned

#This is better for scheduled jobs
$User = "<<enter o365 admin user email here>>"
$PWord = ConvertTo-SecureString -String "<<enter password here>>" -AsPlainText -Force
$UserCredential = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList $User, $PWord

#This will prompt the user for credential
#$UserCredential = Get-Credential

$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection
Import-PSSession $Session

$startDate=(get-date).AddDays(-1)
$endDate=(get-date)
$scriptStart=(get-date)

$sessionName = (get-date -Format 'u')+'pbiauditlog'
# Reset user audit accumulator
$aggregateResults = @()
$i = 0 # Loop counter
Do { 
 $currentResults = Search-UnifiedAuditLog -StartDate $startDate -EndDate $enddate `
 -SessionId $sessionName -SessionCommand ReturnLargeSet -ResultSize 1000 -RecordType PowerBI
 if ($currentResults.Count -gt 0) {
 Write-Host (" Finished {3} search #{1}, {2} records: {0} min" -f [math]::Round((New-TimeSpan -Start $scriptStart).TotalMinutes,4), $i, $currentResults.Count, $user.UserPrincipalName )
 # Accumulate the data
 $aggregateResults += $currentResults
 # No need to do another query if the # recs returned <1k - should save around 5-10 sec per user
 if ($currentResults.Count -lt 1000) {
 $currentResults = @()
 } else {
 $i++
 }
 }
} Until ($currentResults.Count -eq 0) # --- End of Session Search Loop --- #

$data=@()
foreach ($auditlogitem in $aggregateResults) {
 $datum = New-Object –TypeName PSObject
 $d=convertfrom-json $auditlogitem.AuditData
 $datum | Add-Member –MemberType NoteProperty –Name Id –Value $d.Id
 $datum | Add-Member –MemberType NoteProperty –Name CreationTime –Value $auditlogitem.CreationDate
 $datum | Add-Member –MemberType NoteProperty –Name CreationTimeUTC –Value $d.CreationTime
 $datum | Add-Member –MemberType NoteProperty –Name RecordType –Value $d.RecordType
 $datum | Add-Member –MemberType NoteProperty –Name Operation –Value $d.Operation
 $datum | Add-Member –MemberType NoteProperty –Name OrganizationId –Value $d.OrganizationId
 $datum | Add-Member –MemberType NoteProperty –Name UserType –Value $d.UserType
 $datum | Add-Member –MemberType NoteProperty –Name UserKey –Value $d.UserKey
 $datum | Add-Member –MemberType NoteProperty –Name Workload –Value $d.Workload
 $datum | Add-Member –MemberType NoteProperty –Name UserId –Value $d.UserId
 $datum | Add-Member –MemberType NoteProperty –Name ClientIP –Value $d.ClientIP
 $datum | Add-Member –MemberType NoteProperty –Name UserAgent –Value $d.UserAgent
 $datum | Add-Member –MemberType NoteProperty –Name Activity –Value $d.Activity
 $datum | Add-Member –MemberType NoteProperty –Name ItemName –Value $d.ItemName
 $datum | Add-Member –MemberType NoteProperty –Name WorkSpaceName –Value $d.WorkSpaceName
 $datum | Add-Member –MemberType NoteProperty –Name DashboardName –Value $d.DashboardName
 $datum | Add-Member –MemberType NoteProperty –Name DatasetName –Value $d.DatasetName
 $datum | Add-Member –MemberType NoteProperty –Name ReportName –Value $d.ReportName
 $datum | Add-Member –MemberType NoteProperty –Name WorkspaceId –Value $d.WorkspaceId
 $datum | Add-Member –MemberType NoteProperty –Name ObjectId –Value $d.ObjectId
 $datum | Add-Member –MemberType NoteProperty –Name DashboardId –Value $d.DashboardId
 $datum | Add-Member –MemberType NoteProperty –Name DatasetId –Value $d.DatasetId
 $datum | Add-Member –MemberType NoteProperty –Name ReportId –Value $d.ReportId
 $datum | Add-Member –MemberType NoteProperty –Name OrgAppPermission –Value $d.OrgAppPermission

#option to include the below JSON column however for large amounts of data it may be difficult for PBI to parse
 #$datum | Add-Member –MemberType NoteProperty –Name Datasets –Value (ConvertTo-Json $d.Datasets)

#below is a poorly constructed PowerShell statemnt to grab one of the entries and place in the DatasetName if any exist
 foreach ($dataset in $d.datasets) {
 $datum.DatasetName = $dataset.DatasetName
 $datum.DatasetId = $dataset.DatasetId
 }
 $data+=$datum
}

$datestring = $startDate.ToString("yyyyMMdd")
$fileName = ("c:\PBIAuditLogs\" + $datestring + ".csv")
Write-Host (" writing to file {0}" -f $fileName)
$data | Export-csv $fileName

Remove-PSSession -Id $Session.Id

• Notice that you need to enter O365 audit log privileged credentials at the top so that this can be ran automatically. If you have more clever ways to pass these credentials in so they are not exposed in the file by all means, do that
• The Do/Until loop handles if there are more than 5000 records in the result set which would easily be the case for a large Power BI community.
• The foreach loop extracts the AuditData column JSON format and creates an individual record for each entry. This makes the Query Editor in Power BI less complex and easier to accomplish retrieving several hundred thousand records without import errors
• finally we create a CSV for the data with the date of the file entries (yesterdays info if this is ran at midnight every day). This dumps each file in c:\PBIAuditLogs. You can obviously change this file location to wherever you want to store your CSV extracts

You can use Task Scheduler to run the above PowerShell script every night at midnight.

The PBIX file

In the Power BI file, we are connecting to the content of the entire folder shown above. I went ahead and included the PBIX file WITH the sample data so you could get an idea of what your data may look like.

This is where i have to admit that i tried to use a parameter for this but ran into some Query Editor challenges with how Power BI creates a Sample File transform to import multiple files from a single folder. If you can see what i did wrong here I would love your feedback, but for now, you can ignore the file directory parameter in the Query Editor and need to go to “Advanced Editor” on the “AuditLog” query and modify the file location to be the location you are dumping files from the PowerShell script.

Change the below file location as needed.

Once you have made this change, you should be able to “Close and Apply” and your data will now be populated in this basic audit log analytics view.

Using the file

I created a couple basic pages to get this blog post shipped and so you can start taking advantage of the solution, but it is nowhere near as complete as you can eventually make it. I have a simple overview page that was screenshotted above. It can help you determine number of active users, reports, dashboards, and datasets being used for any time period your audit log data covers.

The second page is a user activity view I created from a calculated table. It helps you determine which users in the system may be inactive so you can re-assign power bi licenses and also shows detailed activity for an individual user that you can select from the slicer.

Other things you can mine from this data:

• Who has signed up for Free Power BI trials
• What “Apps” are being created
• what embed tokens are being generated and used
• many other possibilities

The PowerShell script and the PBIX file are located on my GitHub here…

Link to the original post:  http://angryanalyticsblog.azurewebsites.net/index.php/2018/02/16/power-bi-audit-log-analytics-solution/

Thanks again, Steve

Power BI Data Security – Sharing in Email

 

Microsoft has expanded sharing by allowing users to share Power BI content via email. In a previous post, I discussed how sharing content within your organization should be handled carefully. However, the new process opens up the opportunity to share outside your organization by sending an email. In particular, you can now share with users who have a personal email address such as @outlook.com and @gmail.com. Let’s dig into the implications of this capability.

Sharing Using Email

First, you need to be aware that this functionality is as simple as the original methods of sharing. You click the Share button on your report or dashboard to open the Share dialog.

The Share report dialog in this case accepts email addresses which is not a significant change. However, as shown below, you can add personal emails and emails outside your organization. You be warned, but users do not always pay attention to this or understand the implications.

You will also notice that consumers need to still have a Power BI Pro account assigned to them or you need to be using Power BI Premium for this to work.

Following the Email Process

When you share, you usually will need to send an email to the recipient. Here is the email content.

Time to click the report link. This opens a series of dialogs which determine how much you have access. It is important to note that this is all made possible with Azure B2B. More about that in a moment. Let’s trace the story through. The link opens the following page.

As you can see, the next step is to log in. I am using an outlook.com account so it prompts me to authenticate. Once I have authenticated, I get the following notice.

My account does not have Power BI Pro, but now I can try it for free for 60 days and get access to the data while I am on the trial. I clicked both options, because I can. The Upgrade account option would require me to pay for Pro. However, Try Pro for free works and I was able to access the report fully. I have successfully shared my corporate content with a personal user.

Preventing Sharing Outside Your Organization

While in some cases, you need to share outside your organization, we will assume here you need to disable this functionality. There are a few places you can make this happen.

Power BI Admin Portal

First, in Power BI go to the Admin portal and disable sharing outside your organization. If you have followed my previous advice, this will already be disabled.

 

As you can see, this will disable content for users who have been shared with previously. If you need to share, you can specify groups that have that permission.

Office 365 Admin Center

Next, this can be turned off in the Office 365 Admin Center in the Security and privacy area.

This prevents the ability to add guest users to the organization. This will disable this capability across Office 365. There is no option to allow some users this access. Once this is disabled, sharing outside the organization which requires a guest user will not be possible.

Azure Active Directory

Finally, you can shut this down from Azure Active Directory. Guest users are ultimately managed through Azure Active Directory and this is the best place to turn this off corporately if you do not need this functionality.

In AAD you have four options.

1. Guest users permissions are limited. This limits guest user capabilities with regard to the directory. Yes is the default and recommended.
2. Admins and users in the guest inviter role can invite. This would be a typical option we can understand. However, it is important to note that Admin users in Power BI workspaces will have the ability to create guest users and share reports externally with this permission on.
3. Members can invite. Just like it sounds. Any member of a group can invite guest users in.
4. Guests can invite. This allows guests to invite other guests. Seems dangerous to me.

As you can see from my tenant, the options are all on which is the default. Be sure to understand what capability you want to use and set it appropriately within your tenant.

Tracking Sharing

In the Office 365 logging, you can see who and what has been shared. This log covers internal and external shares and should be monitored for auditing and compliance purposes.

Azure B2B

Azure B2B and the sharing capabilities in Power BI go hand in hand. This allows organizations to share content in a controlled fashion to consumers outside their organization. While this is required for certain scenarios, be mindful of who has the capability to share, and track sharing to make sure the data is being handled as you require.

Final Thoughts and References

You need to remember that sharing is at the heart of Power BI and you need to manage how and who can share. If you need to do more extensive sharing, by all means, use these features. For those, who need to lock it down tighter, you can follow the steps above to prevent sharing until you have a process and pattern. Power BI continues to improve and grow and as that happens we can expect more security options to support the new functionality. Enjoy Power BI, it is a great tool and will only continue to get better.

References

Using Azure AD B2B with Power BI

Auditing Power BI

Share your Power BI content with anyone by email