Use SecureString for -AccessToken

[PatrickSpies]

Currently -AccessToken requires the token to be passed as string.

Get-AzAccessToken (mentioned at https://learn.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps#-accesstoken) will return a SecureString in the future:

WARNING: Upcoming breaking changes in the cmdlet 'Get-AzAccessToken' :
The Token property of the output type will be changed from String to SecureString. Add the [-AsSecureString] switch to avoid the impact of this upcoming breaking change.
- The change is expected to take effect in Az version : '13.0.0'
- The change is expected to take effect in Az.Accounts version : '4.0.0'

Is there a way to use the SecureString for -AccessToken?

[paulcbusch]

This took me quite some time. I'm new to the subject and the error message you get when providing a secure string is this:
Login failed for user ''.

Had changed too much in one step, learned an important message ;)

[pfab0]

At this time -AccessToken for Invoke-Sqlcmd accepts only a String, not a SecureString.
If you want to be prepared for the future breaking change you can do something like this:

Powershell 7+

$secureStringAccessToken = (Get-AzAccessToken -ResourceUrl 'https://database.windows.net' -AsSecureString).Token
# Remove next line when SqlServer module adds support for Access Token as SecureString
$accessToken = ConvertFrom-SecureString -SecureString $secureStringAccessToken -AsPlainText

Powershell 5.1

$secureStringAccessToken = (Get-AzAccessToken -ResourceUrl 'https://database.windows.net' -AsSecureString).Token
# Remove next line when SqlServer module adds support for Access Token as SecureString
$accessToken = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto(
    [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($secureStringAccessToken)
)

Not sure what the changes will be if #32 is implemented.