2

I am trying automate the creation of users in a Synapse Dedicated SQL Pool. Building and integrating a DACPAC in an Azure DevOps pipeline works as described in the Microsoft Documentation. The problem is, that i want to specify the username at build-time of the DACPAC as using an SQLCMD variable as follows:

CREATE USER [$(TestUserName)] FROM EXTERNAL PROVIDER;

This throws the following build error:

SQL70604: SqlCmd variable reference is not allowed in object names ($(TestUserName)).

Am I taking a completely wrong approach to the problem, or does anybody know how to get this working?

Sunderam Dubey
  • 1
  • 11
  • 20
  • 40
woozy
  • 33
  • 7

1 Answers1

3

I hit exactly the same problem and moved some of the security to post-deployment scripts. You lose some of the benefits of the users being in the main project but it solves the problem. You also have to write defensive scripts IF EXISTS ...

An example, using sqlcmd variables which are passed in in the YAML pipeline using the /v argument:

IF NOT EXISTS ( SELECT * FROM sys.database_principals WHERE name = 'someUser-$(envShort)-developers' )
    CREATE USER [someUser-$(envShort)-developers] FOR EXTERNAL PROVIDER;

Role membership sp_addrolemember was also used to post-deployment scripts for the same reason

wBob
  • 13,710
  • 3
  • 20
  • 37
  • Thank you, this is what I was looking for! Deploying directly from Visual Studio already works, but on Azure DevOps, there is another problem: Because the DACPAC-Task SqlAzureDacpacDeployment@1 does not yet support AAD-Login for hosted agents, I cannot use "FROM EXTERNAL PROVIDER". Have you found a solution to that? – woozy Jun 28 '22 at 06:28
  • We use `SqlAzureDacpacDeployment@1` with a private pool that solves that problem. While we were waiting for certain things to be set up (i.e. AAD Reader access for the agent account) was to deploy the security scripts manually as it's kind of once per environment, so wasn't too much overhead. We automated it once we were able to. – wBob Jun 28 '22 at 13:00
  • The question is, why have dynamically named users, which ultimately cause this problem? For me, it was because we incorporated environment name (dev, test, prod etc) into the AAD group name, but ask yourself if this is really required. – wBob Jun 28 '22 at 19:50