(SQL Server) Create an Azure Service SAS

Shows how to generate an Azure Service SAS.

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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- 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 Service (Blob, Queue, Table, or File)
    -- -----------------------------------------------------------------------------------------------

    -- See https://docs.microsoft.com/en-us/rest/api/storageservices/create-service-sas
    -- for details.

    DECLARE @authSas int
    -- Use "Chilkat_9_5_0.AuthAzureSAS" for versions of Chilkat < 10.0.0
    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. 

    -- Version 2018-11-09 and later
    -- 
    -- Version 2018-11-09 adds support for the signed resource and signed blob snapshot time fields. 
    -- These must be included in the string-to-sign. To construct the string-to-sign for Blob service resources, use the following format:
    -- 
    -- StringToSign = signedpermissions + "\n" +  
    --                signedstart + "\n" +  
    --                signedexpiry + "\n" +  
    --                canonicalizedresource + "\n" +  
    --                signedidentifier + "\n" +  
    --                signedIP + "\n" +  
    --                signedProtocol + "\n" +  
    --                signedversion + "\n" +  
    --                signedResource + "\n"
    --                signedSnapshotTime + "\n" +
    --                rscc + "\n" +  
    --                rscd + "\n" +  
    --                rsce + "\n" +  
    --                rscl + "\n" +  
    --                rsct  
    -- 

    EXEC sp_OASetProperty @authSas, 'StringToSign', 'signedpermissions,signedstart,signedexpiry,canonicalizedresource,signedidentifier,signedIP,signedProtocol,signedversion,signedResource,signedSnapshotTime,rscc,rscd,rsce,rscl,rsct'

    DECLARE @success int
    EXEC sp_OAMethod @authSas, 'SetTokenParam', @success OUT, 'signedpermissions', 'sp', 'rw'

    DECLARE @dt int
    -- Use "Chilkat_9_5_0.CkDateTime" for versions of Chilkat < 10.0.0
    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

    -- The canonicalizedresouce portion of the string is a canonical path to the signed resource. It must include the service name (blob, table, queue or file) for version
    -- 2021-08-06 or later, the storage account name, and the resource name, and must be URL-decoded. Names of blobs must include the blob�s container. Table names must be
    -- lower-case. The following examples show how to construct the canonicalizedresource portion of the string, depending on the type of resource.
    -- For example:
    -- URL = https://chilkat.blob.core.windows.net/mycontainer/starfish.jpg
    -- canonicalizedresource = "/blob/chilkat/mycontainer/starfish.jpg"  
    -- IMPORTANT: See https://docs.microsoft.com/en-us/rest/api/storageservices/create-service-sas for all details..
    EXEC sp_OAMethod @authSas, 'SetNonTokenParam', @success OUT, 'canonicalizedresource', '/blob/chilkat/mycontainer/starfish.jpg'

    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', '2018-11-09'

    -- Indicate that we are creating a service SAS that is limited to the blob resource.
    -- (Specify b if the shared resource is a blob. This grants access to the content and metadata of the blob.
    --  Specify c if the shared resource is a container. This grants access to the content and metadata of any blob in the container, and to the list of blobs in the container. )
    EXEC sp_OAMethod @authSas, 'SetTokenParam', @success OUT, 'signedResource', 'sr', 'b'

    -- Note that we did not call SetTokenParam for "signedIP", "signedSnapshotTime", "rscc", and others.  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 Service token to a file.
    -- We can then use this pre-generated token for future Azure Storage Account operations.
    DECLARE @fac int
    -- Use "Chilkat_9_5_0.FileAccess" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.FileAccess', @fac OUT

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

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


END
GO