Azure-docs: Route Table Validation is test is faulty

[AndyHerb]

The "prepareSubnet" PowerShell script from https://raw.githubusercontent.com/microsoft/sql-server-samples/master/samples/manage/azure-sql-db-managed-instance/prepare-subnet/prepareSubnet.ps1 tests a number of configuration points including the necessary Route Table. Whilst testing the Route Table, if anything's missing it can create it, otherwise it provides a positive validation result.

When we ran it on an existing vNet, Subnet, and Route Table, where it it passed the validation test.

Following this we ran our SQL MI deployment which failed with the following error:

• { "status": "Failed", "error": { "code": "ResourceDeploymentFailure", "message": "The resource operation completed with terminal provisioning state 'Failed'.", "details": [ { "code": "VnetSubnetConflictWithIntendedPolicy", "message": "Route Table. (https://go.microsoft.com/fwlink/?linkid=871071)" } ] } }

It transpired that the required 0.0.0.0/0 was the only route we'd got in place, meaning that our deployment was failing because we'd not got a route passing the SQL MI's Subnet Traffic on to the vNet.
See the route entitled subnet_to_vnetlocal here: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance-connectivity-architecture#user-defined-routes

If possible, I think it would be useful to both:

1. Update the prepareSubnet.ps1 script to take this requirement into account before providing a false positive validation
2. Improve the error message when one or more routes is missing at deployment time to help users validate what is missing/misconfigured

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: fc2dd547-3ff7-b32a-ba8a-c582a721a0ee
• Version Independent ID: f083bdda-0c66-af32-0d27-f535e46c96dc
• Content: Connectivity architecture for a managed instance in Azure SQL Database
• Content Source: articles/sql-database/sql-database-managed-instance-connectivity-architecture.md
• Service: sql-database
• Sub-service: managed-instance
• GitHub Login: @srdan-bozovic-msft
• Microsoft Alias: srbozovi

[MarileeTurscak-MSFT]

@AndyHerb
Thanks for your feedback! We will investigate and update as appropriate.

[Mike-Ubezzi-MSFT]

@AndyHerb Did you happen upon the following step to prepare your subnet before the adding a Managed Instance? Validate and modify an existing virtual network (link)
The PS script you referenced is already part of a document. It appears you stumbled upon the GitHub repo without the document explaining that this needs to be run beforhand.

[AndyHerb]

Hi @Mike-Ubezzi-MSFT - the link you supplied was the original document we were working from, I simply linked directly to the source PS1 file that document references. That's the one we ran as-per the doc you linked to, which gave us a false-positive outcome on the Route Table. The SQL MI creation failed with the supplied error message (which wasn't as helpful as it could be because it doesn't point to specifically what's missing) so I wanted to bring this to your attention as it could save people time if:

1. The PS1 script correctly identified missing entries in the Route Table (I think it's only checking for the inclusion of 0.0.0.0/0 to the Internet (which, incidentally, isn't entirely accurate as in our case our Subnets are routed through an Azure Firewall, so this could still have worked)
2. The error message returned from the SQL MI creation, which would have saved us time if it had identified that we'd not got routes allowing traffic from the SQL MI subnet to our vNet

Hope that helps clarify,
Andy

[Mike-Ubezzi-MSFT]

@AndyHerb Perfect! I am going to forward this on to the content owner to be evaluated and updated as necessary.

[Mike-Ubezzi-MSFT]

@srdan-bozovic-msft @CarlRabeler For your awareness. Please let me know if there are additional actions required on my behalf. Thank you!

[CarlRabeler]

@stevestein for awareness

[srdan-bozovic-msft]

the script is fixed in the meantime

please-close

[Mike-Ubezzi-MSFT]

@AndyHerb We will now proceed to close this thread. If there are further questions regarding this matter, please comment and we will gladly continue the discussion.