Encrypt=No seems to be required in connection string

[nsaintgeours]

Hello,

I am trying to run dbt debug on a MS SQL server that I have few information about. Here is the Dbt profile I use (host and password hidden):

data_transformation:
  outputs:
    prod:
      type: sqlserver
      driver: 'ODBC Driver 18 for SQL Server'
      server: XXXX.XXXXXXXXXX.fr\
      port: 1433
      database: elweb
      schema: elweb
      user: stephen
      password: XXXXXXXXX
      encrypt: false

  target: prod

When running dbt debug, I get this error:

(data-transformation) C:\dev\data_transformation>dbt debug
21:41:52  Running with dbt=1.1.0
dbt version: 1.1.0
python version: 3.9.10
python path: C:\venv\data-transformation\Scripts\python.exe
os info: Windows-10-10.0.19044-SP0
Using profiles.yml file at C:\Users\natha\.dbt\profiles.yml
Using dbt_project.yml file at C:\dev\data_transformation\dbt_project.yml

Configuration:
  profiles.yml file [←[32mOK found and valid←[0m]
  dbt_project.yml file [←[32mOK found and valid←[0m]

Required dependencies:
 - git [←[32mOK found←[0m]

Connection:
  server: XXXXXX.XXXXX.fr\
  database: elweb
  schema: elweb
  port: 1433
  UID: stephen
  client_id: None
  authentication: sql
  encrypt: False
  trust_cert: False
  Connection test: [←[31mERROR←[0m]

←[31m1 check failed:←[0m
dbt was unable to connect to the specified database.
The database returned the following error:

  >Database Error
  ('08001', "[08001] [Microsoft][ODBC Driver 18 for SQL Server]Fournisseur SSL : La chaîne de certificats a été fournie par une autorité qui n’est pas a
pprouvée.\r\n (-2146893019) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 18 for SQL Server]Le client n'a pas pu établir la connexion (-2146893019
); [08001] [Microsoft][ODBC Driver 18 for SQL Server]Attribut de chaîne de connexion non valide (0)")

Possible solution?

I was able to get dbt debug working fine by just adding two lines in connections.py, line 353, as follows:

            if getattr(credentials, "encrypt", False) is True:
                con_str.append("Encrypt=Yes")
                if getattr(credentials, "trust_cert", False) is True:
                    con_str.append("TrustServerCertificate=Yes")

            # The two lines below were added:
            else:
                con_str.append("Encrypt=No")

When I run dbt debug, I now get:

(data-transformation) C:\dev\data_transformation>dbt debug
21:50:30  Running with dbt=1.1.0
dbt version: 1.1.0
python version: 3.9.10
python path: C:\venv\data-transformation\Scripts\python.exe
os info: Windows-10-10.0.19044-SP0
Using profiles.yml file at C:\Users\natha\.dbt\profiles.yml
Using dbt_project.yml file at C:\dev\data_transformation\dbt_project.yml

Configuration:
  profiles.yml file [←[32mOK found and valid←[0m]
  dbt_project.yml file [←[32mOK found and valid←[0m]

Required dependencies:
 - git [←[32mOK found←[0m]

Connection:
  server: XXXX.XXXXX.fr\
  database: elweb
  schema: elweb
  port: 1433
  UID: stephen
  client_id: None
  authentication: sql
  encrypt: False
  trust_cert: False
  Connection test: [←[32mOK connection ok←[0m]

←[32mAll checks passed!←[0m

Environment details

• Microsoft Windows [version 10.0.19044.1766]
• Driver: ODBC Driver 18 for SQL Server
• Python 3.9.10
• dbt-core 1.1.0
• dbt-sqlserver 1.1.0

[dataders]

this seems to be related to #219 this has to do with security default changes made with the release of MS ODBC Driver 18. This doc has the info. In, short we have work to do here, but that's for the code suggestion!

[dataders]

TODO: add docs on securely connecting

[sdebruyn]

Fixed on master, will be part of next release. Encrypt and TrustServerCertificate are now always set. Details about how these changes might affect you are in the CHANGELOG and will be added to the official docs.