16 votes
Accepted

Restoring encrypted database on another server (using Backup Encryption)

Create a brand new master key on your second instance. i.e. don't create it from backup you taken from 1st instance. Then restore certificate from the backup taken and then try. I guess you don't need ...
SQLPRODDBA's user avatar
  • 1,908
16 votes
Accepted

How can I know what hashing algorithm SQL Server used to decrypt the encrypted data when using the function DECRYPTBYPASSPHRASE?

But how does it know what was the algorithm used to encrypt data if there's no argument related to that on the function DECRYPTBYPASSPHRASE? Is it part of the encrypted data? Yes, right on point. I'...
Sean Gallardy's user avatar
15 votes
Accepted

MySQL SSL error: Unable to get private key

Ok, apparently just changing 'BEGIN PRIVATE KEY' to 'BEGIN RSA PRIVATE KEY' isn't enough. It must be properly converted from PKCS#8 to PKCS#1 Reference openssl rsa -in /etc/mysql/ssl/nginx.key -out ~...
Velkan's user avatar
  • 411
12 votes

TDE - Encryption on a large database

We have recently implemented TDE along with AlwaysON in Production running SQL Server 2014. In our application, we noticed a slight (1-3%) increase in CPU utilization. Your environment is different, ...
Kin Shah's user avatar
  • 62.2k
10 votes
Accepted

Combining Always Encrypted AND Column level encryption in SQL Server 2016

I have yet to come across a definitive "Best Practices" list regarding multiple encryption types for SQL Server, but in your situation I would probably recommend the following: Encrypt necessary data ...
LowlyDBA - John M's user avatar
9 votes

How encryption in SQL login authentication works

how are the password encrypted and how does sql server decrypt it? What kind of encryption is involved in it? Super high level, if you have encryption setup for the instance then the certificate that ...
Sean Gallardy's user avatar
9 votes
Accepted

Always backup database with encryption

Not in SQL Server There is no option to force BACKUP DATABASE & BACKUP LOG to specify WITH ENCRYPTION... as part of backup commands in SQL Server. But if you want data in backups encrypted... If ...
AMtwo's user avatar
  • 16.1k
8 votes
Accepted

How do I check that the database master key encryption is valid?

In order to programmatically determine if the current SMK was used to protect the DMK, you should be able to simply attempt an operation that would require the DMK. Such an operation would need to ...
Solomon Rutzky's user avatar
8 votes

Is network traffic encrypted when writing remote backups using SQL Server TDE?

Yes, backups are encrypted while moving over the network because TDE data is encrypted on disk, and the backup operation never decrypts it. Paul Randal's Backup Myths: Myth 30-09) backups read data ...
BradC's user avatar
  • 9,964
8 votes

Compile TSQL stored procedures into assembly

No, there is no way to do this. And there is not much purpose in doing so anyway as you can easily decompile Assemblies with tools such as ILSpy. The two ways to protect your intellectual property ...
Solomon Rutzky's user avatar
8 votes
Accepted

SQL Server SSL Encryption - SelfSign Cert working - why?

Traffic will be encrypted regardless of the client encryption specification when you turn on the Force Encryption option on the server side (SSCM-->SQL Server Network Configuration-->Protocols for ...
Dan Guzman's user avatar
  • 28.2k
7 votes

Crytography in PostgreSQL

If you want to validate your user's password, one of the best algorithms to use is bcrypt. I'd recommend you implement the encryption and validation at your application layer, so you work with plain ...
MatheusOl's user avatar
  • 2,519
7 votes

Restoring encrypted database on another server (using Backup Encryption)

I can't comment but I'd like to add a bit of detail to this answer. Database master key passwords do not need to match between instances When you backed up the certificate you added a private key ...
marclancy's user avatar
  • 106
7 votes
Accepted

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

The major difference I know is Normal Column Encryption The normal Column encryption which was introduced from SQL Server 2005 uses function Encryptbycert to encrypt the columns. It is not as ...
Shanky's user avatar
  • 19k
7 votes

What is an "Obfuscation key" in SQL Server?

I would guess they mean rows created for symmetric keys. create symmetric key KeyTest with algorithm = aes_128, identity_value = '123456' encryption by password = '123456!Q'; go -- Needs a ...
Mikael Eriksson's user avatar
7 votes
Accepted

How to change SQL Server master key password?

According to the Microsoft Docs page, syntax is: ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'password'; From that docs page: The REGENERATE option re-creates the database master key ...
Hannah Vernon's user avatar
  • 70k
7 votes
Accepted

Why does SQL Server require a private key to encrypt a backup?

I just don't get why the private key is needed during the backup operation. Short Answer That's how it's currently implemented. Longer Answer I didn't look at all the dependencies to see if it ...
Sean Gallardy's user avatar
7 votes
Accepted

How to decrypt zipped data in MSSQL table

The data is not encrypted; it's simply compressed using Deflate, which isn't directly available in TSQL. TSQL COMPRESS uses Gzip, which is similar, but not strictly compatible. So in C# something ...
David Browne - Microsoft's user avatar
6 votes

SQL server service won’t start after disabling TLS 1.0 and SSL 3.0

As of January 29th, Microsoft SQL Server supports TLS 1.2 for SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 and SQL Server 2014 and major client drivers like Server Native Client, Microsoft ...
Amit Banerjee's user avatar
6 votes

Query to List Encryption Certificate for Databases

For a more in-depth query that shows which databases are encrypted or not, their certificate and IMPORTANTLY if the encryption setup has actually completed or not. The encryption can sometimes take a ...
Jhunter1's user avatar
  • 165
6 votes
Accepted

Error: "The certificate cannot be dropped because one or more entities are either signed or encrypted using it."

To find items associated with Certificates and Asymmetric Keys, first try the query posted in this DBA.SE Answer: Find signed procedures, functions, triggers, assemblies and by which certificates / ...
Solomon Rutzky's user avatar
6 votes

Always On Availability Groups with Encrypted Connections

First for an AG you need to Force Protocol Encryption on each instance participating in the AG. With an FCI there is only one instance. Also with an AG clients can connect to the AG Listener, and ...
David Browne - Microsoft's user avatar
6 votes

Can anyone recommend an encryption method for ALL user dbs using SQL Server 2016 STANDARD edition?

There are 3rd party tools which replicate TDE functionality. I have tested the following during an exercise to evaluate whether we can move from Enterprise to Standard Edition: https://www.database-...
TimBrown's user avatar
  • 324
6 votes
Accepted

Issue Restoring Certificate to SQL Server When Different Service Account Used

You need to fix the NTFS permissions on the certificate. By default SQL, when it creates the backup will set the NTFS permissions so that only the account running the SQL Service can read the ...
mrdenny's user avatar
  • 27k
5 votes

Why use asymmetric keys in SQL Server?

The proper key hierarchy is the Service Master Key protects the Database Master Key, the Database Master Key protects either an asymmetric key or certificate, the asymmetric key or certificate can be ...
Michael Keleher's user avatar
5 votes

Options for Data Encryption in SQL Server 2008 R2 Standard Edition?

Old question, I understand, but an answer in addition to the above answers exists since SQL Server 2016 SP1. SQL Server 2016 introduced "Always Encrypted" as well as other security features like ...
Mike Walsh's user avatar
  • 18.2k
5 votes

TDE prep: key/certificate backup for restores

If you want to restore on a different server, you should be able to do so with the certificate, private key and database backup file(s). When a certificate is created in any database in SQL Server, ...
Michael Keleher's user avatar
5 votes
Accepted

searching against decrypted column

What you are trying to do won't work because pgcrypto's decrypt function is not going to receive the KMS key you encrypted the data with. Essentially, when you set up KMS it generates a key pair ...
Larry's user avatar
  • 377
5 votes
Accepted

SQL AES_256 Encryption and Decryption

Try defining the length of the varbinary in your convert statement as varbinary(max). Per https://technet.microsoft.com/en-us/library/ms188362(v=sql.110).aspx it would default to 30. You can ...
Nic's user avatar
  • 4,013
5 votes
Accepted

SQL Server End to End Encryption Options

Always Encrypted feature in SQL server, which is a client side encryption technology designed to provided end to end encryption, meaning it will encrypt the data in the driver of your client ...
Nikhil Vithlani - Microsoft's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible