Press "Enter" to skip to content

Category: Security

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

Private Endpoints and Azure SQL Managed Instance

Published 2023-08-18 by Kevin Feasel

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

Published 2023-08-17 by Kevin Feasel

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

Published 2023-08-04 by Kevin Feasel

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

Published 2023-08-02 by Kevin Feasel

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

Secrets Management in Docker Swarm

Published 2023-08-02 by Kevin Feasel

Andrew Pruski has a secret:

In a previous post I went through how to run SQL Server in Docker Swarm and in this post I want to continue delving into the Docker Swarm platform by looking at secrets.

Like Kubernetes, Docker Swarm has functionality to store sensitive data in secrets. When running SQL Server in containers we usually have to manually type out the environment variable for the MSSQL_SA_PASSWORD which isn’t great if we want to store that code in a public repository so being able to obfuscate that password is great!

Read on for a failed first attempt, followed by analysis and then success.

Comments closed

Mitigating Dynamic Data Masking Side-Channel Attacks

Published 2023-07-31 by Kevin Feasel

Ben Johnston wraps up a series on dynamic data masking:

This is the fifth and final part of this series on SQL Server Dynamic Data Masking. The first part in the series was a brief introduction to dynamic data masking, completing solutions, and use cases. The second part covered setting up masking and some examples. The third and fourth sections explored side channel attacks against dynamic data masking.

This final part covers mitigations to side channel attacks, additional architectural considerations and an analysis of the overall solution.

Throughout the entire series, Ben has done a good job of laying out exactly what dynamic data masking is good for—and what it isn’t good for. I tend to harp a lot on the latter but Ben keeps a reasonable approach throughout this series.

Comments closed