Skip to main content
Solved

SSIS with CData SSIS Components for Gmail fails when run on SQL server when run with proxy account

  • May 21, 2025
  • 2 replies
  • 128 views
G
Forum|alt.badge.img


The SSIS package runs fine on my local computer, but it fails with the following errors when run on the SQL Server.

 

 

Here is our setup:

  • Google API Client ID + Client Secret have been generated using service account.
  • SSIS package is run by an SQL Agent Job using proxy account.
  • Path to OAuthSettings.txt is specified as C:\Users\Public\Documents\GMail. Once I had the OAuthSettings.txt generated, I placed a copy to C:\Users\Public\Documents\GMail on the SQL server.
  • On my local computer I had run SSIS in Visual Studio and created a consent using the service account that I created Google API Client ID with.
  • SQL Server is on-prem and is accessible via RDC.
  • No Visual Studio is installed on the SQL Server.

Am I missing anything in the above? Please advise.

Best answer by GritKit

​@Ethem Q , thank you for your quick response. Your solution did not work for me due to my specific setup, but it helped me to find what works.

I had EncryptSensitiveWithUserKey protection level set by default, but I also had the Client Secret being removed from the package when deployed to the server. Our packages are stored in SSISDB database, not physically on the server.

Here is what works in my case:

  1. I created a project parameter and instructed the CData Gmail Connection Manager to use it for OAuthClientSecret property instead of a hardcoded Client Secret Value (that would be automatically removed during deployment).
  2. Then I added a code to my SQL Server configuration script to create this project parameter on the server. As a result, when the package gets deployed to the server, its Client Secret value is being supplied from the project parameter.
  3. Since I no longer had sensitive data stored in the package, I set the ProtectionLevel to DontSaveSensitive.
    This topic has been closed for replies.

    2 replies

    Ethem Q
    Forum|alt.badge.img
    • Ethem Q
    • Employee
    • May 23, 2025

    Hi ​@GritKit 

    The error message that you are getting "[500] OAuthClientId and OAuthClientSecret are needed to initiate OAuth" is normally thrown when the OAuthClientId and OAuthClientSecret connection properties are missing in the connection manager. It could be that these connection properties which are considered sensitive are being cleared when the package is ran because of the ProtectionLevel of the package. Can you confirm what value you have set for the ProtectionLevel?


    You might want to read more on possible recommendations how to fix the issue below:

    https://learn.microsoft.com/en-us/troubleshoot/sql/integration-services/ssis-package-doesnt-run-when-called-job-step

     

    If you have any other questions or issues you can reach out to the support team at [email protected] and one of our support specialists will further assist you.

      G
      Forum|alt.badge.img
      • GritKit
      • Author
      • Apprentice
      • Answer
      • May 28, 2025

      ​@Ethem Q , thank you for your quick response. Your solution did not work for me due to my specific setup, but it helped me to find what works.

      I had EncryptSensitiveWithUserKey protection level set by default, but I also had the Client Secret being removed from the package when deployed to the server. Our packages are stored in SSISDB database, not physically on the server.

      Here is what works in my case:

      1. I created a project parameter and instructed the CData Gmail Connection Manager to use it for OAuthClientSecret property instead of a hardcoded Client Secret Value (that would be automatically removed during deployment).
      2. Then I added a code to my SQL Server configuration script to create this project parameter on the server. As a result, when the package gets deployed to the server, its Client Secret value is being supplied from the project parameter.
      3. Since I no longer had sensitive data stored in the package, I set the ProtectionLevel to DontSaveSensitive.
        State of AI Data Connectivity Report: 2026 Outlook
        Terms and ConditionsCookie settingsAccessibility statement