SQL Server

Generate an AWS (S3) Pre-Signed URL to Download File

See more Amazon S3 (new) Examples

Demonstrates how to generate a pre-signed URL to download a file from the Amazon S3 service. SQL Server

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @http int
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OASetProperty @http, 'AwsAccessKey', 'AWS_ACCESS_KEY'
    EXEC sp_OASetProperty @http, 'AwsSecretKey', 'AWS_SECRET_KEY'

    -- Make sure to set your AWS region correctly
    EXEC sp_OASetProperty @http, 'AwsRegion', 'us-east-1'

    DECLARE @bucketName nvarchar(4000)
    SELECT @bucketName = 'chilkat100'
    DECLARE @objectName nvarchar(4000)
    SELECT @objectName = 'starfish/starfish.jpg'
    DECLARE @localFilePath nvarchar(4000)
    SELECT @localFilePath = 'qa_output/starfish.jpg'
    DECLARE @awsService nvarchar(4000)
    SELECT @awsService = 's3'

    -- The signed URL will be valid for this number of seconds:
    DECLARE @numSecondsValid int
    SELECT @numSecondsValid = 86400

    -- Choose between a URL beginning with "http://" or "https://"...
    DECLARE @bUseHttps int
    SELECT @bUseHttps = 0

    -- Generate the pre-signed URL
    DECLARE @preSignedUrl nvarchar(4000)
    EXEC sp_OAMethod @http, 'S3_GenPresignedUrl', @preSignedUrl OUT, 'GET', @bUseHttps, @bucketName, @objectName, @numSecondsValid, @awsService
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        RETURN
      END

    -- The following URL can be copy-and-pasted into a browser..

    PRINT 'Pre-Signed AWS S3 URL: ' + @preSignedUrl

    -- Here's an example of a pre-signed URL. 
    -- This URL will only work within 1 day (86400 seconds) after the time this example was published.
    -- 
    --     http://chilkat100.s3.amazonaws.com/starfish/starfish.jpg?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=16QXXDGY83AJJR6RVQ02%2F20170111%2Fus-west-2%2Fs3%2Faws4_request&X-Amz-Date=20170111T161959Z&X-Amz-Expires=86400&X-Amz-SignedHeaders=host&X-Amz-Signature=72bf4fd9a15d7bbcdd313a3318d937a778905f62a3fbff9b789d8dc28acd9232

    -- Now try to do an HTTP Download using the signed URL.
    EXEC sp_OAMethod @http, 'Download', @success OUT, @preSignedUrl, 'c:/temp/qa_output/starfish.jpg'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        RETURN
      END


    PRINT 'Success.'

    EXEC @hr = sp_OADestroy @http


END
GO