Is there a more efficient way to create an Azure Shared Access Signature?

Data Integration CData Arc
1

I have a flow which pulls rows from MySQL, formats them into JSON messages and pushes them to Azure Service Bus.

There’s a step in the flow which creates the necessary signature for the REST POST to Azure Service Bus:

f75b88cf-509a-4433-950b-19a634064a77.png
f75b88cf-509a-4433-950b-19a634064a77.png834×596 29.3 KB

But the SAS Token/Signature header is not dependant on the message.

I only really need to do it once and make sure it is valid for long enough - at the moment I make it valid for a week.

But my flow is calculating it for every row/every message.

Is there a way to calculate it once (say for each time I run SELECT in the MySQL connector) and have it used repeatedly by PublishDirectToServiceBus?

2

You may want to take a look at the authentication options present in the REST connector to see if you can authenticate to this endpoint with any of the available authentication methods, but if you’ve already implement a script to get your SAS token, what you might want to do is use a little cache table to store your SAS token with a timestamp, and look up the stored token against the table first.

If you find a token that’s less than a week old, use that - otherwise, run through your code and update the table. I’ve attached a simple example that uses a SQLite database here:

e986abdd-7199-429e-833b-a7b7658aa96c.png
e986abdd-7199-429e-833b-a7b7658aa96c.png1091×220 24.4 KB

In your script, you can query the table first, and use the value that’s there if it’s not too old before running through your script:

<arc:set attr="db.driver" value="System.Data.SQLite" /><arc:set attr="db.conn" value="DataSource=C:\\Program Files\\CData Arc 23.4.8801\\SASKeyState.db" /><arc:set attr="db.query">SELECT SASKey FROM SASKey WHERE CreationDate > '[_ | now | dateadd("day", -7)]'</arc:set><arc:call op="dbQuery" item="db">  <arc:set attr="tmp.SASKey" value="[db.SASKey]" /></arc:call><!-- did we get a key --><arc:check attr="tmp.SASKey">  <!-- yes, use it -->  <arc:set attr="output.filepath" value="[filepath]"/>  <arc:set attr="output.header:SASKey" value="[tmp.SASKey]" />  <arc:push item="output" />  <!-- no, make it and update the table -->  <arc:else>    <!-- simple GUID for this example -->    <arc:set attr="tmp.SASKey" value="[guid(true)]" />    <arc:set attr="db.query" value="UPDATE `SASKey` SET `SASKey`= '[tmp.SASKey]', `CreationDate` = '[_ | now]' WHERE `ID` = 1" />    <arc:call op="dbNonQuery" item="db" />    <arc:set attr="output.filepath" value="[filepath]"/>    <arc:set attr="output.header:SASKey" value="[tmp.SASKey]" />    <arc:push item="output" />  </arc:else></arc:check>

The attached zip has the connector and the SQLite table; would something like this work for you?

GetSASKey.zip (2.02 KB)