Press "Enter" to skip to content

Category: Security

Parameterizing Dynamic SQL the Right Way

Andy Brownsword does things right, after a fashion:

When building dynamic SQL, safety is crucial. As we saw last week, we have the QUOTENAME function which can help when referencing object names.

Another aspect to consider is use of parameters. Integrating them incorrectly can leave us vulnerable to SQL injection attacks. Let’s take a look at how to handle them the wrong way, followed by the right way.

Why have the wrong way and then the right way? The answer is simple. It is a truth universally acknowledged, that a post with a Gallant must be in want of a Goofus.

Leave a Comment

Using Key Vault in SQL Server on Linux

Aravind Mahadevan shares information on a new bit of functionality:

We’re excited to announce that Extensible Key Management (EKM) using Azure Key Vault in SQL Server on Linux is now generally available from SQL Server 2022 CU12 onwards, which allows you to manage encryption keys outside of SQL Server using Azure Key Vaults.

In this blog post, we’ll explore how to leverage Azure Key Vault as an EKM provider for SQL Server on Linux.

Read on to see how to set this up.

Leave a Comment

Support for Iterators in SQL Server 2022 Password Validation

Michael Howard has an update for us:

We all know that as security threats evolve, we must update our defenses to mitigate newer threats. Over the last few months, some customers have asked us to strengthen the way we secure passwords in SQL Server. The most often-cited reference by customers is to comply with NIST SP 800-63b.

Currently supported versions of SQL Server and Azure SQL DB use a SHA-512 hash with a 32-bit random and unique salt. It is statistically infeasible for an attacker to deduce the password knowing just the hash and the salt. It is considerably easier for an attacker to hunt for insecure storage of database connection strings that contain database credentials than it is to break the password verifier (also called a password authenticator) used by SQL Server and Azure SQL DB. But that’s a discussion for another day and is the main reason we highly recommend using Entra ID authentication rather than using uid/pwd-based connections because Entra ID authentication manages credentials and supports access policies.

Michael gives you an idea as well of why iterators are valuable and how you can tell if your SQL authenticated accounts are using this new setup.

I’m curious how much of an effect this will have on login time. Typically, using PBKDF (or PBKDF2) does increase login time by a couple hundred milliseconds—which is the entire point of the exercise, ensuring that an attacker cannot feasibly brute force passwords in reasonable time—so I would not expect the difference to be too noticeable. For a really busy system, however, I could see some small impact.

Leave a Comment

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.

Comments closed

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.

Comments closed