This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(76.8, 97%, 17%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EO%3C/text%3E%3C/svg%3E)
How do I use the 'Azure SQL Database deployment' task in a release pipeline to deploy a dacpac to an Azure SQL Managed Instance? The error I'm getting is that the server cannot be found. I assume that's because it's not publicly accessible (as it shouldn't be). I've found some instructions that discuss creating a firewall rule to temporarily permit access, but I'm concerned those are for SQL databases, not managed instances. I've also found discussions that indicate I would have to have a self-hosted agent to do the deployments. I could look into that, but it's an additional expense and doesn't feel very cloud-native.
@Oblio Thank you for reaching out.There isn’t integrated capability to add a firewall rule to SQL MI from the Azure SQL Database deployment task in Azure DevOps.
As Nandan Hegde mentioned the options are to use a self-hosted agent on a VM with network access to the database (recommended) or to add additional steps to the pipeline before and after the deployment to add network access to the SQL MI instance. The later is significantly more complicated.
As to the cloud-native aspect of the solution, that’s sort of part for the course with SQL MI - there options move away from cloud native architectures as SQL MI is pointed more towards applications architected for SQL Server instead of Azure SQL Database.
Regards,
Oury
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(227.2, 46%, 32%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ENN%3C/text%3E%3C/svg%3E)
@Oblio
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
2 parts: First, I'm working through trying to get a self-hosted option working. While the pipeline tasks make it clear that 'sqlpackage' is a required capability, there's not good documentation on how to add that. After that, the documentation I found said I needed to add the DevOps Service Principal to the SMI with 'dbmanage' and 'login' roles, however, I'm still getting a 'cannot create database' permission error.
Second, if SMI is not really a cloud-native approach, what is?
For the sqlpackage deployment, did you check this doc https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-pipelines?view=sql-server-ver16?.
Please share the doc you are following.
Regards,
Oury
@Oury Ba-MSFT Yes, I did check that document. However, to be clear, there's precisely 1 sentence on that page that addresses running SqlPackage on a self-hosted environment, and does not link to how to setup SqlPackage at all.
Specifically, one piece that needs to be made clear is this: the link you provided advises that self-hosted environments should "update the application regularly to maintain the environment with the latest version". It in turn links to the install instructions for SqlPackage, where it's advised to use the dotnet tool version because it "is the recommended method to install SqlPackage", citing a more frequent update schedule.
However, the dotnet tool does not install SqlPackage for all users; it only works for the user who runs it. This is problematic because self-hosted agents often run as a service and thus wouldn't have access to the tool.
Regarding the documentation for the DevOps Service Principal, I was reading this page - https://datasharkx.wordpress.com/2021/05/28/automated-deployment-of-sql-server-database-through-azure-devops/
The Azure SQL MI is hosted within a VNet which is not directly accessible from the Azure devops agent.
So ideally we would have to create a server within the VNet in which the SQL MI is hosted and set up Azure devops self hosted agent within that server which would help to deploy the DACPAC.
Sample of On Prem SQL server (similar to SQL MI)
It's really disappointing that I need a VM to deploy to a SaaS. Is there any way to have a serverless devops agent?
Also, the link you provided has two steps: setup an agent, then deploy the file. The problem is "setup an agent" is not sufficient as it does not actually contain the necessary dependencies (sqlpackage) for dacpac deployment. And, that too is turning out to be a confusing rabbit hole.