Blog Post

Azure Database Support Blog
5 MIN READ

Testing connectivity against a Managed Instance using a CNAME

luisaranda's avatar
luisaranda
Icon for Microsoft rankMicrosoft
Jul 28, 2022

As documented hereWe 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

 

  1. 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​.
  2. 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 :smile:

 

Cheers!

Updated Jul 27, 2022
Version 1.0
  • Thank you luisaranda  for the detailed article and it saved lot of time. 

     

    However, I am running into an issue. I am using Excel to connect to the Managed Instance with a CNAME. I looked at options to set excel to trust server cert and came out empty. I started looking to the OLEDB drivers, noticed that OLEDB 19 for SQL serve supports it however, when I try it does not work.  Where as ODBC 18 is working fine. I get an authentication error. Can you provide a sample connection string that worked for you with Encryption on and Trust server cert to true.

     

    It works fine from SSMS as well.