Not Just the How of AD with Linux VM/SQL 2019, but the WHY

Azure Directory is available with Linux SQL Server 2019 in Preview and as I was setting it up in my Azure environment on a Linux Red Hat 7.3 VM, I was, as many are, happy that they list the commands for the Azure CLI to set up authentication with Azure Directory, but was concerned, that with so many new to Linux, that they didn’t describe in the steps WHY we were running certain commands or setting best practices around Linux database server design.

The setup expects that you already have a Linux VM and SQL 2019 already up and running. The first step they go into is role assignment for the AD login, setting the AD login up as the VM Administrator.

az vm extension set \     
--publisher Microsoft.Azure.ActiveDirectory.LinuxSSH \     
--name AADLoginForLinux \     
--resource-group myResourceGroup \     
--vm-name myVM

The above command expects you to replace “myResourceGroup” and “myVM” with the correct values for the resource group and Linux VM.

The next step then proceeds to run more AZ commands, but it also expects the DBA or administrator to be familiar with Linux and Linux scripting techniques after NOT using them in the first command. I find this assumption leaving us all open to human error:

Let’s talk about variables in an OS environment. In Windows, we can set these at the command line, both at the session or consistently as part of login scripts. We can set this in the GUI in the My Computer, advanced settings, environment variables. This also can be done for Linux, either at the command line, via login scripts referred to as run commands, (bash_rc) or profiles owned by individual logins.

To set these, you simple enter a unique word and assign it a value. Some flavors of Linux require you to “export” or “set” the variable, not just state it-

export <variable> = <value>

set <variable> = <value>

You can then verify the variable is set by using the ECHO command and calling the variable with a $ sign before it:

echo $<variable>

You can just as easily remove them by the “unset” command, by overwriting them with new values for the variable keyword, logging out or with other profile/run command scripts.

We can use the set and echo command process to make more sense of what is being performed as part of the Linux AD authentication instructions to the VM, too:

username=$(az account show --query user.name --output tsv) 
 
echo $username 
kgorman@microsoft.com

vm=$(az vm show --resource-group SQL2019_grp --name SQL2019RH1 --query id -o tsv)

echo $vm
/subscriptions/00aa000e-xx00x-xxx-x00-x00x-xx00x/resourceGroups/SQL2019_grp/providers/Microsoft.Compute/virtualMachines/SQL2019RH1

Now we can use these variables as part of the third command and know what is being passed into the full command, knowing that we were able to dynamically push Azure CLI commands into the final command:

 az role assignment create --role "Virtual Machine Administrator Login"     --assignee $username --scope $vm  

The output from the command shows that it pulled the original variables into the third command to complete the requirements to build the role assignment. I did a mass replace to protect the IDs, but you get the idea how to verify that the values are

{
  "canDelegate": null,
  "id": "/subscriptions/00aa000e-xx00x-xxx-x00-x00x-xx00x/resourceGroups/SQL2019_grp/providers/Microsoft.Compute/virtualMachines/SQL2019RH1/providers/Microsoft.Authorization/roleAssignments/
0e00e0b0-bf00-0000-b000-000d0e0fe0d0",
  "name": "0e00e0b0-bf00-0000-b000-000d0e0fe0d0",
  "principalId": "d0c00cba-0c0b-00f0-b00e-d00000000000",
  "resourceGroup": "SQL2019_grp",
  "roleDefinitionId": "/subscriptions/
00aa000e-xx00x-xxx-x00-x00x-xx00x/providers/Microsoft.Authorization/roleDefinitions/0c0000c0-00e0-0000-0000-ea0c00e000e0",
  "scope": "/subscriptions/
00aa000e-xx00x-xxx-x00-x00x-xx00x/resourceGroups/SQL2019_grp/providers/Microsoft.Compute/virtualMachines/SQL2019RH1",
  "type": "Microsoft.Authorization/roleAssignments"

A good update for this doc would be to first explain how and why we set variables and then use the default configurations for the session to require less commands having to be entered:

username=$(az account show --query user.name --output tsv) 
az configure --defaults group=SQL2019_grp
az configure --defaults vm=SQL2019RH1
vm=$(az vm show --query id -o tsv)

az vm extension set \     
--publisher Microsoft.Azure.ActiveDirectory.LinuxSSH \     
--name AADLoginForLinux 


"name": "AADLoginForLinux",
   "protectedSettings": null,
   "provisioningState": "Succeeded",
   "publisher": "Microsoft.Azure.ActiveDirectory.LinuxSSH",
   "resourceGroup": "SQL2019_grp",
   "settings": null,
   "tags": null,
   "type": "Microsoft.Compute/virtualMachines/extensions",
   "typeHandlerVersion": "1.0",
   "virtualMachineExtensionType": "AADLoginForLinux"

And in the end, we can check our work to verify we’ve done everything correctly:

az vm list --query '[].{Name:name, OS:storageProfile.osDisk.osType, Admin:osProfile.adminUsername}' --output table

You should see your new AD login now in the list.

This doesn’t take you too far into Linux scripting, but hopefully it answers the WHY you are running the commands that are displayed in the instructions and the order that you’re running them in.