Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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, in server memory, or at rest.

39
votes
2answers
18k views

In SQL Server 2016, what is the difference between Always Encrypted and Transparent Data Encryption?

As I write this I am still awaiting the official release of SQL Server 2016 so that we can explore the usefulness of its "Always Encrypted" feature. I would just like to know what the specific ...
7
votes
1answer
1k views

Combining Always Encrypted AND Column level encryption in SQL Server 2016

We have requirements to encrypt sensitive column data using SQL Server 2016 and selected the Always Encrypted(AE) feature to encrypt those columns using deterministic approach.. Since, the AE ...
6
votes
2answers
662 views

Can SQL Server Always Encrypted be used for temporary tables?

I'm trying to determine if I can use Always Encrypted on a column in a temporary table (SQL Server 2016 SP1 Enterprise on Windows 2016). I've tried as follows: CREATE TABLE #listofdates (compare_dt ...
6
votes
1answer
322 views

Always Encrypt and SSIS - Security Risk

We have a database using the AlwaysEncrypted feature of SQL Server 2016. We need to create SSIS packages (deployed on SSISDB) for certain application tasks. What options we have to store the key ...
4
votes
1answer
392 views

Do signed certificates from public certificate authorities add any value for always encrypted?

If I create Always Encrypted columns in Microsoft SQL Server from the SSMS gui, it makes a self signed certificate. Is there any value in making a CSR and paying a certificate authority (or using ...
4
votes
1answer
2k views

Is it possible to bulk insert data into a table that has columns encrypted with Always Encrypted?

In SSMS we are attempting to bulk insert from a csv file into a table that has a column encrypted using SQL Server 2016's Always Encrypted feature. This is the command we're using: INSERT INTO ...
3
votes
2answers
1k views

Encryption of Data, Log and Backup files using Always Encrypted in SQL Server 2016

When we implement the Always Encrypted feature in SQL Server 2016, do the .mdf files, .ldf files and .bak files get encrypted as well? I would like to know if the data/log/backup files gets encrypted ...
3
votes
2answers
592 views

Always Encrypted - Out of Memory Exception when attempting to encrypt a column in a table

Using Always Encrypted we are attempting to encrypt one column of type varchar(50) in a table that contains 5.5 million rows with the deterministic encryption. After a while the wizard returns failure ...
3
votes
1answer
2k views

What are the advantages of Always Encrypted 2016 vs Encrypted Columns?

I was reading about SQL Encryption methods, I have a good understanding of TDE, then I read about Encrypted Columns and then I read about Always Encrypted. The description of Always Encrypted is very ...
3
votes
1answer
599 views

Clarification on SQL Server 2016 Always Encrypted

We are planning out a move from SQL Server 2008 to SQL Server 2016. One of the key requirements for us is to have encryption at rest at a minimum. We have been considering SQL Server Enterprise as ...
2
votes
1answer
1k views

How to renew or extend the CMK Certificate in Always Encrypted feature?

I have implemented Always Encrypted feature in SQL Server 2016. The CMK is valid for one year. How do I renew it, or extend the validity?
2
votes
1answer
38 views

Always Encrypted column validation

How can I validate a SSN column that is encrypted in a table using AE without passing one by one value as a parameter? I have to validate if the SSN is formatted properly and numbers and '-' etc. I ...
2
votes
1answer
3k views

Always Encrypted: How to insert or update encrypted columns?

I have configured 'Always Encrypted' on one of the columns of a table of my SQL Server database. I am able to select and view data from client SSMS after passing the 'Column Encryption Setting = ...
2
votes
1answer
714 views

Exporting certificate without private key

I am using the following PowerShell code to create a certificate that can be used as Column Master Key for Always Encrypted feature: New-SelfSignedCertificate -Subject "...
2
votes
2answers
792 views

What is the MySQL equivalent of SQL Server 2016 Always Encrypted technology?

What is the MySQL equivalent of SQL Server 2016 Always Encrypted technology or is there anything similar in MySQL that can act as AE?
2
votes
1answer
56 views

how to encrypt one column of a table without using always encrypted?

I have been working on a solution to synchronise logins (using T-SQL) between 2 servers, or between AlwaysOn nodes, inspired by sqlsoldier. It requires a linked Server. When run, this procedure ...
2
votes
1answer
460 views

SQL 2016 Always Encrypted, options appear in one environment, not the other

We have two different environments that are replicas of each other - DEV/STG and PROD. We recently upgraded both environments to SQL Server 2016 using the same upgrade process (we're on AWS, so it ...
2
votes
1answer
568 views

How do I generate the ENCRYPTED_VALUE for CREATE COLUMN ENCRYPTION KEY?

I can make a certificate in the windows certificate store for the master column key easily enough with: $cert = New-SelfSignedCertificate ` -Subject "Always Encrypted Sample Cert" ` -...
2
votes
1answer
586 views

How to create Always Encrypted Certificate for specific time period?

I have configured Always Encrypted on a custom database using SQL Server 2016. By default, it creates the certificate with a validity of one year. Is there any workaround to create this certificate ...
2
votes
1answer
306 views

sp_refresh_parameter_encryption fails to refresh objects

I am testing always encrypted feature and once the operation of encrypting column is done I have a file with hundreds objects failed to be refresh by the sp_refresh_parameter_encryption procedure. ...
1
vote
1answer
202 views

AlwaysEncrypted error when inserting via stored proc

lets start with the error first: Encryption scheme mismatch for columns/variables . The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '3' ...
1
vote
1answer
56 views

Always encrypted on Temporal tables

Is it now possible to apply Always encrypted to some columns on existing Azure SQL database temporal tables with existing data ? I know it was not possible a few months ago thru SSMS / powershell.
1
vote
1answer
258 views

Error 71501, 71561 while encrypting columns using always encrypted feature of SQL Server 2016

1) I am trying to encrypt some varchar columns which are not identity / primary using the always encrypted feature of SQL Server 2016, but getting an error code 71501,71561 for unresolved and / or ...
1
vote
1answer
639 views

Always Encrypted - certificate cannot be used as a column master key

I want to create a new column master key using certificate created in advanced. I use the following command to create a certificate in the Windows Certificate Store - Local Machine. New-...
1
vote
1answer
425 views

AlwaysEncrypted Conversion Fails

I'm trying to encrypt a column with AlwaysEncrypted and I get the following error. Could not deploy package. Warning SQL72012: The object [AlwaysEncryptedDemo] exists in the target, but it ...
1
vote
0answers
100 views

Case insensitive search on Always Encrypted columns

How would one go about doing a case-insensitive search on a column encrypted with Always Encrypted? The problem arises when you encrypt email addresses for example. When encrypted, John.Doe@gmail.com ...
1
vote
0answers
259 views

How to create encryption certificate using PowerShell in Windows Server 2012 R2?

I'm implementing the Always Encrypted feature in SQL Server 2016. I have tried to create the certificate using PowerShell in Windows 10 environment as per the below article. https://docs.microsoft....
1
vote
0answers
27 views

Always Encrypted 'where' on multiple values

I understand why SQL won't allow this, I want to know if there is some way around this that will allow me to do a where on an encrypted column with multiple values? I have tried to use a TVP, but SQL ...
1
vote
1answer
916 views

Is It Possible to Encrypt varbinary(max) Column Using Always Encrypted Feature?

We want to implement the Always Encrypted feature for our application database. There is a table with a varbinary(max) column which is storing binary data of images, pdf documents, etc. Does the ...
1
vote
1answer
244 views

Error while creating CMK(column master key) in always encrypted

I am trying to explore SQL 2016 always encrypted feature but i am getting an error while generating new column master key, after entering name and selecting 'Windows Certificate Store-Current User' as ...
1
vote
2answers
236 views

Securing Always Encrypted SQL Server from “sa”

I'm currently studying SQL Server Always Encrypted feature. How can I disable column decryption to "sa" if all they have to do is add Column Encryption Setting=enabled in the login's additional login ...
0
votes
1answer
60 views

Microsoft Key Storage Provider for Always Encrypted Master Key

Hoping someone can help- I've been experimenting with Always Encrypted columns and have generated a certificate in the "Microsoft Software Key Storage Provider" - How can I remove the "Always-...
0
votes
1answer
61 views

Restoring encrypted DB to SQL vNext

I wondered if anyone else may have tried restoring a copy of an encrypted database to an instance of SQL vNext. The instance I'm running has been installed on a CentOS box, but I'm not having any luck ...
0
votes
1answer
30 views

Does the Always Encrypted certificates get copied over in availability groups?

We have a column set up with Always Encrypted, and we are adding it to an AG. We'd like for the encryption to keep working if/when we have to fail over to a secondary server. Will the Availability ...
0
votes
1answer
118 views

Replicate encrypted data from Sybase ASE 15.5 to Sybase ASE 15.5

Recently I'm studying on how the replication of encrypted data works from Sybase ASE to Sybase ASE, and I have few queries on below:- What will be the datatype for columns that contain encrypted data ...
0
votes
1answer
230 views

Always Encrypted Certificate clarification

I have configured AE using SSMS on the database server installed with SQL Server 2016. The certificate created under Local Machine option displays the validity as one year. I just want to know what ...
0
votes
0answers
4 views

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 ...
0
votes
0answers
20 views

Azure Database Encryption (ERROR ON WEB APP)

Currently I have an azure subscription which has a server and database. I already encrypt a column in a table. i am using "always encrypted" method. Now I want to generate a .pfx file after I ...
0
votes
0answers
13 views

Azure sql database backup and exporting data-tier application

Currently i have an azure subscription in it has a server and a database. How could i setup azure SQL database backups that will run automatically for example every 15 mins? is there a way? if yes ...
0
votes
1answer
42 views

SQL Server Always Encrypted - Create an external table containing an encrypted column

I'm trying to create an external table MyTable on server S2 that references the table MyTable from server S1. The table MyTable contains a column encrypted using Always Encrypted. I'd like the column ...
0
votes
0answers
33 views

Any way to get a key stored in the Azure Key Vault when retrieving Always Encrypted data through SSRS?

If you are using Always Encrypted and the key is stored in the Azure key vault, is there any way to use on-prem SSRS to view the encrypted data? Obviously with the client application this can be coded....
0
votes
0answers
45 views

Always Encrypted returning Timeout error

I am trying to encrypt a number of tables (1 column in multiple tables) in two different databases. On Database A I manage to encrypt these, while on database B it fails with an error: 'Unable to ...
0
votes
1answer
45 views

Data Security in MS SQL Server

How do I store the sensitive information on SQL server?. We have policy guidelines, DBA and Development engineers are not allowed to view the data as plain text. I am aware of the encryption ...
0
votes
1answer
128 views

Is it possible to store Always Encrypted master key an on-premises key vault? [closed]

I want to store the master key in a centralize key vault and have IIS access the key from there. I know I can use Azure Key Vault or local Windows key store, but I was looking for another option to ...
0
votes
0answers
161 views

When to run sp_refresh_parameter_encryption while using Always Encrypted?

I'm implementing Always Encrypted feature for an ASP.NET Application using SQL Server 2016. I have applied deterministic encryption on first five tables and created few stored procedures that ...
0
votes
1answer
43 views

How to remove an installed AE certificate from server using PowerShell Scripts?

I have created the encryption certificate using below PowerShell script in dev. environment. Now, I need to create deployment document to configure the encryption on staging environment. $cert = New-...
0
votes
1answer
195 views

Always Encrypted - Lock request time out period exceeded

I'm trying to encrypt a column and am getting the following error: Set-SqlColumnEncryption : Lock request time out period exceeded. Are there parameters to specify the lock timeout? What could be ...
0
votes
1answer
210 views

Always Encrypted after restoring an old database backup using C#

I have a Windows Form application. It allows users to restore a SQL Express database. Latest version of SQL Server in app now is SQL Server 2017. Customer can restore an old backup of a SQL Server ...
0
votes
1answer
70 views

Does Always Encrypted cause blocking queries or locking object?

I have perform some test with relatively small tables and the encryption took a very long time (maybe because there are many SQL objects that are referring the target table). I am wondering(worrying)...
0
votes
2answers
398 views

How to rotate the CMK of Always Encrypted using SSMS?

I have implemented the encryption in SQL Server 2016 using Always Encrypted. I want to know how to rotate the column master key after the initial one year validity using SQL Server Management Studio ...