Invariably, when I ask people what Transport Layer Security (TLS) does, they say something like, “it protects my credit card info when I buy things online.”
The response is not incorrect, but it’s not the whole story, either, and let’s just ignore the server-side credit card protection requirements and PCI compliance for a moment.
TLS provide three security services, with an optional fourth.
- The first is server authentication, in other words, “is the server I connect to the real server?”
- The second is channel encryption.
- The third is channel integrity protection.
- The fourth option is client authentication, in other words, the server requires the client also authenticate themselves using TLS.
Now here is the critically important part:
Channel protections are of little use if you do not know which server you are talking to!
I cannot stress enough how important server authentication is. You cannot have a private conversation unless you know who you are talking to. It’s like whispering a secret to someone in a blacked-out room, but you do not know who is in the room!
But people, and developers, make this fundamental mistake all the time by doing things like:
- Using self-signed certificates
- Not performing complete certificate validation
- Ignoring errors during certificate validation
SQL Server has some options you should avoid, if possible, too. Most notably:
- Connecting to SQL Server with no TLS.
- SQL Server installs a self-signed certificate by default.
- SQL Server allows connections that ignore the server certificate.
The first one is obvious, if you’re not using TLS, then you have no clue what server your client code is connecting to. You can opt-in for TLS support by using Encrypt=True in connection strings.
Note that current Azure Data Studio builds default to using TLS:
There’s more information here https://learn.microsoft.com/en-us/sql/azure-data-studio/connect?view=sql-server-ver16#encrypt-and-tr....
I must admit, I do not like the use of the word ‘encrypt’ in connection strings and the ADS dialog above, because as I have mentioned, TLS is more than encryption. I would prefer to use ‘secured’. But that’s just pedantic me!
Next is self-signed certificates.
The problem with self-signed certificates is there is no trust in them whatsoever. There’re tricks you can do in code to ‘pin’ a certificate’s thumbprint or hash, but that defeats the agility, or the ability to move from one certificate to another, that comes with X.509 certificates.
I have heard some people say, “we use self-signed certs in development, but real certs in production!” The problem with that statement is that your code will perform differing certificate tests in dev, test and prod, so any tests you run in dev/test will not test the same scenario in prod, so don’t be surprised if prod fails even if you tested certs in dev/test.
When you add TrustServerCertificate=True to a connection string, you’re basically saying “I trust the server, regardless of what the certificate says.” Or, to be hopelessly cynical, “My client can connect to any server because I don’t care who owns the database, even an evil database server.”
TrustServerCertificate=False doesn’t mean your code does not trust the server certificate, rather it forces the client code to evaluate the server certificate by performing all the appropriate certificate checks such as:
- The certificate is issued by a trusted CA
- The name in the certificate matches the name of the server you are connecting to.
- Date range is correct
- Key Usage is correct
- Etc.
SQL Server 2022 also adds a new argument to Encrypt, Encrypt=Strict, which ignores TrustServerCertificate altogether, and always performs correct certificate validation. Also, this will use TLS 1.3 when possible.
NOTE: We will announce support for Strict in Azure SQL DB and Azure SQL MI soon, so stay tuned!
The moral of this story is please just do the right thing and use real certificates in dev/test that chain up to a trusted root. If you don’t want to use a commercial certificate authority (CA) you could use Windows Certificate Services and deploy the root certificate on your dev boxes.
This is precisely what I do in a Windows Server VM in Azure: I have my own CA and issue certs as needed, and when it’s not in use I just shut the VM down.
Finally, use the following in your connection strings:
Encrypt=True or Encrypt=Strict
TrustServerCertificate=false
Good TLS hygiene is good for security and TLS is a good security defense.
PS: As a selfless plug, I write about this topic in excruciating detail in Chapter 10, “Cryptography in Azure” in Designing and Development Security Azure Solutions from Microsoft Press.
A big thank you to colleagues Andreas Wolter and Srdan Bozovic for their review and comments.