Azure Pipeline connect to SQL DB using service principal

Question

After my azure pipeline has create an azure sql db I'd like execute some sql.

The sql in question must be executed by an AAD authenticated user.

The service connection for the pipeline is an AAD authenticated user of the database.

If Im willing for the script to consume the service principals secret, then I can construct an OAuth call to retrieve a bearer token and use that to connect to the database.

However since the powershell script is running in the context of the service principal I have a gut feeling there is a better way to connect to the db using the service principal without relying on the secret.

Any ideas how I can do this?

iasksillyquestions · Accepted Answer · 2020-06-23 12:58:39Z

3

The solution is went with was:

I added an Azure CLI task which retrieved the bearer token. I then passed this to Azure Powershell task which used the token.

$token= & az account get-access-token --resource=https://database.windows.net --query accessToken
Write-Host("##vso[task.setvariable variable=sqlToken]$token")

edited Jun 23, 2020 at 12:58

answered Jun 23, 2020 at 11:58

iasksillyquestions

5,71913 gold badges61 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Levi Lu-MSFT Over a year ago

Can you share the script which retrieved the bearer token in Azure CLI task? Thanks

Levi Lu-MSFT · Accepted Answer · 2020-06-23 07:42:19Z

0

You can try below scripts in azure powershell task of your azure pipeline to get the accesstoken for Resource https://database.windows.net/

$context = [Microsoft.Azure.Commands.Common.Authentication.Abstractions.AzureRmProfileProvider]::Instance.Profile.DefaultContext

$databaseAccessToken = [Microsoft.Azure.Commands.Common.Authentication.AzureSession]::Instance.AuthenticationFactory.Authenticate($context.Account, $context.Environment, $context.Tenant.Id.ToString(), $null, [Microsoft.Azure.Commands.Common.Authentication.ShowDialog]::Never, $null, "https://database.windows.net/").AccessToken

$databaseAccessToken

answered Jun 23, 2020 at 7:42

Levi Lu-MSFT

30.8k3 gold badges42 silver badges55 bronze badges

Comments

G Clovs · Accepted Answer · 2025-10-16 16:44:59Z

For me I had to run a project .net project (Core.Db.dll) during pipeline and it need connection string to seed specific data after deploying my database.

Service Principal as SQL User

add the service principal name as an User in you database.

-- Run as SQL admin
CREATE USER [service-principal-name] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [service-principal-name];
ALTER ROLE db_datawriter ADD MEMBER [service-principal-name];
ALTER ROLE db_ddladmin ADD MEMBER [service-principal-name];

ConnectionString by environnemnt

You need to set authentication like: **Active Directory Default ** Server=sql-server.database.windows.net,1433; Authentication=Active Directory Default; Database=mydb; Encrypt=True;

Task AzureCLI@2

You need to set the addSpnToEnvironment: true

steps:
- task: AzureCLI@2
  displayName: 'Azure CLI '
  inputs:
    azureSubscription: 'Azure Database (Service Principal)'
    scriptType: ps
    scriptLocation: inlineScript
    inlineScript: |
     # Set environment variables for DefaultAzureCredential to use WIF
       $env:AZURE_CLIENT_ID = $env:servicePrincipalId
       $env:AZURE_TENANT_ID = $env:tenantId
       $env:AZURE_FEDERATED_TOKEN_FILE = $env:AZURE_FEDERATED_TOKEN_FILE
           
     # Run the static data initialization with DbContext
       dotnet exec Core.Db.dll

    addSpnToEnvironment: true
    visibleAzLogin: false

Collectives™ on Stack Overflow

Azure Pipeline connect to SQL DB using service principal

3 Answers 3

1 Comment

Comments

Service Principal as SQL User

ConnectionString by environnemnt

Task AzureCLI@2

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Service Principal as SQL User

ConnectionString by environnemnt

Task AzureCLI@2

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related