Azure Runbook Error: Could not load file or assembly 'Microsoft.SqlServer.BatchParser.dll'

Question

I have a very simple piece of code I am trying to run from an Azure Runbook but when I do, it results in the following error message

Exception calling "Open" with "0" argument(s): "The target principal name is incorrect.  Cannot generate SSPI context."

Code:

workflow test
{
        
    inlinescript {
        try
        {
            "Logging in to Azure..."
            Connect-AzAccount -Identity
        }
        catch {
            Write-Error -Message $_.Exception
            throw $_.Exception
        }


        $ServerName = "sqlserverinstance.database.windows.net"
        $DB = "database_instance"
        $token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token
        $token

       Invoke-SqlCmd -ServerInstance $ServerName -Database $DB -AccessToken $token -Query "some query"
}
}

I am using a Powershell Workflow runbook running 5.1. I have updated the SQLServer module as well with no luck. I don't run into this issue when I run things from the Azure Cloud Shell. Another thing, when I run the command using the -Credentials argument, it works. I want to use an access token though; hence why I am following the approach above.

Answer 1

There are various of reasons that can block SQL server to connect to the database and get the result.

Need to check & resolve:

1. First of all, You need to be the admin of the account and add "sql DB contributor, Sql server contributor" for sql server.

2. Assign server system identity to the SQL server and give the "Directory Readers" access to the server identity by referring to MsDoc.

3. To run SQL commands in a runbook or connect to a SQL server, you need to use the template which specifies a connection string.

4. Check if you enabled system identity for automation accounts as you are trying to connect with connect-azaccount -identity

5. As you are trying to connect through Azure AD, register a new app registration by visiting AzureAD -> App registrations -> New registration and enable API permissions.

After checking the above workaround, I tried below script and able to connect successfully as shown:

connect-Azaccount -identity
$token = (Get-AzAccessToken -ResourceUrl https://<server>.database.windows.net).Token
Invoke-SqlCmd -ServerInstance '<server>.database.windows.net' `
-Database '<Servername>'
-AccessToken $token `
-Query "<query>"

Output:

Reference