Testing connectivity against a Managed Instance using a CNAME
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!Lesson Learned #470: Resolving 'EXECUTE Permission Denied' Error on sp_send_dbmail in Azure SQL MI
We worked on a service request that our customer encountering an error message "Executed as user: user1. The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229). The step failed.", I would like to share with you how was the resolution for this specific error message.Lesson Learned #430: Addressing Error 3201 with Azure Blob Storage: "Operating system error 86"
Today, I worked on a service request that our customer got the following error message: Cannot open backup device 'https://XXX.blob.core.windows.net/NNN/YYY.bak'. Operating system error 86(The specified network password is not correct.). RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201). Following I would like to share with you some details why this issue and the activities done to resolve it.
