When performing an SQL backup using the feature “backup to URL” in, you receive the error:
The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel
Backup to url received an exception from the remote endpoint
Essentially what this error is saying, is that the certificate being used is not trusted. This normally occurs when the certificate is a self signed certificate or it was issued by an non trusted CA. The Azure platform however, uses public trusted certificates, so why are we receiving this error?
The cause is generally due to SSL connections being routed through your corporate firewall/proxy with SSL inspection enabled.
Here’s how it works:
In outbound SSL inspection, when a client in the organization initiates an SSL connection to a secure site, the proxy:
- Intercepts the request.
- Establishes a secure connection to the requested web site and validates the site server certificate (this is the real certificate).
- Creates a new SSL certificate for the communication between the proxy and the client, sends the client the new certificate and continues the SSL negotiation with it (this is a spoofed certificate and cause of the error).
- Using the two SSL connections:
- It decrypts the encrypted data from the client.
- Inspects the clear text content for all blades set in the Policy.
- Encrypts the data again to keep client privacy as the data travels to the destination web server resource.
Step 3 is where our problem arises. At this step the proxy is generating a new (self signed) SSL certificate and presenting it to the client. If you remember above, all self signed certificates are non trusted by default. So from the applications points of view, which in our case is SQL server, it thinks the certificate is being spoofed for malicious purposes and therefore refuses the connection.
We have two (2) options to address this issue:
- Option 1 – Disable SSL inspection; or
- Option 2 – Configure the connection to bypass the proxy
Disable SSL Inspection
Option 1 is fairly easy to implement but brings with it it’s own security concerns. You will need to discuss this with your Network Team as to whether this is a viable solution, but once they know SSL inspection is the cause they should have some ideas on possible solutions. If this is not a feasible/acceptable option for your organisation, you’ll need to implement option 2.
Configure the connection to bypass your proxy
This is not as simple as it sounds. To understand the challenge, I’ll first explain how SQL server backs up data to a URL and how it uses a proxy.
There are hidden proxy settings
When you select the option “Backup to URL” in SQL server, it actually passes the backup job to a separate application outside of SQL server. This application is a small stand alone executable which has been specifically created for this purpose, named:
%programfiles%\Microsoft SQL Server\yourSQLversion\MSSQL\Binn\BackupToURL.exe
This exe has its own proxy settings independent of SQL server and is why Administrators get thrown off – they know their SQL server is not going through a proxy, so they immediately dismiss the error thinking it is erroneous. To complicate matters further, proxy settings for the application cannot be configured – these settings are inherited from Internet Explorer.
How we fixed it
In our case, SQL server wasn’t using a proxy but the IE settings were set to “automatically detect settings”. When this is ticked, IE uses WPAD to find a proxy server and relays SSL connections through it.
So when trying Backup to URL, SQL Server would pass the job to the BackupToURL.exe, the exe would then use the IE settings and send the backup job through the proxy server. This caused the job to fail, because the proxy was using SSL inspection and presenting a “fake” certificate from a non trusted source, prompting the error. The fix was to untick the checkbox in IE, then the backup went straight out to the Internet. This meant the connection wasn’t tampered with and the trust was maintained. This fixed the issue when running manual backups under an interactive session (logged into the server), but still failed when running under maintenance tasks.
Disable Proxy for the SQL service account
The reason it failed under maintenance tasks is because it uses the SQL agent account (service account) to run the backup jobs. IE settings are saved under the user profile, so when we update the proxy settings it only affected apps/tasks that run using that specific account. Since maintenance tasks run under another account (the service account), the settings were not applied to it. The solution here was to log into the server as the SQL agent account and disable the proxy in IE like above, then log back out. After restarting the SQL agent service the error should no longer appear.
If it is more convenient, you can configure all the above through group policy.