Press "Enter" to skip to content

Category: Security

Don’t Try These with SQL MI and Private Endpoints

Published 2023-09-08 by Kevin Feasel

Zoran Rilak wraps up a series on Azure SQL Managed Instance and its support for private endpoints:

The first two installments of this mini-series discussed a couple of basic and advanced scenarios involving private endpoints. Today we’ll look at some ways private endpoints cannot be used to implement scenarios where one might expect otherwise.

Read on for four of these in total, laying out things you cannot do via private endpoint to a SQL Managed Instance. In fairness, Zoran also provides what I would consider reasonable work-arounds for each of those: have a VM jumpbox in the same virtual network for DAC connections, peer your virtual networks for replication, and so on.

Comments closed

Automatic SSL Certificate Updates for SQL Server

Published 2023-09-06 by Kevin Feasel

Jamie Wick doesn’t want to waste time clicking mouse buttons:

In a previous post (How Secure Are Your Client Connections?) I covered using SSL certificates to encrypt client connections to SQL Server. One part of the process that has always been annoyingly repetitive is the need to regularly renew/update the SSL certificate(s) and reconfigure SQL Server to use them. In the past our SSL certs were good for 3 years. Then in 2020 Google, Microsoft, Apple & others announced that they were reducing their certificate validity period to a maximum of 398 days. Recently, there have been proposals to further reduce the validity period to 90 days. When this happens (and I’m certain it is a when, and not an if situation), manually replacing SSL certs on multiple SQL servers will not be a viable option.

Read on to see what Jamie’s solution to the problem is.

Comments closed

Row-Level Security Setup in SQL Server

Published 2023-09-05 by Kevin Feasel

Ben Johnston configures row-level security in SQL Server:

Implementing RLS is as much a business endeavour as technical. Without a business case to implement RLS, there is no reason for the extra effort and testing involved. This is where driving out business requirements and making sure the solution fits the problem is important. Non-technical members of the team or business partners likely won’t know what RLS is or if it should be used. Differential access to the same data, replacing systems (or proposed systems) with multiple reports based on user groups, and multi-tenant access are possible indicators that RLS may be a useful tool. There are always multiple ways to solve a problem. If RLS would simplify the design and make it more robust, that’s when I start to seriously consider it for a design. It does help if the business is aware of RLS and have used it in other projects or databases, but having the business essentially design the system is dangerous too. Use all of the information available during planning sessions and design the system that best fits the need of the business and the skills of the technical team.

Read the whole thing. I’m particularly interested in this series, as I’ve liked row-level security and didn’t find any trivial workarounds or exploits, but also hate how slow it is once you’re dealing with large datasets.

Comments closed

The Concept of Schema in Relational Databases

Published 2023-08-30 by Kevin Feasel

Adron Hall explains how different relational database management systems describe schemas:

From the viewpoint of someone familiar with the general idea of a schema, it can indeed seem unusual that databases like SQL Server, Oracle, MariaDB/MySQL, and PostgreSQL each interpret and implement schemas in slightly (or sometimes, vastly) different ways. While the core idea behind a schema as a structured container or namespace for database objects remains somewhat consistent, the exact nature, utility, and behavior of schemas vary across these systems.

Read on for an overview of these for four products, as well as what the ANSI standard indicates.

Comments closed

Managing Security Roles for Hierarchical Organizations in Power BI

Published 2023-08-29 by Kevin Feasel

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

Published 2023-08-25 by Kevin Feasel

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

Published 2023-08-25 by Kevin Feasel

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

Published 2023-08-23 by Kevin Feasel

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

Published 2023-08-22 by Kevin Feasel

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

Published 2023-08-22 by Kevin Feasel

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