SSIS package to upload file to s3 bucket fails with "The request signature we calculated does not match the signature you provided. Check your key and signing method."

Xander 1 Reputation point
2022-03-25T18:53:44.16+00:00

The package was written in Visual Studio, and I can successfully execute the package there. However, when I run the package from SQL it fails with the below context. I do not have access to AWS; I am provided with keys. I don't think that should matter though, since clearly the keys are correct, as files are successfully uploaded when executed in Visual Studio. I also contacted Cozyroc, which indicated the issue wasn't with Cozyroc, since the package successfully executes in Visual Studio. As such, Cozyroc's position is one I am inclined to agree with. This package used to function fine as a job in SSIS, but stopped working when I updated the package with new keys from AWS and deployed a new package. That was back in August. I have whittled this down to either being an issue with the server environment (maybe my IT pushed out an update and it messed something up) or with SQL Server Integration Services. Any help would be appreciated.

Messages  > Error Context
    on *** at 3/25/2022 2:41:01 PM

    The report shows the context for an error.

        Error Message:
Upload File Amazon S3 Task:Error: An error occurred with the following error message: "The request signature we calculated does not match the signature you provided. Check your key and signing method.".

        Execution Information

        Operation ID    89099

Package VWBH\VWBH\VWBH_UploadToS3.dtsx
Environment -
Status Failed
Duration (sec) 3.936
Start Time 3/25/2022 12:23:28 PM
End Time 3/25/2022 12:23:32 PM
Caller AS***

                Filter:

Context Depth: 1;  (2 more)

        Connection Managers                                 
        Package Path    Context Source Name Property Name   Property Value  Context Depth

        Variables                               
        Package Path    Context Source Name Property Name   Property Value

        All Context Information                                 
        Package Path    Context Source Name Property Name   Property Value  Context Depth

\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task AccessPolicy 0 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task Action 0 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task AmazonS3Connection AmazonS3Connection 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task Bucket 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task ContentType application/octet-stream 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task CreationName CozyRoc.SqlServer.SSIS.AmazonS3Task, CozyRoc.SSISPlus.2017, Version=1.0.0.0, Culture=neutral, PublicKeyToken=*** 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task DebugMode False 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task DelayValidation False 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task Description Amazon S3 Task 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task Disable False 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task DisableEventHandlers False 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task ExecutionDuration 0 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task ExecutionResult 0 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task ExecutionStatus 3 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task ExecutionValue 0 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task ExtendedRemoteFileInfo True 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task FailPackageOnFailure False 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task FailParentOnFailure False 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task Filter 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task ForcedExecutionValue 0 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task ForceExecutionResult -1 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task ForceExecutionValue False 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task ID {28684062-19EE-47A6-BF44-549850EEBF57} 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task IncludeSubfolders False 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task IsDefaultLocaleID True 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task IsFilterVariable False 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task IsLocalVariable False 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task IsolationLevel 1048576 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task IsRemoteVariable False 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task IsRemoteWildcard True 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task Local UploadFile 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task LocaleID 1033 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task LoggingMode 0 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task MaximumErrorCount 1 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task Name Upload File Amazon S3 Task 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task NoFilesFail True 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task NoRemoteFilesFail True 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task Remote /adobe-analytics-mta/VW/ 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task Result 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task StartTime 3/25/2022 12:23:30 PM 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task StopOnFailure True 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task StopTime 3/25/2022 12:23:29 PM 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task SuspendRequired False 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task TargetServerVersion 140 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task TaskDescription Provides actions for working with the Amazon S3 service. You can upload, download files or manage the folder structure. 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task TaskDisplayName Amazon S3 Task 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task TransactionOption 1 1
\Package\Upload Files to Amazon S3\Upload File Amazon S3 Task Upload File Amazon S3 Task Version 0 1

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,486 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. ZoeHui-MSFT 34,576 Reputation points
    2022-03-28T01:50:44.543+00:00

    Hi @Xander ,

    I'm not familiar with 3rd party product, so just analyze the issue from SSIS side.

    You could run the package successfully but it fails when you run it via SQL agent job.

    Usually, the issue maybe related with the account permission.

    When you see a SSIS package fails running in a SQL Agent job, you need to first consider the following conditions:

    1. The user account that is used to run the package under SQL Server Agent differs from the original package author.
    2. The user account does not have the required permissions to make connections or to access resources outside the SSIS package.

    You may try to use a SQL Server Agent proxy account or set the SSIS Package ProtectionLevel.

    More details please refer to ssis-package-doesnt-run-when-called-job-step

    Regards,

    Zoe


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  2. Xander 1 Reputation point
    2022-04-05T16:40:46.61+00:00

    Thanks for the response.

    Can you tell me why it would matter if the user account running the package in SQL being different from the original package author would matter? My account is different from the original author's, and the package still works in Visual Studio. A service account runs the package in SQL Server Agent and always has, even before it started failing. Granted, the package stopped working after the first time I made a change to it (and I am not the original author). But the package is run from a service account in SQL Server Agent, which has not changed. I think you're on to something, but I don't understand yet how it these things can matter.

    Thanks.

    0 comments No comments

  3. Xander 1 Reputation point
    2022-04-06T17:48:03.21+00:00

    Here is the resolution:

    I was looking at the SQL Server Agent Job, right-clicked and chose Script Job As-> Create To -> New Query Editor Window. In the resulting syntax, I saw the line

    @commandcommand =N'/ISSERVER "\"[path to package]"" /SERVER [server]/Par "\"CM.AmazonS3Connection.ServerPassword\""[password] /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E',

    That password line had an old S3 bucket secret access key in it. In the days I've spent trying to solve this, I've never seen a parameter anywhere where that password is entered. I still have no idea where it's coming from. However, I was able to create a new SQL Server Agent Job using the same SSIS package with the server password stored as a variable in the environment. It worked fine after that.

    0 comments No comments