SSIS with CData SSIS Components for Gmail fails when run on SQL server when run with proxy account
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.
Page 1 / 1
Hi @GritKit
The error message that you are getting "m500] 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:
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.
@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:
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).
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.
Since I no longer had sensitive data stored in the package, I set the ProtectionLevel to DontSaveSensitive.
