SQL Server

How to Generate an Azure Storage Account Shared Access Signature (SAS)

See more Azure Cloud Storage Examples

Shows how to generate a Shared Access Signature (SAS) for an Azure Storage Account.

Note: This example requires Chilkat v9.5.0.65 or greater.

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)
    -- Note: Requires Chilkat v9.5.0.65 or greater.

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

    -- --------------------------------------------------------------------------
    -- Create a Shared Access Signature (SAS) token for an Azure Storage Account.
    -- --------------------------------------------------------------------------

    -- See https://docs.microsoft.com/en-us/rest/api/storageservices/fileservices/constructing-an-account-sas 
    -- for details regarding the Azure Storage Account SAS fields.

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

    EXEC sp_OASetProperty @authSas, 'AccessKey', 'AZURE_ACCESS_KEY'

    -- Specify the format of the string to sign.
    -- Each comma character in the following string represents a LF ("\n") character.
    -- The names specified in the StringToSign are replaced with the values specified
    -- in the subsequent calls to SetTokenParam and SetNonTokenParam,.

    -- Note: The trailing comma in the StringToSign is intentional and important. This indicates that the 
    -- string to sign will end with a "\n".

    -- Also note: The names in the StringToSign are case sensitive.  The names
    -- specified in the 1st argument in the calls to SetNonTokenParam and SetTokenParam should
    -- match a name listed in StringToSign. 
    EXEC sp_OASetProperty @authSas, 'StringToSign', 'accountname,signedpermissions,signedservice,signedresourcetype,signedstart,signedexpiry,signedIP,signedProtocol,signedversion,'

    --  The account name is "chilkat".  Use your own account name instead of "chilkat".
    --  Also use your own container name instead of "mycontainer".
    DECLARE @success int
    EXEC sp_OAMethod @authSas, 'SetNonTokenParam', @success OUT, 'accountname', 'chilkat'

    EXEC sp_OAMethod @authSas, 'SetTokenParam', @success OUT, 'signedpermissions', 'sp', 'rwdlacup'
    EXEC sp_OAMethod @authSas, 'SetTokenParam', @success OUT, 'signedservice', 'ss', 'bfqt'
    EXEC sp_OAMethod @authSas, 'SetTokenParam', @success OUT, 'signedresourcetype', 'srt', 'sco'

    DECLARE @dt int
    EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @dt OUT

    EXEC sp_OAMethod @dt, 'SetFromCurrentSystemTime', @success OUT
    EXEC sp_OAMethod @dt, 'GetAsIso8601', @sTmp0 OUT, 'YYYY-MM-DDThh:mmTZD', 0
    EXEC sp_OAMethod @authSas, 'SetTokenParam', @success OUT, 'signedstart', 'st', @sTmp0

    -- This SAS token will be valid for 30 days.
    EXEC sp_OAMethod @dt, 'AddDays', @success OUT, 30
    EXEC sp_OAMethod @dt, 'GetAsIso8601', @sTmp0 OUT, 'YYYY-MM-DDThh:mmTZD', 0
    EXEC sp_OAMethod @authSas, 'SetTokenParam', @success OUT, 'signedexpiry', 'se', @sTmp0

    EXEC sp_OAMethod @authSas, 'SetTokenParam', @success OUT, 'signedProtocol', 'spr', 'https'

    --  Specifiy values and query param names for each field.
    --  If a field is not specified, then an empty string will be used for its value.
    EXEC sp_OAMethod @authSas, 'SetTokenParam', @success OUT, 'signedversion', 'sv', '2015-04-05'

    -- Note that we did not call SetTokenParam for "signedIP".  For any omitted fields
    -- the value will default to the empty string.

    -- Generate the SAS token.
    DECLARE @sasToken nvarchar(4000)
    EXEC sp_OAMethod @authSas, 'GenerateToken', @sasToken OUT
    EXEC sp_OAGetProperty @authSas, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @authSas, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @authSas
        EXEC @hr = sp_OADestroy @dt
        RETURN
      END


    PRINT 'SAS token: ' + @sasToken

    -- Save the SAS token to a file.
    -- We can then use this pre-generated token for future Azure Storage Account operations.
    DECLARE @fac int
    EXEC @hr = sp_OACreate 'Chilkat.FileAccess', @fac OUT

    EXEC sp_OAMethod @fac, 'WriteEntireTextFile', @success OUT, 'qa_data/tokens/azureStorageAccountSas.txt', @sasToken, 'utf-8', 0

    EXEC @hr = sp_OADestroy @authSas
    EXEC @hr = sp_OADestroy @dt
    EXEC @hr = sp_OADestroy @fac


END
GO