Press "Enter" to skip to content

Category: Security

sqlcmd and Self-Signed Certificates

Published 2024-04-17 by Kevin Feasel

Vlad Drumea is a trusting fellow:

This post covers a few ways to fix the SSL certificate error 1416F086 returned by sqlcmd on Linux when connecting to SQL Server.

If you’re looking for ways to fix the Windows equivalent of this error when using dbatools, check out this blog post.

It’s interesting how much controversy we’re seeing around tools like sqlcmd and (especially) SQL Server Management Studio defaulting to mandatory encryption. Having signed and valid certificates is a critical part of validating that this SQL Server is actually the one you think it is, and no intermediary attacker has swapped the certificate out with a phony one that allows the attacker to spy on your interactions.

I can understand people who are just messing around with SQL Server locally to experience pain on this, but the sheer number of actual companies—including companies using Central Management Servers, which implies having multiple SQL Server instances—with garbage-tier self-signed certificates is discouragingly high.

By the way, I’m aiming none of my rant at Vlad or this post. It’s just top-of-mind and this was as good a vehicle for rant delivery as I could find.

Comments closed

Roles and Privileges in PostgreSQL

Published 2024-04-08 by Kevin Feasel

Muhammad Ali explains quite a few of the security terms in PostgreSQL:

PostgreSQL has a fine grained system for for managing user roles and privileges. This helps admins decide who can access certain data and what they’re allowed to do with it. It’s about managing permissions, where you can create different roles and roles can be a member of other roles.

In this blog, we will dive into PostgreSQL user roles and permissions, covering databases, schemas, and other objects level privileges, following a FAQ format.

Most of this is similar to security in SQL Server, though there are some differences to watch out for.

Comments closed

Row-Level Security in Postgres

Published 2024-04-04 by Kevin Feasel

Craig Kerstiens implements row-level security:

Row-level security (RLS) in Postgres is a feature that allows you to control which rows a user is allowed to access in a particular table. It enables you to define security policies at the row level based on certain conditions, such as user roles or specific attributes in the data. Most commonly this is used to limit access based on the database user connecting, but it can also be handy to ensure data safety for multi-tenant applications.

Read on to see how you can implement it. It’s pretty similar to the way we do it in SQL Server, though SQL Server has a few parts of policy evaluation that are more explicit versus putting the execution function code itself in a USING clause.

Comments closed

Maintaining Dynamic IP Rules for Azure Network Security Groups

Published 2024-04-02 by Kevin Feasel

Daniel Hutmacher shares a couple scripts:

Recently, my home ISP has started changing my public IP address. This causes me some headache because I have a couple of Azure Network Security Group rules (think of them as firewall rules) that specifically allow my home IP access to all of my Azure resources. When my home IP changes, those rules have to be updated accordingly.

So I made a PowerShell-based solution to automatically maintain them.

Read on for the process.

Comments closed

SSMS 20 and Mandatory Connection Security

Published 2024-03-29 by Kevin Feasel

Chad Callihan hits an annoyance:

I tried to run a new query for a CMS but the query window opened as disconnected. If I selected one server out of the group and tried to open a new query, I received an error that “A connection was successfully established with the server, but then an error occurred during the login process.”

That can get really annoying if you have a few hundred instances in your Central Management Server. They’d all go away if you set up certificates for the servers, but until then, it would be a major annoyance.

Comments closed

Parameterizing Dynamic SQL the Right Way

Published 2024-03-27 by Kevin Feasel

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.

Comments closed

An Example of an MD5 Hash Collision

Published 2024-03-22 by Kevin Feasel

John Cook shares an example of a hash collision:

Marc Stevens gave an example of two alphanumeric strings that differ in only one byte that have the same MD5 hash value. It may seem like beating a dead horse to demonstrate weaknesses in MD5, but it’s instructive to study the flaws of broken methods. And despite the fact that MD5 has been broken for years, lawyers still use it.

Click through for the example.

Comments closed

Using Key Vault in SQL Server on Linux

Published 2024-03-21 by Kevin Feasel

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.

Comments closed

Support for Iterators in SQL Server 2022 Password Validation

Published 2024-03-19 by Kevin Feasel

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.

Comments closed