Infrastructure at your Service

Nathan Courtine

Automate CNOs and VCOs for SQL Server AAG

By February 26, 2021 SQL Server 2 Comments

During the installation of a new SQL Server environment in a Project, we wanted to automate the whole process deployment and configuration when installing a new SQL Server Always On Availability Group (AAG).
This installation requires to prestage cluster computer objects in Active Directory Domain Services, called Cluster Name Objects (CNOs) and Virutal Computer Objects (VCOs).
For more information on the prestage process, please read this Microsoft article.

In this blog, we will see how to automate the procedure through PowerShell scripts. ActiveDirectory module is required.

CNO Creation

First, you need an account with the approriate permissions to create Objects in a specific OU of the domain.
With this account, you can create the CNO object as follows:

# To configure following your needs
$Ou1='CNO-VCO';
$Ou2='MSSQL';
$DC1='dbi';
$DC2='test';
$ClusterName='CLST-PRD1';
$ClusterNameFQDN="$(ClusterName).$($DC1).$($DC2)";

# Test if the CNO exists
If (-not (Test-path "AD:CN=$($ClusterName),OU=$($Ou1),OU=$($Ou2),DC=$($DC1),DC=$($DC2)")){
	# Create CNO for Windows Cluster
	New-ADComputer -Name "$ClusterName" `
          -SamAccountName "$ClusterName" `
            -Path "OU=$($Ou1),OU=$($Ou2),DC=$($DC1),DC=$($DC2)" `
              -Description "Failover cluster virtual network name account" `
                 -Enabled $false -DNSHostName $ClusterNameFQDN;

	# Wait for AD synchronization
	Start-Sleep -Seconds 20;
};

Once the CNO created, we have to configure the correct permissions. We have to give, to the account we will use for the creation of the Windows Server Failover Cluster (WSFC), the correct Access Control Lists (ACLs) to be able to claim the object during the WSFC installation process.

# Group Account use for the installation
$GroupAccount='MSSQL-Admins';

# Retrieve existing ACL on the CNO
$acl = Get-Acl "AD:$((Get-ADComputer -Identity $ClusterName).DistinguishedName)";

# Create a new access rule which will give to the installation account Full Control on the object
$identity = ( Get-ADGroup -Identity $GroupAccount).SID;
$adRights = [System.DirectoryServices.ActiveDirectoryRights] "GenericAll";
$type = [System.Security.AccessControl.AccessControlType] "Allow";
$inheritanceType = [System.DirectoryServices.ActiveDirectorySecurityInheritance] "All";
$ACE = New-Object System.DirectoryServices.ActiveDirectoryAccessRule $identity,$adRights,$type,$inheritanceType;

# Add the new acess rule to the existing ACL, then set the ACL on the CNO to save the changes
$acl.AddAccessRule($ace); 
Set-acl -aclobject $acl "AD:$((Get-ADComputer -Identity $ClusterName).DistinguishedName)";

Here, our CNO is created disabled with the correct permissions we require for the installation.
We need to create its DNS entry, and give to the CNO read/write permissions on it.

# Specify the IP address the Cluster we will use
$IPAddress='192.168.0.2';

# Computer Name of the AD / DNS server name
$ADServer = 'DC01';

Add-DnsServerResourceRecordA -ComputerName $ADServer -Name $ClusterName -ZoneName "$($DC1).$($DC2)" -IPv4Address $IPAddress;

#Retrieve ACl for DNS Record
$acl = Get-Acl "AD:$((Get-DnsServerResourceRecord -ComputerName $ADServer -ZoneName '$($DC1).$($DC2)' -Name $ClusterName).DistinguishedName)";

#Retrive SID Identity for CNO to update in ACL
$identity = (Get-ADComputer -Identity $ClusterName).SID;

# Contruct ACE for Generic Read
$adRights = [System.DirectoryServices.ActiveDirectoryRights] "GenericRead";
$type = [System.Security.AccessControl.AccessControlType] "Allow";
$inheritanceType = [System.DirectoryServices.ActiveDirectorySecurityInheritance] "All";
$ACE = New-Object System.DirectoryServices.ActiveDirectoryAccessRule $identity,$adRights,$type,$inheritanceType;
$acl.AddAccessRule($ace);

# Contruct ACE for Generic Write
$adRights = [System.DirectoryServices.ActiveDirectoryRights] "GenericWrite";
$type = [System.Security.AccessControl.AccessControlType] "Allow";
$inheritanceType = [System.DirectoryServices.ActiveDirectorySecurityInheritance] "All";
$ACE = New-Object System.DirectoryServices.ActiveDirectoryAccessRule $identity,$adRights,$type,$inheritanceType;
$acl.AddAccessRule($ace);

#Update ACL for DNS Record of the CNO
Set-acl -aclobject $acl "AD:$((Get-DnsServerResourceRecord  -ComputerName $ADServer  -ZoneName "$($DC1).$($DC2)" -Name $ClusterName).DistinguishedName)";

At this step, the installation process will be able to claim the CNO while creating the new Cluster.
The prestage for the CNO object is completed.

VCO Creation

The creation of the VCO, used by the AAG for its Listener, is quite similar.
As there is no additional complexity compared to the creation of the CNO, here is the whole code:

# To configure following your needs
$Ou1='CNO-VCO';
$Ou2='MSSQL';
$DC1='dbi';
$DC2='test';
$ListenerName='LSTN-PRD1';
$ListenerNameFQDN="$(ListenerName).$($DC1).$($DC2)";
$IPAddress='192.168.0.3';
$ADServer = 'DC01';

If (-not (Test-path "AD:CN=$($ListenerName),OU=$($Ou1),OU=$($Ou2),DC=$($DC1),DC=$($DC2)")){
	# Create VCO for AAG
	New-ADComputer -Name "$ListenerName" -SamAccountName "$ListenerName" -Path "OU=$($Ou1),OU=$($Ou2),DC=$($DC1),DC=$($DC2)" -Description "AlwaysOn Availability Group Listener Account" -Enabled $false -DNSHostName $ListenerNameFQN;

	# Wait for AD synchronization
	Start-Sleep -Seconds 20;
};

# Retrieve existing ACL on the VCO
$acl = Get-Acl "AD:$((Get-ADComputer -Identity $ListenerName).DistinguishedName)"; `

# Create a new access rule which will give CNO account Full Control on the object
$identity = (Get-ADComputer -Identity $ClusterName).SID;
$adRights = [System.DirectoryServices.ActiveDirectoryRights] "GenericAll";
$type = [System.Security.AccessControl.AccessControlType] "Allow";
$inheritanceType = [System.DirectoryServices.ActiveDirectorySecurityInheritance] "All";
$ACE = New-Object System.DirectoryServices.ActiveDirectoryAccessRule $identity,$adRights,$type,$inheritanceType;

# Add the ACE to the ACL, then set the ACL to save the changes
$acl.AddAccessRule($ace);
Set-acl -aclobject $acl "AD:$((Get-ADComputer -Identity $ListenerName).DistinguishedName)";

# Create a new DNS entry for the Listener
Add-DnsServerResourceRecordA -ComputerName $ADServer -Name $ListenerName -ZoneName "$($DC1).$($DC2)" -IPv4Address $IPAddress;

# We have to give the CNO the access to the DNS record
$acl = Get-Acl "AD:$((Get-DnsServerResourceRecord -ComputerName $ADServer -ZoneName '$($DC1).$($DC2)' -Name $ListenerName).DistinguishedName)";

#Retrive SID Identity for CNO to update in ACL
$identity = (Get-ADComputer -Identity $ClusterName).SID;

# Contruct ACE for Generic Read
$adRights = [System.DirectoryServices.ActiveDirectoryRights] "GenericRead";
$type = [System.Security.AccessControl.AccessControlType] "Allow";
$inheritanceType = [System.DirectoryServices.ActiveDirectorySecurityInheritance] "All";
$ACE = New-Object System.DirectoryServices.ActiveDirectoryAccessRule $identity,$adRights,$type,$inheritanceType;
$acl.AddAccessRule($ace);

# Contruct ACE for Generic Write
$adRights = [System.DirectoryServices.ActiveDirectoryRights] "GenericWrite";
$type = [System.Security.AccessControl.AccessControlType] "Allow";
$inheritanceType = [System.DirectoryServices.ActiveDirectorySecurityInheritance] "All";
$ACE = New-Object System.DirectoryServices.ActiveDirectoryAccessRule $identity,$adRights,$type,$inheritanceType;
$acl.AddAccessRule($ace); `

#Update ACL for DNS Record of the CNO
Set-acl -aclobject $acl "AD:$((Get-DnsServerResourceRecord  -ComputerName $ADServer -ZoneName "$($DC1).$($DC2)" -Name $ListenerName).DistinguishedName)";

In this blog, we saw how to automate the creation and the configuration of CNOs and VCOs in the AD/DNS.
This is useful when you have several Clusters to install and several Listeners to configure, and you want to make sure there is no mistake while saving time.

2 Comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Nathan Courtine
Nathan Courtine

Senior Consultant