Net framework data provider for sql server настройка
Contents
The SqlClient provider ships with ADO.NET and resides in the System.Data.SqlClient namespace. It should be used to access SQL Server 2000. The classes within SqlClient provider all begin with "Sql", so the connection class is SqlConnection, the command class is SqlCommand, and so on. Lets look at an example to access SQL Server 2000.
using System;
using System.Data.SqlClient;
public class CSharpAdoSqlClient
public static void Main(string[] args)
// Setup connection string to access local SQL Server 2000
string connectionString = "server=localhost;" +
"database=Northwind;uid=sa;pwd=manager";
// Instantiate the connection, passing the
// connection string into the constructor
SqlConnection con = new SqlConnection(connectionString);
// Open the connection
con.Open();
// Create and execute the query
SqlCommand cmd = new SqlCommand("SELECT * FROM Customers",con);
SqlDataReader reader = cmd.ExecuteReader();
// Iterate through the DataReader and display row
while(reader.Read()) Console.WriteLine(" - ",
reader.GetString(0), reader.GetString(1));
>
>
>
This file is called CSharpAdoSqlClient.cs , we can compile it from the command line simply by typing csc CSharpAdoSqlClient.cs. There is no need to add any references.
If you are not using SQL Server, it is almost certain that your best bet will be to use the OleDb provider, at least until more vendor specific .NET providers are released. If your data source has an ODBC driver, but not an OleDb provider, then you will need to use the Odbc .NET provider. Like the SqlClient provider, the OleDb provider resides in System.Data.dll, and ships with the .NET Framework. The classes that compose the provider are in the System.Data.OleDb namespace and all have the prefix "OleDb". Lets look at an example to access Oracle 9.
using System;
using System.Data.OleDb;
public class CSharpAdoOleDb
public static void Main(string[] args)
// Setup connection string to access Oracle9 database
// using the TNSNAMES connect string ARK2
string connectionString = "Provider=MSDAORA;" +
"Data Source=ARK2;User Password=tiger";
// Instantiate the connection, passing the
// connection string into the constructor
OleDbConnection con = new OleDbConnection(connectionString);
// Open the connection
con.Open();
// Create and execute the query
OleDbCommand cmd = new OleDbCommand("SELECT ename,job FROM emp",con);
OleDbDataReader reader = cmd.ExecuteReader();
// Iterate through the DataReader and display row
while(reader.Read()) Console.WriteLine(" - ",
reader.GetString(0), reader.GetString(1));
>
>
>
This file is called CSharpAdoOleDb.cs , we can compile it from the command line simply by typing c sc CSharpAdoOleDb.cs . There is no need to add any references.
Unlike the two other .NET providers, the Odbc provider is not shipped with the .NET Framework. To an ADO programmer, an ideal world would be one in which every data source exposes an OLE DB interface, so that ADO could call directly into the data source.
Although increasingly more database vendors are implementing OLE DB interfaces, some data sources are not yet exposed this way. However, virtually all DBMS systems in use today can be accessed through ODBC. The Microsoft ODBC Provider, however, allows ADO to connect to any ODBC data source.
The classes that compose the provider are in the Microsoft.Data.Odbc namespace and all have the prefix " Odbc " . Lets look at an example to access SQL Server 2000.
using System;
using Microsoft.Data.Odbc;
public class CSharpAdoOdbc
public static void Main(string[] args)
// Setup connection string to access local SQL Server 2000
string connectionString = "Driver=;" +
"Server=localhost;database=Northwind;uid=sa;pwd=manager";
// Instantiate the connection, passing the
// connection string into the constructor
OdbcConnection con = new OdbcConnection(connectionString);
// Open the connection
con.Open();
// Create and execute the query
OdbcCommand cmd = new OdbcCommand("SELECT * FROM Customers",con);
OdbcDataReader reader = cmd.ExecuteReader();
// Iterate through the DataReader and display row
while(reader.Read()) Console.WriteLine(" - ",
reader.GetString(0), reader.GetString(1));
>
>
>
Where Is the Performance Boost Coming From?
Installation and Configuration
All of these files, except Mtxoci8.dll, are installed in C:\Program Files\Microsoft.NET\OracleClient.Net by default (assuming that C:\Program Files is your default Program Files folder location). Mtxoci8.dll is installed in the windows system directory (for example, C:\Windows\System32 on a Windows 2000 computer on which C: is the system drive).
As part of Setup, the System.Data.OracleClient namespace is added to the global assembly cache.
The classes that compose the provider are in the System.Data.OracleClient namespace and all have the prefix " Oracle " . Lets look at an example to access Oracle 9.2.0.
using System;
using System.Data.OracleClient;
public class CSharpAdoOra
public static void Main(string[] args)
// Setup connection string to access local Oracle 9
string connectionString = "Data Source=ARK2;User Password=tiger";
// Instantiate the connection, passing the
// connection string into the constructor
OracleConnection con = new OracleConnection(connectionString);
// Open the connection
con.Open();
// Create and execute the query
OracleCommand cmd = new OracleCommand("SELECT ename,job FROM emp",con);
OracleDataReader reader = cmd.ExecuteReader();
// Iterate through the DataReader and display row
while(reader.Read()) Console.WriteLine(" - ",
reader.GetString(0), reader.GetString(1));
>
>
>
This file is called CSharpAdoOra.cs , we can compile it from the command line by typing csc /r:System.Data.OracleClient.dll CSharpAdoOra.cs . The reference to the System.Data.OracleClient.dll is needed.
This topic shows you how to connect to a Microsoft SQL Server data source from the Choose a Data Source or Choose a Destination page of the SQL Server Import and Export Wizard. There are several data providers that you can use to connect to SQL Server.
If you're on a network with multiple servers, it may be easier to enter the server name rather than expand the drop-down list of servers. If you click the drop-down list, it may take a lot of time to query the network for all available servers, and the results may not even include the server you want.
The connection options for this data provider are the same whether SQL Server is your source or your destination. That is, the options you see are the same on both the Choose a Data Source and the Choose a Destination pages of the wizard.
The connection options for this data provider are the same whether SQL Server is your source or your destination. That is, the options you see are the same on both the Choose a Data Source and the Choose a Destination pages of the wizard.
Data Source
Enter the name or IP address of the source or destination server, or select a server from the drop-down list.
To specify a non-standard TCP port, enter a comma after the server name or IP address, then enter the port number.
Initial Catalog
Enter the name of the source or destination database, or select a database from the drop-down list.
Integrated Security
Specify True to connect with Windows integrated authentication (recommended), or False to connect with SQL Server authentication. If you specify False, you must enter a user ID and password. The default value is False.
User ID
Enter a user name if you're using SQL Server authentication.
Password
Enter the password if you're using SQL Server authentication.
Copying Encrypted Data using SqlBulkCopy
With SqlBulkCopy, you can copy data, which is already encrypted and stored in one table, to another table, without decrypting the data. To do that:
Here is an example that copies data from one table to another. The SSN and BirthDate columns are assumed to be encrypted.
Prerequisites
Controlling round trips to retrieve metadata for query parameters
Setting Always Encrypted at the query level
To control performance impact of retrieving encryption metadata for parameterized queries, you can enable Always Encrypted for individual queries, instead of setting it up for the connection. This way, you can ensure that sys.sp_describe_parameter_encryption is invoked only for queries that you know have parameters targeting encrypted columns. Note, however, that by doing so, you reduce transparency of encryption: if you change encryption properties of your database columns, you may need to change the code of your application to align it with the schema changes.
To control the Always Encrypted behavior of individual queries, you need to use this constructor of SqlCommand and SqlCommandColumnEncryptionSetting. Here are some useful guidelines:
- If most queries a client application sends over a database connection access encrypted columns:
- Set the Column Encryption Setting connection string keyword to Enabled.
- Set SqlCommandColumnEncryptionSetting.Disabled for individual queries that do not access any encrypted columns. This will disable both calling sys.sp_describe_parameter_encryption as well as an attempt to decrypt any values in the result set.
- Set SqlCommandColumnEncryptionSetting.ResultSet for individual queries that do not have any parameters requiring encryption, but retrieve data from encrypted columns. This will disable calling sys.sp_describe_parameter_encryption and parameter encryption. The query will be able to decrypt the results from encryption columns.
- Set the Column Encryption Setting connection string keyword to Disabled.
- Set SqlCommandColumnEncryptionSetting.Enabled for individual queries that have any parameters that need to be encrypted. This will enable both calling sys.sp_describe_parameter_encryption as well as the decryption of any query results retrieved from encrypted columns.
- Set SqlCommandColumnEncryptionSetting.ResultSet for queries that do not have any parameters requiring encryption, but retrieve data from encrypted columns. This will disable calling sys.sp_describe_parameter_encryption and parameter encryption. The query will be able to decrypt the results from encryption columns.
In the below example, Always Encrypted is disabled for the database connection. The query, the application issues, has a parameter that targets the LastName column that is not encrypted. The query retrieves data from the SSN and BirthDate columns that are both encrypted. In such a case, calling sys.sp_describe_parameter_encryption to retrieve encryption metadata is not required. However, the decryption of the query results needs to be enabled, so that the application can receive plaintext values from the two encrypted columns. SqlCommandColumnEncryptionSetting.ResultSet setting is used to ensure that.
Controlling Performance Impact of Always Encrypted
- Additional round trips to the database to retrieve metadata for query parameters.
- Calls to a column master key store to access a column master key.
Inserting Data Example
This example inserts a row into the Patients table. Note the following:
Connect to SQL Server with the ODBC driver for SQL Server
Get the latest driver. Download the Microsoft ODBC Driver for SQL Server.
Inserting Data Example
This example inserts a row into the Patients table. Note the following:
Column encryption key caching
Unsupported data type conversion errors
Always Encrypted supports few conversions for encrypted data types. See Always Encrypted for a detailed list of supported type conversions. Do the following to avoid data type conversion errors:
Включение Always Encrypted для запросов приложений
Самым простым способом включения шифрования параметров и расшифровки результатов запросов к зашифрованным столбцам является установка для ключевого слова строки подключения "Параметр шифрования столбца" значения Включено.
Ниже приведен пример строки подключения, включающий постоянное шифрование.
Далее приведен эквивалент примера с использованием свойства SqlConnectionStringBuilder.ColumnEncryptionSetting.
Постоянное шифрование также можно включить для отдельных запросов. См. раздел Управление влиянием постоянного шифрования на производительность ниже. Включения функции Always Encrypted недостаточно для успешного шифрования или расшифровки. Необходимо также проверить выполнение следующих условий:
- Приложение имеет разрешения VIEW ANY COLUMN MASTER KEY DEFINITION и VIEW ANY COLUMN ENCRYPTION KEY DEFINITION для базы данных, необходимые для доступа к метаданным о ключах постоянного шифрования в базе данных. Для дополнительных сведений см. страницу о разрешениях в статье "Always Encrypted (ядро СУБД)".
- Приложение может получить доступ к главному ключу столбца, который защищает ключи шифрования столбцов, шифрующие запрашиваемые столбцы базы данных.
Avoiding common problems when querying encrypted columns
Retrieving and Modifying Data in Encrypted Columns
The below table summarizes the behavior of queries, depending on whether Always Encrypted is enabled or not:
Query characteristic Always Encrypted is enabled and application can access the keys and key metadata Always Encrypted is enabled and application can't access the keys or key metadata Always Encrypted is disabled Queries with parameters targeting encrypted columns. Parameter values are transparently encrypted. Error Error Queries retrieving data from encrypted columns, without parameters targeting encrypted columns. Results from encrypted columns are transparently decrypted. The application receives plaintext values of the .NET datatypes corresponding to the SQL Server types configured for the encrypted columns. Error Results from encrypted columns aren't decrypted. The application receives encrypted values as byte arrays (byte[]). The following examples illustrate retrieving and modifying data in encrypted columns. The examples assume the target table with the below schema. The SSN and BirthDate columns are encrypted.
Query metadata caching
Кэширование метаданных запроса
Использование встроенных поставщиков хранилища главных ключей столбцов
Для использования этих поставщиков не требуется изменять код приложения, однако необходимо учесть следующие моменты.
- Вы (или ваш администратор баз данных) должны проверить правильность имени поставщика, настроенного в метаданных главного ключа столбца, и убедиться, что путь к ключу главного ключа столбца соответствует формату пути к ключу, который является допустимым для данного поставщика. Для настройки ключей рекомендуется использовать средство, такое как среда SQL Server Management Studio, которое при выполнении инструкции CREATE COLUMN MASTER KEY (Transact-SQL) автоматически создает допустимые имена поставщиков и пути к ключам. Дополнительные сведения см. в разделах Configuring Always Encrypted using SQL Server Management Studio (Настройка Always Encrypted с помощью среды SQL Server Management Studio ) и Настройка постоянного шифрования с помощью PowerShell.
- Убедитесь, что приложение может получить доступ к ключу в хранилище ключей. Для этого может потребоваться предоставить приложению доступ к ключу или хранилищу ключей (в зависимости от хранилища ключей) или выполнить другие действия по настройке конкретного хранилища ключей. Например, для доступа к хранилищу, реализующему CNG или CAPI (например, к аппаратному модулю безопасности), на компьютере с приложением необходимо установить библиотеку, реализующую CNG или CAPI для хранилища. Дополнительные сведения см. в разделе Создание и хранение главных ключей столбцов для Always Encrypted.
Enabling Always Encrypted for Application Queries
The easiest way to enable the encryption of parameters, and the decryption of query results targeting the encrypted columns, is by setting the value of the Column Encryption Setting connection string keyword to enabled.
The following is an example of a connection string that enables Always Encrypted:
The following is an equivalent example using the SqlConnectionStringBuilder.ColumnEncryptionSetting Property.
- The application has the VIEW ANY COLUMN MASTER KEY DEFINITION and VIEW ANY COLUMN ENCRYPTION KEY DEFINITION database permissions, required to access the metadata about Always Encrypted keys in the database. For details, see Permissions section in Always Encrypted (Database Engine).
- The application can access the column master key that protects the column encryption keys, encrypting the queried database columns.
Copying Encrypted Data using SqlBulkCopy
With SqlBulkCopy, you can copy data, which is already encrypted and stored in one table, to another table, without decrypting the data. To do that:
Here is an example that copies data from one table to another. The SSN and BirthDate columns are assumed to be encrypted.
Using Azure Key Vault provider
Using built-in column master key store providers
You do not need to make any application code changes to use these providers but note the following:
- You (or your DBA) need to make sure the provider name, configured in the column master key metadata, is correct and the column master key path complies with the key path format that is valid for a given provider. It is recommended that you configure the keys using tools such as SQL Server Management Studio, which automatically generates the valid provider names and key paths when issuing the CREATE COLUMN MASTER KEY (Transact-SQL) statement. For more information, see Configuring Always Encrypted using SQL Server Management Studio and Configure Always Encrypted using PowerShell.
- Ensure your application can access the key in the key store. This may involve granting your application access to the key and/or the key store, depending on the key store, or performing other key store-specific configuration steps. For example, to access a key store implementing CNG or CAPI (e.g. a hardware security module), you need to make sure a library implementing CNG or CAPI for your store is installed on your application machine. For details, see Create and store column master keys for Always Encrypted.
Retrieving Plaintext Data Example
The following example demonstrates filtering data based on encrypted values, and retrieving plaintext data from encrypted columns. Note the following:
Queries can perform equality comparisons on columns if they are encrypted using deterministic encryption. For more information, see Selecting Deterministic or Randomized Encryption.
Query metadata caching
Управление влиянием постоянного шифрования на производительность
Поскольку постоянное шифрование является технологией шифрования на стороне клиента, значительное влияние на производительность происходит на стороне клиента, а не в базе данных. Помимо затрат на операции шифрования и расшифровки существуют и другие источники снижения производительности на стороне клиента:
- Дополнительные обращения к базе данных для получения метаданных для параметров запроса.
- Вызовы хранилища главных ключей столбцов для доступа к главному ключу столбца.
Копирование зашифрованных данных с помощью SqlBulkCopy
С помощью SqlBulkCopy можно скопировать данные, которые уже зашифрованы и хранятся в одной таблице, в другую таблицу, не расшифровывая при этом данные. Для этого выполните указанные далее действия.
Ниже приведен пример копирования данных из одной таблицы в другую. Предполагается, что столбцы SSN и BirthDate зашифрованы.
Avoiding common problems when querying encrypted columns
Enabling Additional Protection for a Compromised SQL Server
Controlling round trips to retrieve metadata for query parameters
Ошибки, возникающие из-за передачи значений в виде открытого текста, а не в зашифрованном виде
Любое значение, предназначенное для зашифрованного столбца, должно быть зашифровано внутри приложения. Попытка вставки, изменения или фильтрации по значению в виде открытого текста в зашифрованном столбце приведет к возникновению ошибки, подобной следующей:
Чтобы избежать таких ошибок, убедитесь, что:
- функция Always Encrypted включена для запросов приложений, предназначенных для зашифрованных столбцов (для строки подключения или в отдельном объекте SqlCommand для конкретного запроса);
- для отправки данных, предназначенных для зашифрованных столбцов, используется параметр SqlParameter. В примере ниже показан запрос, который вместо передачи литерала внутри объекта SqlParameter неправильно фильтрует по литералу или константе в зашифрованном столбце (SSN).
Errors due to passing plaintext instead of encrypted values
Any value that targets an encrypted column needs to be encrypted inside the application. An attempt to insert/modify or to filter by a plaintext value on an encrypted column will result in an error similar to this:
- Always Encrypted is enabled for application queries targeting encrypted columns (for the connection string or in the SqlCommand object for a specific query).
- You use SqlParameter to send data targeting encrypted columns. The following example shows a query that incorrectly filters by a literal/constant on an encrypted column (SSN)(instead of passing the literal inside a SqlParameter object).
Пример получения зашифрованных данных
Если постоянное шифрование не включено, запрос может получать данные из зашифрованных столбцов, пока для него не будут указаны параметры, предназначенные для зашифрованных столбцов.
В следующем примере показано извлечение двоичных зашифрованных данных из зашифрованных столбцов. Следует отметить следующее.
- Так как постоянное шифрование не включено в строке подключения, запрос будет возвращать зашифрованные значения SSN и BirthD в виде байтовых массивов (программа преобразует значения в строки).
- Запрос, получающий данные из зашифрованных столбцов с отключенным постоянным шифрованием, может иметь параметры при условии, что ни один из параметров не предназначен для зашифрованного столбца. Приведенный выше запрос выполняет фильтрацию по LastName, который не зашифрован в базе данных. Запрос, отфильтрованный по SSN или BirthDate, завершится ошибкой.
Retrieving and Modifying Data in Encrypted Columns
The below table summarizes the behavior of queries, depending on whether Always Encrypted is enabled or not:
Query characteristic Always Encrypted is enabled and application can access the keys and key metadata Always Encrypted is enabled and application can't access the keys or key metadata Always Encrypted is disabled Queries with parameters targeting encrypted columns. Parameter values are transparently encrypted. Error Error Queries retrieving data from encrypted columns, without parameters targeting encrypted columns. Results from encrypted columns are transparently decrypted. The application receives plaintext values of the .NET datatypes corresponding to the SQL Server types configured for the encrypted columns. Error Results from encrypted columns aren't decrypted. The application receives encrypted values as byte arrays (byte[]). The following examples illustrate retrieving and modifying data in encrypted columns. The examples assume the target table with the below schema. The SSN and BirthDate columns are encrypted.
Implementing a custom column master key store provider
If you want to store column master keys in a key store that is not supported by an existing provider, you can implement a custom provider by extending the SqlColumnEncryptionCngProvider Class and registering the provider using the SqlConnection.RegisterColumnEncryptionKeyStoreProviders method.
Retrieving Encrypted Data Example
If Always Encrypted is not enabled, a query can still retrieve data from encrypted columns, as long as the query has no parameters targeting encrypted columns.
The following example demonstrates how to retrieve binary encrypted data from encrypted columns. Note the following:
- As Always Encrypted is not enabled in the connection string, the query will return encrypted values of SSN and BirthDate as byte arrays (the program converts the values to strings).
- A query retrieving data from encrypted columns with Always Encrypted disabled can have parameters, as long as none of the parameters target an encrypted column. The above query filters by LastName, which isn't encrypted in the database. If the query filtered by SSN or BirthDate, the query would fail.
Реализация настраиваемого поставщика хранилища главных ключей столбцов
Чтобы сохранить главные ключи столбцов в хранилище ключей, не поддерживаемом существующим поставщиком, можно реализовать настраиваемый поставщик, расширив класс SqlColumnEncryptionCngProvider и зарегистрировав поставщик с помощью метода SqlConnection.RegisterColumnEncryptionKeyStoreProviders .
Errors due to passing plaintext instead of encrypted values
Any value that targets an encrypted column needs to be encrypted inside the application. An attempt to insert/modify or to filter by a plaintext value on an encrypted column will result in an error similar to this:
- Always Encrypted is enabled for application queries targeting encrypted columns (for the connection string or in the SqlCommand object for a specific query).
- You use SqlParameter to send data targeting encrypted columns. The following example shows a query that incorrectly filters by a literal/constant on an encrypted column (SSN)(instead of passing the literal inside a SqlParameter object).
Включение Always Encrypted с безопасными анклавами
Общие сведения о роли клиентского драйвера в вычислениях анклава и аттестации анклава см. в статье Разработка приложений с помощью Always Encrypted с безопасными анклавами.
Включите Always Encrypted для запросов приложения, как описано в предыдущем разделе.
Интегрируйте пакет NuGet Microsoft.SqlServer.Management.AlwaysEncrypted.EnclaveProviders в приложение. NuGet — это библиотека поставщиков анклавов, которая реализует логику на стороне клиента для протокола аттестации, а также для установки безопасного канала с безопасным анклавом.
Измените конфигурацию приложения (например, в файле web.config или app.config), чтобы определить сопоставление типа анклава, настроенного для базы данных, и поставщика анклава.
- При использовании SQL Server и службы защиты узла (HGS) необходимо соотнести тип анклава VBS с классом Microsoft.SqlServer.Management.AlwaysEncrypted.EnclaveProviders.HostGuardianServiceEnclaveProvider из пакета NuGet.
- При использовании База данных SQL Azure и службы "Аттестация Microsoft Azure" необходимо соотнести тип анклава SGX с классом Microsoft.SqlServer.Management.AlwaysEncrypted.EnclaveProviders.AzureAttestationEnclaveProvider из пакета NuGet.
В строке подключения к базе данных задайте для ключевого слова Enclave Attestation URL URL-адрес аттестации (конечная точка службы аттестации). Необходимо получить URL-адрес аттестации для имеющейся среды у администратора службы аттестации.
- Если вы используете SQL Server и службу защитника узлов (HGS), см. сведения в разделе об определении и совместном использовании URL-адреса аттестации HGS.
- Если вы используете База данных SQL Azure и Аттестацию Microsoft Azure, см. сведения об определении URL-адреса аттестации для политики аттестации.
Как избежать распространенных проблем при запросе зашифрованных столбцов
Setting Always Encrypted at the query level
To control performance impact of retrieving encryption metadata for parameterized queries, you can enable Always Encrypted for individual queries, instead of setting it up for the connection. This way, you can ensure that sys.sp_describe_parameter_encryption is invoked only for queries that you know have parameters targeting encrypted columns. Note, however, that by doing so, you reduce transparency of encryption: if you change encryption properties of your database columns, you may need to change the code of your application to align it with the schema changes.
To control the Always Encrypted behavior of individual queries, you need to use this constructor of SqlCommand and SqlCommandColumnEncryptionSetting. Here are some useful guidelines:
- If most queries a client application sends over a database connection access encrypted columns:
- Set the Column Encryption Setting connection string keyword to Enabled.
- Set SqlCommandColumnEncryptionSetting.Disabled for individual queries that do not access any encrypted columns. This will disable both calling sys.sp_describe_parameter_encryption as well as an attempt to decrypt any values in the result set.
- Set SqlCommandColumnEncryptionSetting.ResultSet for individual queries that do not have any parameters requiring encryption, but retrieve data from encrypted columns. This will disable calling sys.sp_describe_parameter_encryption and parameter encryption. The query will be able to decrypt the results from encryption columns.
- Set the Column Encryption Setting connection string keyword to Disabled.
- Set SqlCommandColumnEncryptionSetting.Enabled for individual queries that have any parameters that need to be encrypted. This will enable both calling sys.sp_describe_parameter_encryption as well as the decryption of any query results retrieved from encrypted columns.
- Set SqlCommandColumnEncryptionSetting.ResultSet for queries that do not have any parameters requiring encryption, but retrieve data from encrypted columns. This will disable calling sys.sp_describe_parameter_encryption and parameter encryption. The query will be able to decrypt the results from encryption columns.
In the below example, Always Encrypted is disabled for the database connection. The query, the application issues, has a parameter that targets the LastName column that is not encrypted. The query retrieves data from the SSN and BirthDate columns that are both encrypted. In such a case, calling sys.sp_describe_parameter_encryption to retrieve encryption metadata is not required. However, the decryption of the query results needs to be enabled, so that the application can receive plaintext values from the two encrypted columns. SqlCommandColumnEncryptionSetting.ResultSet setting is used to ensure that.
Работа с хранилищами главных ключей столбцов
Процедура получения ключа шифрования столбца:
- Имя поставщика хранилища ключей, который инкапсулирует хранилище ключей, содержащее главный ключ столбца.
- Путь к ключу, указывающий положение главного ключа столбца в хранилище ключей.
Сведения о ключе шифрования столбца включают в себя следующее:
- Зашифрованное значение ключа шифрования столбца.
- Имя алгоритма, который использовался для шифрования ключа шифрования столбца.
Retrieving Plaintext Data Example
The following example demonstrates filtering data based on encrypted values, and retrieving plaintext data from encrypted columns. Note the following:
Queries can perform equality comparisons on columns if they are encrypted using deterministic encryption. For more information, see Selecting Deterministic or Randomized Encryption.
Using built-in column master key store providers
You do not need to make any application code changes to use these providers but note the following:
- You (or your DBA) need to make sure the provider name, configured in the column master key metadata, is correct and the column master key path complies with the key path format that is valid for a given provider. It is recommended that you configure the keys using tools such as SQL Server Management Studio, which automatically generates the valid provider names and key paths when issuing the CREATE COLUMN MASTER KEY (Transact-SQL) statement. For more information, see Configuring Always Encrypted using SQL Server Management Studio and Configure Always Encrypted using PowerShell.
- Ensure your application can access the key in the key store. This may involve granting your application access to the key and/or the key store, depending on the key store, or performing other key store-specific configuration steps. For example, to access a key store implementing CNG or CAPI (e.g. a hardware security module), you need to make sure a library implementing CNG or CAPI for your store is installed on your application machine. For details, see Create and store column master keys for Always Encrypted.
Using column master key store providers for programmatic key provisioning
Connect to SQL Server with the Microsoft OLE DB Provider for SQL Server or SQL Server Native Client
Microsoft OLE DB Provider for SQL Server is the current OLE DB provider for SQL Server.
Prerequisites
Пример вставки данных
В этом примере показана вставка строки в таблицу Patients. Следует отметить следующее.
Forcing Parameter Encryption
Although using the SqlParameter.ForceColumnEncryption Property helps improve security, it also reduces the transparency of encryption to the client application. If you update the database schema to change the set of encrypted columns, you might need to make application changes as well.
The following code sample illustrates using the SqlParameter.ForceColumnEncryption Property to prevent social security numbers to be sent in plaintext to the database.
Управление обращениями для получения метаданных для параметров запроса
Enabling Always Encrypted with Secure Enclaves
For general information on the client driver role in enclave computations and enclave attestation, see Develop applications using Always Encrypted with secure enclaves.
To configure your application:
Enable Always Encrypted for your application queries, as explained in the previous section.
Integrate the Microsoft.SqlServer.Management.AlwaysEncrypted.EnclaveProviders NuGet package with your application. The NuGet is a library of enclave providers, implementing the client-side logic for attestation protocols and for establishing a secure channel with a secure enclave.
Update your application configuration (for example in web.config or app.config) to define the mapping between an enclave type, configured for your database, and an enclave provider.
- If you're using SQL Server and Host Guardian Service (HGS), you need to map the VBS enclave type to the Microsoft.SqlServer.Management.AlwaysEncrypted.EnclaveProviders.HostGuardianServiceEnclaveProvider class from the NuGet package.
- If you're using Azure SQL Database and Microsoft Azure Attestation, you need to map the SGX enclave type to the Microsoft.SqlServer.Management.AlwaysEncrypted.EnclaveProviders.AzureAttestationEnclaveProvider class from the NuGet package.
Set the Enclave Attestation URL keyword in your database connection string to an attestation URL (an attestation service endpoint). You need to obtain an attestation URL for your environment from your attestation service administrator.
- If you're using SQL Server and Host Guardian Service (HGS), see Determine and share the HGS attestation URL.
- If you're using Azure SQL Database and Microsoft Azure Attestation, see Determine the attestation URL for your attestation policy.
Использование поставщиков хранилищ главных ключей столбцов для программной подготовки ключей
Retrieving Encrypted Data Example
If Always Encrypted is not enabled, a query can still retrieve data from encrypted columns, as long as the query has no parameters targeting encrypted columns.
The following example demonstrates how to retrieve binary encrypted data from encrypted columns. Note the following:
- As Always Encrypted is not enabled in the connection string, the query will return encrypted values of SSN and BirthDate as byte arrays (the program converts the values to strings).
- A query retrieving data from encrypted columns with Always Encrypted disabled can have parameters, as long as none of the parameters target an encrypted column. The above query filters by LastName, which isn't encrypted in the database. If the query filtered by SSN or BirthDate, the query would fail.
Working with Column Master Key Stores
The process to obtain a column encryption key:
- The name of a key store provider that encapsulates a key store containing the column master key.
- The key path that specifies the location of the column master key in the key store.
The information about the column encryption key includes:
- The encrypted value of a column encryption key.
- The name of the algorithm that was used to encrypt the column encryption key.
Предварительные требования
Задание постоянного шифрования на уровне запроса
Для управления влиянием процесса получения метаданных шифрования для параметризованных запросов на производительность можно включить постоянное шифрование для отдельных запросов, а не настраивать его для соединения. Таким образом, это гарантирует, что sys.sp_describe_parameter_encryption вызывается только для запросов, которые точно имеют параметры, предназначенные для зашифрованных столбцов. Обратите внимание, что в этом случае снижается прозрачность шифрования: при изменении свойств шифрования столбцов базы данных может потребоваться изменить код приложения в соответствии с изменениями схемы.
Для управления поведением функции Always Encrypted в отдельных запросах необходимо использовать этот конструктор SqlCommand и SqlCommandColumnEncryptionSetting. Ниже приведены некоторые полезные рекомендации.
- Если большинство запросов, отправляемых клиентским приложением через подключение к базе данных, получает доступ к зашифрованным столбцам:
- Задайте для ключевого слова строки подключения Параметр шифрования столбца значение Включено.
- Задайте SqlCommandColumnEncryptionSetting.Disabled для отдельных запросов, которые не обращаются к зашифрованным столбцам. Будет отключена возможность вызова sys.sp_describe_parameter_encryption и расшифровки всех значений в наборе результатов.
- Задайте SqlCommandColumnEncryptionSetting.ResultSet для отдельных запросов, которые не имеют параметров, требующих шифрования, но получают данные из зашифрованных столбцов. Будет отключена возможность вызова sys.sp_describe_parameter_encryption и шифрования параметров. Запрос сможет расшифровывать результаты из столбцов шифрования.
- Задайте для ключевого слова строки подключения Параметр шифрования столбца значение Отключено.
- Задайте SqlCommandColumnEncryptionSetting.Enabled для отдельных запросов, имеющих параметры, которые требуется зашифровать. Будет включена возможность вызова sys.sp_describe_parameter_encryption и расшифровки результатов запроса, полученных из зашифрованных столбцов.
- Задайте SqlCommandColumnEncryptionSetting.ResultSet для запросов, которые не имеют параметров, требующих шифрования, но получают данные из зашифрованных столбцов. Будет отключена возможность вызова sys.sp_describe_parameter_encryption и шифрования параметров. Запрос сможет расшифровывать результаты из столбцов шифрования.
В следующем примере постоянное шифрование отключено для подключения к базе данных. Запрос, выполняемый приложением, имеет параметр, предназначенный для незашифрованного столбца LastName. Запрос получает данные из зашифрованных столбцов SSN и BirthDate. В этом случае вызывать sys.sp_describe_parameter_encryption для получения метаданных шифрования не требуется. Однако необходимо включить расшифровку результатов запроса, чтобы приложение могло получать значения в виде обычного текста из двух зашифрованных столбцов. Для этого используется параметр SqlCommandColumnEncryptionSetting.ResultSet.
Working with Column Master Key Stores
The process to obtain a column encryption key:
- The name of a key store provider that encapsulates a key store containing the column master key.
- The key path that specifies the location of the column master key in the key store.
The information about the column encryption key includes:
- The encrypted value of a column encryption key.
- The name of the algorithm that was used to encrypt the column encryption key.
Forcing Parameter Encryption
Although using the SqlParameter.ForceColumnEncryption Property helps improve security, it also reduces the transparency of encryption to the client application. If you update the database schema to change the set of encrypted columns, you might need to make application changes as well.
The following code sample illustrates using the SqlParameter.ForceColumnEncryption Property to prevent social security numbers to be sent in plaintext to the database.
Enabling Always Encrypted for Application Queries
The easiest way to enable the encryption of parameters, and the decryption of query results targeting the encrypted columns, is by setting the value of the Column Encryption Setting connection string keyword to enabled.
The following is an example of a connection string that enables Always Encrypted:
The following is an equivalent example using the SqlConnectionStringBuilder.ColumnEncryptionSetting Property.
- The application has the VIEW ANY COLUMN MASTER KEY DEFINITION and VIEW ANY COLUMN ENCRYPTION KEY DEFINITION database permissions, required to access the metadata about Always Encrypted keys in the database. For details, see Permissions section in Always Encrypted (Database Engine).
- The application can access the column master key that protects the column encryption keys, encrypting the queried database columns.
Options to specify (ODBC driver for SQL Server)
The connection options for the ODBC driver are the same whether SQL Server is your source or your destination. That is, the options you see are the same on both the Choose a Data Source and the Choose a Destination pages of the wizard.
To connect to SQL Server with the latest ODBC driver, assemble a connection string that includes the following settings and their values. The format of a complete connection string immediately follows the list of settings.
Get help assembling a connection string that's just right. Or, instead of providing a connection string, provide an existing DSN (data source name) or create a new one. For more info about these options, see Connect to an ODBC Data Source.
Driver
The name of the ODBC driver. The name is different for different versions of the driver.Server
The name of the SQL Server.Database
The name of the database.Trusted_Connection; or, Uid and Pwd
Specify Trusted_Connection=Yes to connect with Windows integrated authentication; or, specify Uid (user id) and Pwd (password) to connect with SQL Server authentication.Column encryption key caching
Принудительное шифрование параметров
Хотя использование свойства SqlParameter.ForceColumnEncryption помогает повысить безопасность, оно также снижает прозрачность шифрования для клиентского приложения. Если вы обновляете схему базы данных для изменения набора зашифрованных столбцов, вам может потребоваться изменить и само приложение.
В следующем примере кода показано, как с помощью свойства SqlParameter.ForceColumnEncryption предотвратить отправку номеров социального страхования в базу данных в формате открытого текста.
Кэширование ключа шифрования столбца
Получение и изменение данных в зашифрованных столбцах
В приведенной ниже таблице описывается поведение запросов в зависимости от того, включено постоянное шифрование или нет.
Характеристика запроса Постоянное шифрование включено, и приложение может получать доступ к ключам и метаданным ключей Функция Always Encrypted включена, и приложение не может получать доступ к ключам и метаданным ключей Постоянное шифрование отключено Запросы с параметрами, предназначенными для зашифрованных столбцов. Значения параметров прозрачно шифруются. Error Error Запросы, получающие данные из зашифрованных столбцов, без параметров, предназначенных для зашифрованных столбцов. Результаты из зашифрованных столбцов прозрачно расшифровываются. Приложение получает значения типов данных .NET в виде открытого текста, которые соответствуют типам SQL Server, настроенным для зашифрованных столбцов. Error Результаты из зашифрованных столбцов не расшифровываются. Приложение получает зашифрованные значения в виде массивов байтов (byte[]). В следующих примерах показано получение и изменение данных в зашифрованных столбцах. В примерах предполагается использование целевой таблицы с приведенной ниже схемой. Столбцы SSN и BirthDate зашифрованы.
Connection string format
Here's the format of a connection string that uses Windows integrated authentication.
Here's the format of a connection string that uses SQL Server authentication instead of Windows integrated authentication.
Enabling Always Encrypted with Secure Enclaves
For general information on the client driver role in enclave computations and enclave attestation, see Develop applications using Always Encrypted with secure enclaves.
To configure your application:
Enable Always Encrypted for your application queries, as explained in the previous section.
Integrate the Microsoft.SqlServer.Management.AlwaysEncrypted.EnclaveProviders NuGet package with your application. The NuGet is a library of enclave providers, implementing the client-side logic for attestation protocols and for establishing a secure channel with a secure enclave.
Update your application configuration (for example in web.config or app.config) to define the mapping between an enclave type, configured for your database, and an enclave provider.
- If you're using SQL Server and Host Guardian Service (HGS), you need to map the VBS enclave type to the Microsoft.SqlServer.Management.AlwaysEncrypted.EnclaveProviders.HostGuardianServiceEnclaveProvider class from the NuGet package.
- If you're using Azure SQL Database and Microsoft Azure Attestation, you need to map the SGX enclave type to the Microsoft.SqlServer.Management.AlwaysEncrypted.EnclaveProviders.AzureAttestationEnclaveProvider class from the NuGet package.
Set the Enclave Attestation URL keyword in your database connection string to an attestation URL (an attestation service endpoint). You need to obtain an attestation URL for your environment from your attestation service administrator.
- If you're using SQL Server and Host Guardian Service (HGS), see Determine and share the HGS attestation URL.
- If you're using Azure SQL Database and Microsoft Azure Attestation, see Determine the attestation URL for your attestation policy.
Configuring Trusted Column Master Key Paths
Although setting trusted key paths improves security of your application, you will need to change the code or/and the configuration of the application, whenever you rotate your column master key (whenever the column master key path changes).
The following example shows how to configure trusted column master key paths:
Включение дополнительной защиты для скомпрометированного SQL Server
Other data providers and more info
For info about how to connect to SQL Server with a data provider that's not listed here, see SQL Server connection strings. This third-party site also contains more info about the data providers and the connection parameters described on this page.
Ошибки преобразования неподдерживаемых типов данных
Постоянное шифрование поддерживает несколько преобразований для зашифрованных типов данных. Подробный список поддерживаемых преобразований типов см. в статье Always Encrypted. Чтобы избежать ошибок при преобразовании типов данных, сделайте следующее:
Configuring Trusted Column Master Key Paths
Although setting trusted key paths improves security of your application, you will need to change the code or/and the configuration of the application, whenever you rotate your column master key (whenever the column master key path changes).
The following example shows how to configure trusted column master key paths:
Using column master key store providers for programmatic key provisioning
Enter the connection string
Enter the connection string in the ConnectionString field, or enter the DSN name in the Dsn field, on the Choose a Data Source or Choose a Destination page. After you enter the connection string, the wizard parses the string and displays the individual properties and their values in the list.
The following example uses this connection string.
Here's the screen that you see after entering the connection string.
Использование поставщика хранилища ключей Azure
Enabling Additional Protection for a Compromised SQL Server
Пример получения данных в виде открытого текста
В следующем примере показана фильтрация данных на основе зашифрованных значений и получение данных в виде открытого текста из зашифрованных столбцов. Следует отметить следующее.
Запросы могут выполнять сравнение на равенство для столбцов, если они шифруются с помощью детерминированного шифрования. Дополнительные сведения см. в разделе Выбор детерминированного или случайного шифрования.
Unsupported data type conversion errors
Always Encrypted supports few conversions for encrypted data types. See Always Encrypted for a detailed list of supported type conversions. Do the following to avoid data type conversion errors:
Using Azure Key Vault provider
Controlling Performance Impact of Always Encrypted
- Additional round trips to the database to retrieve metadata for query parameters.
- Calls to a column master key store to access a column master key.
Implementing a custom column master key store provider
If you want to store column master keys in a key store that is not supported by an existing provider, you can implement a custom provider by extending the SqlColumnEncryptionCngProvider Class and registering the provider using the SqlConnection.RegisterColumnEncryptionKeyStoreProviders method.
Настройка доверенных путей для главных ключей столбцов
Несмотря на то, что установка доверенных путей повышает безопасность приложения, вам потребуется изменять код или (и) конфигурацию приложения при каждой смене главного ключа столбца (или при изменении пути к главному ключу столбца).
В следующем примере показано, как настраивать доверенные пути к главному ключу столбца:
Читайте также: