Recently I wanted to verify that my data access layer could properly read and write to a SQL Server database, and I wanted to have these tests automated. I wanted to answer these questions:

1. Can my DbContext roundtrip entities to the database and back?
2. Does the schema in my migration scripts match the expected schema in my code? (follows from 1)
3. Can my migration scripts be applied to the database correctly?

Since I was using SQL Server I could utilize SQL Server LocalDB that comes with Visual Studio. To keep performance acceptable I do not want to create and destroy a database for each test, so I need a way to reset the database after a test has run.

When trying to deploy a simple web application and Azure SQL database through Azure DevOps pipelines, I wanted to use a system managed application identity to authorize the web application to access the database. This requires running something like the following SQL script on the Azure SQL database.

CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<identity-name>];
ALTER ROLE db_datawriter ADD MEMBER [<identity-name>];
ALTER ROLE db_ddladmin ADD MEMBER [<identity-name>];

I was having a lot of trouble getting the Azure SqlCmd task to work, while the error(s) it was showing was not helpful at all. For example: