We have an Azure SQL Server database. I'm trying to implement a better CI/CD workflow by using SQL database projects. I want to create a DACPAC from the production database and apply it to a local container for development.
I've installed the extension Database Projects in Azure Data Studio and I have Docker running.
Deploy dacpac: Could not deploy package.
Warning SQL0: A project which specifies SQL Server 2019 as the target platform may experience compatibility issues with SQL Server 2017. Warning SQL0: The source contains users that rely on an external authentication provider that is not supported by the target. These users will be treated as users without logins.
Error SQL72014: Core Microsoft SqlClient Data Provider: Msg 15007, Level 16, State 1, Line 1 'DATAFACTORY' is not a valid login or you do not have permission.
Error SQL72045: Script execution error. The executed script: CREATE USER [DATAFACTORY] FOR LOGIN [DATAFACTORY];
Go back and change the target platform to Azure SQL Server and build the project again.
Publish the project with the base image set to Azure SQL Database emulator full. Same error:
Deploy dacpac: Could not deploy package.
Warning SQL0: The source contains users that rely on an external authentication provider that is not supported by the target. These users will be treated as users without logins.
Error SQL72014: Core Microsoft SqlClient Data Provider: Msg 15007, Level 16, State 1, Line 1 'DATAFACTORY' is not a valid login or you do not have permission.
Error SQL72045: Script execution error. The executed script: CREATE USER [DATAFACTORY] FOR LOGIN [DATAFACTORY];
I've also tried adding the master as a database reference. I've tried this in Azure Data Studio, Visual Studio Code and Visual Studio 2019.
According to an answer from the Azure Data Studio team this could be because of a bug that will be fixed in the next release.
To exclude the User Object Type from the publish action to the Docker container, I had to create a DAC Publish Profile in Visual Studio 2019 and then use it when publishing to my local emulator. That particular error disappeared but I've not verified that it's actually resolved since the next error came up.
If anyone else gets a similar problem you can try using this database.publish.xml:
<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="Current" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
<PropertyGroup>
<IncludeCompositeObjects>True</IncludeCompositeObjects>
<TargetDatabaseName>database</TargetDatabaseName>
<DeployScriptFileName>database.sql</DeployScriptFileName>
<ProfileVersionNumber>1</ProfileVersionNumber>
<CreateNewDatabase>True</CreateNewDatabase>
<ExcludeObjectTypes>Users</ExcludeObjectTypes>
</PropertyGroup>
</Project>