Prerequisites<\/span><\/h2>\r\nBefore we perform the steps listed in this article, you will need to make sure the following prerequisites are met:\r\n\r\n \t- SSIS<\/abbr> designer installed.\u00a0Sometimes it is referred to as BIDS<\/abbr> or SSDT<\/abbr> (download it from the Microsoft site<\/a>).<\/li>\r\n \t
- Basic knowledge of SSIS package\u00a0development using\u00a0Microsoft SQL Server Integration Services<\/em>.<\/li>\r\n \t
- Make sure\u00a0ZappySys SSIS PowerPack<\/a><\/span>\u00a0is installed (download it<\/a>, if you haven't already).<\/li>\r\n \t
- (Optional step<\/em>).<\/em>\u00a0Read this article<\/a>, if you are planning to deploy packages to a server and schedule their execution later.<\/li>\r\n<\/ol><\/div>\n
What is HMAC Authentication<\/h2>\n
If you are new to HMAC term then please check Wiki description here<\/a>. Basically in HMAC Authentication you create a unique signature by hashing API Request (i.e. URL, Method, Body) using secret key \/ password. Once server receives your data it tries to validate signature. Unlike other authentication methods, in HMAC Auth your Secret key is never transmitted so in order to alter message by middle man, attacker has to gain access to your secret key before he can recreate signature to alter your message.<\/p>\nExample APIs which uses HMAC Authentication<\/h2>\n
Here is a list of few API providers which we tested to support HMAC. There many more APIs out there which should also work but we have not tested.<\/p>\n
\n- Microsoft Azure CosmosDB<\/strong> (formally known as DocumentDB) uses HMAC Signature – See API documentation here<\/a><\/li>\n
- CSOD<\/strong> uses HMAC (This is 2 steps Dynamic Token<\/a> approach) – See API documentation here<\/a><\/li>\n
- Acquia<\/strong> uses HMAC – See API documentation here<\/a><\/li>\n
- Amazon AWS<\/strong> Cloud uses HMAC Signature – ZappySys uses\u00a0this approach<\/a>\u00a0to call AWS APIs with AWS v4 Signature<\/a>.<\/li>\n<\/ul>\n
New HMAC Authentication feature of ZappySys HTTP Connection can be used in SSIS connectors \/ ODBC Drivers for REST. Use HTTP connection for all HMAC scenarios except AWS. For AWS we make it very simple by using dedicated\u00a0OAuth Provider for AWS v4<\/a>.<\/p>\nPros and Cons of HMAC Authentication<\/h2>\n
Now let’s look at what are some pros and cons of HMAC Authentication.<\/p>\n
Advantages of HMAC Authentication<\/h3>\n\n- Unlike other Authentication methods like Basic Authentication<\/a> or OAuth 2<\/a>, you dont have to pass Secret Key \/ Password along with your request<\/li>\n
- SSL is optional so you can host your API over HTTP rather than HTTPS (However its strongly suggested that you always use HTTPS when possible)<\/li>\n
- You can avoid Replay attack because Requests are Hashed along with some Timestamp, so after few mins most servers will reject the signature if same request is retried again by attacker.<\/li>\n<\/ul>\n
Disadvantages of HMAC Authentication<\/h3>\n\n- It is very complex compared to other approaches. Here is an example (AWS Signature)\u00a0<\/a> of how complex it can be :).<\/li>\n
- You need to have Cryptography Libraries Access in order to HASH with correct algorithm<\/li>\n<\/ul>\n
Anyways enough said now lets see something in Action.<\/p>\n
HMAC Authentication Example for SSIS<\/h2>\n
So to demo how HMAC Authentication can be implemented in SSIS we will use Azure CosmosDB API<\/a> which uses HMAC Authentication.<\/p>\n\n- Open existing SSIS Solution or create new Project<\/li>\n
- From SSIS Toolbox drag and drop ZS REST API Task\n
![\"Drag](\"https:\/\/zappysys.com\/onlinehelp\/ssis-powerpack\/scr\/images\/rest-api-task\/ssis-rest-api-web-service-task-drag.png\")
Drag and Drop REST API Task from SSIS Toolbox<\/p><\/div><\/li>\n
- Now let’s, Configure HTTP Connection as below<\/li>\n
- Right click on\u00a0Connection Managers<\/b>\u00a0Panel to Create HTTP Connection, Select New Connection from the Context Menu.
\n![\"SSIS](\"https:\/\/zappysys.com\/onlinehelp\/ssis-powerpack\/scr\/images\/ssis-new-connection.png\" "\\"SSIS")
<\/li>\n- Select\u00a0ZS-HTTP<\/b>\u00a0Connection Manager from the\u00a0Connection Managers<\/b>\u00a0list and Click on Add Button
\n![\"SSIS](\"https:\/\/zappysys.com\/onlinehelp\/ssis-powerpack\/scr\/images\/rest-api-task\/ssis-rest-api-web-service-task-http-connection.png\" "\\"SSIS")
<\/li>\n- Once HTTP Connection UI is visible select HMAC Authentication option, enter User ID and Password or API Key as below. If CosmosDB API example ignore Username and enter master API key. You can enter any valid URL (We will override this in next step anyways). Click on Configure HMAC Signature<\/strong> link.\n
![\"HTTP](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/04\/ssis-http-connection-hmac-authentication.png\")
<\/a>HTTP Connection with HMAC Authentication Option (Azure CosmosDB API Example)<\/p><\/div><\/li>\n
- Enter following 4 pieces to create correct HMAC Signature for Azure CosmosDB API call.\n
![\"Configure](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/04\/configure-http-rest-api-hmac-authentication-sha256-signature.png\")
<\/a>Configure HMAC String To Sign, Headers, Timestamp format, Algorithm<\/p><\/div>\n
\n- Select HMAC algorithm<\/strong> as sha256<\/span>\u00a0 \u00a0this is the most common algorithm for hashing and used by many APIs. Few other possible choices would be sha1, sha512, md5 and any other valid name from your system.<\/li>\n
- Enter String To Sign<\/strong>. Refer to your API and see how its expecting you to build this string. Each API may have different pieces in different order so be careful to define this else Signature will not match with server signature. In Azure CosmosDB API<\/strong> we use this documentation<\/a> to understand. Refer to HTTP Connection Manager<\/a> help file or See next section to learn about all possible placeholders.\n
[$http-method$]-lc[$lf$][$url-part-right-1$]-lc[$lf$][$url-part-right-2-4$][$lf$][$timestamp$]-lc[$lf$][$lf$]<\/pre>\nWhere
\n[$lf$]<\/strong>\u00a0is new line (i.e. \\n)
\n[$http-method$]<\/strong> is your HTTP Request Method (i.e. GET, POST, DELETE…)
\n[$url-part-right-1$]<\/strong> will extract first part from right side in URL Path (e.g.\u00a0colls if URL is\u00a0https:\/\/zappycosmos.documents.azure.com\/dbs\/northwind\/colls)
\n[$url-part-right-2-4$]<\/strong> will extract string from 2nd part from right till 4th part from left (e.g.\u00a0dbs\/northwind if URL is\u00a0https:\/\/zappycosmos.documents.azure.com\/dbs\/northwind\/colls)
\n[$timestamp$]<\/strong> will return current time in UTC in specified format (see next)
\n-lc suffix<\/strong>\u00a0in some placeholders will return value in lowercase. e.g.\u00a0[$http-method$]-lc<\/strong>\u00a0 will return get<\/strong> rather than GET<\/strong><\/p>\nExample Request:<\/p>\n
GET https:\/\/zappycosmos.documents.azure.com\/dbs\/northwind\/colls HTTP\/1.1\r\nx-ms-date: Mon, 22 Apr 2019 20:59:20 GMT<\/pre>\nString To Sign at runtime this may be like below<\/p>\n
get\\n\r\ncolls\\n\r\ndbs\/northwind\\n\r\nmon, 22 apr 2019 20:59:20 gmt\\n\r\n\\n\r\n\\n<\/pre>\n<\/li>\n
- Enter Extra Headers<\/strong> as below to make sure each REST API call includes x-ms-date<\/strong> ,\u00a0x-ms-version<\/strong> and\u00a0Authorization<\/strong> header.\u00a0 See next section to learn about all possible placeholders. In below format we used\u00a0[$signature$]-enc<\/strong> which means Base64 Signature string in URL encoded format. Remove -enc if API doesn’t need URL encoded string.\n
x-ms-date: [$timestamp$]||x-ms-version: 2015-08-06||Authorization:type%3dmaster%26ver%3d1.0%26sig%3d[$signature$]-enc<\/pre>\n<\/li>\n
- Enter Timestamp format<\/strong> as r<\/span>\u00a0 this will produce date as Mon, 22 Apr 2019 20:48:26 GMT<\/span>\u00a0 for example. Refer to API for exact date time format. These are<\/a> supported format specifiers. r<\/span>\u00a0 OR\u00a0R<\/span>\u00a0\u00a0is RFC 1123 standard format<\/a>.<\/li>\n<\/ol>\n<\/li>\n
- That ist now click OK to save HMAC Auth settings and on REST API Task check Use Direct URL option and enter URL like below to Fetch List of all collections. Replace MY-HOST<\/strong> and MY-DATABASE<\/strong> with your own values.\n
https:\/\/MY-HOST.documents.azure.com\/dbs\/MY-DATABASE\/colls<\/pre>\n<\/li>\n
- Click Test and see it returns status 200 OK\n
![\"Call](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/04\/ssis-call-azure-cosmosdb-rest-api-get-documentdb-table-list.png\")
<\/a>Call Azure CosmosDB REST API in SSIS (List DocumentDB Tables Example)<\/p><\/div><\/li>\n<\/ol>\n
HMAC Authentication Example for ODBC Drivers<\/h2>\n
Same example above can be achieved using following SQL Query described in below section using\u00a0ODBC PowerPack using JSON \/ REST API Driver<\/a><\/p>\nUse below steps to try HMAC feature which calls CosmosDB API. Now lets look at step by step.<\/p>\n
\n- Install ODBC PowerPack (It includes JSON Driver for REST API \/ Files<\/a>)<\/li>\n
- Type odbc in start menu and select ODBC Data Sources (64-bit)<\/li>\n
- \u00a0Click New to add new DSN and select ZappySys JSON Driver<\/li>\n
- When JSON Driver UI is visible click on Load Connection String Button and enter below string and click OK. Before you can load you can change DataPath, Password, RequestData properties as per your need. You can also change later on too once loaded.\n
DRIVER={ZappySys JSON Driver};DataPath='https:\/\/MY-HOST.documents.azure.com\/dbs\/MY-DATABASE\/colls\/MY-TABLE\/docs';DataConnectionType=HTTP;UserName='MY-HOST';HashTimestampFormat='r';ExtraHeaders='x-ms-date: [$timestamp$]||x-ms-version: 2015-08-06||Authorization:type%3dmaster%26ver%3d1.0%26sig%3d[$signature$]-enc';HashSignatureFormat='[$http-method$]-lc[$lf$][$url-part-right-1$]-lc[$lf$][$url-part-right-2-4$][$lf$][$timestamp$]-lc[$lf$][$lf$]';CredentialType=HashSignature;Password='xxxxxxxxxxxxxxxxxxxxxxxx==';Filter='$.Documents[*]';RequestData='{\"query\":\"SELECT * FROM root\"}';RequestMethod='POST';PagingMode=ByResponseHeaderContinuationToken;PagingByUrlAttributeName='x-ms-continuation,x-ms-session-token';RequestHeaders='Content-Type: application\/query+json || x-ms-documentdb-query-enablecrosspartition: true || x-ms-documentdb-isquery: true'<\/pre>\n<\/li>\n - Now go to preview tab and click execute to see preview data.<\/li>\n
- Here is Full query to override one or more connection attributes per query\n
SELECT * FROM $\r\nWITH(\r\n\t Src='https:\/\/MY-HOST-NAME.documents.azure.com\/dbs\/MY-DB-NAME\/colls\/MY-TABLE-NAME\/docs'\r\n\t,DataConnectionType='HTTP'\r\n\t,UserName='MY-HOST-NAME'\r\n\t,HashTimestampFormat='r'\r\n\t,ExtraHeaders='x-ms-date: [$timestamp$]||x-ms-version: 2015-08-06||Authorization:type%3dmaster%26ver%3d1.0%26sig%3d[$signature$]-enc'\r\n\t,HashSignatureFormat='[$http-method$]-lc[$lf$][$url-part-right-1$]-lc[$lf$][$url-part-right-2-4$][$lf$][$timestamp$]-lc[$lf$][$lf$]'\r\n\t,CredentialType='HashSignature'\r\n\t,Password='Hfs7k5wxxxxxxxxxxxxxxxxxxxxxxxxx4Ue0eucwDHMg=='\r\n\t,Filter='$.Documents[*]'\r\n\t,RequestData='{\"query\":\"SELECT * FROM root\"}'\r\n\t,Header='Content-Type: application\/query+json || x-ms-documentdb-query-enablecrosspartition: true || x-ms-documentdb-isquery: true'\r\n\t,RequestMethod='POST'\r\n\t,PagingMode='ByResponseHeaderContinuationToken'\r\n\t,PagingByUrlAttributeName='x-ms-continuation,x-ms-session-token'\r\n\t--,PagingByUrlMaxPages='4'\r\n)<\/pre>\nIn above Query replace\u00a0Src, Password and RequestData (if you need different SQL).<\/p>\n
\n- Change Src to correct URL to (first part and Resource Link (MY-HOST-NAME , MY-DB-NAME, and MY-TABLE-NAME)
\nExample:
\nhttps:\/\/zappycosmos.documents.azure.com\/dbs\/northwind\/colls\/customers\/docs
\nor (using explicite region)
\nhttps:\/\/zappycosmos-eastus.documents.azure.com\/dbs\/northwind\/colls\/customers\/docs<\/li>\n - Change\u00a0Password with your Key, change username with something (e.g. instance name) this is ignored for CosmosDB API so enter anything.<\/li>\n
- Change RequestData with desired SQL query you like to execute or leave it default to return all.<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n
![\"Query](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2019\/04\/azure-cosmosdb-driver-odbc-example-sql-query.png\")
<\/a>Query CosmosDB Documents using ODBC Driver (JSON \/ REST API)<\/p><\/div>\n
<\/p>\n
Supported Placeholders for String To Sign and Extra Headers<\/h2>\n
Now let’s look at various placeholders you can use in String to Sign and Extra Headers Setting we saw earlier.<\/p>\n
Placeholder Modifiers<\/strong><\/p>\n-lc\u00a0 : Output as Lower Case\u00a0 ( Example ABCD will be returned as abcd)<\/p>\n
-enc : Output as URL Encoded\u00a0 (Example:\u00a0 A=B will be encoded as A%3DB )<\/p>\n
Placeholders<\/strong><\/p>\n\n
What is HMAC Authentication<\/h2>\n
If you are new to HMAC term then please check Wiki description here<\/a>. Basically in HMAC Authentication you create a unique signature by hashing API Request (i.e. URL, Method, Body) using secret key \/ password. Once server receives your data it tries to validate signature. Unlike other authentication methods, in HMAC Auth your Secret key is never transmitted so in order to alter message by middle man, attacker has to gain access to your secret key before he can recreate signature to alter your message.<\/p>\n Here is a list of few API providers which we tested to support HMAC. There many more APIs out there which should also work but we have not tested.<\/p>\n New HMAC Authentication feature of ZappySys HTTP Connection can be used in SSIS connectors \/ ODBC Drivers for REST. Use HTTP connection for all HMAC scenarios except AWS. For AWS we make it very simple by using dedicated\u00a0OAuth Provider for AWS v4<\/a>.<\/p>\n Now let’s look at what are some pros and cons of HMAC Authentication.<\/p>\n Anyways enough said now lets see something in Action.<\/p>\n So to demo how HMAC Authentication can be implemented in SSIS we will use Azure CosmosDB API<\/a> which uses HMAC Authentication.<\/p>\n Drag and Drop REST API Task from SSIS Toolbox<\/p><\/div><\/li>\n HTTP Connection with HMAC Authentication Option (Azure CosmosDB API Example)<\/p><\/div><\/li>\n Configure HMAC String To Sign, Headers, Timestamp format, Algorithm<\/p><\/div>\n Where Example Request:<\/p>\n String To Sign at runtime this may be like below<\/p>\n Call Azure CosmosDB REST API in SSIS (List DocumentDB Tables Example)<\/p><\/div><\/li>\n<\/ol>\n Same example above can be achieved using following SQL Query described in below section using\u00a0ODBC PowerPack using JSON \/ REST API Driver<\/a><\/p>\n Use below steps to try HMAC feature which calls CosmosDB API. Now lets look at step by step.<\/p>\n In above Query replace\u00a0Src, Password and RequestData (if you need different SQL).<\/p>\n Query CosmosDB Documents using ODBC Driver (JSON \/ REST API)<\/p><\/div>\n <\/p>\n Now let’s look at various placeholders you can use in String to Sign and Extra Headers Setting we saw earlier.<\/p>\n Placeholder Modifiers<\/strong><\/p>\n -lc\u00a0 : Output as Lower Case\u00a0 ( Example ABCD will be returned as abcd)<\/p>\n -enc : Output as URL Encoded\u00a0 (Example:\u00a0 A=B will be encoded as A%3DB )<\/p>\n Placeholders<\/strong><\/p>\nExample APIs which uses HMAC Authentication<\/h2>\n
\n
Pros and Cons of HMAC Authentication<\/h2>\n
Advantages of HMAC Authentication<\/h3>\n
\n
Disadvantages of HMAC Authentication<\/h3>\n
\n
HMAC Authentication Example for SSIS<\/h2>\n
\n
\n<\/li>\n
\n<\/li>\n<\/a><\/a>\n
[$http-method$]-lc[$lf$][$url-part-right-1$]-lc[$lf$][$url-part-right-2-4$][$lf$][$timestamp$]-lc[$lf$][$lf$]<\/pre>\n
\n[$lf$]<\/strong>\u00a0is new line (i.e. \\n)
\n[$http-method$]<\/strong> is your HTTP Request Method (i.e. GET, POST, DELETE…)
\n[$url-part-right-1$]<\/strong> will extract first part from right side in URL Path (e.g.\u00a0colls if URL is\u00a0https:\/\/zappycosmos.documents.azure.com\/dbs\/northwind\/colls)
\n[$url-part-right-2-4$]<\/strong> will extract string from 2nd part from right till 4th part from left (e.g.\u00a0dbs\/northwind if URL is\u00a0https:\/\/zappycosmos.documents.azure.com\/dbs\/northwind\/colls)
\n[$timestamp$]<\/strong> will return current time in UTC in specified format (see next)
\n-lc suffix<\/strong>\u00a0in some placeholders will return value in lowercase. e.g.\u00a0[$http-method$]-lc<\/strong>\u00a0 will return get<\/strong> rather than GET<\/strong><\/p>\nGET https:\/\/zappycosmos.documents.azure.com\/dbs\/northwind\/colls HTTP\/1.1\r\nx-ms-date: Mon, 22 Apr 2019 20:59:20 GMT<\/pre>\n
get\\n\r\ncolls\\n\r\ndbs\/northwind\\n\r\nmon, 22 apr 2019 20:59:20 gmt\\n\r\n\\n\r\n\\n<\/pre>\n<\/li>\n
x-ms-date: [$timestamp$]||x-ms-version: 2015-08-06||Authorization:type%3dmaster%26ver%3d1.0%26sig%3d[$signature$]-enc<\/pre>\n<\/li>\n
https:\/\/MY-HOST.documents.azure.com\/dbs\/MY-DATABASE\/colls<\/pre>\n<\/li>\n
<\/a>HMAC Authentication Example for ODBC Drivers<\/h2>\n
\n
DRIVER={ZappySys JSON Driver};DataPath='https:\/\/MY-HOST.documents.azure.com\/dbs\/MY-DATABASE\/colls\/MY-TABLE\/docs';DataConnectionType=HTTP;UserName='MY-HOST';HashTimestampFormat='r';ExtraHeaders='x-ms-date: [$timestamp$]||x-ms-version: 2015-08-06||Authorization:type%3dmaster%26ver%3d1.0%26sig%3d[$signature$]-enc';HashSignatureFormat='[$http-method$]-lc[$lf$][$url-part-right-1$]-lc[$lf$][$url-part-right-2-4$][$lf$][$timestamp$]-lc[$lf$][$lf$]';CredentialType=HashSignature;Password='xxxxxxxxxxxxxxxxxxxxxxxx==';Filter='$.Documents[*]';RequestData='{\"query\":\"SELECT * FROM root\"}';RequestMethod='POST';PagingMode=ByResponseHeaderContinuationToken;PagingByUrlAttributeName='x-ms-continuation,x-ms-session-token';RequestHeaders='Content-Type: application\/query+json || x-ms-documentdb-query-enablecrosspartition: true || x-ms-documentdb-isquery: true'<\/pre>\n<\/li>\nSELECT * FROM $\r\nWITH(\r\n\t Src='https:\/\/MY-HOST-NAME.documents.azure.com\/dbs\/MY-DB-NAME\/colls\/MY-TABLE-NAME\/docs'\r\n\t,DataConnectionType='HTTP'\r\n\t,UserName='MY-HOST-NAME'\r\n\t,HashTimestampFormat='r'\r\n\t,ExtraHeaders='x-ms-date: [$timestamp$]||x-ms-version: 2015-08-06||Authorization:type%3dmaster%26ver%3d1.0%26sig%3d[$signature$]-enc'\r\n\t,HashSignatureFormat='[$http-method$]-lc[$lf$][$url-part-right-1$]-lc[$lf$][$url-part-right-2-4$][$lf$][$timestamp$]-lc[$lf$][$lf$]'\r\n\t,CredentialType='HashSignature'\r\n\t,Password='Hfs7k5wxxxxxxxxxxxxxxxxxxxxxxxxx4Ue0eucwDHMg=='\r\n\t,Filter='$.Documents[*]'\r\n\t,RequestData='{\"query\":\"SELECT * FROM root\"}'\r\n\t,Header='Content-Type: application\/query+json || x-ms-documentdb-query-enablecrosspartition: true || x-ms-documentdb-isquery: true'\r\n\t,RequestMethod='POST'\r\n\t,PagingMode='ByResponseHeaderContinuationToken'\r\n\t,PagingByUrlAttributeName='x-ms-continuation,x-ms-session-token'\r\n\t--,PagingByUrlMaxPages='4'\r\n)<\/pre>\n\n
\nExample:
\nhttps:\/\/zappycosmos.documents.azure.com\/dbs\/northwind\/colls\/customers\/docs
\nor (using explicite region)
\nhttps:\/\/zappycosmos-eastus.documents.azure.com\/dbs\/northwind\/colls\/customers\/docs<\/li>\n<\/a>Supported Placeholders for String To Sign and Extra Headers<\/h2>\n