Unable to deploy to SQL Server 2017 database using Appveyor Agent 5.0.8

pochu's Avatar

pochu

16 Dec, 2017 07:23 PM

When we try to deploy to SQL Server 2017 database using AppVeyor Agent 5.0.8 (latest at time of post) we get the following error messages:

Error message if dacpac Target Platform is Sql Server 2014

Publishing SSDT package "Auth_DB.dacpac" to server "DATABASE-SERVER_NAME" database "DATABASE-NAME"
Initializing deployment (Start)
Initializing deployment (Failed)
Error publishing database: Could not deploy package. Unable to connect to master or target server 'DATABASE-NAME'. You must have a user with the same password in master or target server 'DATABASE-NAME'.

Error message if dacpac Target Platform is Sql Server 2017

Publishing SSDT package "Auth_DB.dacpac" to server "DATABASE-SERVER_NAME" database "DATABASE-NAME"
Initializing deployment (Start)
Initializing deployment (Failed)
Error publishing database: Could not deploy package. Internal Error. The database platform service with type Microsoft.Data.Tools.Schema.Sql.Sql140DatabaseSchemaProvider is not valid. You must make sure the service is loaded, or you must provide the full type name of a valid database platform service. Internal Error. The database platform service with type Microsoft.Data.Tools.Schema.Sql.Sql140DatabaseSchemaProvider is not valid. You must make sure the service is loaded, or you must provide the full type name of a valid database platform service.

It seems the error is caused because Appveyor Agent does not reference DACFx version that supports SQL Server 2017.

Temporary Workaround:

  1. Stop Restart AppVeyor Deployment Agent service (Windows Services)
  2. Backup AppVeyor Agent installation folder
  3. Replace the following files from the latest installed version of DAC on your machine into your AppVeyor Agent Installation folder:(in my case C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin)
    • Microsoft.SqlServer.Dac.dll
    • Microsoft.SqlServer.TransactSql.ScriptDom.dll
    • Microsoft.Data.Tools.Schema.Sql.dll
    • Microsoft.Data.Tools.Utilities.dll
  4. Modify Appveyor.DeploymentAgent.Service.exe.config to add following runtime assembly bindings
        <dependentAssembly>
        <assemblyIdentity name="Microsoft.Data.Tools.Schema.Sql" publicKeyToken="b03f5f7f11d50a3a"/>
        <bindingRedirect oldVersion="0.0.0.0-14.0.0.0" newVersion="14.0.0.0"/>
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="Microsoft.Data.Tools.Utilities.Sql" publicKeyToken="b03f5f7f11d50a3a"/>
        <bindingRedirect oldVersion="0.0.0.0-14.0.0.0" newVersion="14.0.0.0"/>
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="Microsoft.SqlServer.Dac" publicKeyToken="b03f5f7f11d50a3a"/>
        <bindingRedirect oldVersion="0.0.0.0-14.0.0.0" newVersion="14.0.0.0"/>
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="Microsoft.SqlServer.TransactSql.ScriptDom" publicKeyToken="89845dcd8080cc91"/>
        <bindingRedirect oldVersion="0.0.0.0-14.0.0.0" newVersion="14.0.0.0"/>
      </dependentAssembly>
    
  5. Restart AppVeyor Deployment Agent service

This has been working fine for my deployment agent.
Ideally it would be nice to update Agent to use newer references that support Sql Server 2017.
I hope this helps.

As always thanks for your support.

  1. Support Staff 1 Posted by Feodor Fitsner on 17 Dec, 2017 04:42 AM

    Feodor Fitsner's Avatar

    Thanks for pointing that out and sharing the workaround!

    Could you please check what's the version of Microsoft.SqlServer.Dac.dll assembly that you are copying from you DAC installation?

  2. 2 Posted by pochu on 18 Dec, 2017 03:09 AM

    pochu's Avatar

    File version: 14.0.3881.1
    Product Version: 14.0.3881.1

  3. Ilya Finkelshteyn closed this discussion on 25 Aug, 2018 02:25 AM.

Comments are currently closed for this discussion. You can start a new one.

Keyboard shortcuts

Generic

? Show this help
ESC Blurs the current field

Comment Form

r Focus the comment reply box
^ + ↩ Submit the comment

You can use Command ⌘ instead of Control ^ on Mac