Read data from external SQL database (ODBC connection) which requires SSL

Hi @markcoopersdc​ 

When creating a Database connection or Embeded connection, you need to click on the box to next "Encrypt Connection" parameter to enable SSL support see documentation here

NOTE:

When Encrypt Connection is selected, please provide a fully qualified Server name. For example, a server named safe-sql-server might have a fully qualified name of safe-sql-server.dev.safe. This fully qualified name should be an exact match for the server name on the trusted certificate. The certificate needs to be present on the client machine where FME Desktop/Server is installed.

Thanks for the reply. I have had a look now at the documentation and, unfortunately, I do not really understand much of it! It says "There are multiple ways to trust a server certificate on a client machine" but then doesn't go on to explain any of them! Do I need to ask the external company whose database we are connecting to email a copy of the certificate file to go on my fme machine, if that's even a possibility? If so, where does it need to go? Thanks

I got some advice from GCP Support trying to connect AppEngine to CloudSQL with sslMode = “TRUSTED_CLIENT_CERTIFICATE_REQUIRED”.

• First, when connecting to the Cloud SQL instance, you need to use the dns name of the SQL Server e.g. 14-XXXX-XXXX-4ac4-b1d3-XXXXX.us-west3.sql.goog instead of IP address or instance name. But this dns name is not easy to obtain except from the error logs. 

• Disable verifying server certificate: turn off verify-full form. Example:
  • psql "sslmode=verify-ca sslrootcert=server-ca.pem sslcert=client-cert.pem sslkey=client-key.pem hostaddr=35.222.184.209 port=5432 user=postgres dbname=postgres host=my-tu-test:pg-a"

• • psql "sslmode=verify-full sslrootcert=server-ca.pem sslcert=client-cert.pem sslkey=client-key.pem hostaddr=35.222.184.209 port=5432 user=postgres dbname=postgres host=my-tu-test:pg-a"

ALLOW_UNENCRYPTED_AND_ENCRYPTED, is a cloud SQL concept and is a bit different from the DB engine ssl mode verify-full.

It is much easier to connect with sslMode=”ENCRYPTED_ONLY”, if your SecOps team allows it. Then the above steps are not necessary to make a connection with just: 

  database:

    ssl: true

    hostname: <IPaddress>