Solving SSL Certification Errors in SSIS Downloads from HTTPS
Introduction
When working with SQL Server Integration Services (SSIS), you may encounter SSL certification errors while downloading files over HTTPS. This often occurs when executing packages outside of Visual Studio, such as from a SQL Agent job or directly from the server.
In this post, we’ll troubleshoot a common scenario where an SSIS package fails to download a CSV file from an HTTPS URL due to an invalid SSL certificate response. We’ll provide a step-by-step guide to resolve this issue effectively.
Understanding the Problem
The primary error message “SSL certificate response obtained from the server was not valid” suggests that the SSL certificate presented by the server is either expired, self-signed, or not trusted by the SSIS package’s environment.
Troubleshooting Steps
1. Verify Certificate Validity on the Server
First, ensure that the SSL certificate on the server hosting the CSV file is valid and issued by a trusted certification authority (CA). You can use tools like OpenSSL or browser developer tools to check the certificate details.
Visit the URL in question from the SQL server where you are experiencing the error. If the website is redirecting correctly to HTTPS, but the SSIS package is still failing, proceed to the next steps.
2. Check System Account Permissions
When executing a package using a SQL Agent job or directly from the server, the context is likely running under a system account with reduced permissions compared to your development environment. Ensure that the account has sufficient network access and SSL certificate trust levels to process the HTTPS request.
3. Update Bypass Certificate Validation Code
In your C# script within the SSIS package, you mentioned adding code to ignore certificate warnings. However, it seems that the current implementation is not effectively bypassing the validation errors.
Update your Main method as follows:
public void Main()
{
// TODO: Add your code here
try
{
ServicePointManager.ServerCertificateValidationCallback =
(sender, cert, chain, sslPolicyErrors) => true;
bool fireAgain = true;
Dts.Events.FireInformation(0, "Download File", "Start downloading " + Dts.Connections["TEST"].ConnectionString, string.Empty, 0, ref fireAgain);
Object mySSISConnection = Dts.Connections["TEST"].AcquireConnection(null);
HttpClientConnection myConnection = new HttpClientConnection(mySSISConnection);
String filename = Dts.Variables["varFullPathScript"].Value.ToString();
myConnection.DownloadFile(filename, true);
Dts.Events.FireInformation(0, "Download File", "Finished downloading", string.Empty, 0, ref fireAgain);
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
Dts.Events.FireError(0, "Download File", "Download failed: " + ex.Message, string.Empty, 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
Dts.TaskResult = (int)ScriptResults.Success;
}
4. Check Network Settings and Firewall Rules
Ensure that the network settings on the SQL server allow outbound traffic to the HTTPS URL in question. Check the firewall rules on both the client and server sides to confirm that port 443 is open for communication.
Conclusion
Resolving SSL certification errors in SSIS downloads often requires a combination of verifying certificate validity, checking system account permissions, updating bypass code effectively, and ensuring proper network connectivity.
Remember that disabling certificate validation should be used as a last resort only when necessary. It’s generally recommended to ensure your servers have valid certificates issued by trusted authorities for optimal security.
If you’re still facing issues after following these troubleshooting steps, feel free to explore our detailed guides on SSIS best practices and advanced scripting at Laravel Company.
Happy coding!