Create SQL user at deployment time

Question

In our release pipeline in Azure DevOps we provision a SQL server and a SQL database.
After the database has been created a contained user is created in that database manually, before the application can be started.
I'm trying to create the contained user after the SQL database has been created.

Because of security guidelines, I need create the connection with an Azure AD account and create a contained user that is a Azure AD user.

I'm trying to do this with the following script:

$dbConnectionString = "Server=tcp:mysqlserver.database.windows.net,1433;Initial Catalog=mydatabase;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication='Active Directory Password';User ID='mysqladmin@mytentant.onmicrosoft.com';Password=mypassword"

$createUser = "CREATE USER [mycontaineduser@mytentant.onmicrosoft.com] FROM EXTERNAL PROVIDER"
$alterRole = "ALTER ROLE db_datawriter ADD MEMBER [mycontaineduser@tenant.onmicrosoft.com]"

Invoke-Sqlcmd -Query $createUser -ConnectionString $dbConnectionString -Verbose
Invoke-Sqlcmd -Query $alterRole -ConnectionString $dbConnectionString -Verbose

This gives an error 'Invoke-Sqlcmd : One or more errors occurred.'.

Any one have a clue or best practice on how to create the contained used in Powershell?

Answer 1

@MrEco-9773 Currently Invoke-Sqlcmd does not work with AAD authentication.
There is already a User Voice item in the product team's backlog to implement the same. Please upvote for the same so that it can be prioritized.