TLS Handshake failed when connecting to Azure SQL

[urbim]

Describe the bug
When connecting to Azure SQL with the following connection string:

sqlserver://<sql_server_name>.0bec7b324353.database.windows.net:1433?database=<db_name>&fedauth=ActiveDirectoryDefault&encrypt=true&trustServerCertificate=false&hostNameInCertificate=*.0bec7b324353.database.windows.net&loginTimeout=30

I get the following exception:

TLS Handshake failed: tls: failed to verify certificate: x509: certificate is valid for tr66307.westeurope1-a.worker.database.windows.net, *.tr66307.westeurope1-a.worker.database.windows.net, *.hs1.tr66307.westeurope1-a.worker.database.windows.net, *.hs2.tr66307.westeurope1-a.worker.database.windows.net, *.hs3.tr66307.westeurope1-a.worker.database.windows.net, *.database.windows.net, *.secondary.database.windows.net, *.sql.azuresynapse.net, not *.0bec7b324353.database.windows.net

The problem seems to be the hostNameInCertificate parameter. If I omit this parameter the connection works. However this parameter is included in Azure Portal SQL Database Coonnection Strings section and I'm not sure that omitting it is the right thing to do.

Interestingly, if I set the hostNameInCertificate parameter to *.database.windows.net and leave everything else the same, the error is different:

TLS Handshake failed: tls: failed to verify certificate: x509: certificate is valid for *.0bec7b324353.database.windows.net, *.secondary.0bec7b324353.database.windows.net, *.public.0bec7b324353.database.windows.net, *.secondary.public.0bec7b324353.database.windows.net, 0bec7b324353.database.windows.net, not *.database.windows.net

To me, the error seems to suggest to set the parameter exactly to the value in my first example (and the connection strings provided in Azure Portal), that is why I think my first example should work.

To Reproduce

Connect to Azure SQL instance with the connection strings provided above.

Expected behavior

I expect no exception when connecting with the connection string in my first example. Alternatively, some assurance that omitting this parameter is ok from security perspective.

Further technical details

SQL Server version: Azure SQL (last test performed on 11. 12. 2024)
Operating system: Fedora Linux 39
go-mssqldb version: v1.8.0
go version: go1.22.9 linux/amd64

[shueybubbles]

Hmm. Maybe the string from the portal isn't a good one. @dlevy-msft can you take a look?

I don't think you would need to set hostnameincertficate for any Azure SQL connection. You'd be better off using Encrypt=strict and just let TLS do all the work for you; it will validate the identity of the server before any TDS packets get sent.

For comparison - SSMS 21 doesn't even let me enter the "Host name in certificate" value in the connection dialog unless I choose Strict encryption.