sql-serverazure-sql-databasedacpacazure-data-studio

Cannot publish DACPAC to local SQL Server container, external users not valid logins


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.

  1. In the Database Projects extension, create project from database. I select our existing production database in Azure. Folder structure = Schema/Object Type, SDK-Style project enabled.
  2. Build the project. I get 0 errors and 357 warnings. Mostly warnings about that object references differ in case SQL71558 or unresolved references SQL71502.
  3. Publish project to new SQL Server local development container. The docker container is published, but the "Deploy dacpac"-stage fails with error:

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.


Solution

  • 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>