SQL Server
SQL Server
Create an Azure Service SAS
See more Azure Cloud Storage Examples
Shows how to generate an Azure Service SAS.Chilkat SQL Server Downloads
-- 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
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