Unable to connect to Azure SQL Database using Invoke-Sqlcmd in PowerShell

Question

I'm currently facing an issue with connecting to an Azure SQL Database using PowerShell's Invoke-Sqlcmd cmdlet. I am able to successfully connect to the database using sqlcmd (Version 18.2.0001.1 Linux) from WSL Ubuntu on my machine, but when I try to use Invoke-Sqlcmd from the same machine, it fails. I'm trying to connect with a service principle set as Microsoft Entra admin on the Azure SQL Server.

Here's how I'm fetching and storing the access token for the service principal:

az account get-access-token --resource https://database.windows.net --output tsv | cut -f 1 | tr -d '\n' | iconv -f ascii -t UTF-16LE > TOKEN_LOCATION

This is the sqlcmd command that works fine:

sqlcmd -S myserver.database.windows.net -d mydatabase -G -P TOKEN_LOCATION -C -Q "print 'hello'"

However, when I try to use the following Invoke-Sqlcmd command, it does not work:

Invoke-Sqlcmd -ServerInstance myserver.database.windows.net -Database mydatabase -AccessToken (TOKEN_LOCATION or $TOKEN) -query "print 'hello'" -TrustServerCertificate -Verbose

I am using Invoke-Sqlcmd version 22.2. When I run the above command, I get the following error message:

Invoke-Sqlcmd: Login failed for user '<token-identified principal>'.
Invoke-Sqlcmd: Incorrect syntax was encountered while parsing ''.

I have also tried create a System.Data.SqlClient.SqlConnection to open the connection. Like this:

$SQLConnection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)
$SQLConnection.AccessToken = $token
$SQLConnection.Open()

Received the following error:

Exception calling "Open" with "0" argument(s): "A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error
: 0 - An existing connection was forcibly closed by the remote host.)"

Can anyone help me understand why Invoke-Sqlcmd is not working in this case and how I can resolve this issue? Or even how I can get more diagnostics.

Thank you in advance!

Answer 1

Invoke-Sqlcmd: Login failed for user '<token-identified principal>'.
Invoke-Sqlcmd: Incorrect syntax was encountered while parsing ''.

There may be issue while retrieving access token that may be reason to get above while connecting with sql database with service principle authentication. Add service principle as sql server admin and you can use below code to connect sql database:

$clientid = "<SPNcleintId>"
$tenantid = "<SPNtenantId>"
$secret = "<SPNsecret>"
$request = Invoke-RestMethod -Method POST `
           -Uri "https://login.microsoftonline.com/$tenantid/oauth2/token"`
           -Body @{ resource="https://database.windows.net/"; grant_type="client_credentials"; client_id=$clientid; client_secret=$secret }`
           -ContentType "application/x-www-form-urlencoded"
$Token = $request.access_token
$Params = @{
    ServerInstance = '<serverName>.database.windows.net'
    Database = '<databaseName>'
    AccessToken = $Token
}

Invoke-Sqlcmd @Params -Query 'select * from <tableName>'

It will connect successfully as shown below: