Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 31 of 212

21 C H A P T E R 2 | Better security parameter, which only accepts a value of AEAD_AES_256_CBC_HMAC_SHA_256. The third parameter is the COLUMN_ENCRYPTION_KEY, which is the encryption key you use to encrypt the value. Example 2-3: Creating a table using Always Encrypted CREATE TABLE [dbo].[Customers]( [CustomerId] [int] IDENTITY(1,1), [TaxId] [varchar](11) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = MyColumnKey) NOT NULL, [FirstName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL, [MiddleName] [nvarchar](50) NULL, [Address1] [nvarchar](50) NULL, [Address2] [nvarchar](50) NULL, [Address3] [nvarchar](50) NULL, [City] [nvarchar](50) NULL, [PostalCode] [nvarchar](10) NULL, [State] [char](2) NULL, [BirthDate] [date] ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = MyColumnKey) NOT NULL PRIMARY KEY CLUSTERED ([CustomerId] ASC) ON [PRIMARY] ); GO The sample code shown in Example 2-3 creates two encrypted columns. The first encrypted column is the TaxId column, which is encrypted as a deterministic value because our application allows a search of customers based on their government-issued tax identification number. The second encrypted column is the BirthDate column, which is a randomized column because our application does not require the ability to search, join, or group by this column. Indexing and Always Encrypted Columns containing encrypted data can be used as key columns within indexes—provided that those columns are encrypted by using the DETERMINISTIC encryption type. Columns encrypted by using the RANDOMIZED encryption type return an error message when you try to create an index on those columns. Columns encrypted by using either encryption type can be used as INCLUDE columns within nonclustered indexes. Because encrypted values can be indexes, no additional performance-tuning measures are required for values encrypted with Always Encrypted beyond the indexing and tuning that you normally perform. Additional network bandwidth and greater I/O are the only side effects that result from the increased size of the values being returned. Application changes The beauty of the Always Encrypted feature of SQL Server 2016 is that applications already using stored procedures, ORMs, or parameterized T-SQL commands should require no application changes to use Always Encrypted, unless nonequality operations are currently being used. Applications that build SQL statements as dynamic SQL within the application and execute those commands against the database directly need to be modified to use parameterization of their queries, a recommended security best practice for all applications, before they can take advantage of the Always Encrypted feature.

Articles in this issue

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016