Deploying Dacpac

Hi,

I have an action to deploy my SQL Server changes to my remote server via SqlPackage.exe however I can’t see if the action completed successfully as there are no outputs from the SqlPackagecommand.

If I run the SqlPackagelocally I see an output like
Publishing to database ‘MyDatabase’ on server ‘MyRemoteServer’.
Initializing deployment (Start)
Initializing deployment (Complete)
Analyzing deployment plan (Start)
Analyzing deployment plan (Complete)
Updating database (Start)

My yml snippet

# call sqlpackage.exe to deploy my db schema using my dacpac
    - name: Update database schema using dacpac
      run: >
        "C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\150\sqlpackage.exe"
          /Action:Publish
          /SourceFile:"Database.dacpac"
          /TargetServerName:"${{ secrets.SQL_Server }}"
          /TargetDatabaseName: "${{ secrets.SQL_Database }}"
          /TargetUser:"${{ secrets.SQL_User }}"
          /TargetPassword:"${{ secrets.SQL_Password }}"
          /p:BlockOnPossibleDataLoss=False

This makes me think the GH Action SqlPackage isn’t even being run even though I can see this in the logs:-

sqlpackage.exe /Action:Publish /SourceFile:Database.dacpac /TargetServerName: ***/TargetDatabaseName:*** /TargetUser: ***/TargetPassword:*** /Diagnostics:true

Have I missed something?

Hi @tdk1964 ,

Please confirm the target server is reachable from the github workflow(ping remote server).

And what if you use publish profile for the command? It’s recommended to enable debug logging for the workflow to check more details.

If possible, could you please share a repo/sample repo for further investigation?

Thanks.

I am making progress but it looks like the firewall on my server is blocking access. My server only allows access to SQL from selected IP addresses. How do I get round this?

Hi @tdk1964 ,

The github hoster runner has ip scope which mentioned in the link here, however it will be updated weekly by Microsoft, hence i would recommend you to set up a self-hosted runner with your local machine, add the ip to the allow list on your server for your workflow.

Thanks.