Enable Registry Key Password Expiration for SQL Server SA User via Wix Installer
Introduction
In this article, we will discuss how to enable password expiration for the SQL Server SA user by modifying the registry key during the installation of SQL Server Express using the Wix Installer. By enforcing password expiration, we can improve the security posture of our SQL Server instances.
Registry Keys for SQL Server Password Expiration
SQL Server stores its configuration settings in the Windows Registry. To enable password expiration for the SA user, we need to modify the following registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\LoginProperties\SA
To enable password expiration, set the value of the PasswordExpirationEnabled key to 1.
Wix Installer Overview
The Windows Installer XML (WiX) toolset is a free software development kit for creating Windows installation packages. Wix Installer supports creating custom actions to modify the system during installation. By using Wix Installer, we can create a registry key to enable password expiration for the SQL Server SA user.
Wix Installer Custom Action
To create a custom action in Wix Installer, we first need to define a CustomAction element in the Product.wxs file. Then, we can use the RegWrite standard action to modify the registry key.
CustomAction Element
Define the CustomAction element to set the value of the PasswordExpirationEnabled registry key.
<CustomAction Id="CA_EnableSAUserPasswordExpiration"
BinaryKey="WixCA"
DllEntry="CAQuietExec"
Execute="deferred"
Return="check">
"msiexec.exe" /x{5B282E9E-A3E1-4986-B36B-13EA5633E1E4}
/qn REBOOT=ReallySuppress</CustomAction>
The CustomAction element uses the CAQuietExec DllEntry from the WixCA binary key to execute a command line. The command line modifies the registry key with the following registry script:
[SystemFolder]reg.exe ADD "HKLM\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\LoginProperties\SA" /v PasswordExpirationEnabled /t REG_DWORD /d 1 /f
This command line uses the reg.exe utility to add the PasswordExpirationEnabled registry key to the SQL Server SA user registry key.
Wix Installer UI
To enable the user to choose whether to enable password expiration, we can create a checkbox in the user interface (UI) of the Wix Installer. We can use the Checkbox element to create the checkbox and the Property element to store the checkbox value.
<Checkbox Id="chkEnableSAUserPasswordExpiration"
Text="Enforce Password Expiration for SA User"
Checked="no">
<Condition>SQLServer2019 >= v15.0</Condition>
</Checkbox>
<Property Id="PROPEOPSAUserExpiration"
Secure="yes">
<![CDATA[0]]>
</Property>
The Checkbox element uses the Checked attribute to set the initial value of the checkbox and the Condition element to only show the checkbox if SQL Server 2019 is installed. The Property element stores the value of the checkbox with the Id PROPEOPSAUserExpiration.
Wix Installer Conditional Custom Action
To execute the custom action only if the user selects the checkbox, we can use the Condition element to control the execution of the custom action.
<InstallExecuteSequence>
<Custom Action="CA_EnableSAUserPasswordExpiration"
After="InstallFiles">
NOT Installed AND (PROPEOPSAUserExpiration = "1")
</Custom>
</InstallExecuteSequence>
By using the Condition element, the custom action to enable password expiration for the SQL Server SA user will only execute if the user selects the checkbox and the installation is not already installed.
In this article, we discussed how to enable password expiration for the SQL Server SA user during installation using a Wix Installer custom action.
We showed how to create a custom action using the Wix Installer CustomAction and RegWrite elements to modify the registry key for the SQL Server SA user.
We demonstrated how to create a UI checkbox element to allow the user to choose whether to enable password expiration and used the Property element to store the checkbox value.
Finally, we used the Condition element to only execute the custom action if the user selects the checkbox and the installation is not already installed.