question

DiptiMamidala-5875 avatar image
0 Votes"
DiptiMamidala-5875 asked emcarter answered

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

Hello,
I am referring this article (https://docs.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:

81023-image.png
Any help would be appreciated


windows-server-powershellazure-sql-database
image.png (28.6 KiB)
image.png (28.8 KiB)
· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Can you please share the version of SqlServer powershell module.
I assume you may be using the unsupported version

0 Votes 0 ·

@JoeGladston-8890 we are using 21.1.18221 version of SqlServer powershell module.

0 Votes 0 ·

@DiptiMamidala-5875 Can you please check this document for the details regarding this issue as @JoeGladston-8890 mentioned - it could be an issue with Powershell version

Regards
Navtej S


0 Votes 0 ·

@DiptiMamidala-5875 Please let us know if you were able to check the above info to further TS the issue.

0 Votes 0 ·
Show more comments
JoeGladston-8890 avatar image
0 Votes"
JoeGladston-8890 answered NavtejSaini-MSFT commented

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

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@DiptiMamidala-5875 Were you able to check this and please confirm if the issue is resolved for you?

0 Votes 0 ·

@NavtejSaini-MSFT I have tried with updating to latest sql module but it is the same error

0 Votes 0 ·

@DiptiMamidala-5875 At this time we will advise you to contact support. You can share the ticket number and we can try to get the traction for the same.

0 Votes 0 ·
emcarter avatar image
0 Votes"
emcarter answered

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...}

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.