How to implement SQL Server 2016 Always Encrypted

Environment :

Client Machine ( CM ) - Windows 7 Professional - SSMS 17 SQL Server Machine ( SM ) - Windows Server 2012 R2 Datacenter - SQL Server 2016 - SSMS 17
Step : 1 Create Database On SM Connect to database via SSMS Create testing database named 'Demo' Step : 2 Creating Column Master Key and Column Encryption Key
Create Master Key
Open DEMO -> Security -> Always Encrypted Keys -> Column Master Keys -> Right Click -> New Column Master Key

Name : CMK1 Key Store : Windows Certificate Store - Current User

Click Generate Certificate to create the cert

Select the new certificate and Click OK to create the key pointed to the Cert Store of Current User (CMK1 only store the meta data which point the location of cert. The Cert is the Key to decrypt the Column encryption key.)

Create Column Encryption Keys Open DEMO -> Security -> Always Encrypted Keys -> Column Encrytion Keys Right Click -> New Column Encryption Key
Name : CEK1
Column Master Key : CMK1

Click OK

Step 3 : Creating Always Encrypted Table
Config BirthDate and SSN Field Encrypted by CEK1

CREATE TABLE dbo.Demo_Always_Encrypted 
(
   ID INT IDENTITY(1,1) PRIMARY KEY,
   LastName NVARCHAR(45),
   FirstName NVARCHAR(45),
   BirthDate DATE ENCRYPTED WITH 
    (
        ENCRYPTION_TYPE = RANDOMIZED, 
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
        COLUMN_ENCRYPTION_KEY = CEK1
    ),
   SSN CHAR(11) COLLATE Latin1_General_BIN2 
   ENCRYPTED WITH 
   (
     ENCRYPTION_TYPE = DETERMINISTIC, 
     ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
     COLUMN_ENCRYPTION_KEY = CEK1
   )
);

Step 4 : Insert testing Data via SSMS

DECLARE @SSN CHAR(10) = '795-73-983'
DECLARE @BirthDate Date = '2010-10-10'

INSERT INTO dbo.Demo_Always_Encrypted
(LastName, FirstName, BirthDate, SSN) 
VALUES ('Larsen','Gregory',@BirthDate, @SSN);

Step 5 : Query in Server SSMS

SELECT [ID]
      ,[LastName]
      ,[FirstName]
      ,[BirthDate]
      ,[SSN]
  FROM [DEMO].[dbo].[Demo_Always_Encrypted]

Step 6 : Export the Private key from Server for client access data Run Cert Management Console (reference to Additional Information) in SM Open Certificates -Current - > Personal -> Certificates

Right Click "Always Encrypted Certificate" -> All Task -> Export -> Next

Checked "Export as Private Key" -> Next

Checked "Include all certificates in the certification path if possible" -> Next

Key in password ex: 12345678 -> Next

Key in Filename for the certificate and save path -> Next -> Finish

This exported certificate is the master key for decrypt the CMK1. If this cert is lost, all encrypted column will not able to decrypt.

Step 7 : Import the Private key to client machine. Run Cert Management Console in CM Open Certificates - Current User -> Personal -> Certificates Right Click "All Task" -> Import -> - Include all extended properties - Mark this key as exportable ( optional ) Step 8 : Delete Server Cert

Delete the server cert is to prevent the system administrator or DBA have a means to view the encrypted data.


Testing :
Step 9 : Verify Query the encrypted data in Server machine is deniable Step 10 : Verify Query the encrypted data in Client machine is successful ( with the certificate )


Additional Information:

A: Login Database via with Column Encryption Setting Enable

1. Run SSMS V17 or above.
2. Connect Database
3. Config Parameter "Column Encryption Setting=Enabled"

B: To add Certificate Manager to Microsoft Management Console

1. Click Start, click Run, type mmc, and then click OK.
2. In the File menu, click Add/Remove Snap-in.
3. In the Add/Remove Snap-in box, click Add.
4. In the Available Standalone Snap-ins list, click Certificates, and then click Add.
5. Click Computer Account, and then click Next.
6. Click the Local computer (the computer this console is running on) option, and then click Finish.
7. Click Close, and then click OK.

Reference:

Tutorial http://www.databasejournal.com/features/mssql/exploration-of-sql-server-2016-always-encrypted-part-1.html http://www.databasejournal.com/features/mssql/exploration-of-sql-server-2016-always-encrypted-part-1-2.html Always Encrypted Limitation https://blogs.sentryone.com/aaronbertrand/t-sql-tuesday-69-always-encrypted-limitations/

.Net Framework 4.5 access .Net Framework 3.5 Website via Form authentication

Background

This essay is going to tell you how to develop a protected site (Site B) which the login authenticated by Site A. User is going to authenticated by login process in site A. For the site A authentication method could be AD or simple Login Process.


Server Configuration :
IIS 7.5

Site A :

URL : http://www.example.com
IDE : Visual Studio 2008
.NET Framework 3.5
IIS application pool version : 2.0, Integrated

Site B :

URL : http://www.example.com:8000
IDE : Visual Studio 2012
.NET Framework 4.5
IIS application pool version : 4.0, Integrated

Objective :

- CORS setting for the site A
User access site B protected resource by site A login authentication



Solution

Step 1: Change the Machine Key to custom key from auto generate of site A


Open IIS of site A

Double Click 

Encryption Method : SHA1
Decryption Method : Auto


Click and click "Apply"

Copy the validationKey and decryptionKey to a notepad.

Step 2: Configure  web.config of site B 

site B Web.config

<system.web>

<machineKey compatibilityMode="Framework20SP2" validationKey="{Key copy from website A IIS}" decryptionKey="{Key copy from website A IIS}" validation="SHA1" />

    <authentication mode="Forms">
      <forms name="{Cookie Name of Website A Form Auth}" loginUrl="Login.aspx" protection="All" timeout="120" path="/">
      </forms>
    </authentication>
    <authorization>
      <deny users="?"/>
      <allow users="*"/>
    </authorization>



....... // other configuration

</system.web>

where cookie write from site A code behind

            // create the authentication cookie                                        
            HttpCookie cookie = new HttpCookie(FormsAuthentication.FormsCookieName, FormsAuthentication.Encrypt(ticket));

            Response.Cookies.Add(cookie);

Step 3: Configure Web.config of site A

  <system.webServer>
<httpProtocol>
  <customHeaders>
    <add name="Access-Control-Allow-Origin" value="{specified url with port number for access this website resource}" />
    <add name="Access-Control-Allow-Credentials" value="true" />
  </customHeaders>
</httpProtocol>

...... // other configuration
  </system.webServer>

Testing 

scenario A: 
1. Goto Site B protected page
2. Authentication Fail
3. Redirect to Default page (configure in site B web.config file)

scenario B: 
1. Goto Site A Login Page
2. Login Site A Successfully
3. Goto Site B protected page
4. Load Site B protected page successfully
5. Goto Site A and Logout
6. Reload Site B protected page
7. Redirect to Default page (configure in site B web.config file)

Others Reference 

A. Send a request to site with credentials

 <script type="text/javascript">
    
        $(document).ready(function () {
            var xhr = new XMLHttpRequest();
            xhr.open('GET', 'http://lacsuat.hkcic.org', true);
            xhr.withCredentials = true;
            xhr.send(null);
        });
</script>

B. Excluded Page from form authentication

<configuration>
  <location path="ExcludedPage.aspx">
    <system.web>
      <authorization>
        <allow users="*" />
      </authorization>
    </system.web>
  </location>
</configuration>

Use SyntaxHighlighter on Blogger

Prepare your template to support code formatting

Go to your Blogger's blog online editor and choose "Template" from the menu at the left.




Click on "Edit HTML".

Within the code search for the closing head-tag

</b:template-skin>
    <b:include data='blog' name='google-analytics'/>
  </head>

  <body expr:class='&quot;loading&quot; + data:blog.mobileClass'>
  <b:section class='navbar' id='navbar' maxwidgets='1' name='Navbar' showaddelement='no'>

And copy the following code (only the links to the files you prepared for hosting) right before the end head-tag

<!-- Begin SyntaxHighlighter-->
    <link href='{Host URL}/shCore.css' rel='stylesheet' type='text/css'/> 
    <link href='{Host URL}/shThemeDefault.css' rel='stylesheet' type='text/css'/> 
    <script src='{Host URL}/shCore.js' type='text/javascript'/> 
 <script src='{Host URL}/shBrushCpp.js' type='text/javascript'/> 
    <!--script src='{Host URL}/shBrushCpp.js' type='text/javascript'/--> 
    <!--script src='{Host URL}/shBrushCSharp.js' type='text/javascript'/> 
    <script src='{Host URL}/shBrushCss.js' type='text/javascript'/> 
    <script src='{Host URL}/shBrushJava.js' type='text/javascript'/> 
    <script src='{Host URL}/shBrushJScript.js' type='text/javascript'/> 
    <script src='{Host URL}/shBrushPhp.js' type='text/javascript'/> 
    <script src='{Host URL}/shBrushPython.js' type='text/javascript'/> 
    <script src='{Host URL}/shBrushRuby.js' type='text/javascript'/> 
    <script src='{Host URL}/shBrushSql.js' type='text/javascript'/> 
    <script src='{Host URL}/shBrushVb.js' type='text/javascript'/> 
    <script src='{Host URL}/shBrushXml.js' type='text/javascript'/> 
    <script src='{Host URL}/shBrushPerl.js' type='text/javascript'/--> 
    <script type='text/javascript'>
    window.setTimeout(function() {
        SyntaxHighlighter.config.bloggerMode = true;
        SyntaxHighlighter.all();
    }, 20);
    </script>
<!-- End SyntaxHighlighter-->
</head>

Test it

Let's put some source-code into a blog article to check if it shows up correctly. There are 2 possibilities how to do this:

Method 1:

<script type="syntaxhighlighter" class="brush: cpp"><![CDATA[
// 'Hello World!' program 
 
#include <iostream>
 
int main()
{
  std::cout << "Hello World!" << std::endl;
  return 0;
}
]]></script>

Result:

?

1 2 3 4 5 6 7 8 9

// 'Hello World!' program    #include <iostream>    int main() {   std::cout << "Hello World!" << std::endl;   return 0; }

Method 2:

<pre class="brush: cpp">
// 'Hello World!' program 
 
#include &lt;iostream&gt;
 
int main()
{
  std::cout << "Hello World!" << std::endl;
  return 0;
}
</pre>

Result:

?

1 2 3 4 5 6 7 8 9

// 'Hello World!' program    #include <iostream>    int main() {   std::cout << "Hello World!" << std::endl;   return 0; }

Brush name	Brush aliases	File name
ActionScript3	as3, actionscript3	shBrushAS3.js
Bash/shell	bash, shell	shBrushBash.js
ColdFusion	cf, coldfusion	shBrushColdFusion.js
C#	c-sharp, csharp	shBrushCSharp.js
C++	cpp, c	shBrushCpp.js
CSS	css	shBrushCss.js
Delphi	delphi, pas, pascal	shBrushDelphi.js
Diff	diff, patch	shBrushDiff.js
Erlang	erl, erlang	shBrushErlang.js
Groovy	groovy	shBrushGroovy.js
JavaScript	js, jscript, javascript	shBrushJScript.js
Java	java	shBrushJava.js
JavaFX	jfx, javafx	shBrushJavaFX.js
Perl	perl, pl	shBrushPerl.js
PHP	php	shBrushPhp.js
Plain Text	plain, text	shBrushPlain.js
PowerShell	ps, powershell	shBrushPowerShell.js
Python	py, python	shBrushPython.js
Ruby	rails, ror, ruby	shBrushRuby.js
Scala	scala	shBrushScala.js
SQL	sql	shBrushSql.js
Visual Basic	vb, vbnet	shBrushVb.js
XML	xml, xhtml, xslt, html, xhtml	shBrushXml.js

References:

1. http://itsonlycode.blogspot.hk/2015/06/blogger-setup-syntaxhighlighter-for.html
2. http://alexgorbatchev.com/SyntaxHighlighter/
3. Embed Code Syntax Highlighting in Blog
4. How to Host JavaScript or CSS Files on Google Drive
5. Change Code to HTML friendly