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. 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 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.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
    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.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.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 @json2 int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json2 OUT

    DECLARE @jarr int
    EXEC @hr = sp_OACreate 'Chilkat.JsonArray', @jarr OUT

    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'

        EXEC sp_OAMethod @jResp, 'ObjectOf2', @success OUT, 'SecretList[i].SecretVersionsToStages', @json2
        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
            EXEC sp_OAMethod @json2, 'ArrayOf2', @success OUT, @versionName, @jarr
            DECLARE @stage nvarchar(4000)
            EXEC sp_OAMethod @jarr, 'StringAt', @stage OUT, 0

            PRINT 'versionName = ' + @versionName

            PRINT 'stage = ' + @stage
            SELECT @j = @j + 1
          END

        SELECT @i = @i + 1
      END

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


END
GO