How can I get Invoke-Sqlcmd to return a Msg 40632 "Password validation failed." exception-CodePudding

When I run Invoke-Sqlcmd with a -Query that contains a password retrieved via Read-Host with the -AsSecureString parameter, and the value entered is too simple, it will fail without returning an exception.

When I run Invoke-Sqlcmd with a -Query that contains a password retrieved via Read-Host without the -AsSecureString parameter, and the value entered is too simple, it will return an exception, as expected:

Password validation failed. The password does not meet policy requirements because it is not complex enough.

Msg 40632, Level 16, State 1, Procedure , Line 1.

Examples

This code returns the "Msg 40632" exception when I enter 01234567 as a password, and will successfully execute when I enter 012345A! as a password:

$local:password = Read-Host ("Enter the new password for {0} on {1}" -f $local:login, $local:serverDomain)
$local:updatePasswordQuery = ("ALTER LOGIN {0} WITH PASSWORD = '{1}'" -f $local:login, $local:password)
Invoke-Sqlcmd `
    -ServerInstance ("{0}.database.windows.net" -f $local:server.name) `
    -Database "master" `
    -AccessToken $local:access_token `
    -ErrorAction Stop `
    -Query "$local:updatePasswordQuery"

This code will fail silently when I enter 01234567 as a password, and will also successfully execute when I enter 012345A! as a password:

$local:securePassword = Read-Host ("Enter the new password for {0} on {1}" -f $local:login, $local:serverDomain) `
                       -AsSecureString
$local:updatePasswordQuery = ("ALTER LOGIN {0} WITH PASSWORD = '{1}'" -f $local:login, $local:securePassword)
Invoke-Sqlcmd `
    -ServerInstance ("{0}.database.windows.net" -f $local:server.name) `
    -Database "master" `
    -AccessToken $local:access_token `
    -ErrorAction Stop `
    -Query "$local:updatePasswordQuery"

So my question is: how can I get Invoke-Sqlcmd to return an exception when I pass a secure string with a password that is not complex enough? Or is this simply impossible due to the nature of secure strings?

> $PSVersionTable

Name                           Value
----                           -----
PSVersion                      7.3.0
PSEdition                      Core
GitCommitId                    7.3.0
OS                             Microsoft Windows 10.0.22000
Platform                       Win32NT
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

Imported Modules:

Az.Accounts                    2.10.3                
SqlServer                      21.1.18256

CodePudding user response：

Officially SecureStrings are write only. They cannot officially be read, ergo you can't check its complexity and in turn can't do anything if you don't like the password's value.

Yes it IS possible to turn a SecureString back into plaintext, notably if you didn't use an explicit IV (using -Key on *-Securestring cmdlets) or if you did have access to that key.

Of course that reduces SecureStrings ad absurdum. Unless there's an additional point to using them, if you employ SecureStrings only to then turn around because you need to read them... it's easier not to use them in the first place.

For reference:

$myTest=ConvertTo-SecureString -String 'Abcd' -AsPlainText -Force
$nc = [System.Net.NetworkCredential]::new('dummy', $myTest)      
$nc.Password

(The 'dummy' is just a dummy, its value is not considered but is required.)

CodePudding user response：

My code was incorrect: I needed to a ConvertFrom-SecureString with -AsPlainText when putting the value in the query:

$local:securePassword = Read-Host ("Enter the new password for {0} on {1}" -f $local:login, $local:serverDomain) `
                       -AsSecureString
Invoke-Sqlcmd `
    -ServerInstance ("{0}.database.windows.net" -f $local:server.name) `
    -Database "master" `
    -AccessToken $local:access_token `
    -ErrorAction Stop `
    -Query ("ALTER LOGIN {0} WITH PASSWORD = '{1}'" -f $local:login, (ConvertFrom-SecureString $local:securePassword -AsPlainText))

I made this mistake because I was first testing the unhappy flow (does it fail on a too simple password) before the happy flow (does updating with a valid password actually work?).