Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link: http://hub-nl.insight.com/i/692679

Contents of this Issue

Navigation

Page 27 of 212

17 C H A P T E R 2 | Better security Windows 8.1 and Windows Server 2012 R2: https://technet.microsoft.com/en- us/library/hh848630(v=wps.630).aspx. Windows 10 and Windows Server 2016: https://technet.microsoft.com/en- us/library/hh848630(v=wps.640).aspx. Certificate stores and special service accounts When you import certificates into the certificate store on the computers with the application that encrypts and decrypts the data, you must import the certificates into either the machine certificate store or the certificate store of the domain account running the application. As an alternative, you can create a column master key by using T-SQL. Although you might find that creating the key is easier using SSMS, T-SQL scripts provide you with a repeatable process that you can check into a source control system and keep safe in case you need to rebuild the server. Furthermore, because best practices for SQL Server 2016 discourage installation of SSMS on the server's console and Windows security best practices discourage certificate installation on unsecured systems such as users' desktops, the use of T-SQL scripts to create column master keys is recommended. To create a column master key, use the CREATE COLUMN MASTER KEY statement, as shown in Example 2-1. This statement requires you to supply a name for the definition, such as MyKey, as shown in the example. You must also set the value for KEY_STORE_PROVIDER_NAME as MSSQL_CERTIFICATE_STORE. Last, you specify the path for the certificate in the certificate store as the KEY_PATH value. This value begins with CurrentUser when you use a certificate stored in the user account's certificate store or LocalMachine when using a certificate stored in the computer's certificate store. The rest of the value is a random-looking string of characters that represents the thumbprint of the selected certificate. This thumbprint is unique to each certificate. Example 2-1: Creating a column master key USE [Samples] GO CREATE COLUMN MASTER KEY MyKey WITH ( KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE', KEY_PATH = N'CurrentUser/My/DE3A770F25EBD6071305B77FB198D1AE434E6014' ); GO Other key store providers? You may be asking yourself what key-store providers are available besides the Microsoft SQL Server certificate store. You can choose from several other key-store providers. One option is MSSQL_CSP_PROVIDER, which allows you to use any HSM supporting Microsoft CryptoAPI. Another option is MSSQL_CNG_STORE, which allows you to use any HSM supporting Cryptography API: Next Generation. A third option is to specify AZURE_KEY_VAULT as the key-store provider, which requires you to download and install the Azure Key Vault key store provider on the machines accessing the protected data, which will be protected as described in "Using the Azure Key Vault Store Provider for Always Encrypted," at http://blogs.msdn .com/b/sqlsecurity/archive/2015/11/10/using-the-azure-key-vault-key-store-provider.aspx. Last,

Articles in this issue

Links on this page

Archives of this issue

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