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
2
votes
2 answers

Parameterization(Always Encrypted)- Inside stored proc

I have a scenario where i need to have literals(hard coded strings) inside proc used against "Always Encrypted" columns, Since this fails with the following error, Operand type clash: varchar is incompatible with nvarchar(20) encrypted with…
Samuel A C
  • 406
  • 3
  • 16
2
votes
1 answer

Using Always Encrypted of SQL Server from .NET Core 2.1

I am aware that Microsoft has not specified any plan to support Always Encrypted from within Core, yet. However, this is supported by classical .NET Framework (4.5 onward). Our Core 2.1 project's usage of Always Encrypted is limited to two simple…
Adam
  • 3,872
  • 6
  • 36
  • 66
2
votes
1 answer

Why SQL Server stored procedure selecting encrypted data into variable fails

Stored procedure that selects data from encrypted column (Always Encrypted) into variable fails with an error Cannot continue the execution because the session is in the kill state if XACT_ABORT is set to on. Removing SET XACT_ABORT ON; line makes…
Maxim
  • 23
  • 3
2
votes
1 answer

SQL Server SSL + TDE vs Always Encrypted

What is the difference between using SQL Server SSL (Encrypted=true in the connection string) + TDE, vs using SQL Server Always Encrypted? With regards to RGPD, is one more adapted than the other?
DotNetMatt
  • 648
  • 2
  • 9
  • 26
2
votes
0 answers

Operand type clash: nvarchar(max) is incompatible with nvarchar(max)

Hi I am getting a following error any idea how can i solve this Operand type clash: nvarchar(max) is incompatible with nvarchar(max) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name =…
dev88
  • 37
  • 6
2
votes
1 answer

Operand type clash issue with Always Encrypted with Asp.net Identity

We are trying to use Always Encrypted feature of Sql 2016 with Asp.net Identity 2.0. We have set Always Encrpted on PhoneNumber and Email columns. We are able to encrypt phone number but the register method in Account Controller fails when we try to…
TerryV
  • 56
  • 6
2
votes
1 answer

How to search by multiple values in "Always Encrypted" column?

If I am using deterministic encryption I am able to perform searches in encrypted column. For example: DECLARE @email NVARCHAR(222) = 'test'; SELECT * FROM Users Where email = @email; But how to perform searches by multiple values? For example, if…
gotqn
  • 42,737
  • 46
  • 157
  • 243
2
votes
2 answers

EF Core decimal precision for Always Encrypted column

Hello I have SQL server with setting up always encrypted feature, also I setup EF for work with always encrypted columns, but when I try to add/update, for Db manipulation I use DbContext, entry in my Db I get follow error: Operand type clash:…
3axap
  • 321
  • 2
  • 15
2
votes
3 answers

Using always encrypted on a entity framework [code first] database

I have an MVC application that uses entity framework / code first. I'm trying to set up always encrypted in order to encrypt a column (social security number / SSN). I'm running everything in Azure, including using Azure vault to store keys. I have…
Jeff Davies
  • 21
  • 1
  • 3
2
votes
1 answer

Using SQL Server Always Encrypted with classic ASP

We have a business application that was written a long time ago using classic ASP. Now we want to utilize the Always Encrypted feature of SQL 2016. From this article I saw that my only option is to use Microsoft ODBC driver 13.1, however that is not…
Martin Rousev
  • 33
  • 6
2
votes
1 answer

SQL Server Always encrypt collation incompatibility on insert

I have a Windows Forms application that has been working for a long time as is and now I have to encrypt/decrypt some columns in the database. I made all the configurations on my database, configured my columns to be encrypted, changed column's…
Dimitris Laliotis
  • 83
  • 10
2
votes
1 answer

How to create Always Encrypted column encryption key from c# client

I see that the various articles on SQL Server 2016's Always Encrypted feature describe creating the column encryption key using either SQL Server Management Studio UI or by using the New-​Sql​Column​Encryption​Key powershell command. But what if the…
explunit
  • 18,967
  • 6
  • 69
  • 94
2
votes
1 answer

SQL Server always encrypted with Dynamic SQL

I am using SQL Server always encrypted on feature for SSN column. I have created a stored procedure and calling it from .net code. I am using column encryption setting=true in my connection string. Calling the dynamic stored procedure from the .net…
Naz_S
  • 21
  • 3
2
votes
1 answer

How to perform "Order by" on an encrypted column (deterministic encryption - SQL 2016)

How to perform "Order by" on an encrypted column (deterministic encryption - SQL Server 2016) ? I am getting error when executed on SSMS 2017 (with required settings for AE) SELECT * FROM [dbo].[X] ORDER BY lastName The lastName column is defined…
se7vanj
  • 160
  • 2
  • 8
2
votes
1 answer

Use Always Encrypted on decimal(18,2)

I am using a new SQL Server 2016 feature - Always Encrypted. It is very cool and simple technology - but not for decimal type. When I insert a value, I am getting errors like this one (ZP_Test - my DB name): Operand type clash: decimal(4,2)…
Артем Самохвалов
  • 23
  • 3
1 2 3
…
19 20