次の方法で共有

Developing Web Apps using Always Encrypted

  • [アーティクル]

In our first post on the Always Encrypted technology, Getting Started with Always Encrypted, we showed how to develop a simple console app using Always Encrypted to protect sensitive information. In this article, we will demonstrate the process of developing a web application using Always Encrypted with ASP .NET and Entity Framework. We will follow the EF Database First with ASP.NET MVC tutorial, injecting a few additional steps to set up Always Encrypted.

For this app, you need to install the same software, as in the example from the previous post:

  1. Database Engine from CTP2 (or later) of SQL Server 2016 (on a SQL Server machine).
  2. SQL Server Management Studio from CTP2 (or later) of SQL Server 2016 (on your development machine).
  3. Visual Studio, preferably 2015 (on your development machine).

Create a Database Schema using Always Encrypted

First, create a new database, named ContosoUniversityData. Set up Always Encrypted keys, following instructions in Step1 and Step 2 from the Create a Database Schema using Always Encrypted section in our previous post.

Using a New Query window in SSMS, issue the following statement to create tables for your application. Please, note, you are setting up two encrypted columns: Student.Lastname and Enrollment.Grade.

 CREATE TABLE [dbo].[Student] (
 [StudentID] INT IDENTITY (1, 1) NOT NULL,
 [LastName] NVARCHAR (50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = CEK1) NULL,
 [FirstName] NVARCHAR (50) NULL,
 [EnrollmentDate] DATETIME NULL,
 PRIMARY KEY CLUSTERED ([StudentID] ASC)
 )
 
CREATE TABLE [dbo].[Course] (
 [CourseID] INT IDENTITY (1, 1) NOT NULL,
 [Title] NVARCHAR (50) NULL,
 [Credits] INT NULL,
 PRIMARY KEY CLUSTERED ([CourseID] ASC)
 )
 
CREATE TABLE [dbo].[Enrollment] (
 [EnrollmentID] INT IDENTITY (1, 1) NOT NULL,
 [Grade] DECIMAL(3, 2) ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = CEK1) NOT NULL,
 [CourseID] INT NOT NULL,
 [StudentID] INT NOT NULL,
 PRIMARY KEY CLUSTERED ([EnrollmentID] ASC),
 CONSTRAINT [FK_dbo.Enrollment_dbo.Course_CourseID] FOREIGN KEY ([CourseID]) 
 REFERENCES [dbo].[Course] ([CourseID]) ON DELETE CASCADE,
 CONSTRAINT [FK_dbo.Enrollment_dbo.Student_StudentID] FOREIGN KEY ([StudentID]) 
 REFERENCES [dbo].[Student] ([StudentID]) ON DELETE CASCADE
)

In addition, run the following statement to populate the Course table.

 MERGE INTO Course AS Target 
 USING (VALUES 
 (1, 'Economics', 3), 
 (2, 'Literature', 3), 
 (3, 'Chemistry', 4)
 ) 
 AS Source (CourseID, Title, Credits) 
 ON Target.CourseID = Source.CourseID 
 WHEN NOT MATCHED BY TARGET THEN 
 INSERT (Title, Credits) 
 VALUES (Title, Credits);

As both the Student and Enrollment tables contain encrypted columns, you need to use parameterized queries in ADO .NET to add rows to these tables, which cannot be done in SSMS. You will populate the Student and Enrollment tables from the application in a later step.

Create a Web Application using Always Encrypted

First, create a Visual Studio project for your app and generate an Entity Framework model for your ContosoUinversityData database. For this part, follow the instructions in Step 2: Creating the Web Application and
Data Models, in the EF Database First with ASP.NET MVC tutorial, but do the following steps differently:

  • When you create the project, make sure .Net Framework 4.6 is configured as Target Framework.
  • When you use the Entity Framework Model wizard:
    • Connect to your instance of SQL Server 2016 CTP2, instead of localdb.
    • Once you specify your database name in the Connection Properties window, click Advanced.
    • In the Advanced Properties window, locate the Column Encryption Setting keyword in the Security section and set the value of the keyword to Enabled. This will allow Visual Studio and your app to access encrypted data in your database throughout the development process.

One of the outcomes of this step is a database connection string keyword, you can find in the Web.config file. The connection string contains Column Encryption Setting = Enabled.

Follow the remaining steps in Step 2: Creating the Web Application and Data Models to complete the model. Then, complete Step 3: Generating Views to create a basic version of the Contoso University application, sufficient to demonstrate the benefits of Always Encrypted.

Optionally, you may complete Steps 4-6 of the tutorial. If you choose to continue, in Step 4: Changing the Database, modify the database from SSMS instead of using Visual Studio, as you do not have a Visual Studio Database Project at this point.

Wrapping Up

Let’s test the app and verify it really uses Always Encrypted to protect the LastName and Grade columns.

In Visual Studio, click Internet Explorer to run the application. Add a couple of students and enroll them in a few courses. The application should show and accept plaintext data. This is because the application connects to the database using a connection string containing Column Encryption Setting = Enabled and since the application runs under your user context, it can access the certificate, which is stored in your (Current User) Certificate Store, and transparently encrypt and decrypt data stored in encrypted columns. If you moved the application to another machine, you would need to move a certificate with it. Note: we will cover application deployment and migration scenarios in one of the later blog posts.

On the other hand, if you connect to the ContosoUniversityData database using SSMS (without specifying Column Encryption Setting = Enabled) and retrieve all rows from the Student and Enrollment tables, you should notice the LastName and Grade columns contain encrypted binary data.

 

For another experiment, you can connect to the database from SSMS, specifying Column Encryption Setting = Enabled in Additional Connection Parameters.

When you run the same queries as before, you will get plaintext values from encrypted columns – SSMS will behave the same way as the application, i.e. it will fetch the certificate from your Certificate Store and transparently decrypt the data.  

You can also try to run SSMS on another machine and connect to the database with Column Encryption Setting = Enabled. This time, the same queries should fail, as you do not have the certificate deployed on the other machine.

Conclusion

In this example, we have demonstrated developing a simple web app in Visual Studio, showing how to generate an Entity Framework model from an database with encrypted columns, simply by including Column Encryption Setting = Enabled in the database connection.

Comments

  • Anonymous
    June 18, 2015
    Scirpting with  powershell and sharepoint sql server windows.

  • Anonymous
    January 01, 2016
    Hi  when I deploy the application to azure and using SQL V12 always encrypted, , certificate errors. Code runs without any issues on the development machine, when I publish to azure , the application complains about the master Key certificate , how can i export  the certificates from my local machine to azure. Do i need to use key vault service for this problem. What is the best way to deploy the applications with this set up. Regards Janu

    • Anonymous
      August 13, 2016
      How did you solve this?