Use the same CREATE USER command as Azure SQL DB
Currently, Azure SQL DB supports a version of CREATE USER that allows an SPN to connect to the database via an access token and create Azure AD users. Azure SQL DW does not, and fails with an error from Azure AD. SQL DW requires an authenticated AD user be logged into the database directly and issue the CREATE USER command.
Making this function work the same on both platforms will allow a single DevOps deployment to create either DB and DW resources, as well as configure users for those resources, from within the deployment script.
Ben Jarvis commented
Is there any update on this feature? It would be good to get to a stage where Synapse and SQL DB are aligned in terms of authentication.
Erwyn van der Meer commented
Great suggestion. Having this CREATE USER 'WITH SID' work as well for Azure SQL Datawarehouse is crucial for my customer to properly automate deployment of the identity and access. Like we are already able to do for Azure SQL Database. The current lack of support is blocking the consumption of the DW service at this customer. Automation is a requirement and being able to use a service principal for executing the CREATE USER commands to create Azure AD based contained database users a must have support for this.