Stack Overflow
Stack Overflow

Questions tagged [always-encrypted]

Always Encrypted is a feature of Microsoft SQL Server 2016+ and Azure SQL Database, designed to protect sensitive data. It allows client applications to encrypt sensitive data without revealing the encryption keys to the database engine, so the data cannot be accessed either in flight on the wire or at rest on SQL Server.

Always Encrypted is a feature of Microsoft SQL Server 2016 and Azure SQL Database, designed to protect sensitive data. It allows client applications to encrypt sensitive data without revealing the encryption keys to the database engine, so the data cannot be accessed either in flight or at rest.

See MSDN for more details.

Encryption Keys

This is mainly focused on 2 Keys

  1. Column Master Key (CMK)
  2. Column Encryption Key (CEK)

CMK

Creates a column master key metadata object in a database. A column master key metadata entry that represents a key, stored in an external key store, which is used to protect (encrypt) column encryption keys when using the Always Encrypted (Database Engine) feature. Multiple column master keys allow for key rotation; periodically changing the key to enhance security. You can create a column master key in a key store and its corresponding metadata object in the database by using the Object Explorer in SQL Server Management Studio or PowerShell.

Read more here

CEK

Creates a column encryption key with the initial set of values, encrypted with the specified column master keys. This is a metadata operation. A CEK can have up to two values which allows for a column master key rotation. Creating a CEK is required before any column in the database can be encrypted using the Always Encrypted (Database Engine) feature. CEK's can also be created by using SQL Server Management Studio

Before creating a CEK, you must define a CMK by using Management Studio or the CREATE COLUMN MASTER KEY statement.

Read more here

Types of Encryption

  1. Deterministic Encryption
  2. Randomized Encryption

Deterministic Encryption

Deterministic encryption always generates the same encrypted value for any given plaintext value. Using deterministic encryption allows point lookups, equality joins, grouping and indexing on encrypted columns. However, but may also allow unauthorized users to guess information about encrypted values by examining patterns in the encrypted column, especially if there is a small set of possible encrypted values, such as True/False, or North/South/East/West region. Deterministic encryption must use a column collation with a binary2 sort order for character column

Randomized Encryption

Randomized encryption uses a method that encrypts data in a less predictable manner. Randomized encryption is more secure, but prevents searching, grouping, indexing, and joining on encrypted columns.

Read more here

299 questions
0
votes
0 answers

Always Encrypted Operand Clash In Query

I've recently started to use Always Encrypted with SQL Server 2016 to encrypt sensitive information. For the most part, everything has been plain sailing with regards to seamless transition. One thing has cropped up though, the following error -…
SK2017
  • 753
  • 9
  • 38
0
votes
1 answer

Set-SqlColumnEncryption cannot find or retrieve master column key from ms certificate store when setting up Always-Encrypted feature

I am trying to implement the Always-Encrypted feature on a few columns of an MS SQL Server 2016 DB. I have, in fact, successfully implemented it on my UAT DB. However, when trying to do the identical steps on my production DB, I am getting the…
David I. McIntosh
  • 2,038
  • 4
  • 23
  • 45
0
votes
1 answer

Always Encrypted Wizard option not displayed

I'm using SQL Server 2016 and SQL Server Management Studio updated to v17.4. I want to use Always Encrypted to encrypt a column. I tried to run the Always Encrypted wizard as illustrated in a number of places. I right click the database node, choose…
Dov Miller
  • 1,958
  • 5
  • 34
  • 46
0
votes
1 answer

Always encrypted mapping in NHibernate

Currently I'm using SQL Server 2016 to make benefit of Always Encrypted feature. There are a couple of columns that I should encrypt. I've encrypted those columns with SQL Server. NHibernate can easily read data from SQL Server but when it tries to…
Ali Bahrami
  • 5,935
  • 3
  • 34
  • 53
0
votes
1 answer

Unable to insert into an always encrypted table using PowerShell

I'm not able to insert into an always encrypted table using PowerShell, the code I'm using is: $serverName = "ServerName" $databaseName = "SecureDB" $connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; Integrated Security =…
A Seyam
  • 347
  • 2
  • 13
0
votes
1 answer

Exclusive client application to Always Encrypted database

SQL Server 2016, encrypted with always encrypted, using certificate. In order for the client application to receive the decrypted data, it is enough to have a certificate installed on the PC, and connection string with ColumnEncryption = Enabled;.…
bmi
  • 652
  • 2
  • 10
  • 17
0
votes
0 answers

EF 6.1.3 takes more time (few minutes) but SSMS 17 executes in a second - SQL Server 2016 (AlwaysEncrypted) with encrypted columns

.NET Core REST API with EF 6.1.3 runs on net452 as targetFramework, takes minutes to execute a Procedure and return the results to model, but with same SP parameters it takes only a second to execute in SSMS 17. Its a SQL Server 2016 DB with few…
se7vanj
  • 160
  • 2
  • 8
0
votes
1 answer

Always Encrypted Certificate for Network Service or AppPool Accounts

I am trying to deploy an Always Encrypted app to Amazon AWS. There is a 2016 RDS database and a Windows Server 2016 hosting the ASP.NET 4.6 app. On the Windows Server, when the application is run under Network Service or the Application Pool…
FirstDivision
  • 1,340
  • 3
  • 17
  • 37
0
votes
1 answer

Configure Always Encrypted for SQL Azure with KeyVault using C#/.NET

I have a working script via https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/configure-always-encrypted-keys-using-powershell (KeyVault + SQL Azure setup). Now I have plain Azure PS (KeyVault creation), reflected…
Christoph Wille
  • 311
  • 3
  • 16
0
votes
1 answer

Not able to apply max() for Encrypted Column on SQL Server

I have a table with a datetime column that was encrypted using Always Encrypted feature on SQL Server 2016. Now I am trying to perform this simple select: select max(dt_order) from orders where customer = 123; I am running into this error: Msg…
Will Silva
  • 1
0
votes
0 answers

Always Encrypted works sometimes on IIS10

First off a bit of background; We have IIS10 running on Windows Server 2016, and we have a SQL db containing columns encrypted with Always Encrypted on a separate SQL server running 2016. The application in question is ASP MVC in C#.NET using a…
AranDG
  • 406
  • 4
  • 16
0
votes
1 answer

How to pass parameters to a stored procedure that returns multiple records

How to pass parameters to a stored procedure that returns multiple records using EF6 - DbContext with SQL Server 2016 stored procedure having encrypted columns? Using EF 6.1.3, SQL Server 2016 - AlwaysEncrypted (deterministic) There are a few…
se7vanj
  • 160
  • 2
  • 8
0
votes
1 answer

Connect to SQL Server 2016 with always encrypted feature

I am trying to use always encrypted column with SQL Server 2016 and RHEL 7. I have created my DSN in /etc/odbc.ini. Info reg odbc.ini is [test] Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.8.0 Description=My Sample ODBC Database…
Sanjay Yadav
  • 1
  • 1
0
votes
1 answer

insert into always encrypted field sqlserver 2016 from php 7.1

I am using PHP, IIS with SQL server 2016 as backend. I am using always encrypted feature. for that I am able to select the data in plain text. but not able to insert the data into the table. I am using the code like this. $queryInsert = "insert into…
Sanjay Yadav
  • 1
  • 1
0
votes
1 answer

MS SQL 2016 PHP Always Encrypted

Might be early days for this - Does anyone have a PHP example of connecting to an MS SQL 2016 database, and retrieving an "Always Encrypted" column, de-crypted in plain text? I can retrieve the binary encrypted columns without problem (PDO or ODBC),…
R Taylor
  • 29
  • 3
1 2 3
…
19 20