Skip to main content

Hi

 

I am trying to set up connection to an external SQL database (the supplier describes it as an ODBC connection). I am using the SQL non spatial reader. However, we have been told it needs to use SSL to connect. Is there an option in FME to use SSL for the connection please?

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.

 


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>

 


Reply