Category: Security

Solomon Rutzky shows how to use module signing to grant granular permissions to users:

Scenario: We want to allow one or more Users and/or Database Roles to be able to truncate certain Tables, but not all Tables. We certainly do not want to allow anyone the ability to make structural changes to the Table.

Also, it is likely that, over time, at least one more Tables will be added that the User(s) and/or Role(s) should be able to truncate, and less likely, though not impossible, that one or more tables that they should be able to truncate now might be removed.

Truncation is a great example of the kind of right you’d want behind a signed stored procedure, as the level of right necessary to truncate a table is absurd:  practically full control of the table.  Module signing is something that I wish more DBAs knew and implemented.

Kenneth Fisher notes a behavior change for SQLCMD with SQL Server 2014 and later:

This error is seen when using a Windows Authenticated id and NTLM or an untrusted Kerberos connection. From what I understand, with Kerberos the client (SQL in this case) gets a ticket containing your credentials, it can then pass this ticket on to other servers that trust it to have authenticated you to be sure you are who you say you are. Note the mention of trust. This means that even if you are using Kerberos if the two servers aren’t in a trusted relationship then you will still see the error. And NTLM doesn’t have the option to do this at all (again, not sure why).

At this point it’s important to note that this is not a SQL Server error. You will see this error in other places where you bounce across multiple machines and aren’t using Kerberos with a trusted relationship. I’ve seen it when trying to access files before (it was a weird situation admittedly).

Read on to learn about the ramifications of this behavioral change and how you can prevent double-hop problems when running newer versions of SQLCMD.

Wayne Sheffield has a post on using gMSA with SQL Server:

A gMSA is a sMSA [standalone managed service account] that can be used across multiple devices, and where the Active Directory (AD) controls the password. PowerShell is used to configure a gMSA on the AD. The specific computers that it is allowed to be used on is configured using some more PowerShell commands. The AD will automatically update the password for the gMSA at the specified interval – without requiring a restart of the service! Because the AD automatically manages the password, nobody knows what the password is.

Not all services support a gMSA – but SQL Server does. During a SQL Server installation you can specify the gMSA account. The SQL Server Configuration Manager (SSCM) tool can be used to change an existing SQL Server instance to use a gMSA. After entering the gMSA account you simply do not enter a password. The server automatically retrieves the password from the AD.

This is a nice way of improving service account security in a scenario where, for example, you can’t or don’t want to use virtual service accounts.

Anthony Nocentino shows how to use Powershell to distribute SSH keys to remote machines:

Folks in the Linux world are used to moving SSH keys to and from systems enabling password-less authentication using SSH user keys. Let’s take a minute to look at what it takes to use PowerShell to distribute SSH user keys to remote systems.

In the OpenSSH package there’s a command ssh-copy-id which is a bash script that copies a user’s public key to a remote systems. There’s a little intelligence in the script to set things up properly on the remote system for password-less key based authentication. If the appropriate directory and key file aren’t set up, ssh-copy-idwill create the directory and key file with the correct permissions on remote system. As far as I can tell, ssh-copy-id has not been implemented in the Win32-OpenSSH port. So that leaves us with implementing this functionality ourselves, in PowerShell.

Read on to see how he does it.

Solomon Rutzky shows how you can use certificate-based module signing to grant users fine-grained permissions:

-- 1) Create the Certificate:
CREATE CERTIFICATE [Permission$ViewServerState] ENCRYPTION BY PASSWORD = 'UseBetterPassword!' WITH SUBJECT = 'VIEW SERVER STATE permission', EXPIRY_DATE = '2099-12-31';
/*
The password is used to protect only the Private Key, not the Public
Key or the Certificate in general.
The default expiration date is 1 year from the date the Certificate is
created. However, in many situations, such as with Module Signing, the
expiration date is actually ignored. Still, I prefer to set the
expiration date to 2099.
*/

The DBA handling this server in the year 2100 is going to be really upset.  Certificate signing works wonders in conjunction with stored procedures, as you can grant procedures all kinds of rights needed for your application but users cannot run ad hoc queries expecting those same rights.

The folks at Instaclustr have done some analysis on how Meltdown has affected Cassandra performance on AWS:

In our Security Advisory published 8 January, we advised of up to 20% increase in CPU utilization and small increase in latency across managed clusters in AWS and GCP following the rollout of the patches to the cloud provider hypervisors. We have since observed a reversal of this impact in the weeks following the initial announcements. That is, these effects disappeared when further AWS and GCP patches were rolled out by the cloud providers.

We assessed the risk of the vulnerabilities to our environment as Low. Our clusters run as single tenant and customer access is limited to the application layer.  If a user were able to exploit either of the vulnerabilities they could only gain access to their own information.

In short, they saw a change early on, but subsequent patching has removed that performance degradation.  Read the whole thing for more details.

Daniel Hutmacher shows how to roll your own data masking with SQL Server:

Dynamic data masking is a neat new feature in recent SQL Server versions that allows you to protect sensitive information from non-privileged users by masking it. But using a brute-force guessing attack, even a non-privileged user can guess the contents of a masked column. And if you’re on SQL Server 2014 or earlier, you won’t have the option of using data masking at all.

Read on to see how you can bypass dynamic data masking, and for an alternative approach that uses SQL Server column-level security instead.

Click through for the demo.