Share via

BizTalk Server: Inserting Data Into An Always Encrypted Column using WCF-Custom/WCF-SQL adapter

  • Article

Introduction 

Always Encrypted Columns in SQL

 Always Encrypted feature was introduced in SQL server 2016 which enables the application store encrypted data in the SQL tables.  This allows the application to encrypt the data and never let the data store know about the sensitive data. This ensures that the data is available only to the data owners and not the persons who operate on the data. This is achieved by encrypting the data while it is being inserted into the SQL tables. This is achieved using a combination of Column Master Key and Column Encryption Key. The earlier key is hosted on the application server from which the application inserts the data while the later is stored on the SQL server. 

BizTalk WCF-Custom/WCF-SQL Adapter and BizTalk 2016 FeaturePack 1 

With the release of Feature Pack 1 for BizTalk 2016, it has become possible to interact with the SQL tables which have Always encrypted columns in them. This feature is available with the BizTalk WCF-SQL adapter as well as the WCF-Custom adapter. Each one has a flag which indicates if the encryption needs to be enabled or not while inserting the data. Refer following screenshots for the WCF-SQL and WCF-Custom Adapters.

Fig: WCF -SQL Adapter Setting For Always Encrypted Column 

Fig: WCF -Custom Adapter Setting For Always Encrypted Column

↑Back To Top

Scope 

This article discusses how to insert the sensitive data into the SQL table using the BizTalk WCF-SQL adapter. This article assumes that the reader has a basic idea of BizTalk development. In case the reader is new to the BizTalk product, refer the See Also section which will guide the user to the pertinent learning resources for BizTalk server product. 

↑Back To Top 

Fictional Scenario 

OnlinePayBuddy is a fictional online payment management application which helps users to make online payments whenever they wish to buy any product on any shopping sites, OnlinePayBuddy allows the user to store their credit card information in the application to facilitate quick payments. At the same time, OnlinePayBuddy has a separate support team which maintains the data stores. Hence in order to provide a secure store for the credit card details for the clients, OnlinePayBuddy uses the Always Encrypted feature of SQL Server. In addition to this OnlinePayBuddy use Microsoft BizTalk Server 2016 as their Integration server. The details customer enter for their credit card are stored in SQL store by BizTalk which has exposed its own WCF Service.  

↑Back To Top 

Designed Solution

Following diagram illustrates the designed solution.

↑Back To Top 

Implementation

The implementation of the Solution can be divided into two parts. 

  1. Database Design 
  2. BizTalk Application Design 

Database Design

The payment tables which stores the CustomerId and the Credit Card Details are available under the POCDatabase.  In this, the credit card details are stored in an always encrypted column. Following are the steps that need to be completed. 

Create Column Master Keys 

Refer to the following screen shot to create sample Column Master Keys.

Create Column Encryption Keys

Refer following screenshots to create encryption key corresponding to the master key created above.

Create Table

Following is the Script Used to create the Payment Details table. 

CREATE TABLE  PaymentsDetails
(
    CustomerId nvarchar(10),
 
    CreditCardNumber nvarchar(17) COLLATE  Latin1_General_BIN2 ENCRYPTED WITH ( 
                COLUMN_ENCRYPTION_KEY = CreditCardColumnEncryptionKey,
                ENCRYPTION_TYPE = DETERMINISTIC ,  
                ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256')            
)

 

Insert Stored Procedure 

Following is the Script used to create the insert Stored Procedure which will consumed by BizTalk 

CREATE PROCEDURE  usp_InsertCreditCardDetails
    @CustomerId nvarchar(10),
    @CreditCardNumber nvarchar(17)
 
AS
BEGIN
    INSERT INTO  PaymentDetails 
        (CustomerId,
        CreditCardNumber)
    VALUES
        (@CustomerId,
        @CreditCardNumber)
END

BizTalk Application Design

Following are various steps that are performed to design the BizTalk application

Create Service Request and Response WCF Service Schemas 

Following are the request and response schemas exposed by the BizTalk WCF service.

  1.  Request Schema 

    <?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns="http://SQLAlwaysEncryptedDemo.Request" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" targetNamespace="http://SQLAlwaysEncryptedDemo.Request" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="Req">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="CustomerId" type="xs:string" />
        <xs:element name="CreditCardNumber" type="xs:string" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

  2. Response Schema

    <?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns="http://SQLAlwaysEncryptedDemo.Response" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" targetNamespace="http://SQLAlwaysEncryptedDemo.Response" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="Resp">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="Acknowledgement" type="xs:string" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Consume Insert Credit Card Details Stored Procedure

Refer following screen shots to consume the insert procedure.

Select the sqlbinding from the drop down and then click on Configure and provide the Uri Properties as shown in the sample below.

Click on the Bindings Properties tab and set the Always Encrypted flag to True as shown below. 

Click on the Security tab and Select Certificate from Dropdown and select the Certificate which matches with the one used to create the Column Master Key earlier.

Click on OK and Finally Connect to the SQL Server Database. Select the stored procedure created earlier from the list of available procedures and Click on OK after adding. 

This will generate the Custom Binding File and the necessary schemas. The Request Response Schema generated by the Wizard is as follows.

<?xml version="1.0" encoding="utf-8"?>
<xs:schema xmlns:ns3="http://schemas.datacontract.org/2004/07/System.Data" elementFormDefault="qualified" targetNamespace="http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo" version="1.0" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:import schemaLocation=".\DataSetSchema.xsd" namespace="http://schemas.datacontract.org/2004/07/System.Data" />
  <xs:annotation>
    <xs:appinfo>
      <fileNameHint xmlns="http://schemas.microsoft.com/servicemodel/adapters/metadata/xsd">Procedure.dbo</fileNameHint>
    </xs:appinfo>
  </xs:annotation>
  <xs:element name="usp_InsertCreditCardDetails">
    <xs:annotation>
      <xs:documentation>
        <doc:action xmlns:doc="http://schemas.microsoft.com/servicemodel/adapters/metadata/documentation">Procedure/dbo/usp_InsertCreditCardDetails</doc:action>
      </xs:documentation>
    </xs:annotation>
    <xs:complexType>
      <xs:sequence>
        <xs:element minOccurs="0" maxOccurs="1" name="CustomerId" nillable="true">
          <xs:simpleType>
            <xs:restriction base="xs:string">
              <xs:maxLength value="10" />
            </xs:restriction>
          </xs:simpleType>
        </xs:element>
        <xs:element minOccurs="0" maxOccurs="1" name="CreditCardNumber" nillable="true">
          <xs:simpleType>
            <xs:restriction base="xs:string">
              <xs:maxLength value="17" />
            </xs:restriction>
          </xs:simpleType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
  <xs:element name="usp_InsertCreditCardDetailsResponse">
    <xs:annotation>
      <xs:documentation>
        <doc:action xmlns:doc="http://schemas.microsoft.com/servicemodel/adapters/metadata/documentation">Procedure/dbo/usp_InsertCreditCardDetails/response</doc:action>
      </xs:documentation>
    </xs:annotation>
    <xs:complexType>
      <xs:sequence>
        <xs:element minOccurs="0" maxOccurs="1" name="usp_InsertCreditCardDetailsResult" nillable="true" type="ns3:ArrayOfDataSet" />
        <xs:element minOccurs="1" maxOccurs="1" name="ReturnValue" type="xs:int" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

The Mapping from WCF Request to SQL Insert Stored procedure is simple One to One Mapping as shown below.

The Orchestration For the Process is as shown below. The Response message Sent to service is "Card Details Saved Successfully". 

Deploy the Application and Expose the BizTalk Schemas as WCF Service.

This completes the design Steps.

↑Back To Top

Testing

Following are the test requests that were fired from SOAP UI.

Following are the entries inserted by the BizTalk WCF-Custom/WCF-SQL adapter in the table PaymentsDetails. 

In order to view these results and to confirm that the BizTalk has inserted proper entries, an additional setting needs to be enabled while login into the SSMS. Refer following screen shots.

 

Following is the Select query output after enabling the settings as shown above. 

↑Back To Top

Conclusion

After observing the Test Results it can be concluded that BizTalk WCF-Custom / WCF-SQL adapter can be used to insert data into the Always Encrypted Column in an SQL table.

↑Back To Top

See Also 

Following are the articles that can be used to read more related to the Always Encrypted Feature of SQL server and BizTalk Feature Packs

  1. BizTalk Server 2016 Feature Pack 1 is live 
  2. Microsoft BizTalk Server 2016 Feature Pack 2 
  3. Working with the Always Encrypted Feature in SQL Server 2016 

For those who are new to the BizTalk Server product, it is recommended to visit the landing page for the BizTalk Server Resources on Technet wiki, which contains links to various articles published on Technet Wiki related to the Microsoft BizTalk Product. Following is the link to the landing page.

BizTalk Server Resources on the TechNet Wiki 

↑Back To Top

References

Following articles and video were used as reference material while writing this article.

  1. Configure the BizTalk feature pack 
  2. Always Encrypted (Database Engine) 
  3. Keeping Sensitive Data Secure with Always Encrypted 

↑Back To Top