Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted December 3, 2015

Exploration of SQL Server 2016 Always Encrypted – Part 1-3 - Page 3

By Greg Larsen

Generating SQL Server Login for C# Application

Lastly I need to create a SQL Server login and database user that my C# application will use.  Below is the code I used to create the SQL authenticated login and database user. 

--Create SQL Authenticated login
USE [master]
GO
CREATE LOGIN [Greg] WITH PASSWORD=N'Test', DEFAULT_DATABASE=[Demo], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

--Create Database User
USE [DEMO]
GO
CREATE USER [Greg] FOR LOGIN [Greg] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_owner] ADD MEMBER [Greg]
GO

Normally I would use a Windows authenticated service account for an application connection.  But since this was just a demo I used a SQL Authenticated login.

Running Iteration #2 Inserting Always Encrypted Data

Now that my CMK certificate is on stored  in the cert store on my laptop I will run the C# code that is at the end of this article, in a  Visual Studio 2015 .NET 4.6 project.  Remember NET 4.6 framework is the mechanism from encrypting and decrypting Always Encrypted columns.   When I execute my C# code via Visual Studio I first see the following Message Box.

Inserted Demo Record
Inserted Demo Record

The text in this message box identifies the values for the BirthDate and SSN columns involved in the INSERT statement. My C# code uses the stored procedure name “Insert_Always_Encrypted” to performed the actual INSERT statement.      

When I click “Ok” on the message box above the following message box is displayed:

Selected Data with ID …
Selected Data with ID …

This message box displays the information that my C# code read when it runs through the code in the SelectData method.  In that method, a simple SELECT statement is used to select data for each column in the Demo_Always_Encrypted table.    Before this message box can be displayed, ADO.NET has to decrypt the data in SQL Server since both the BirthDate, and SSN are Always Encrypted columns.  As you can see the message box displayed the BirthDate and SSN in clear text.  In the next section I’ll verify my BirthDate and SSN columns are actually encrypted inside of SQL Server table Demo_Always_Encrypted.

Verifying Iteration #2 Inserted Encrypted Data

In order to verify that ADO.NET encrypted the data that I send to my insert stored procudure I will run the following SELECT statement in a SSMS query window:

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

When I run this code I get the following results:

ID        LastName   FirstName BirthDate                               SSN
--        --------   --------- --------------------------------------- --------------------------------
1         Larsen     Gregory   0x011065959924B9E9DE4AA77626F5CCF08...  0x0130F5B22FAD807B90653ED072B14...

 

Here you can see that the BirthDate and SSN column are encrypted. 

Validating Admin/DBA’s Can’t See Encrypted Data

Remember I had to add the “Column Encryption Setting = Enabled” parameter to my connection string in order for my .NET C# program to encrypt or decrypt the Always Encrypted columns.  Well for my next test I’m going to add this connection parameter to my connection string when I bring up a SSMS Query on my VM machine.  I’m going to use the connection to verify that Admins/DBA’s can see the encrypted data.   

To do that I’ll right click on my VM SSMS query window and then hover over the “Connection” item, and when the additional window comes up I select the “Change Connection” item as shown below.  

Change Connection
Change Connection

 When I do this the following connection information window is displayed:

Connect to Database Engine
Connect to Database Engine

On this screen I click on the “Options >>” button to add my additional connection parameters.  When I click on this button the following screen is displayed:

Connect to Database Engine – Additional Connection Parameters
Connect to Database Engine – Additional Connection Parameters

I don’t enter my additional connection parameter here, but instead I click on the “Additional Connection Properties” tab, which then brings up this window:

Connect to Database Engine – Enter Additional Parameters
Connect to Database Engine – Enter Additional Parameters

Here I add the additional connection property “Column Encryption Setting = Enabled”.  Once I add that addition parameter I click on the “Connect” button to  connect me to SQL Server with column encryption enabled.  Now that I’m connected to SQL Server with this new connection parameter let’s see if I can see my super secret BirthDate and SSN by running the following SELECT statement:

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

When I run this code I get these results:

ID         LastName   FirstName  BirthDate                     SSN
---------- --------- ----------- ---------------------------- --------------------------------
1          Larsen     Gregory    2015-01-02                   555-55-5555

This isn’t good.  I can now read that encypted data within SQL Server Management Studio in clear text.   Thought the point was Always Encrypted should keep the confidential data out of the prying eyes of the DBA.  There must be something wrong in how I set up my test. 

Summary

In this article I setup a new SQL Server 2016 CTP3 database and table to store some data in a couple of Always Encrypted columns.   I then proved I could write a C# program to insert encrypted data into my table, and then read it back out with a SELECT statement to display it in clear text.  What I wasn’t successful doing was hiding the encrypted data from the admin/dba account on my actual SQL Server instance.   In Part 2 of this article I will show you what I did wrong with my first set of iteration tests of Always Encrypted, and how to set up an Always Encrypted environment where encrypted columns are not available to the admin/DBA. 

Additional Resources for Understanding Always Encrypted

 

If you want to get started learning about Always Encrypted here are a few links that might help:

Complete C# Code used in Article:

using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

// Demo of using Always Encrypted Columns
class AlwaysEncryptedDemo
{
    SqlConnection conn;
    public AlwaysEncryptedDemo()
    {
        // Instantiate the connection
        conn = new SqlConnection(
          "data source=WIN-B95MVF88UT5;initial catalog=Demo;integrated security = False; 
             Column Encryption Setting=Enabled; User ID = Greg; Password = Test;");
    }

    // call methods that demo Always Encrypted
    static void Main()
    {
        AlwaysEncryptedDemo scd = new AlwaysEncryptedDemo();
        scd.Insertdata();
        scd.Selectdata();
    }

    public void Insertdata()
    {
        try
        {
            // Open the connection for Insertion 
            conn.Open();

            // Constructed command to execute stored proceudre
            string insertString = @"dbo.Insert_Always_Encrypted";

             // Declare variable tho hold insdert command
            SqlCommand icmd = new SqlCommand(insertString, conn);

             //set command type to stored procedure
            icmd.CommandType = CommandType.StoredProcedure;

             // Set value of LastName
            SqlParameter paramLastName = icmd.CreateParameter();
            paramLastName.ParameterName = @"@LastName";
            paramLastName.DbType = DbType.AnsiStringFixedLength; ;
            paramLastName.Direction = ParameterDirection.Input;
            paramLastName.Value = "Larsen";
            icmd.Parameters.Add(paramLastName);

            // Set value of LastName
            SqlParameter paramFirstName = icmd.CreateParameter();
            paramFirstName.ParameterName = @"@FirstName";
            paramFirstName.DbType = DbType.AnsiStringFixedLength; ;
            paramFirstName.Direction = ParameterDirection.Input;
            paramFirstName.Value = "Greg";
            icmd.Parameters.Add(paramFirstName);

             // Set value of Birth Date
            SqlParameter
            paramBirthdate = icmd.CreateParameter();
            paramBirthdate.ParameterName = @"@BirthDate";
            paramBirthdate.SqlDbType = SqlDbType.Date;
            paramBirthdate.Direction = ParameterDirection.Input;
            paramBirthdate.Value = "2015-01-02";
            icmd.Parameters.Add(paramBirthdate);

            // Set value of SSN
            SqlParameter
            paramSSN = icmd.CreateParameter();
            paramSSN.ParameterName = @"@SSN";
            paramSSN.DbType = DbType.AnsiStringFixedLength;
            paramSSN.Direction = ParameterDirection.Input;
            paramSSN.Value = "555-55-5555";
            paramSSN.Size = 10;
            icmd.Parameters.Add(paramSSN);
 
            // Exexute Insert 
            icmd.ExecuteNonQuery();
            MessageBox.Show("Inserted Demo Record With BirthDate=" + paramBirthdate.Value + "SSN=" + paramSSN.Value);

        }
        finally
        {
            // Close the connection
            if (conn != null)
            {
                conn.Close();
            }
        }
    }
    public void Selectdata()
    {
        try
        {
            // Open the connection for Selection 
            conn.Open();

             // Read Encrypted data 
            string selectString = @"SELECT ID, LastName, FirstName, BirthDate, SSN FROM [dbo].[Demo_Always_Encrypted] ";
            SqlCommand scmd = new SqlCommand(selectString, conn);
            SqlDataReader dataRead = scmd.ExecuteReader();
            while (dataRead.Read())
            {
                MessageBox.Show("Selected Data with ID=" + dataRead["ID"].ToString() +
                                " LastName=" + dataRead["LastName"] +
                                " FirstName=" + dataRead["FirstName"] +
                                " BirthDate =" + dataRead["BirthDate"].ToString() +
                                " SSN=" + dataRead["SSN"].ToString());
            }
        }
        finally
        {
            // Close the connection
            if (conn != null)
            {
                conn.Close();
            }
        }
    }
}

See all articles by Greg Larsen



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM