3

We're implementing SQL Server Always Encrypted in our 2019 environment. We've done several successful POC's over the last few months, but in moving the solution to Prod, I was expecting to use a Public Trusted CA for the certificates. But now, combing back over approximately 10 AE tutorials on the web, including the official Microsoft instructions, I've noticed that using a Public Trusted CA is never even mentioned...ever...at all. There's even an article on here asking if using a Trusted CA cert even adds any benefit, and it's suggested that it does not.

I'm trying to satisfy all of our security initiatives that are being rolled out company wide, so I guess I'm trying to find some sort of standard answer I can point to.

I've been talking about how self signed certs are subject to man-in-the-middle attacks, but I think I'm beginning to realize this is only for SQL connections being made, and any data that flows through that connection (because in the tables the data is unencrypted, and the SSL encrypts it). But with AE, the data in the database is already simply an encrypted string.

So does the nature of an Always Encrypted solution, if done where the self signed certs are never made or live on the same server that holds the data, preclude any MIM attacks? Is simply using self signed certs the accepted "standard" way of doing it? Thinking it through, I believe the answer is yes, but would just like some sort of "official" answer lol.

Charlieface
  • 17,078
  • 22
  • 44
Emo
  • 143
  • 1
  • 7

2 Answers2

5

So does the nature of an Always Encrypted solution, if done where the self signed certs are never made or live on the same server that holds the data, preclude any MIM attacks?

Always Encrypted (AE) has two major parts to it, the database engine and the client driver. The database engine always* works with encrypted data and the data is never in a decrypted state. Thus, the data sent to and from the client or server, across the wire, is also in an encrypted state. In fact, the only place it is not in an encrypted state is any endpoint that has an appropriate driver and access to the correct certificate to decrypt the data.

Given the above, unless the MiM has a copy of the certificate (and any other certificates needed for TLS) there shouldn't be a way (not counting ridiculous brute forcing) for this to occur. This does, however, push the onus for security to the endpoints which have a fairly terrible track record of being secured properly.

To answer your question, yes, given the nature there typically won't be a MiM attack available. Additionally, I didn't see anywhere in the most common driver where the certificates where ever checked for trust, nor CRLs checked... granted I could have easily missed something as I didn't make studying the code my thesis.

Is simply using self signed certs the accepted "standard" way of doing it?

Pretty much, that's why you don't see any real reference to it. Most places have PKI setup, and those that don't can easily use built in tools to create certificates.

Thinking it through, I believe the answer is yes, but would just like some sort of "official" answer lol.

Microsoft is the only place (I guess technically, companies are people too, so the only person?) that can give you an "official" answer.

* This does not include secure enclaves

Sean Gallardy
  • 38,135
  • 3
  • 49
  • 91
0

Self-signed certificates are no more subject to MitM attacks than any other kind. The only thing special about them is that they're not automatically trusted (because they're not signed by a trusted authority), so configuring trust is your problem.

Also, note that the main benefit of certificate authorities is when the connecting clients are not under your control... e.g. when a million anonymous users are accessing your web site, you can't manually establish trust with all of them one by one.

That's less relevant for something like a database server, which will typically be accessed by a much smaller set of clients, and will not be accessed anonymously. In that environment, each client will need to be set up to authenticate with the server anyway, so ensuring that they trust the server certificate becomes part of that process.

Simon Geard
  • 101