Press "Enter" to skip to content

Category: Security

Managing Security Roles for Hierarchical Organizations in Power BI

Marco Russo and Alberto Ferrari are working for The Man:

The security model in Tabular used by Power BI can filter rows of a table based on a DAX expression. When security is applied to a hierarchical structure, every hierarchy level is represented by a different column in the table. This structure can make it challenging to define a dynamic security filter based on the name of a node in the hierarchy, because the DAX expression must filter the column corresponding to the hierarchical level in which that node exists. If the security needs to be maintained dynamically in a configuration table, the resulting code may end up being extremely complex and hard to maintain, as well as create possible performance issues.

Without describing the complexity of solutions based on a filter applied directly to the appropriate hierarchy level, we want to describe a solution that minimizes the effort required in maintaining a configuration table for the dynamic security rules, while also providing good performance at execution time by minimizing the processing overhead required to apply the dynamic security.

Click through for the scenario and how you can implement this kind of security model in Power BI.

Comments closed

Testing Row-Level Security in Power BI

Wolfgang Strasser puts on the Mission Impossible face replacement mask:

Long time, no Power BI blog post from my side. But today I found out, that the testing of your row-level-security (RLS) logic in the Power BI service changed “a little bit” since I last used it.

Whenever you want to test your RLS logic, you can do this in Power BI Desktop (Mange Roles for definition, “View as” for testing).

Click through for an example of how this works. I like this approach a lot because the people who are developing these reports usually have access to everything, so it’s hard to ensure that you got everything right until people start complaining.

Comments closed

Advanced Scenarios for Private Endpoints to Azure SQL MI

Zoran Rilak digs in:

In the previous installment of this mini-series, we covered basic scenarios involving private endpoints. If you aren’t familiar with private endpoints and Private Link in general, it might be a good idea to quickly review them to get the feel of how they apply when Azure SQL Managed Instance is in the mix.

In this article, we’ll dive into more involved scenarios that build on those from last week:

5. Hub and spoke topology

6. Partner or ISV giving access to their customers

7. Two SQLs talking to each other: linked server, transactional replication

8. Failover group listener using private endpoints

Read on for architecture diagrams and descriptions for each of these scenarios.

Comments closed

Delegating Permissions Management in SQL Server

Andreas Wolter has people for that:

Both roles can GRANT/DENY/REVOKE any permission within a database. Since that makes members of these roles super powerful, their membership should always be tightly controlled and audited. (also see: Security concept: Audit Trail).
Consequently, most customers just keep this power to the main Administrators-group.

However, there are cases where it is useful to enable a set of non-admin users—let’s call them LowPermission-Managers—to work in a more self-sufficient way and enable them to grant some permissions to other users without the need to involve “the Administrators”.
The emphasis is on “some permissions” asthis only makes sense for low privileges. Making the LowPermissionManagers members of db_owner or db_securityadmin -roles would defeat the purpose.

Read on for two techniques which allow you to do just that.

Comments closed

Decrypting Stored Procedures with SQL Compare

Steve Jones cracks the case:

I had a client that was struggling with some encrypted stored procedures. They needed to decrypt them, which I know is a pain in the #@$%@#$@#$#@. I had to do this one. This post shows how I sent them some code to do this.

In a previous post I set up some procedures and then showed code to decrypt them. Here, I’ll use SQL Compare 15, which makes this easy.

Read on to see how it works.

Comments closed

Tenant Configuration in Microsoft Fabric

Marc Lelijveld collects some data:

It has been quiet for a few weeks due to summer break. But now, it’s time to ramp up again and continue posting about Fabric. This time, I’ll start with a question that many Fabric (and Power BI) users ask themselves: “What does my tenant configuration look like?”. Often, users find themselves eager to explore new features they’ve come across online. But somehow, they cannot get it to work, or the feature does not even show for them.

In this blog, I’ll elaborate on the challenges and scenarios in which questions like these come up, and what you can do as a Fabric / Power BI administrator to ease answering this question.

Read on for one of the most common scenarios.

Comments closed

Private Endpoints and Azure SQL Managed Instance

Zoran Rilak begins a new series:

Last week we announced the general availability (GA) of private endpoints for Azure SQL Managed Instance. Today, we bring you examples of private endpoints in practical scenarios, starting from the basics and building to the more complex ones to follow in the second installment of this mini-series.

In this post, we’ll cover the following scenarios:

  1. Accessing SQL MI from another virtual network
  2. A more secure kind of public access
  3. Accessing SQL MI from your premises
  4. Making SQL MI available to managed Azure services

Click through to see these four scenarios at the architecture diagram level.

Comments closed

Decrypting Stored Procedures in SQL Server

Steve Jones breaks the connection:

I had a client that was struggling with some encrypted stored procedures. They needed to decrypt them, which I know is a pain in the #@$%@#$@#$#@. I had to do this one. This post shows how I sent them some code to do this.

Note, SQL Compare 15 does this easier and simpler. If you own it, I’d use that instead. A future post will show how easy that it.

Stored procedure encryption is one of the more annoying features in SQL Server. The idea was, if you wanted to prevent end users from reading your code, you could encrypt the procedures. But in order to use the procedures, SQL Server needed to decrypt them and you needed this to work on restored backups, so the decryption keys needed to be available to that SQL Server. The infrastructure is a bit different from how Microsoft eventually landed Transparent Data Encryption, enough so that it turned out breaking these procedures is trivial, as Steve shows.

I didn’t know that SQL Compare did decryption. The couple of times I needed to do this, I had used a standalone tool which was released in the 2005 timeframe, so it’s good to see something still supported which does this.

Comments closed

Connecting to SQL Server 2022 via Azure AD

Deepthi Goguri makes a connection:

Applicable to-

SQL Server 2022 on-prem on Windows and Linux and SQL Server 2022 hosted on Windows Virtual Machines.

Once you install the SQL Server, there are three different authentication methods that you can use to connect SQL Server along with the Windows and SQL Server authentication. They are –

  1. Azure Active Directory Universal with Multi-Factor Authentication
  2. Azure Active Directory Password
  3. Azure Active Directory Integrated

Read on for the pre-requisites as well as a detailed guide on how to set everything up.

Comments closed

Creating a Power BI VNet Data Gateway

Meagan Longoria rolls up her sleeves:

If you are using Power BI to connect to a PaaS resource on a virtual network in Azure (including private endpoints), you need a data gateway. While you can use an on-premises data gateway (the type of Power BI gateway we have had for years), there is an offering called a virtual network data gateway that is currently in preview.

The VNet data gateway securely communicates with the data source, executes queries, and transmits results back to the service, just like the on-premises data gateway. But it doesn’t require us to provision a virtual machine in the same network (or a peered network) of our Azure data source.

Read on to see some important caveats, as well as a step-by-step guide.

Comments closed