Generating SAS Shared Access Signature Automatically

Question

I have some small kind of problem. Our company is using Azure Logic Apps + Azure SQL Store Procedures for loading data into Azure SQL Tables from Azure Blob Storage. The data are generated via PowerShell scripts in .csv format at on-premise servers and sending into Azure Blob storage via AZCOPY. AZCOPY is using SAS Token. Azure SQL is using SAS token for access into External Data Sources. My question is - Is it possible to Generate somehow SAS token and next step Execute Query at SQL server with the new Token ?

ALTER DATABASE SCOPED CREDENTIAL BlobCredential WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'sv=SAS_TOKEN';

I know that the token in AZCOPY i cant propably automatically update and I will have to update it manually.

I am sorry for my english.

Thanks for opinions! Petr

Answer 1

This is how you would connect to Azure SQL DB.

string ConnectionString =@"Data Source=n9lxnyuzhv.database.windows.net; Initial Catalog=testdb;"
SqlConnection conn = new SqlConnection(ConnectionString);
conn.AccessToken = "Your JWT token"
conn.Open();

For the JWT Token: Search for OAuth Grant flow for Azure AD. There are several samples on how to get a token, usually they will require a Client Secret(password) or a Certificate.

You can install a certificate on your OnPremise VMs. You can refer here to how to get the token with a Client Certificate.

https://github.com/AzureAD/microsoft-authentication-library-for-dotnet/wiki/Client-credential-flows#construction-of-confidentialclientapplication-with-client-credentials

var app = ConfidentialClientApplicationBuilder.Create(config.ClientId)
           .WithAuthority(AzureCloudInstance.AzurePublic, "{tenantID}")
           .WithClientSecret(config.ClientSecret)
           .Build();

X509Certificate2 certificate = ReadCertificate(config.CertificateName);
var app = ConfidentialClientApplicationBuilder.Create(config.ClientId)
           .WithAuthority(AzureCloudInstance.AzurePublic, "{tenantID}")
           .WithCertificate(certificate)
           .Build();

string[] scopes = new string[] { "https://graph.microsoft.com/.default" };

AuthenticationResult result = null;
try
{
 result = await app.AcquireTokenForClient(scopes)
                   .ExecuteAsync();
}
catch(MsalServiceException ex)
{
 // Case when ex.Message contains:
 // AADSTS70011 Invalid scope. The scope has to be of the form "https://resourceUrl/.default"
 // Mitigation: change the scope to be as expected
}

Answer 2

I think you need to write a stored procedure and pass the sas token as parameter to it:

ALTER PROCEDURE XXXX
    @sastoken NVARCHAR(200)
AS

BEGIN TRY
    BEGIN TRANSACTION       
        
        DECLARE @Command NVARCHAR(MAX)
        SET @Command  = 'ALTER DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential WITH IDENTITY = ' + Char(39) + 'SHARED ACCESS SIGNATURE'+ Char(39) + ' ,SECRET = '+ CHAR(39) + @sastoken + CHAR(39)
        EXEC (@Command)

        ALTER EXTERNAL DATA SOURCE MyAzureBlobStorage
            SET
                LOCATION = 'YYYY', 
                CREDENTIAL= MyAzureBlobStorageCredential

        
        BULK INSERT ZZZZ
        FROM 'somefile.csv'
        WITH
        (
            DATA_SOURCE = 'MyAzureBlobStorage',
            FIRSTROW=2,
            FIELDTERMINATOR = ',',
            ROWTERMINATOR = '\n'
        )

COMMIT TRANSACTION
END TRY