CODEWITHSUNDEEP
sql-serverazurecicddatabase-projectdacpac
Adam Craven
Adam Craven

Reputation: 11

CI/CD Pipeline DACPAC Deployment Creating SQL Users from External Providers Offline in Azure SQL Database

I have an Azure SQL Database and I am populating it using a DACPAC deployment in my CI/CD pipeline. I have a task which builds my DACPAC from a database project in my Azure DevOps repository and another task which deploys the DACPAC.

In my pipeline, I am using the SqlAzureDacpacDeployment@1 task and authenticating to my SQL Server using a service principal. My service principal is a member of a group which is assigned as the servers Entra admin. The system assigned managed identity associated with my SQL Server is assigned the Entra Directory Readers role to be able to reference the Microsoft Entra identities as EXTERNAL PROVIDERS.

My DACPAC deployment works fine, the objects are created as expected. However, whenever a user is created, it creates the user in an "offline" state. For example, in my DACPAC deployment I have the following SQL script:

CREATE USER [[email protected]] FROM EXTERNAL PROVIDER;

When I login to my server and check the user has been created, it is there but in an offline state with a red cross appearing next to the name of the user (see photo). If I create the user manually by logging into the SQL Server with an account that has the same permission as the service principal, everything works as normal.

Anyone any ideas why the service principal is creating users offline when referencing EXTERNAL PROVIDER inside of my database during a pipeline DACPAC deployment?

I have tried adding the SQL Server managed identity as a user with the db-owner role to the database, as well as assigning a role to the user I am creating but these did not have any affect.

Upvotes: 0

Views: 433

Answers (1)

Adam Craven
Adam Craven

Reputation: 11

I managed to fix the issue...

When you create a user manually by logging into a SQL server, you are usually executing the script inside of the database context. By doing so, it automatically grants permission to connect to the database.

When creating the user through a service principal in a CI/CD pipeline, it appears that it doesn't know which database context to use and will therefore create the user but not grant connect permissions to the database.

Therefore, you need explicitly grant connect permission to the user being created:

CREATE USER [[email protected]] FROM EXTERNAL PROVIDER;
GO
GRANT CONNECT TO [[email protected]];
GO

Upvotes: 1

Related Questions