0

I am setting up 32 and 64 bit ODBC connections on 2 identical SQL Servers running SQL 2019 CU18. These are part of the same cluster with AOAG. The primary node gave no problems with 32 or 64 bit ODBC(both readwrite and read only connections). The 32 bit ODBC on the secondarary also gave no problems(readwrite and readonly). When I tried to create the 64 bit ODBC connection on the secondary using SQL Server Native Client 11.0, I get the below error as soon as I click the drop down to select the default DB.

64 bit ODBC error

We do have force encryption enabled on SQL Server, but we are using just the default certificates(no self signed or ones bought from a CA).

Why would everything be fine on the primary, but not working for just the 64 bit ODBC on the secondary?

Primary node Encryption: Force Encyption primary node

Certificate not present in config manager: enter image description here

5
  • The default certificate is a self-signed one. As to why it's untrusted, why should it be trusted? Maybe the 32-bit connection is not actually connecting to the secondary, but to the primary, which has a trusted cert? Commented May 16, 2023 at 0:07
  • I meant to say we didn't explicitly create a self signed cert, but are using the one that SQL creates itself. We don't have any certificate selected in the SQL Server configuration manager on either node.
    – DBA Greg14
    Commented May 16, 2023 at 13:03
  • Either encryption is off on the primary node, or the certificate has been force-trusted, or you are mistaken. There is no way you have encryption switched on with a self-signed untrusted cert and are not getting an error. Commented May 16, 2023 at 13:18
  • I added to images from the primary showing force encyption is yes, and there is no certificate in sql configuration manager. How would i check that a certificate is force trusted?
    – DBA Greg14
    Commented May 16, 2023 at 15:44
  • I'm inclined to think that there actually is a certificate installed: the configuration manager is very buggy, and doesn't show certificates properly. Use Powershell $thumb = Get-ItemPropertyValue -Path 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\' -Name 'Certificate'; dir Cert:\ -Recurse | where Thumbprint -eq $thumb | fl; will show you if the certificate exists in the central certificate store and what it is. In other words: use the Registry to look up the certificate thumbprint, then search the store to find it. Commented May 16, 2023 at 15:51

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.