Blog Post

SQL Server Blog
3 MIN READ

ODBC Driver 18.0 for SQL Server Released

David-Engel's avatar
David-Engel
Steel Contributor
Feb 15, 2022

Version 18.0 of the Microsoft ODBC Driver 18 for SQL Server has been released. Version 18.0 brings several changes, new features, and fixes to the driver.

 

Features

  • Added compatibility with OpenSSL 3.0
  • Ability to send long types as max types
  • Support for TDS 8.0
  • Compatibility extensions for SQLGetData
  • Support for Ubuntu 21.10
  • Support for Debian 11

Changes

  • BREAKING CHANGE - Default Encrypt to Yes/Mandatory.
  • BREAKING CHANGE - Changed certificate validation behavior to validate when encryption is negotiated from either the client or the server side, not just the client side.

Fixes

  • Fixed UI issues where text was cut off and position of items was off.
  • Fixed issue with Active Directory Interactive login where attempting to login after closing the window of the first failure would automatically succeed if cached credentials were available.
  • Fixed use of XADTC with Azure SQL Managed Instance.
  • Fixed loss of Azure Active Directory authentication mode when reconnecting an idle connection.
  • Fixed an issue with federated authentication when using PingFed.

 

Breaking Changes

 

Encrypt = true, by default

There a couple breaking changes in 18.0 over previous releases that may affect a lot of users. Similar to the HTTP to HTTPS default changes made in web browsers a few years back (and the security reasons for them), we are changing the default value of the `Encrypt` connection option from `no` to `yes`/`mandatory`. (New encrypt values "mandatory" and "optional", synonymous with "yes" and "no", respectively, have been added to better describe encryption behavior.) With the increased emphasis on secure-by-default, the growing use of cloud databases, and the need to ensure connections are secure, it's time for this backwards-compatibility-breaking change. We realize this will cause some disruption, but letting clients try to connect without encryption by default leaves them open to attack from malicious actors.

 

We also changed the behavior of TrustServerCertificate to not be tied to the Encrypt setting. Previously, if Encrypt was set to no, certificates wouldn't be validated regardless of what TrustServerCertificate was set to. This allowed servers using self-signed certificates and Force Protocol Encryption to encrypt their client connections without requiring clients to change their default settings.

 

The action item if you are affected by the Encrypt change is to either (in order of recommendation):

  • Install a trusted certificate on your server.
  • Change your client's Encrypt connection string setting (or connection property) to optional/no.

If you are using a self-signed certificate and the Force Encryption setting on the server to ensure clients connect with encryption, you will need to do one of the following (in order of recommendation):

  • Change to a certificate that is trusted as part of the client's trust chain.
  • Add the self-signed certificate as a trusted certificate on the client.
  • Change your client's TrustServerCertificate connection string setting (or connection property) to yes.

If you maintain an application that uses the Microsoft ODBC Driver for SQL Server and you don't expose the Encrypt and TrustServerCertificate settings to your users, it's recommended that you make application changes to allow users to opt-in to non-encrypted connections or connections that use an untrusted certificate. It's also recommended that you expose the HostNameInCertificate setting. This new, related setting allows users to specify a known hostname defined in a certificate that doesn't match the hostname they need to use to connect to the server.

 

 

Next steps

For Windows installations, you can directly download the Microsoft ODBC Driver 18 for SQL Server.

Linux and macOS packages are also available. For installation details see the online instructions.

 

Roadmap

We are committed to improving quality and bringing more feature support for connecting to SQL Server Azure SQL Database Azure Synapse Analytics, and Azure SQL Managed Instance through regular driver releases. We invite you to explore the latest the Microsoft Data Platform has to offer via a trial of Microsoft Azure SQL Database or by evaluating Microsoft SQL Server.

David Engel

Updated Feb 15, 2022
Version 1.0
SQLServerDrivers
  • TomWickerath's avatar
    TomWickerath
    Copper Contributor
    Mar 01, 2022

    It would be really nice if Microsoft can create a video that shows how to "Install a trusted certificate on your server", for both stand-alone servers and Azure-based servers (including VM's in Azure). The video could also cover the three items that follow (in order of recommendation):
       

    • Change to a certificate that is trusted as part of the client's trust chain.
    • Add the self-signed certificate as a trusted certificate on the client.
    • Change your client's TrustServerCertificate connection string setting (or connection property) to yes.

     

    I'd like to see how this is done for a Microsoft Access "Front-End" application file, with data stored in SQL Server. 

     

    Thank you for your consideration.

  • fetzere's avatar
    fetzere
    Copper Contributor
    Dec 20, 2022

    Philip,

     

    Here is an example of my connection string with the Encrypt and TrustServerCertificate settings.   I am connecting to an older SqlServer that doesn't support TLS, so encryption is turned off.

     

    $conn = new PDO('sqlsrv:$url,1433;Database=$db;Encrypt=0;TrustServerCertificate=1', $user, $password);

     

    I also had to modify my /etc/ssl/openssl.cnf and appended this to the end:

    [default_conf]

    ssl_conf = ssl_sect

     

    [ssl_sect]

    system_default = system_default_sect

     

    [system_default_sect]

    MinProtocol = TLSv1

    CipherString = DEFAULT@SECLEVEL=0 

     

    Check this article out

     

    I was able to upgrade the ODBC Driver for SqlServer to Version 18.x and to successfully connect on my upgraded Ubuntu 22.04 system.

  • tonimaeder's avatar
    tonimaeder
    Copper Contributor
    Sep 23, 2022

    Are all versions of ODBC Driver for SQL Server from version 11 up to 18.x supported by Microsoft?

  • -jie-'s avatar
    -jie-
    Copper Contributor
    Jun 11, 2024

    对于bcp后面加 -u 参数就可以，比如：

    /usr/local/bin/bcp "$DATABASE.dbo.$TABLE" in "$DATA_FILE" -c -t"$DELIMITER" -r"\n" -S tcp:$SERVER,$PORT -U $USERNAME -P $PASSWORD -F 2 -u

     

    对于sqlcmd 后面加 -C 参数就可以，比如：

    /usr/local/bin/sqlcmd -S tcp:$SERVER,$PORT -U $USERNAME -P $PASSWORD -d $DATABASE -Q "IF OBJECT_ID('$TABLE', 'U') IS NOT NULL DROP TABLE [$TABLE]; $CREATE_TABLE_SQL" -C
  • JRaigoza's avatar
    JRaigoza
    Copper Contributor
    Jul 11, 2024

    Hi, it looks like the Trusted_Connection=yes|1|true not works, is a pain, still can't connect, only the Encrypt=0; works, like say fetzere, of course no encription is never recomended