Active Directory Authentication using JWT Token connection string issue

Question

I am trying to run a SQL script to create a user role in Azure SQL Database using the task Azure SQL database deployment by specifying the connection string with JWT token.

From the Microsoft document I have tried the syntax in the DevOps task but its throwing error .

string ConnectionString =@"Data Source=n9lxnyuzhv.database.windows.net; Initial Catalog=testdb;" SqlConnection conn = new SqlConnection(ConnectionString); conn.AccessToken = "Your JWT token" conn.Open();

Error

##[error]Invalid
 keyword, contain one or more of 'no characters', 'control characters', 'leading or trailing whitespace' or 'leading semicolons'.

Please find the Steps followed 
Created service principle in azure ad.
Granted directory consent.
Added the service principle to a AD GROUP.
Added that group as the ACTIVE DIRECTORY ADMIN in sql server so that all the members of the group can login using single sign on.

Now I need to provide access to other users who are not in the admin group so trying to add contained users and I have achieved that by running the create user query .

But I need to automate it so in order to automate there are 4 options under the SQL Deployment task which are 1.sql password authentication 2.active directory integrated 3.Active directory password 4.Connection String

What I am trying to achieve is through connection string by generating the JWT token for service principal.

But for some reason its failing can someone help me to provide the exact syntax for connection string with JWT TOKEN TO RUN inside the SQL deployment task in Azure DevOps Pipeline.

Answer 1

I am afraid JWT token cannot be used in connection string. You can check the available connection Strings from your azure database UI portal.

However, If you want to automate the sql query by using JWT TOKEN, you can write scripts to run in a azure powershell task or powershell task instead of the Azure SQL database deployment task. See below example powershell scripts:

steps:
- task: AzurePowerShell@5
  displayName: 'Azure PowerShell script: InlineScript'
  inputs:
    azureSubscription: 'myazureSubscription'
    ScriptType: InlineScript
    Inline: |
     
     $Token = "JWT Token"
     
     $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
     
     #change the below to your server and database
     $SqlConnection.ConnectionString = "Data Source=<yourserver>.database.windows.net; Initial Catalog=<yourdatabase>"
      
     $SqlConnection.AccessToken = $Token
     $SqlConnection.Open()
     
     #get a query from a file
     #$query= get-content ".\GetProcDefs.sql"
     
     $query ="SELECT * from Persons"
      
     $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
     $SqlCmd.CommandText = $query
     $SqlCmd.Connection = $SqlConnection

     $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
     $SqlAdapter.SelectCommand = $SqlCmd
     $DataSet = New-Object System.Data.DataSet
     $SqlAdapter.Fill($DataSet)
      
     $DataSet.Tables[0]
    
     $SqlConnection.Close()
    azurePowerShellVersion: LatestVersion