(SQL Server) AWS Secrets Manager - List Secrets

See more AWS Secrets Manager Examples

Lists the secrets that are stored by Secrets Manager in the AWS account. Lists the secrets that are stored by Secrets Manager in the AWS account.

For more information, see https://docs.aws.amazon.com/secretsmanager/latest/apireference/API_ListSecrets.html

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

// 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
    DECLARE @sTmp0 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- Sends the following sample request.
    -- Note: Chilkat will automatically add Content-Length, X-Amz-Date, Accept-Encoding, and Authorization

    -- POST / HTTP/1.1
    -- Host: secretsmanager.region.domain
    -- Accept-Encoding: identity
    -- X-Amz-Target: secretsmanager.ListSecrets
    -- Content-Type: application/x-amz-json-1.1
    -- X-Amz-Date: <date>
    -- Authorization: AWS4-HMAC-SHA256 Credential=<credentials>,SignedHeaders=<headers>, Signature=<signature>
    -- Content-Length: <payload-size-bytes>
    -- 
    -- {}

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

    -- Connect to the Amazon AWS REST server.
    -- such as https://secretsmanager.us-west-2.amazonaws.com/
    DECLARE @bTls int
    SELECT @bTls = 1
    DECLARE @port int
    SELECT @port = 443
    DECLARE @bAutoReconnect int
    SELECT @bAutoReconnect = 1
    DECLARE @success int
    EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'secretsmanager.us-west-2.amazonaws.com', @port, @bTls, @bAutoReconnect

    -- Provide AWS credentials for the REST call.
    DECLARE @authAws int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.AuthAws', @authAws OUT

    EXEC sp_OASetProperty @authAws, 'AccessKey', 'AWS_ACCESS_KEY'
    EXEC sp_OASetProperty @authAws, 'SecretKey', 'AWS_SECRET_KEY'
    -- the region should match our URL above..
    EXEC sp_OASetProperty @authAws, 'Region', 'us-west-2'
    EXEC sp_OASetProperty @authAws, 'ServiceName', 'secretsmanager'

    EXEC sp_OAMethod @rest, 'SetAuthAws', @success OUT, @authAws

    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Content-Type', 'application/x-amz-json-1.1'
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'X-Amz-Target', 'secretsmanager.ListSecrets'

    DECLARE @strResponse nvarchar(4000)
    EXEC sp_OAMethod @rest, 'FullRequestString', @strResponse OUT, 'POST', '/', '{}'
    EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @authAws
        RETURN
      END
    DECLARE @respStatusCode int
    EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @respStatusCode OUT

    PRINT 'response status code = ' + @respStatusCode

    DECLARE @jResp int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.JsonObject', @jResp OUT

    EXEC sp_OASetProperty @jResp, 'EmitCompact', 0
    EXEC sp_OAMethod @jResp, 'Load', @success OUT, @strResponse

    IF @respStatusCode >= 400
      BEGIN

        PRINT 'Response Status Code = ' + @respStatusCode

        PRINT 'Response Header:'
        EXEC sp_OAGetProperty @rest, 'ResponseHeader', @sTmp0 OUT
        PRINT @sTmp0

        PRINT 'Response Body:'
        EXEC sp_OAMethod @jResp, 'Emit', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @authAws
        EXEC @hr = sp_OADestroy @jResp
        RETURN
      END


    PRINT 'Response Body:'
    EXEC sp_OAMethod @jResp, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- Sample response body:

    -- {
    --   "SecretList":[
    --     {
    --       "ARN":"arn:aws:secretsmanager:us-west-2:123456789012:secret:MyTestDatabaseSecret-a1b2c3",
    --       "Description":"My test database secret",
    --       "LastChangedDate":1.523477145729E9,
    --       "Name":"MyTestDatabaseSecret",
    --       "SecretVersionsToStages":{
    --         "EXAMPLE2-90ab-cdef-fedc-ba987EXAMPLE":["AWSCURRENT"]
    --       }
    --     },
    --     {
    --       "ARN":"arn:aws:secretsmanager:us-west-2:123456789012:secret:AnotherDatabaseSecret-d4e5f6",
    --       "Description":"Another secret created for a different database",
    --       "LastChangedDate":1.523482025685E9,
    --       "Name":"AnotherDatabaseSecret",
    --       "SecretVersionsToStages":{
    --         "EXAMPLE3-90ab-cdef-fedc-ba987EXAMPLE":["AWSCURRENT"]
    --       }
    --     }
    --   ]
    -- }

    DECLARE @ARN nvarchar(4000)

    DECLARE @Description nvarchar(4000)

    DECLARE @LastChangedDate nvarchar(4000)

    DECLARE @Name nvarchar(4000)

    DECLARE @strVal nvarchar(4000)

    DECLARE @i int
    SELECT @i = 0
    DECLARE @count_i int
    EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'SecretList'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jResp, 'I', @i
        EXEC sp_OAMethod @jResp, 'StringOf', @ARN OUT, 'SecretList[i].ARN'
        EXEC sp_OAMethod @jResp, 'StringOf', @Description OUT, 'SecretList[i].Description'
        EXEC sp_OAMethod @jResp, 'StringOf', @LastChangedDate OUT, 'SecretList[i].LastChangedDate'
        EXEC sp_OAMethod @jResp, 'StringOf', @Name OUT, 'SecretList[i].Name'

        DECLARE @json2 int
        EXEC sp_OAMethod @jResp, 'ObjectOf', @json2 OUT, 'SecretList[i].SecretVersionsToStages'
        DECLARE @count int
        EXEC sp_OAGetProperty @json2, 'Size', @count OUT
        DECLARE @j int
        SELECT @j = 0
        WHILE @j < @count
          BEGIN
            DECLARE @versionName nvarchar(4000)
            EXEC sp_OAMethod @json2, 'NameAt', @versionName OUT, @j
            DECLARE @jarr int
            EXEC sp_OAMethod @json2, 'ArrayOf', @jarr OUT, @versionName
            DECLARE @stage nvarchar(4000)
            EXEC sp_OAMethod @jarr, 'StringAt', @stage OUT, 0

            PRINT 'versionName = ' + @versionName

            PRINT 'stage = ' + @stage
            SELECT @j = @j + 1
            EXEC @hr = sp_OADestroy @jarr

          END
        EXEC @hr = sp_OADestroy @json2

        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @rest
    EXEC @hr = sp_OADestroy @authAws
    EXEC @hr = sp_OADestroy @jResp


END
GO