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
16
votes
4 answers

SQL Server Always Encrypted with .NET Core not compatible

I'm trying to use the Always Encrypted feature of SQL Server 2016 with .NET Core and seems like it can not be used (yet). Trying to import the Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider from Nuget, I get an error stating…
Los Morales
  • 2,061
  • 7
  • 26
  • 42
12
votes
1 answer

Always encrypted with Entity Framework and Includes fails to materialise query

I am having issues getting always encrypt to play nice with Entity Framework. I am targetting .Net 4.6.1, have enabled Column Encryption Setting=Enabled in my connection string and i can successfully make a call and receive the decrypted content…
Lukie
  • 905
  • 1
  • 11
  • 21
12
votes
5 answers

Column encryption in ASP MVC app with SQL Server 2016 using .net Core / EF Core

I am trying to use the "Always Encrypted" feature in SQL Server 2016 to encrypt some columns. I used this post as a guide to set the columns as encrypted in SSDT. That part goes fine, it's when I attempt to query the data from the application that I…
Brian
  • 2,318
  • 3
  • 19
  • 20
9
votes
2 answers

Always Encrypted in Entity Framework Core Support

As of EF Core 2.1, Always Encrypted SQL Server feature is not yet supported. I am using both SQL Server 2016 and Azure SQL and all clients are Windows-based. Can we use ADO.NET to achieve this while waiting for the feature to be implemented? Is…
Adam
  • 3,872
  • 6
  • 36
  • 66
8
votes
1 answer

"Value cannot be null. Parameter name: reportedElement" when adding a new Always Encrypted column to an existing table

Using Visual Studio database projects (SSDT) I added a new column to an existing table. I am using Always Encrypted to encrypt individual columns. When I add the column and try to publish, I get a popup in Visual Studio that says "Value cannot be…
Jonathan
  • 320
  • 1
  • 10
8
votes
5 answers

Always Encryption: Failed to decrypt a column encryption key using key store provider: 'MSSQL_CERTIFICATE_STORE'

I am performing Always encryption in my web application, Which insert data by a WCF service which is hosted by a window service. I am getting following error while insertion of data into encrypted column. Failed to decrypt a column encryption key…
user2640965
  • 83
  • 1
  • 1
  • 6
8
votes
4 answers

Where does one place the Always Encrypted Certificate on an IIS 7.5 web server?

We have a SQL Server 2016 database that employs Always Encrypted. Our recently published ASP.net web site attempts to pull data from this database, and when it does we get this error: Error: Failed to decrypt column 'EnSSd'. Failed to decrypt a…
RoastBeast
  • 1,059
  • 2
  • 22
  • 38
7
votes
1 answer

Always Encrypted, LINQ, and Where Contains

Typically, one would do this to return a data set that matches a list: string[] ssn = { "123456789", "987654321" }; var result_set = db.employee.Where(w => ssn.Contains(w.SSN)).ToList(); However, when the SSN column is encrypted via Always…
John Chase
  • 328
  • 3
  • 10
6
votes
0 answers

Is there an "Always Encrypted" equivalent encryption implementation/extension available in postgresql which could work with .net core 3.1?

I have checked the implementation of the pgcrypto extension. It doesn't automatically encrypt and decrypt data, using EF Core and requires separate functions. Whereas, the "Always Encrypted" feature of SQL Server does it transparently without much…
Kinjal Munot
  • 61
  • 1
6
votes
2 answers

SQL Server 2016: Hide column data from DBAs but specific users can view data through application

I'm trying to enable access to team leaders of salary information through PowerBI, but encrypt this data from other users and the DBAs. Users denied access to this column data should still be able to execute the query but only see encrypted…
Mako
  • 83
  • 6
5
votes
1 answer

SSIS: Error from ODBC provider when inserting into Always Encrypted table using CMK in Azure Key Store

I'm trying to use SSIS (Visual Studio 2017) to insert data into an Azure SQL table with a column that's encrypted via Always Encrypted, and a column master key stored in Azure Key Vault, but I keep getting errors. Per advice I've found in other…
jessewdouglas
  • 51
  • 3
5
votes
1 answer

Always Encrypted : Certificate with thumbprint not found in certificate store

I have a mvc application that uses sql express 2016. I have follow all the necessary steps to configure always encrypted to one of my columns in the table. And install Certificate to my machine for testing purpose. All Works Fine in my local…
Shekhar Patel
  • 641
  • 10
  • 20
5
votes
1 answer

Visual Studio Code SQL Server connection to encrypted database with Azure Key Vault (Always Encrypted)

I'm trying to get a connection from VSCode on macOS to a SQL Server database that uses always encrypted mechanism to protect some of the columns. The master key is stored in an Azure Key Vault. Using the always encrypted guide provided by Microsoft…
mschmieder
  • 73
  • 8
5
votes
1 answer

How to insert data using stored procedure after encrypted column using Always Encrypt option?

I am using sql server 2016 and i have encrypted the column using always encryption option. I have connected sql with entity frameworking in ASP.NET MVC application. The dats are inserting properly using insert option. But i cant able to insert using…
Melody
  • 1,203
  • 2
  • 17
  • 28
5
votes
0 answers

How to decrypt(offline) value that is protected by SQL Server AEAD_AES_256_CBC_HMAC_SHA_256

It is possible to manually decrypt value that is encrypted by sql server always encrypted mechanizm? I mean how to decrypt column value that is encrypted by AEAD_AES_256_CBC_HMAC_SHA_256 algorithm. I have encryption key(in other topic I was asking…
sglogowski
  • 321
  • 3
  • 12
1
2 3
…
19 20