Authentication using AccessToken / ServicePrincipal

Question

Am having a SSDT unit test project with EntraID authentication to my azure database.

This SSDT unit test project shall be executed (see image below):

• Locally in VS Code using the connection string +

  Active Directory InteractiveAuthentication

• And within my AzureDevOps Pipeline + Using ??Service Principal?? authentication

... this is the app.config for local execution:

How do i need to configure my app.config in order to use my ServicePrincipal from the AzureDevOps-Pipeline?

Answer 1

How do i need to configure my app.config in order to use my ServicePrincipal from the AzureDevOps-Pipeline?

To use the AAD Service Principal in the app.config -> ConnectionString, you can set the Authentication to ActiveDirectoryServicePrincipal.

Here is an example:

Data Source=xx.database.windows.net,1433;Initial Catalog=DATABASE;Persist Security Info=False;User ID={appid};Password={appsecret};Multiple Active Result Sets=False;Encrypt=True;TrustServerCertificate=False;Authentication=ActiveDirectoryServicePrincipal

If you want to avoid storing sensitive information in a connection string in app.config. You can store the connectionString in a secret variable in your pipeline.

And use tokens connectionString="#{sqlDbTestConnectionString}#" in app.config in place of real connection strings.

Then add replace token task before VsTest task in your pipeline to replace the tokens #{sqlDbTestConnectionString}# in app.config with the real connectionString defined in secret variable.

You can check this blog for more information.