Press "Enter" to skip to content

Category: Security

The Value of the keyring Package

Maelle Salmon looks at a good package in R

Does your package need the user to provide secrets, like API tokens, to work? Have you considered telling your package users about the keyring package, or even forcing them to use it?

The keyring package maintained by Gábor Csárdi is a package that accesses the system credential store from R: each operating system has a special place for storing secrets securely, that keyring knows how to interact with. The credential store can hold several keyrings, each keyring can be protected by a specific password and can hold several keys which are the secrets.

Read on for several advantages of using the keyring package.

Comments closed

Row-Level Security and USERELATIONSHIP() with Inactive Relationships

Marco Russo and Alberto Ferrari have a public service announcement:

USERELATIONSHIP is a very common and helpful function, used whenever there are multiple relationships between tables and developers need to decide which relationship to use. However, in some scenarios, this common function raises an annoying error:

The UseRelationship() and CrossFilter() functions may not be used when querying ‘Sales’ because it is constrained by row-level security.

As with all the error messages, this requires some understanding and further explanation. Moreover, a workaround is straightforward to find. However, the workaround has some subtle restrictions that need to be well understood.

Read on to learn more.

Comments closed

Feature Toggles for Databases

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

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

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

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

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

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

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

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