Events
Power BI DataViz World Championships
Feb 14, 4 PM - Mar 31, 4 PM
With 4 chances to enter, you could win a conference package and make it to the LIVE Grand Finale in Las Vegas
Learn moreThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
This article spells out Always Encrypted support in Power BI Report Server when using the data source types Microsoft SQL Server and Microsoft Azure SQL Database. For more information about Always Encrypted capabilities in SQL Server, see the Always Encrypted article.
At this time, Power BI Report Server doesn't restrict access to Always Encrypted columns in reports if a user has access to the report. Therefore if the server has been given access to the column encryption keys via the column master key, then users have access to all the columns for the reports they can access.
Storage | Supported |
---|---|
Windows Certificate Store | Yes |
Azure Key Vault | No |
Cryptography Next Generation (CNG) | No |
The account that requires access to the certificate is the service account. The certificate should be stored in the local computer certificate store. For more information, see:
In Power BI Report Server, the column encryption strategy can be deterministic or randomized. The following table spells out differences, depending on which strategy it uses.
Use | Deterministic | Randomized |
---|---|---|
Can be read as-is in the results of a query, for example, SELECT statements. | Yes | Yes |
Can be used as a Group By entity within the query. | Yes | No |
Can be used as an aggregate field, except for COUNT and DISTINCT. | No, except for COUNT and DISTINCT | No |
Can be used as a report parameter | Yes | No |
Read more about deterministic vs. randomized encryption.
Parameter usage only applies to deterministic encryption.
Single-value parameter. You can use a single-value parameter against an Always Encrypted column.
Multi-value parameter. You can't use a multi-value parameter with more than one value against an Always Encrypted column.
Cascading parameters. You can use cascading parameters with Always Encrypted if all the following are true:
SQL Data type | Supports reading field | Supports use as Group By element | Supported aggregations (COUNT, DISTINCT, MAX, MIN, SUM, etc.) | Supports filtering via equality using parameters | Notes |
---|---|---|---|---|---|
int | Yes | Yes | COUNT, DISTINCT | Yes, as Integer | |
float | Yes | Yes | COUNT, DISTINCT | Yes, as Float | |
nvarchar | Yes | Yes | COUNT, DISTINCT | Yes, as Text | Deterministic encryption must use a column collation with a binary2 sort order for character columns. See the SQL Server Always Encrypted article for details. |
varchar | Yes | Yes | COUNT, DISTINCT | No | |
decimal | Yes | Yes | COUNT, DISTINCT | No | |
numeric | Yes | Yes | COUNT, DISTINCT | No | |
datetime | Yes | Yes | COUNT, DISTINCT | No | |
datetime2 | Yes | Yes | COUNT, DISTINCT | Yes, as Date/Time | Supported if column has no millisecond precision (in other words, no datetime2(0)) |
Currently the only supported aggregations against deterministic Always Encrypted columns are those aggregations that directly use the Equals (=) operator. This SQL Server limitation is due to the nature of Always Encrypted columns. Users must aggregate within the report instead of in the database.
You need to to enable Always Encrypted in the connection string for a SQL Server data source. Read more about enabling Always Encrypted in application queries.
More questions? Try asking the Power BI Community
Events
Power BI DataViz World Championships
Feb 14, 4 PM - Mar 31, 4 PM
With 4 chances to enter, you could win a conference package and make it to the LIVE Grand Finale in Las Vegas
Learn moreTraining
Certification
Microsoft Certified: Azure Database Administrator Associate - Certifications
Administer an SQL Server database infrastructure for cloud, on-premises and hybrid relational databases using the Microsoft PaaS relational database offerings.