MSSQL 2012 Update (or create new certificate with same name) that uses Symmetric Key encryption

Super short version of my question:

For a MSSQL 2012 DB is it possible to update the current certificate with a new expiry date and new PW (or create new certificate with same name)? Or some other option I have not thought of that would be quick/easy to implement?

Now the details (there is probably simple answer I am just not able to find online):

We have a SQL 2012 DB and in lower environments (single server only for each) and our production which has failover server (so whatever my solution needs to include the ability to work with primary and failover server).

First two notes:

  1. The key/certificate I know is outdated/not best way and we are working on redesiging our encryption.

  2. We are working on upgrading this MS 2012 DB to 2019 soon, but not before I need to make this update/change.

The code for select (but we also do update/inserts) is used like this in SP’s (and also a trigger):

OPEN SYMMETRIC KEY KeyNameHere DECRYPTION BY CERTIFICATE CertificateNameHere        Select DecryptByKey(EncryptedColumn)     From dbo.TableName  CLOSE SYMMETRIC KEY KeyNameHere;  

I thought I had a solution (and using these steps testing it does work):

  1. Backups existing certificate

  2. Creates new certificate (and then back it up)

  3. Updates all SP’s using the certificate to use a new certificate name in code like:

Existing: OPEN SYMMETRIC KEY KeyNameHere DECRYPTION BY CERTIFICATE CertificateNameHere

New: OPEN SYMMETRIC KEY KeyNameHere DECRYPTION BY CERTIFICATE NewCertificateNameHere

  1. Drops old certificate

As I said the above does work in testing on dummy DB but there are a LOT more SP’s than I was told and I can update them all to use new certificate name (as above) but I would prefer to find a way to not have to update all these SP’s code, but I can if needed.

Key question/concern is around the certificate in production (primary and failover servers) and what/how to ensure the update is completed on both and will work if/when a failover occurs.

If there is anything else I am missing or not considering please let me know.

Trying to understand the encryption used at various steps of Tor

So I read up on "Diffie-Hellman" key exchange which to my knowledge is a way for two parties to create a "shared secret" but I don’t really know if this is a public key or a private key or something else entirely.

When I look up how Tor works, many videos use the analogy of the locked box. We establish an entry node, relay node, exit node (call them A, B, C). I send a locked box to A who unlocks it and inside is another locked box with a message included, "send this to B." A sends this box to B, who unlocks it and sees another locked box with a note, "send this to C." B sends the box to C, who unlocks it and sees a message, "Fetch the contents of somewebsite.com" (the final request).

I don’t know if this analogy makes sense or if it’s even accurate.

Is this key exchange something that is done with the client and each of the three nodes, and that is how I am able to encrypt the message several times? Or is there some other algorithm used instead? I see that Tor uses RSA but I’m still unclear on how it all works together.

Trying to understand what is going on. It would help to see a minimal example using the actual encryption techniques.

How to pass both passphrase and password in GPG signed symmetric encryption?

I am new to GPG so I may be going about this all wrong. I have some files I would like to encrypt symmetrically using AES256, but I want to sign them with my GPG key. I need to automate this process however so I’m using --batch and I pass the symmetric key after --passphrase. Since it needs to be automated, I’ve disabled passphrase caching by following the steps here. However, this means my script has no way to pass in the passphrase for my GPG private key. My script will be piping the files to gpg so passing the passphrase to gpg via stdin is also not an option.

If there is no reasonable way to pass both the AES password and private key passphrase, I might consider doing this in two steps, with gpg symmetrically encrypting and then a second round of gpg for signing. It seems excessive though, considering gpg can clearly do this in one step if one passes the private key passphrase interactively.

For reference, I’m using gpg2 exclusively and don’t care about backwards compatibility with gpg 1.x.

Here is the command I’m currently using for encryption. It encrypts and signs as expected, but I can only pass it the private key’s passphrase interactively in the text-based dialog "window".

gpg2 --batch --passphrase <my-long-symmetric-password> --sign --symmetric --cipher-algo AES256 plaintext.txt

Safety of encryption when knowing part of the content [migrated]

I want to encrypt a JSON file while exposing its interface (the name of the object fields) in clear text.

Since this exposes part of the content of the file, my guess is that an attacker could use this to hack the encryption key.

Are there encryption methods that can circumvent, or at least be safer for cases like this?

I am mostly convinced that knowing part of the content of an encrypted file can make attacks easier, but I do not know if there is a technical term for such conditions or how to evaluate if some algorithms are safer or not. So far I am specially interested if using aes-192-cbc or gpg would be OK.

Pattern for access controlled client side encryption

How would you design a server/client system where a client is granted a key to encrypt/decrypt data, but the key could be revoked/redistributed by the server? Data encrypted prior must still be readable with the new key.

A simple scenario:

  1. Client wants to send a document to a server
  2. Client encrypts the document with some client-side credentials and sends to server
  3. Server receives document and stores in database
  4. Client requests document, receives, then decrypts. The roundtrip is complete.

Now, suppose the client credentials are compromised and key used to encrypt/decrypt data is stolen. The client changes their password, etc, but the key that can decrypt incoming data is still an issue.

My question is about redistributing an encryption key without having to re-encrypt all of the clients data. Are there any patterns that can help me with this? It feels like a variation of symmetric encryption with a KEK and DEK, but I’m having trouble figuring out how to encrypt something on the client side without exposing the DEK.

Recovery possibilities with Zero knowledge encryption

I have some encryption understanding however I fail to get my head around following scenarios. I would like to know if they are possible with a zero knowledge encryption system.

What the system can or can’t do can be added to the answer. Example:

  • The system needs to keep a encrypted copy of the key.
  • The user has to have the key on a USB stick.

In the end, all scenarios ask the same questions.

  • Can the user access his data?
  • Does the system know about his data?

Scenario 1: User logs in on a new computer. Does not have the key with him.

Scenario 2: User logs in on a new computer. Does have the key with him (e.g. USB stick).

Scenario 3: User lost his password. His identity has been verified and approved.

Scenario 4: New sub-users are assigned to the same resource.

SFTP server with storage encryption

I’m looking for an unusual solution that uses SFTP server for data transfer but said SFTP server also should act an encryption proxy i.e. all the data it stores on the server side should be encrypted. Although I could use host (OS-wide) encryption it is not gonna be effective during runtime if the hoster I use decided to peek at it or will be forced by 3rd party or crappy government.

I did some googling but the only thing I found was: https://github.com/libfuse/sshfs Problem is I dont want no custom clients, I want to hide ANY implementation from the client, it should be just your basic SFTP you can use anywhere, even on your microwave, let alone phone or notebook.

This variant: https://serverfault.com/questions/887167/sftp-with-data-encryption-at-rest seems useable but again, at runtime it only protects against other normal users (which I dont have).