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 https://github.com/microsoft/SQLServerPSModule/issues/32 is implemented.

[Matteo-T]

Fix is coming in v22.4: -AccessToken will take both a String and a SecureString

[Matteo-T]

This was fixed in 22.4.5.1

[PatrickSpies]

@Matteo-T thanks! :)

Small note: Invoke-Sqlcmd now supports String or SecureString for -AccessToken, whereas Write-SqlTableData already supported String or a PSAccessToken-object.. Would have been nice to have the same behavior for all cmdlets..

Invoke-Sqlcmd
    -AccessToken <PSObject>
        The access token used to authenticate to SQL Server, as an alternative to user/password or Windows
        Authentication.

        This can be used, for example, to connect to `SQL Azure DB` and `SQL Azure Managed Instance` using a `Service
        Principal` or a `Managed Identity` (see references at the bottom of this page)

        In common scenarios, this parameter is obtained with something like `(Get-AzAccessToken -ResourceUrl
        https://database.windows.net).Token` (requires the Az.Accounts module)

        The type of the parameter can be either `string` (clear text token) or `SecureString` (encrypted token, as
        returned by newer version of the Get-AzAccessToken cmdlet.)

Write-SqlTableData
    -AccessToken <PSObject>
        The access token used to authenticate to SQL Server, as an alternative to user/password or Windows
        Authentication.

        This can be used, for example, to connect to `SQL Azure DB` and `SQL Azure Managed Instance` using a `Service
        Principal` or a `Managed Identity`.

        The parameter to use can be either a string representing the token or a `PSAccessToken` object as returned by
        running `Get-AzAccessToken -ResourceUrl https://database.windows.net`.