SQL Server

Create an Azure Service SAS

See more Azure Cloud Storage Examples

Shows how to generate an Azure Service SAS. 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
    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
    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
    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
    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