Category: Security

Denny Cherry explains how to integrate Azure VMs with your existing Active Directory domain:

The first step is to put some domain controllers in Azure.  To do this, you’ll need a site to site VPN between Azure and your on-premises environment.  If you have multiple on-premises sites, then you’ll want to create a VPN between Azure and all your on-premises environments.  If your Azure environment is hosted in multiple regions, then you’ll want to create a mesh network when each on-premises site in VPNed into all of your vNets.  You’ll probably also want your vNets VPNed to each other (Peering of your networks between sites may be an option as well depending on how you’ve set things up).  If you have an extremely large number of users at your site, then Express Route might be something worth looking into instead of a site to site VPN.

Click through for the full process.

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.