1

I am attempting to use bicep to replicate the following scenario:

  • An Azure SQL instance with a single database
  • An Azure function which needs to connect to that database via managed identity.

As it stands, once the infrastructure is deployed we have to use SSMS or similar to add the relevant roles:

CREATE USER [functioName] FROM EXTERNAL PROVIDER
ALTER ROLE db_datareader ADD MEMBER [functionName]

I have been investigating a way to do this within bicep itself but have not been able to get it to work, I have attempted to create a user managed identity with a contributor role and used that identity in a deployment script containing powershell to run InvokeSQL against the dB but am running into errors.

I'm unsure if this is the right way to go about it or if this is possible at all in bicep, so any guidance would be appreciated.

Stix
  • 182
  • 1
  • 10
  • Is your database schema and security under source control? – DreadedFrost Jun 28 '23 at 14:54
  • @DreadedFrost Schema is handled by Entity Framework Core, so table updates etc are source controlled yes – Stix Jun 28 '23 at 15:09
  • what is the rror you are seeing with invokeSql ? – Thomas Jun 28 '23 at 20:08
  • @Thomas I've had a couple, one around 'Principal could not be created. Only connections established with Active Directory Accounts can create other Active Directory Users' and 'SQL exception Login failed for user . The server is not configured to accept this token (this was after trying to get a token within the Powershell itself – Stix Jun 29 '23 at 04:39
  • 1
    Yeah the identity running the deployment script need sql permission so it s kind of chicken and eggs situation. You could always run the sql script in a subsequent task of your dpeloyment ? – Thomas Jun 29 '23 at 05:27

0 Answers0