As documented here. We can change the SQL Managed Instance default DNS zone ".database.windows.net" to any of your choice. However, the instance name part of the FQDN is mandatory.
Whether you are connecting through a public or private endpoint, the virtual cluster manages the connection and then either proxies or redirects it to the appropriate SQL MI resident within the same cluster based on the connection type configured.
The virtual cluster utilizes the hostname part of the FQDN in the connection string to lookup for the MI. If the instance name doesn't match an existing one, the connection attempt will fail.
For instance, if the FQDN is sqlmi01.a1b2c3d4.database.windows.net and the CNAME is db01.contoso.com it will not work
- A workaround is to create a 'user@sqlmi01' login on sqlmi01 and pass it in the connection string using SQL Server authentication. This explicitly tells the virtual cluster to disregard the hostname in the FQDN and look up a managed instance called sqlmi01.
- Another option is to use the same hostname in the CNAME record, so sqlmi01.contoso.com would work. Although this won't suit multiple SQL MIs because SQL MI names are globally unique.
One thing to note is that when using option 2 from the section above, is that all encrypted connections will fail by default. Reason being that the server-side certificate will not match with the FQDN provided in the connection string. There are a few ways around this.
The first one is to use set the property trustServerCertificate to true. Although we should mention that this is not a recommended setting as that option is inherently insecure.
Recently, there were some new releases and announcements for ODBC and OLEDB drivers, I was curious enough to set a test with a CNAME pointing to a managed instance public endpoint and perform some tests with the new settings of said drivers. Specifically, the hostNameInCertificate setting, that could help us with the problem described in option 2 above.
As explained on the articles linked above the new releases of ODBC (18) and OLEDB (19) have a few breaking changes, that could impact the connectivity for your particular scenario.
Below are the results of my tests using SSMS (.Net SqlClient), ODBC (17 and 18) and OLEDB (18 and 19).
Test # | Tool | Version | Authentication | Encrypt Connection | Encrypt Value | Trust Server Certificate | Hostname in Certificate | Result |
1 | SSMS (.Net SqlClient) | 18.11.1 | SQL | FALSE | N/A | FALSE | N/A | Success |
2 | SSMS (.Net SqlClient) | 18.11.1 | SQL | TRUE | N/A | FALSE | N/A | Failure |
3 | SSMS (.Net SqlClient) | 18.11.1 | SQL | TRUE | N/A | TRUE | N/A | Success |
4 | SSMS (.Net SqlClient) | 18.11.1 | AAD | FALSE | N/A | FALSE | N/A | Failure |
5 | SSMS (.Net SqlClient) | 18.11.1 | AAD | TRUE | N/A | FALSE | N/A | Failure |
6 | SSMS (.Net SqlClient) | 18.11.1 | AAD | TRUE | N/A | TRUE | N/A | Success |
7 | ODBC | 17.9.1.1 | SQL | FALSE | N/A | FALSE | N/A | Success |
8 | ODBC | 17.9.1.1 | SQL | TRUE | N/A | FALSE | N/A | Failure |
9 | ODBC | 17.9.1.1 | SQL | TRUE | N/A | TRUE | N/A | Success |
10 | ODBC | 17.9.1.1 | AAD | FALSE | N/A | FALSE | N/A | Success |
11 | ODBC | 17.9.1.1 | AAD | TRUE | N/A | FALSE | N/A | Failure |
12 | ODBC | 17.9.1.1 | AAD | TRUE | N/A | TRUE | N/A | Success |
13 | OLEDB | 18.6.3 | SQL | FALSE | N/A | FALSE | N/A | Success |
14 | OLEDB | 18.6.3 | SQL | TRUE | N/A | FALSE | N/A | Failure |
15 | OLEDB | 18.6.3 | SQL | TRUE | N/A | TRUE | N/A | Success |
16 | OLEDB | 18.6.3 | AAD | FALSE | N/A | FALSE | N/A | Failure |
17 | OLEDB | 18.6.3 | AAD | TRUE | N/A | FALSE | N/A | Failure |
18 | OLEDB | 18.6.3 | AAD | TRUE | N/A | TRUE | N/A | Failure |
19 | ODBC | 18.0.1.1 | SQL | FALSE | Optional | FALSE | Blank | Failure |
20 | ODBC | 18.0.1.1 | SQL | FALSE | Optional | FALSE | Set | Success |
21 | ODBC | 18.0.1.1 | SQL | FALSE | Optional | TRUE | Blank | Success |
22 | ODBC | 18.0.1.1 | SQL | TRUE | Mandatory | FALSE | Blank | Failure |
23 | ODBC | 18.0.1.1 | SQL | TRUE | Mandatory | FALSE | Set | Success |
24 | ODBC | 18.0.1.1 | SQL | TRUE | Mandatory | TRUE | Blank | Success |
25 | ODBC | 18.0.1.1 | SQL | TRUE | Strict | N/A | Blank | Failure |
26 | ODBC | 18.0.1.1 | SQL | TRUE | Strict | N/A | Set | Failure |
27 | ODBC | 18.0.1.1 | AAD | FALSE | Optional | FALSE | Blank | Failure |
28 | ODBC | 18.0.1.1 | AAD | FALSE | Optional | FALSE | Set | Success |
29 | ODBC | 18.0.1.1 | AAD | FALSE | Optional | TRUE | Blank | Success |
30 | ODBC | 18.0.1.1 | AAD | TRUE | Mandatory | FALSE | Blank | Failure |
31 | ODBC | 18.0.1.1 | AAD | TRUE | Mandatory | FALSE | Set | Success |
32 | ODBC | 18.0.1.1 | AAD | TRUE | Mandatory | TRUE | Blank | Success |
33 | ODBC | 18.0.1.1 | AAD | TRUE | Strict | N/A | Blank | Failure |
34 | ODBC | 18.0.1.1 | AAD | TRUE | Strict | N/A | Set | Failure |
35 | OLEDB | 19.0.0 | SQL | FALSE | Optional | FALSE | Blank | Failure |
36 | OLEDB | 19.0.0 | SQL | FALSE | Optional | FALSE | Set | Success |
37 | OLEDB | 19.0.0 | SQL | FALSE | Optional | TRUE | Blank | Failure |
38 | OLEDB | 19.0.0 | SQL | TRUE | Mandatory | FALSE | Blank | Failure |
39 | OLEDB | 19.0.0 | SQL | TRUE | Mandatory | FALSE | Set | Success |
40 | OLEDB | 19.0.0 | SQL | TRUE | Mandatory | TRUE | Blank | Failure |
41 | OLEDB | 19.0.0 | SQL | TRUE | Strict | N/A | Blank | Failure |
42 | OLEDB | 19.0.0 | SQL | TRUE | Strict | N/A | Set | Failure |
43 | OLEDB | 19.0.0 | AAD | FALSE | Optional | FALSE | Blank | Failure |
44 | OLEDB | 19.0.0 | AAD | FALSE | Optional | FALSE | Set | Success |
45 | OLEDB | 19.0.0 | AAD | FALSE | Optional | TRUE | Blank | Failure |
46 | OLEDB | 19.0.0 | AAD | TRUE | Mandatory | FALSE | Blank | Failure |
47 | OLEDB | 19.0.0 | AAD | TRUE | Mandatory | FALSE | Set | Success |
48 | OLEDB | 19.0.0 | AAD | TRUE | Mandatory | TRUE | Blank | Failure |
49 | OLEDB | 19.0.0 | AAD | TRUE | Strict | N/A | Blank | Failure |
50 | OLEDB | 19.0.0 | AAD | TRUE | Strict | N/A | Set | Failure |
As we can see there might be a difference on the result depending on the driver, the settings and the version used. Whenever possible try updating and testing your drivers to ensure the stability of your connections
Cheers!