Unable to automate AD user creation in Azure SQL database

user_18973280 0
Viewed 277

I'm trying to set up automated pipeline for database creation and need to open access for all users of some AD group. Last part is done through CREATE USER [Group Name] FROM EXTERNAL PROVIDER;

In order to execute this command, one needs to be logged in with AAD and the only Azure DevOps task used to execute SQL scripts (SqlAzureDacpacDeployment@1) has limited options to sign with AD. Currently it supports sign in with AD username/password and AD Integrated. User/password option is not possible as we use two factor authentication. And the latter requires self-hosted agent for pipeline which we do not have.

Additionally, there is one more sign in option that look promising (Service Principal: Uses the Authentication data from Azure Subscription), but after trying it failed miserably with error:

##[error]Principal 'web-API' could not be created. Only connections established with Active Directory accounts can create other Active Directory users.

Are the any other options we could use to create AD users in Azure SQL database? Any help would be appreciated.

azure-pipelines azure-sql-database azure-devops
1 Answers

How to solve above error ?

Please follow below steps:

Step 1: Go to Azure portal and find out your SQL server resource and you will find Active director left side under settings. Please click Set Admin. Now your Active Directory user account becomes Admin to the SQL server.

Step 2: Now use SSMS login with Active directory authentication if Multi-factor Authentication (MFA) is enabled. Otherwise, you can choose either 'Active directory - Integrated' or 'Active Directory - Password.'

Step 3: Create new logins which you can see in the below code:

CREATE USER [User1@Domain.com]
FROM EXTERNAL PROVIDER
WITH DEFAULT_SCHEMA = dbo;

add user to roles for the particular database

ALTER ROLE dbmanager ADD MEMBER [User1@Domain.com];
ALTER ROLE loginmanager ADD MEMBER [User1@Domain.com];

Note : If you add a domain user that is configured for MFA, then for that user to log on using SSMS they should select the SSMS authentication option Azure Active Directory - Universal with MFA.

Regarding SqlAzureDacpacDeployment@1 follow this Link.

For more detail information refer this:

https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-overview?view=azuresq

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql?view=sql-server-ver15

https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-mfa-ssms-overview?view=azuresql

user_182299800
Related