Press "Enter" to skip to content

Category: Security

Migrating Always Encrypted to a Secure Enclave

Pieter Vanhove has an enclave, which is sort of like a Bat-cave:

Always Encrypted is a feature of Azure SQL and SQL Server that allows you to encrypt sensitive data in your database. The data is never exposed in plaintext to the database engine, or anyone who has access to it.

However, Always Encrypted has some limitations. For example, you cannot perform any computations or operations on the encrypted data, such as sorting, filtering, or indexing. Secondly, an initial encryption must be done on the application side which can be time consuming on a large set of data.

That’s where Always Encrypted with secure enclaves comes in.

Read on to see what secure enclaves give you, as well as how you can enable it and what changes your application might require.

Leave a Comment

Analyzing TDS 8.0 Handshake Packets with Wireshark

Matt Changchien fires up the packet capture tool:

The main difference between TLS1.2 and TLS1.3 is that TLS 1.3 reduces the number of round trips from two to one during the handshake phase, making it faster and more secure than TLS 1.2. The server hello packet containing server certificate is encrypted and one Round Trip Time (1-RTT) resumption is discontinued, and replaced with 0-RTT resumption based on client key share. Added security of TLS 1.3 comes from discontinuing certain cyphers and algorithms. (TLS 1.3 support – SQL Server | Microsoft Learn)

Click through to see what the differences look like between TLS 1.2 and TLS 1.3, as well as the specific example of Azure SQL Database.

Leave a Comment

Avoid the securityadmin Role

Etienne Lopes recommends against a particular SQL Server role:

I usually avoid using the two “radical” words: “never” and “always” but regarding the membership need for the “securityadmin” server role since SQL 2005/2008 onward, I find it hard to come up with a good reason why it should ever be used, especially considering the security risks involved. A few weeks ago, while checking permissions for some logins in a (critical) SQL Server 2019 instance, I came across some really worrying situations, among which I found this one… again! Although it wasn’t the worst I found there (the worst was to bad to even mention here) I felt impelled to write about this one, maybe because the risks may not be so obvious or are somewhat concealed… Well, let’s bring them to light! 

Cosnidering that securityadmin can increase its own rights to sysadmin, you’d might as well just grant that login sysadmin.

Leave a Comment

SSMS 20 and Default Security

Brent Ozar notes a change:

SQL Server Management Studio 20 Preview 1 is out, and the new connection dialog has a big change:

When you click Connect, you’re likely going to get an error:

Read on for the quick-and-easy solution, which brings behavior back to the pre-SSMS 20 default, as well as the long-term solution to prevent it from being an issue at all.

This brings SSMS in line with Azure Data Studio, which has defaulted to requiring certificates for quite some time. Note that you will need to select “Trust server certificate” if you are using a self-signed cert, though self-signed certs remove one of the two benefits of using certificates in the first place. The first is that certificates allow for encrypting the Tabular Data Stream (TDS) packets SQL Server sends over the network. Self-signed certs do just as good a job of that task as certificates you get from a trusted authority.

The second use case of certificates, however, is ensuring that this is definitely the machine and service you intend to connect to. If an attacker takes over the machine and swaps out the certificate with their own, your client should panic a bit because that’s your early-warning indicator that something is wrong.

Leave a Comment

Strict Encryption in SSMS 20

Erin Stellato shares an update:

SSMS 20 is the first major version of SSMS that supports Strict encryption and TLS 1.3, thanks to the migration to Microsoft.Data.SqlClient (MDS) 5.1.4.  MDS is the data access library used by SSMS 19 and higher, as well as other SQL Server tools.

Read on for a quick primer on terminology, as well as what it means to force strict encryption. I’m not sure how quickly companies will jump on this, especially given the features that don’t support strict encryption yet, such as availability groups, replication, SQL Server Agent, database mail, linked servers, and PolyBase’s connector to SQL Server.

Leave a Comment

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.

Leave a Comment

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.

Leave a Comment

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