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.
SQL MI Restore with valid SAS key failing with Operating System Error 86
We will provide details about a workaround for resolving the "Operating System Error 86" encountered during database restoration from Azure Storage on SQL Managed Instance. In this scenario, the Storage Account was not behind firewall and the credential already had a valid SAS token.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 issqlmi01.a1b2c3d4.database.windows.net and the CNAME isdb01.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, sosqlmi01.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 propertytrustServerCertificate to true. Although we should mention that this is not a recommended setting as that option is inherently insecure. Recently, there weresome new releasesand announcementsfor 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 hostNameInCertificatesetting, 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 yourparticular 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 EncryptConnection 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 #473:Harnessing the Synergy of Linked Server, Python, and sp_execute_external_script
In an era where data management transcends individual database systems, SQL Server offers a sophisticated feature set that includes Linked Server integration, Python scripting, and the powerful sp_execute_external_script function. The main objective of this approach is to leverage a Python script within SQL Server using sp_execute_external_script connecting to other database outside of SQL Server On-premise, for example, Azure SQL Database or Azure SQL Managed Instance as an alternative to employing the pyodbc library. This method not only streamlines processes but also addresses key concerns in security and network configuration, such as opening ports, which are prevalent when using external libraries for database connections. By focusing on querying a Linked Server, we can achieve seamless data integration and manipulation while maintaining a secure and efficient environment.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 #434:Adjusting NSG Port Ranges:Azure SQL Manage Instance Tier Migration from GP to BC
We encountered an interesting case with our customer who faced connectivity issues immediately after migrating from the General Purpose to Business Critical tier in Azure SQL Managed Instance. We would like to share the lessons learned from this experience and how we resolved the issue.Lesson Learned #424:Detecting and Notifying Deadlocks in Azure SQL Managed Instance
Deadlocks in any database system can be a performance nightmare, leading to transactions getting blocked and ultimately terminated. Azure SQL Managed Instance is no different. Thankfully, with Extended Events and Database Mail, we can monitor and promptly react to such occurrences. Today, we got a new service request that our customer request to have an example how to detect a deadlock and receive an email with the details. I would like to share an example, please, feel free to customize this code.Lesson Learned #360: Unsupported Key Size or Key Type. The supported RSA Key Size is 2048 or 3072.
We worked on a service request that our customer faced the following error message. Failed to save Transparent Data Encryption settings for SQL resource: azmsqldbunuatcog01. Error message: The key vault provided 'https://XYZ.vault.azure.net/keys/XYZ1/fdXXXXX on server 'ServerName' uses unsupported Key Size or Key Type. The supported RSA Key Size is 2048 or 3072 and Key Type is RSA or RSA-HSM.
