Press "Enter" to skip to content

Category: Security

Feature Toggles for Databases

Published 2024-02-20 by Kevin Feasel

Phil Factor discusses feature toggles for the database:

In software development the concept of feature toggles are used to selectively turn on and off features. They are, for example, used to restrict some newly introduced features to a select group to see how these features work. While this concept has been long used for user-facing application code, it is also a practice that is useful for database code.

In databases, feature toggles are used for several loosely related purposes. One common use is to separate, or decouple, rollout of database features from deployment of new versions of the application code. This allows developers to release software faster and with less risk.

The idea of using roles to determine whether a given user should see a particular feature makes sense, though I was a bit confused at first because I was thinking about feature flags: behavioral changes you slowly turn on for groups of people. This is a bit different.

Comments closed

Troubleshooting a Problem with sp_send_dbmail

Published 2024-02-16 by Kevin Feasel

Shane O’Neill applies the Sgt. Schultz defense:

I don’t like posting about issues unless I fundamentally understand the root cause. That’s not the case here. A lot of the explanation here will be hand-waving while spouting “here be dragons, and giants, and three-headed dogs”, but I know enough to give you the gist of the issue.

Click through for a fun story about Kerberos and behavioral changes after absolutely nothing happened.

Comments closed

Identifying Old OLEDB and ODBC Drivers on Machines

Published 2024-02-15 by Kevin Feasel

Lucas Kartawidjaja goes on a quest:

The vulnerabilities are affecting Microsoft ODBC Driver 17 and 18, as well as OLE DB Driver 18 and 19. For more information and also download location for the security update/ hotfix can be found on the following page: Update: Hotfixes released for ODBC and OLE DB drivers for SQL Server

We do an automated security scanning tool that would flag the systems (servers, desktops, latptops, etc.) that haven’t been patched. So we can quickly identify the systems that need to be patch and patched those systems quickly.

For this post, I was wondering if there is a quick way to identify Microsoft ODBC and OLE DB drivers that are being installed on the systems. 

Click through to see what Lucas came up with.

Comments closed

Function App Caching of Key Vault Secrets

Published 2024-02-05 by Kevin Feasel

Koen Verbeeck runs into an odd issue:

In the PowerShell function, this application setting is retrieved as an environment variable so it can authenticate with the Graph API using the app registation. So far so good, except that the secret of this app registration expires after 1 year (some time ago you could configure an expiration date for in the future, but it seems this isn’t possible anymore). The Azure Function started crashing with a 401 (Unauthorized) error.

Read on to see what Koen tried, what eventually fixed it, and a pair of updates to the post.

Comments closed

Connect to Azure SQL Database via Azure Entra ID Service Principal

Published 2024-02-05 by Kevin Feasel

Jaime Garcia de Alba becomes the machine:

In this guide, I am going to outline the steps on how to connect to an Azure SQL database using Entra SPN with tools such as SSMS and PowerShell. This demo covers detailed steps for using an existing user when the token is received correctly. Additionally, the steps cover creating a new user from scratch in case there are issues with the existing user.

I’ve used service principals and managed identities in the past in application code, but it wasn’t until this post that I learned you could also use them directly to connect to an instance.

Comments closed

Linked Servers from SQL MI using Azure Entra ID

Published 2024-01-31 by Kevin Feasel

Luis Aranda has the first of a two-part series:

Lately, we have seen some customers interested on the options available to use linked servers from Managed Instance and using Entra Authentication (formerly Azure Active Directory). It is certainly possible to create Linked Servers on SQL Managed instance (SQL MI) to connect to other PaaS databases such as other SQL MIs, Azure SQL Databases or Synapse databases using Entra Authentication.

Click through to see how you can do this using a managed identity. In the next article, Luis promises to show us how to do it with pass-through authentication, so you use your credentials instead of the managed identity’s credentials to access the remote server.

Comments closed

Firewalls and TLS in SQL Server on Linux

Published 2024-01-24 by Kevin Feasel

I have a new video out:

In this video, we harden our SQL Server instance in two ways: by using a firewall to limit inbound traffic, and by using a certificate to force encrypted connections to SQL Server.

This was a video I enjoyed creating. It also shows the progress of SQL Server security: go back to 2005 (pre-SP1) and even SQL authentication over TDS was unencrypted by default. They fixed it so that the authentication would use a self-signed cert but the data you’d get back from query results was unencrypted. Nowadays, encryption is easy (if you’re okay with a self-signed cert) and some future version of SQL Server will make it mandatory.

Comments closed

Granting Users Access to Create Fabric Items

Published 2024-01-17 by Kevin Feasel

Gilbert Quevauvilliers is in a giving mood:

I was recently working with a customer where I was showing them the awesome new features of Microsoft fabric. I then created a workspace and attempted to grant the individual users access to the workspace to create fabric items or workloads.

When the users went into the app workspace with the fabric settings, enabled, the users could not create any workloads.

Read on to see what the problem was and how you can resolve it.

Comments closed

Adding a Service Principal to a Power BI Workspace

Published 2024-01-17 by Kevin Feasel

Marc Lelijveld reads the manual, disagrees with it, and stubbornly sets out to prove it right:

Today, I run into an issue by adding a service principal to a workspace. To my experience so far, this should be straight forward and a matter of adding with the right permissions in the workspace settings. However, for some reason I couldn’t get it to work. In this blog I will elaborate on what caused this issue and which tenant setting influences this.

Click through for the story behind this and the resolution to Marc’s issue.

Comments closed

Managing Azure Data Factory IP Ranges for Azure Firewalls

Published 2024-01-03 by Kevin Feasel

Meagan Longoria has a script for us:

While a private endpoint and vNets are preferred, sometimes we need to configure Azure SQL Database or Azure Storage to allow use of public endpoints. In that case, an IP-based firewall is used to prevent traffic from unauthorized locations. But Azure Data Factory’s Azure Integration Runtimes do not have a single static IP. So how do we keep the firewall updated so that ADF can access these resources?

One option is to run everything through a self-hosted integration runtime, which can have a static public IP. But that also means you have to install (and keep updated) a Java SDK if you are converting data to Parquet or ORC files.

Another option is to obtain the IP range list published by Microsoft and update the firewall. That is what I did for a recent project.

Read on to learn more about the latter option, including a Powershell script to do the work.

Comments closed