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.
70
questions
0
votes
1
answer
22
views
How to migrate a database from 2016 to 2014 if there are encrypted/locked objects(SPs) associated with it?
I am trying to migrate a database from 2016 to 2014 through Task-->Generate Scripts. There are some stored procedures that are locked and seems like they are encrypted as well. They are causing ...
0
votes
1
answer
93
views
SQL Always Encrypted- can be decrypted in sql function?
I turned on Always Encrypted in sql 2016 and was able to see the column to be crypted. My question is this: can I decrypt this column in sql function without "column encryption setting = Enabled&...
- 3
0
votes
2
answers
35
views
Always Encrypt C++
Our client application is developed in C++ and uses ODBC Driver (Version 17) to connect to remote SQL database. We have enabled Always Encrypt option to encrypt the data. Using proper flags in ...
- 41
0
votes
0
answers
38
views
Error from Recordset class
We have some fields of type nchar to support Unicode characters.
Always encrypt is enabled for the table.
Inserting data works fine but when I try to retrieve data, the below error is thrown from ...
- 41
0
votes
0
answers
32
views
"Invalid object name" While Encrypting Columns
Using SSMS with a SQL Server 2019 instance. While trying to encrypt some of the more sensitive columns in a table, I am getting an error:
Error SQL72014: .Net SqlClient Data Provider: Msg 208, Level ...
- 173
0
votes
0
answers
58
views
Always Encrypt - C++ SQL_COLUMN_ENCRYPTION_ENABLE
I am trying to set up my C++ client code to support Always Encrypted feature.
Using the SQLSetConnectAttr API, I plan to set the flags for the same as shown below:
retcode = SQLSetConnectAttr(hdbc, ...
- 41
-1
votes
2
answers
323
views
Always Encrypted SQL Server : How to insert / updated using C++ code?
I have set the Always Encrypted option to my table columns.
Now I need to insert or update my table from my C++ Client application.
I know that we need to declare params and use them for Always ...
- 41
0
votes
0
answers
99
views
how to fix SQL Server Bulk Copy SQLServerException Source and destination schemas do not match
I have a springboot - Spark application that will inject a dataFrame into an SQL Server DB, the app was working fine before we decide to enable Alaway Encrypted for a couple sensible columns. Just ...
- 101
2
votes
3
answers
1k
views
SQL Server Management Studio throws exception when using Always Encrypted
With the latest version of SSMS (v18.10), when we choose to sign in to Azure to use key vault, it always gives this exception:
Rotate (Microsoft.SqlServer.Management.ServiceManagement)
And the error ...
- 121
2
votes
1
answer
866
views
Error while querying 'Always encrypted' column
I have set up 'Always encrypted' on one of the column in Azure SQL DB. Everything worked well except when a user is trying to access he is getting below error. '
I have tried some of the rememdies as ...
- 31
1
vote
0
answers
181
views
Error when trying to apply Always Encrypted encryption on aspnet_Membership table
I'm trying to encrypt the Email and LoweredEmail columns on a SQL database using Always Encrypted encryption using SSMS and I always get the same error:
Lock request time out period exceeded. There ...
- 11
0
votes
2
answers
753
views
Backup and restore SQL Server database with encrypted columns: what should I backup along with database?
Assume I've backed up a SQL Server database with Management Studio, and that database have some encrypted columns. Now I want to restore this database into another fresh-enrolled MSSQL server. To use ...
- 101
3
votes
1
answer
2k
views
Always Encrypted: How do I do an equality join with a non-encrypted column to a deterministic encrypted column?
The official Microsoft documentation for Always Encrypted on SQL Server 2017 states:
Deterministic encryption always generates the same encrypted value for any given plain text value.
Using ...
0
votes
1
answer
565
views
How do we restore database configured with Always Encrypted between environments and enable web app to encrypt/decrypt data?
I have a SQL Server 2016 database configured with AE in Production environment. Column Master key Certificate is available(under Windows Certificate Store -> Local Machine location) in a different ...
- 393
0
votes
3
answers
616
views
Always Encrypted on Sql 2017 Express Edition
I am so tentative to decide to use Always Encrypted tool, however I have a question → I'm working on a project with SQL Server 2017 - 14 Express Edition.
And I'm pretty sure they aren't going to buy ...
- 103
1
vote
1
answer
332
views
Always Encrypted Data Validation [closed]
Just a question in how could i validate duplicate data's in my database if columns is set to be encrypted(deterministic) in db?
I've tried running the following code, but receive the error below.
...
- 29
0
votes
1
answer
128
views
How to Configure Always Encrypted using PowerShell
I just want to ask if there is a way to configure always encrypted columns using power shell? like for example i want to encrypt a column in a table with deterministic using powershell codes?
- 29
0
votes
1
answer
291
views
Database backup and restore incase Always Encrypted feature is enabled
I would like to know that How SQL database backup and its restoration on other server will work if Always Encrypted feature is implemented on most of its tables?
Thanks
- 65
1
vote
1
answer
39
views
Does AE certificate include both master and encryption keys together?
After setting up Always Encrypted, I can see a certificate in my Current User
when selecting
select * from sys.column_master_keys
under the key_path I see this location, but there is no location ...
- 2,098
0
votes
1
answer
309
views
Domain Certificate Authority Generated Certificate and SQL Server - Keyset does not exist
I do not have much experience with certificates or certificate terminology, so I may be incorrect in how I title things.
I am trying to use a domain issued certificate as the certificate used in the ...
- 169
1
vote
1
answer
132
views
SQL Server Security - Encryption [closed]
I have requirement to store the some sensitive information in SQL Server 2016. Organization policy demands, it should be encrypted.
Business Requirement:
Data cannot be decrypted by DBA.
Data cannot ...
- 2,194
1
vote
1
answer
1k
views
Why does setting my column as Always Encrypted change its collation from SQL_Latin1_General_CP1_CI_AS to Latin1_General_BIN2?
I am encrypting a VARCHAR(50) NULL column as randomized. When doing this, the collation is changed from SQL_Latin1_General_CP1_CI_AS to Latin1_General_BIN2.
I cannot find any resources explaining why ...
- 169
1
vote
0
answers
617
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 ...
2
votes
1
answer
609
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 ...
- 99
0
votes
0
answers
430
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 ...
- 29
0
votes
1
answer
247
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 ...
- 3
0
votes
0
answers
359
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 ...
- 21
2
votes
1
answer
9k
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' ...
0
votes
1
answer
71
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 ...
- 3,018
0
votes
1
answer
87
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 ...
- 2,194
0
votes
1
answer
173
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 ...
1
vote
0
answers
516
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 ...
- 173
0
votes
1
answer
184
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 "...
- 111
0
votes
1
answer
336
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 ...
- 33
1
vote
0
answers
507
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://learn.microsoft....
- 393
0
votes
1
answer
80
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-...
- 393
4
votes
1
answer
16k
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 = ...
- 367
1
vote
0
answers
49
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 ...
- 173
0
votes
1
answer
921
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 ...
- 101
-2
votes
1
answer
1k
views
Type clash on update of always encrypted varchar in ssms 2017
I am using SSMS 17.6 and I am trying out the Always Encrypted feature.
I was able to update/insert/search a datetime field by declaring a parameter and then using the parameter, e.g.:
DECLARE @...
- 99
0
votes
1
answer
400
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 ...
2
votes
1
answer
113
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 ...
- 15.6k
0
votes
1
answer
556
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 ...
- 393
1
vote
1
answer
323
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.
- 11
1
vote
1
answer
3k
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 ...
- 393
2
votes
1
answer
1k
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 ...
- 393
-1
votes
1
answer
123
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)...
- 3,721
2
votes
1
answer
1k
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.
...
- 3,721
2
votes
1
answer
2k
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 "...
- 3,721
1
vote
2
answers
610
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 ...
- 113