invoke-sqlcmd a parameter cannot be found that matches parameter name 'accesstoken'

Question

Hello,
I am referring this article (https://learn.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps - example11) to use Invoke-SqlCMD to execute SQL Script from powershell using service principa

l I am using the below code to execute it from powershell script task

Import-Module SQLServer  
Import-Module Az.Accounts -MinimumVersion 2.2.0  
  
# If you already have your Access Token available, you can skip this section  
# and just pass it to the cmdlet.  
# Note: the sample assumes that you or your DBA configured the server to accept connections using  
#       that Service Principal and has granted it access to the database (in this example at least  
#       the SELECT permission).  
  
### Obtain the Access Token: there are many ways to do it, this is one of them  
Connect-AzAccount  
$access_token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token  
  
 
Invoke-Sqlcmd -ServerInstance myserver.database.windows.net -Database mydb -AccessToken $access_token`  
              -query 'select * from Table1'  

But it is not working and getting below error:

Any help would be appreciated

Answer 1

Looks like -AccessToken parameter was added to Invoke-Sqlcmd on version 21.1.18228 - I have 21.1.18226 which explains why I'm also getting parameter cannot be found.

21.1.18228

Updates:
* Added new -AccessToken parameter to Invoke-Sqlcmd: this will allow connecting to SQL Server
(e.g. Azure SQL DB, Azure SQL Managed Instance) using a Service Principal or a Managed Identity.

Answer 2

I guess 21.1.18221 doesnt support -AccessToken.

Can you use the latest version?

PS C:\Users\joegl> Get-Module sqlserver

ModuleType Version Name ExportedCommands

---

Script 21.1.18245 SqlServer {Add-RoleMember, Add-SqlAvailabilityDatabase, Add-SqlAvail...

PS C:\Users\joegl> Get-Help Invoke-SqlCmd
NAME
Invoke-Sqlcmd

SYNOPSIS
Runs a script containing statements supported by the SQL Server SQLCMD utility.

SYNTAX
Invoke-Sqlcmd [[-Query] <String>] [-AbortOnError] [-AccessToken <String>] [-ApplicationIntent <ApplicationIntent>]
[-ApplicationName <String>] [-ConnectionTimeout <Int32>] [-Credential <PSCredential>] [-Database <String>]
[-DedicatedAdministratorConnection] [-DisableCommands] [-DisableVariables] [-EncryptConnection] [-ErrorLevel
<Int32>] [-FailoverPartner <String>] [-HostName <String>] [-IgnoreProvi

Answer 3

I am having the same problem. If this is resolved with another version, I am very interested.

This is the SQLServer version I'm seeing for powershell.
PS C:\WINDOWS\system32> get-module

ModuleType Version Name ExportedCommands

---

Script 2.6.1 Az.Accounts {Add-AzEnvironment, Clear-AzContext, Clear-AzDefault, Connect-AzAccount...}
Script 1.0.0.0 ISE {Get-IseSnippet, Import-IseSnippet, New-IseSnippet}
Manifest 3.1.0.0 Microsoft.PowerShell.Management {Add-Computer, Add-Content, Checkpoint-Computer, Clear-Content...}
Manifest 3.0.0.0 Microsoft.PowerShell.Security {ConvertFrom-SecureString, ConvertTo-SecureString, Get-Acl, Get-AuthenticodeSignature...}
Manifest 3.1.0.0 Microsoft.PowerShell.Utility {Add-Member, Add-Type, Clear-Variable, Compare-Object...}
Manifest 3.0.0.0 Microsoft.WSMan.Management {Connect-WSMan, Disable-WSManCredSSP, Disconnect-WSMan, Enable-WSManCredSSP...}
Script 1.4.7 PackageManagement {Find-Package, Find-PackageProvider, Get-Package, Get-PackageProvider...}
Script 1.0.0.1 PowerShellGet {Find-Command, Find-DscResource, Find-Module, Find-RoleCapability...}
Script 21.1.18256 SQLServer {Add-RoleMember, Add-SqlAvailabilityDatabase, Add-SqlAvailabilityGroupListenerStaticIp, Add-SqlFirewallRule...}