A Fine Slice Of SQL Server
We are pleased to announce the GA release of enhanced patching capabilities for SQL Server on Azure VMs using Azure Update Manager. When you register your SQL Server on Azure VM with the SQL IaaS Agent extension, you unlock a number of feature benefits, including patch management at scale with Azure Update Manager.
Read on to see what this does, how you can set it up, and how you can migrate from the SQL Server IaaS agent extension’s automated patching service.
Comments closedVlad 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 closedMuhammad 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 closedCraig 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.
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 closedChad 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 closedAndy Brownsword does things right, after a fashion:
When building dynamic SQL, safety is crucial. As we saw last week, we have the
QUOTENAMEfunction 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 closedJohn 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 closedAravind 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 closedAndy Brownsword uses the QUOTENAME() function:
I’m a big fan of dynamic SQL in the right conditions. One key to crafting safe dynamic query of the use of the
QUOTENAMEfunction.
My personal preference is to use exec sp_executesql for parameterization instead, simply because it’s a lot harder to mess up.