Option to auto-trust a changed certificate when a sync failure triggers a retry
Answered
Hi,
We frequently face "Revalidate TLS Certificate Error" whenever our source SQL Server restarts and a new certificate is issued. We want to have an option to auto-trust a changed certificate when Fivetran tries to sync the job after a certificate change.
By default, if no certificate is manually installed, SQL Server issues a fresh self-signed cert every time the service starts. When this occurs, Fivetran SQL Server connections detect a new certificate and asks us to re-validate it. We have nearly 1000+ SQL Server connections and automated configuration and management of these connections via Terraform. Going into the dashboard to rerun the setup test is not viable. Also we do not otherwise use the REST API and adding scripts to manage this via the API adds complexity and overhead to the management of these connections. Instead of requiring us to manually revalidate each certificate in the UI or build and schedule a custom REST API script to fix them, we would like to have Fivetran resolve this automatically during its standard sync retry process. E.g. can there be an option to configure default permissions for TLS certificates which would allow Fivetran to automatically accept and revalidate the new certificate whenever a sync failure triggers a retry, ensuring that connections are restored without requiring manual intervention.
Many thanks,
Ajit
-
Official comment
Hi Ajit,
Thanks for the detailed write-up, and for flagging this on behalf of the Q2 team.
We looked into this closely, and it turns out there's a way to eliminate the underlying issue. The root cause is that when no persistent certificate is installed on a SQL Server instance, SQL Server falls back to generating a fresh self-signed certificate every time the service starts. Because that certificate is regenerated on each restart, its thumbprint changes, and that's what triggers Fivetran's TLS revalidation requirement, forcing a manual re-trust step.
Rather than adding an auto-trust option (which would mean Fivetran silently accepting a new, unverified certificate on every restart, something that may create issues from a security standpoint), we'd recommend installing a persistent certificate (CA-signed, or a stable self-signed one you control) on your SQL Server instances. Once that's in place, the thumbprint stays stable across restarts, and you won't hit this revalidation prompt at all.
A few pointers that should help, especially given you're managing this at scale via Terraform:
1. Start here: Microsoft's guide to encrypting SQL Server connections by importing a certificate walks through installing the cert into the Windows cert store, granting the SQL Server service account access to the private key, and selecting it in SQL Server Configuration Manager.
2. Certificate requirements: the cert must sit in the local computer certificate store (not the service account store), use the AT_KEYEXCHANGE KeySpec, and its SAN needs to cover every hostname your clients use to connect. Using the local computer store specifically means you won't need to reconfigure the cert if the SQL Server startup account ever changes, which matters for a fleet of your size.
3. If you're on SQL Server 2019 or later, certificate management is built directly into SQL Server Configuration Manager, which simplifies deploying and managing certs across failover cluster instances and availability groups compared to the manual approach.
Since you're already managing 1000+ connections via Terraform, this should be a one-time cert deployment you can roll into your existing provisioning rather than an ongoing operational burden.
Let us know if this works for your team. Happy to dig in further if you hit any snags.
Best,
Rean Rodrigues - Product Manager, Fivetran
Please sign in to leave a comment.
Comments
1 comment