“Who uses SQL authentication these days” I hear you ask. Well unfortunately there are some legacy applications out there that only work with a SQL account, not an Active Directory one. Not ideal but that’s life if you want to use that vendor’s application but what if your corporate security policy insists that passwords are changed regularly, which, let’s face it, isn’t exactly unreasonable in this day of heightened security awareness? Oh, and perhaps we also should change passwords when people who know them leave an organisation – now there’s a radical idea!
A Scripted Solution
I’m no SQL expert but it didn’t take me long to figure out a TSQL query to return SQL account details, assuming you run it with an account that has sufficient SQL privileges to query accounts although I found that a non-privileged SQL account can query itself which is ideal for my first use case as we have no privileged access to the SQL instance.
Putting this into PowerShell is relatively easy although I tend to use the SQL cmdlets available with .NET rather than relying on the SQLPS module having been installed. A little more code is required to use them but with the advantage that the script should run wherever the .NET Framework is installed.
When I write any script that takes credentials, I’m acutely aware that if someone is sniffing process command lines, either live with task manager, Process Explorer, etc. or trawling through historic data, depending on what process auditing options are set, then passing a clear text password on the command line isn’t a great idea.
Therefore there are a number of ways to make it more difficult, but not impossible, for the password to be obtained by a third-party.
- The -password option can be omitted if the %SQLval1% environment variable has been set to contain the clear text password as the script will use that instead. However, this still requires the plain text password to be stored somewhere although file system permissions can be used to restrict access. If mail server credentials need to be specified then the environment variable %_MVal12% can be used to store that.
- PowerShell includes the cmdlet ConvertTo-SecureString which can take a plain text string and encrypt it so the script has the ability to encrypt a plain text password such that it can later be passed via the -hashedPassword option to the script (or -mailHashedPassword if this is the password for the account required to connect to the mail server). This encrypted password can only be used, which includes decryption, on the same machine as it was encrypted on and by the same user. For example, to encrypt the password we can run this which places it into the clipboard:
& '.\Check SQL account expiry.ps1' -encryptPassword -password thepassword|Set-Clipboard
and then we can paste that into the argument for the -hashedPassword option when we call the script, such as via a scheduled task. This still isn’t 100% secure because an administrator could reset the password on the account used to encrypt the password so they could then logon and decrypt it but it’s a whole lot better than passing a clear text password on a command line!
There is also a -mailOnFail argument so that if the connection to the SQL server fails then an email can be sent by the script in case the potential outage or account problem hasn’t been detected already.
Additionally, there is -includeDisabled, which will include all accounts, unless -accountName is specified (as a regular expression) to limit what accounts are reported, that cannot currently login since password expiry for an account is a little moot if that account is already disabled, or set to require a password change after first use.
The script includes full PowerShell compatible help including examples but I’ll include one here so you get the gist of it:
& '.\Check SQL account expiry.ps1' -sqlServer SQL01\instance01 -recipients firstname.lastname@example.org -mailServer smtp.hell.com -mailOnFail -expireWithinDays 10
This will connect to the specified SQL server instance as the user running the script and email a list of all SQL accounts which expire within the next 10 days to the given recipient. If the script fails to connect to the SQL server then an email will also be sent containing details of the error.
If no mail parameters, such as the mail server and recipients, are specified then the output will go to an onscreen grid view, unless -nogridview is specified whereupon the account objects will be output directly so they can be piped into another script or manipulated manually.
With mail parameters set, an email should be sent which looks similar to the following, where I’ve overridden the default of 7 days for the expiry check to 39 days to ensure that it captures some data for demonstration purposes:
If the script fails to connect to SQL and the -mailOnFail argument is specified then an email similar to the following will be sent:
If you just want the script to check that connections to SQL can be made, specify the -accountName argument with the name of an account that doesn’t exist, such as “non-existent” so that it won’t email any expiring accounts, just connection failures. Even without privileged credentials being used to connect to SQL, either by running the script under a Windows account that has the securityadmin server/instance privilege or with a privileged SQL account via -username, a SQL account can query itself so that can be used.
The digitally signed script is available on GitHub here and you use it at your own risk although it is purely passive so cannot inflict any damage.