Error connecting to Azure Managed Instance through ODBC

Taha Souri 20 Reputation points
2024-08-20T19:51:42.22+00:00

I am unable to connect to the Azure Managed Instance using the OBDC SQL Server Driver 18. The error message says the connection string is not valid [87]. I have tried different ODBC driver versions and changing the TLS version on Azure portal but it did not resolve the issue. I can connect through other tools like MSSMS and PowerBI. What might be causing this error? Update:
It was actually what suspected. When copying the server name from the connection string to Teams and then copying again from Teams it adds some invisible characters that probably have to do with formatting. I copied the server name to a sticky note then I copied it again into ODBC and it worked. It has certainly driven me crazy, but I am thankful that it was a simple mistake.

Azure SQL Database

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Vinodh247 22,871 Reputation points
    2024-08-22T00:32:58.1033333+00:00

    Check for any typos or missing components in the connection string.

    Driver={ODBC Driver 18 for SQL Server};Server=<server_name>.database.windows.net;Database=<database_name>;UID=<username>;PWD=<password>;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;

    If the above steps don't resolve the issue, you might want to capture detailed ODBC logs to identify where the connection is failing.

    Driver Compatibility:

    • You mentioned trying different ODBC driver versions. However, ensure you are using a version that supports TLS 1.2 or higher, as Azure Managed Instances require TLS 1.2 for security reasons.
    • It's recommended to use the latest ODBC Driver 18, but if issues persist, you can try downgrading to ODBC Driver 17, which may have better compatibility in some cases.

    TLS Version Configuration:

    • Even though you adjusted the TLS version on the Azure portal, ensure that your client machine also supports and uses TLS 1.2. You can force the ODBC driver to use TLS 1.2 by adding Encrypt=yes;TrustServerCertificate=no; in the connection string.

    ODBC Driver Setup:

    • Ensure the ODBC driver is correctly installed on your machine. Sometimes, a corrupt or incomplete installation can lead to connection issues. Reinstall the ODBC driver and try connecting again.

    SQL Authentication:

    • If you're using SQL Authentication, verify that the username and password are correct. Ensure that the account has the necessary permissions to access the database.

    Networking Configuration:

    • Since you can connect via MSSMS and PowerBI, it's less likely a networking issue, but double-check that your machine can resolve the Managed Instance's DNS name correctly.
    • Make sure there are no firewall or network settings blocking ODBC traffic.

    Driver-Specific Issues:

    • ODBC drivers sometimes have bugs or quirks that might not affect other tools like SSMS or PBI. Try connecting using a DSN (Data Source Name) instead of a direct connection string to see if that resolves the issue.

    If the above steps don't resolve the issue, you might want to capture detailed ODBC logs to identify where the connection is failing.

    Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.