There's only one instance in an FCI, so only one SMK. When the instance restarts on another node, it uses the same service account, and so is able to decrypt the SMK. There's an informative thread https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9514616d-173f-40df-9dee-fef6de82b273/cluster-failover-forced-service-master-key-regeneration-consequences?forum=sqlsecurity with answers from https://www.linkedin.com/in/laurentiu-cristofor/ from the SQL Server security dev team.

How can I test whether a service account can access the SMK or not? Try this to open and close the master key. USE GO OPEN MASTER KEY DECRYPTION BY PASSWORD = '' GO CLOSE MASTER KEY GO If you would like to re-encrypt your master key with new SMK. Then you could do this, USE GO OPEN MASTER KEY DECRYPTION BY PASSWORD = '' GO ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY GO CLOSE MASTER KEY GO

The data is not encrypted; it's simply compressed using https://en.wikipedia.org/wiki/Deflate , which isn't directly available in TSQL. TSQL https://docs.microsoft.com/en-us/sql/t-sql/functions/compress-transact-sql?view=sql-server-ver15 uses Gzip, which is similar, but not strictly compatible. So in C# something like: using System.Data.SqlClient; using System.IO; using System.IO.Compression; using var con = new SqlConnection("server=.;database=spmcopy;integrated security=true;multipleactiveresultsets=true"); con.Open(); var cmd = new SqlCommand("select counter, cast(cast([text] as varchar(max)) as varbinary(max)) ZippedText from notescopy where Zipped=1 ", con); var cmdWrite = new SqlCommand("update notescopy set [text] = cast(cast(@t as varchar(max)) as text), Zipped = 0 where counter = @c",con); var ptext = cmdWrite.Parameters.Add(new SqlParameter("@t", System.Data.SqlDbType.VarBinary, -1)); var pCounter = cmdWrite.Parameters.Add(new SqlParameter("@c", System.Data.SqlDbType.Int)); using var reader = cmd.ExecuteReader(); while (reader.Read()) { pCounter.Value = reader.GetInt32(0); var compressedStream = reader.GetStream(1); var dest = new MemoryStream(); var decompressedStream = new DeflateStream(compressedStream, CompressionMode.Decompress); decompressedStream.CopyTo(dest); dest.Position = 0; ptext.Value = dest.ToArray(); cmdWrite.ExecuteNonQuery(); } Also, it looks like you can prepend a standard 10-byte header to the Deflate stream to make it GZip compatible, so select cast(decompress(cast(0x1F8B0800000000000400 as varbinary(max)) + cast(cast([text] as varchar(max)) as varbinary(max))) as varchar(max)) d from notescopy As it looks like DECOMPRESS ignores the CRC32 and ISIZE trailer fields if they are absent, which is http://www.zlib.org/rfc-gzip.html#file-format but not mandatory decompressor behavior.

The private key is used as part of the BACKUP CERTIFICATE syntax. Per https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-certificate-transact-sql?view=sql-server-ver15 , the syntax is: BACKUP CERTIFICATE certname TO FILE = 'path_to_file' [ WITH PRIVATE KEY ( FILE = 'path_to_private_key_file' , ENCRYPTION BY PASSWORD = 'encryption_password' [ , DECRYPTION BY PASSWORD = 'decryption_password' ] ) ] As you discovered, A "simple" BACKUP CERTICIATE without a private key isn't sufficient to be restored for use to decrypt. To make your certificate backup useful for restoring encrypted backups you must backup the certificate WITH PRIVATE KEY. Once you know this, it's easier to remember, because you need the key to unlock the encryption---but the fact that the "simple" certificate backup is possible without any warning does represent a big pitfall, and also demonstrates the importance of testing your restore strategy, particularly when encryption keys are involved.

These connections are most likely the HADR Endpoints for your Availability Group. If you include more columns in your sys.dm_exec_connections query (protocol_type, client_tcp_port, local_tcp_port, num_reads and num_writes) you should find: Both connections have a protocol_type of Database Mirroring One of the connections has a client_tcp_port value of 5022 (if you used the default port during AG setup), a high number for num_writes and a low number for num_reads. This connection is the one sending data to the other AG replica One of the connections has a local_tcp_port value of 5022 (if you used the default port during AG setup), a high number for num_reads and a low number for num_writes. This connection is the one receiving data from the other AG replica If your findings match the above info, you can check sys.database_mirroring_endpoints and check the columns is_encryption_enabled and encryption_algorithm_desc to confirm that your HADR endpoints were created with encryption enabled. Since the AG data connections don't use the normal SQL Server ports and don't operate as 'normal' connections, the session_id column is NULL for these connections, and they won't use normal encrypted connections, even if available. Instead, even on SQL Servers not configured for encryption, these HADR Endpoint connections are encrypted separately using the configuration of the HADR Endpoints. From an auditing perspective, this can probably be dismissed as a false positive provided your HADR Endpoints have encryption enabled. Reference: https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/transport-security-database-mirroring-always-on-availability?view=sql-server-ver15

I found the answer in the comments to my question: Appearently, you don't have to put in an "encryption password" when booting up anymore. It was required with FDE (Full Device Encryption), but is not with FBE (File-Based Encryption). I installed a custom recovery and tried to decrypt my data and drum-roll: it works! I can only decrypt data when using my current android-lockscreen password. Thanks a lot!

This is final (d) state that there is no method to achieve this on FBE different files to be encrypted with different keys please note the distinction between terms keys and credentials each file get it's own encryption key, but all keys encrypted with same credentials (for this user) that can be unlocked independently probably refers to CE / DE where each user can unlock CE independently edit: Samsung devices provide (FBE) "Strong protection" which is successor to (FDE) Secure startup -> Settings -> Biometrics and security -> Other security settings -> Strong protection

"Format Data" via the TWRP recovery -> Advanced Wipe -> Reboot -> Reinstalling LineageOS 16 seemed to do the trick, and the OS now loads. (Interestingly, I still cannot identify the device via adb/fastboot, I wonder if the stock ROM will resolve that).

The newly flashed ROM was xiaomi.eu 12.0.4. I then tried various modifications of it, but all of them expose the same issue over and over again. After I flashed Lineage OS, the behavior was as expected: no way to see any files on "Internal Storage" (/sdcard or /data/media/0). In fact, as I expected in my original post, even though /sdcard is viewable in OrangeFox, the only directory there is Fox, where it puts recovery logs and other volatile data, but there are no files from user even visible whatsoever. Accordingly the "Data" partition is not even treated as mounted, and going to "Mount" option reveals that the checkbox of it is unchecked and one cannot even check it until one explicitly decrypts it. Interestingly, I would have expected such mount-related behavior only with FDE, but I can confirm that this installation has FBE. So this looks good now, but it's a pity that latest MIUI ROMs are that broken, what a mess... :(

The best thing you could try and use is the AlwaysEncrypted feature. There are pros and cons to that - but you can effectively "turn it on" on the column and then use a cert to decrypt it. So it can be done "outside" of code and only require some changes to connection strings and use of certs. You can read more about https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver15 . It's the closest you can get without buying a third-party product or changing code.

Looks like the problem was caused by some transaction log records encrypted with the "old" and dropped certificate. I could not truncate the transaction log until I decrypted an re-encrypted the database. I assume this is a bug, because SQL Server should not allow to drop a certificate if still being used anywhere.

Yes, encryption on a unix socket is an option, but it's not necessary. If someone is able to listen in on your socket connection, it means your system has already been compromised. Low-level access to the system is required to intercept data on a unix socket connection. Also, from the https://dev.mysql.com/doc/refman/5.7/en/connection-options.html : --ssl-mode=mode PREFERRED: Establish an encrypted connection if the server supports encrypted connections, falling back to an unencrypted connection if an encrypted connection cannot be established. This is the default if --ssl-mode is not specified. Connections over Unix socket files are not encrypted with a mode of PREFERRED. To enforce encryption for Unix socket-file connections, use a mode of REQUIRED or stricter. (However, socket-file transport is secure by default, so encrypting a socket-file connection makes it no more secure and increases CPU load.)

Never store encrypted passwords. Store one-way hashes of the salted passwords. Don't store a VARBINARY value in a VARCHAR column lest you risk never getting it back. The fact that your client software shows you a hexadecimal representation of a binary string starting with 0x does not at all mean that 0x is part of the value. There is no reliable way to tell by looking at a sequence of bytes if that sequence is encrypted in any way.

So, why it's loaded in a database but can't be found under SQL Configuration and Register Editor? By default, SQL Server will check the certificate store for any certificates that match the requirements to be used for SSL. If one is found then it is used in place of a self-seigned certificate which is generated upon startup (if needed). Since this is done automagically for you, there is no configuration required, though it is best to be explicitly configured if forcing encryption. There are various reasons it may not show up in the configuration manager for SQL Server. I've spent far too much time debugging these to know that previous to SQL Server 2019, it's a crap shoot at best. Has it been installed correctly? It wouldn't be able to be used if it weren't.

Yes, this can be done using the below trace event, " You can find more details of the actual negotiated TLS protocol and cypher from this Extended Event trace but this requires SQL Server 2016 SP1 or later:" CREATE EVENT SESSION [tls] ON SERVER ADD EVENT sqlsni.trace( WHERE ([sqlserver].[equal_i_sql_ansi_string]([function_name],'Ssl::Handshake') AND [sqlserver].[like_i_sql_unicode_string]([text],N'%TLS%'))) ADD TARGET package0.event_file(SET filename=N'tls_trace') WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF); " The original answer is https://dba.stackexchange.com/a/189626/10267 by https://dba.stackexchange.com/users/44434/dan-guzman Also note, if this needs to be done in 2019 change the event to sqlsni.sni_trace and the code will be like CREATE EVENT SESSION [tls_new] ON SERVER ADD EVENT sqlsni.sni_trace( WHERE ([sqlserver].[equal_i_sql_ansi_string]([function_name],'Ssl::Handshake') AND [sqlserver].[like_i_sql_unicode_string]([text],N'%TLS%'))) ADD TARGET package0.event_file(SET filename=N'tls_trace') WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF) EDIT After the OP's comment, the requirement is to find the connection is encrypted or not and not TLS. I don't know an option in Extended event for this, but to acheive this you can leverage https://docs.microsoft.com/en-us/sql/relational-databases/triggers/logon-triggers?view=sql-server-ver15 in SQL server. Below is an attempt to capture the sys.dm_exec_connections after each login. DROP TABLE IF EXISTS test.dbo.tmp_conn SELECT * INTO test.dbo.tmp_conn FROM sys.dm_exec_connections dec WHERE 1=0 GO CREATE TRIGGER connection_encrypt ON ALL SERVER WITH EXECUTE AS N'sa' FOR LOGON AS BEGIN INSERT INTO test.dbo.tmp_conn SELECT * FROM sys.dm_exec_connections dec WHERE dec.session_id=@@spid END; Please change the code as per your need for the audit.

No, you do not need to for databases that do not have TDE. You are confusing two different things: The database master key Databases that do not use encryption: If you backup the certificate separately then this does not normally need backing up. You would just restore the whole DB from a backup with its certificate. You only need to back this up if you are protecting secrets within the database that you have no other backup of (such as a DEK or other symmetric key). Databases that use any form of encryption, such as TDE or CLE The master key protects the other encryption key. So you may be at risk of losing the DEK/other encryption keys, therefore you must backup the master key in order to recover that info. You need the key even if you have a separate backup certificate. The service master key You only need to back up the service master key if you intend to restore the master database, and you don't have access to a master backup, only the .mdf files. Note that things like linked server credentials are stored here, so you may want to be able to recover these, but it is not normally necessary. https://dba.stackexchange.com/questions/149776/restoring-encrypted-database-on-another-server-using-backup-encryption to create a new service master key (created automatically) and database master key, before you restore the database certificate to the server, because it needs protecting there. But then you are able to restore the backup. Backups of other databases are only encrypted using their own certificates. The database master key protects the certificate within the database headers (as well as the DEK for TDE-enabled databases). The service master key is what protects the database headers while on the server. Because the certificate is not in unencrypted form in the backup, there is never a need to protect it, so the master key is not used for that.

How much is sensitive data? Typically i would encrypt the data being passed to the server for example MD5. I don't need to see this information myself, but as long as the user puts in the same value that was used to encrypt then the server / code / database matches on the md5 value and can retrieve whatever is needed. There is no real way to reverse this unless someone knows the original string, or its been included in the pubic md5 lookup databases. For example, email, username, password. All stored in MD5, as long as the user inputs the same email, username and password. then the MD5 value will match what is in the database. There is no need for keys, no decrypting (As this will use a lot of resource power) This may be ill advising you, as without really know the content. For example if you have a whole database of Names, Addresses, Phone Numbers, card numbers etc etc etc an you need to view this information then you will need to encrypt and decrypt as and when you need. (Would of posted as a comment, but too much text)

Is this possible ... Yes. ... when someone takes backup of my postgres db ... Question: Why would anyone other than you (and your team) be taking a backup of your database? [I'm guessing] It's your responsibility to keep the database secure, recoverable and performant, so nobody else should be monkeying about with it or taking copies of it. ... it should be in encrypted form ... pg_dump produces a file. Encrypt that file, which you can in a "one-liner", something like this: pg_dump ... | tar -czf file.tar.gz ... and how we can decrypt that backup. Exactly as you say - decrypt the [encrypted] backup file and restore what comes out of that, something like this: tar -xzf file.tar.gz | psql This assumes that you're extracting a single, "plain" format file, rather than the [faster] directory-based backup options. I suspect that to protect the latter, you'd have to do the backup and then combine/encrypt the resulting files in a second step (and, of course, the reverse for restoring them).

First of all, all data you can compile can be decompiled if you really want its a question about time, so you are correct here. I did not know where in the world you are, but I will recommend AWS, Azure, Google Cloud and some of the big players out there, its not cheap but here you are sure about the provider did not have your root access key. If you lose your root key or SSH key you its will require you can create a snapshot of the VM disk and restore it for a new VM. Both AWS, Azure and Google have a very high level of security and use a vary advance ACL for users so its your responsibility to have the security ofc. I'm located in EU and working for a company where GDPR is very important, so I will recommend one of the big players, and read the data protection privacy. If needed contact a lawyer to review the company based on your use case. If its still not securer enough for the data you working with, I will highly recommend locally hardware and here the customer shut pay for that hardware fee you spent based on your contract. that's my recommend

This is a defense in depth tactic. There are other defense mechanisms in place at AWS to prevent data leakage such as disk destruction on disposal or limiting access to data centers to approved employees. If those defense controls were to fail the encryption at rest would provide a layer of defense to prevent data leakage. As you pointed out, access to your account provides access to the data. The defense in depth for this attack surface is that you keep your access keys secure, rotate them every 90 days, and don't provide access where it isn't need. Your user accounts should have strong passwords, no reuse, rotated every 90 days, the right access controls, and MFA enabled. These are all defense in depth tactics to keep your environment secure. In general, from a non-AWS perspective, data at rest encryption could be the only layer of data leakage protection. Not everyone is SOC2/ISO27001/PCI/HIPAA compliant. Bottom line, is data at rest encryption security theater? No. Is it the first line of defense? No (in the case of AWS). Is it part of a broader security defense in depth strategy? Yes. References: https://csrc.nist.gov/glossary/term/defense_in_depth https://www.cisa.gov/uscert/bsi/articles/knowledge/principles/defense-in-depth