Question

Encrypting SQL without TLS

Laserfiche Directory Server Security

Updated February 20

asked on February 19 • Show version history

We have a client that needs to encrypt the sql running between the database and the various applications, and they do not want to use TLS. Reason being certificates and the need to renew them adds one more item to the "ToDo" list every year.

For Workflow and Forms we can just add “Encrypt=yes; TrustServerCertificate=no; " to a connection string.

But for LFS and DS, there is no such option. We can use IPSec, but that's pretty cumbersome with 9 LF servers total. Is there a cheat code somewhere we can use to force the connection string behaviors onto the DB connections for these two components?

0 0

Replies

replied on February 19

That was fast - Related Posts answered my own question:

How to enable Workflow server SSL-TLS to the SQL server? - Laserfiche Answers

Thanks, Sam!

0 0

replied on February 19 • Show version history

Update - Now the plot thickens. Turns out their environments (Dev, QA, Prod) are all multitenant as far as the DB server goes, each machine hosting 50+ databases for 50+ different applications.

In this MS article, (Configure SQL Server Database Engine for encryption - SQL Server | Microsoft Learn) it states:

"SQL Server uses a certificate from a trusted certification authority if available for encrypting login packets. If a trusted certificate isn't installed, SQL Server generates a self-signed certificate (fallback certificate) during startup and use that self-signed certificate to encrypt the credentials. This self-signed certificate helps increase security, but it doesn't protect against identity spoofing by the server. If the self-signed certificate is used, and the value of the ForceEncryption option is set to Yes, all data transmitted across a network between SQL Server and the client application is encrypted using the self-signed certificate."

You can force encryption from the server by setting ForceEncryption = yes in Network Settings.

Moreover, in post referenced above, Sam stated:

"It is best to configure and enable the Force Encryption option within SQL Server itself. Enabling Force Encryption causes all client connections to the SQL Server instance to use encryption without any special configuration required for each client. "

Since all clients connect to SQL via some MS technology, my read is that all connection methods, including OLE, ODBC, Native Client all support this functionality. i.e. You set encryption at the server and it does not break anything.

However, because of the complexity of this environment they are very concerned that this would break something.

You can force encryption from the client side by using “Encrypt=yes; TrustServerCertificate=no; in a connection string. They want to do this on an application by application basis. The question is, are there registry settings that can be added or used on the SQL Native Client that would enforce encryption in this manner. It would be easy if LFS & WF used a connection string, but that is not the case.

0 0

replied on February 19 • Show version history

Update 2: Info from Support - the LF Server uses ODBC Driver for SQL Server 17 (x64), and the connection string is encrypted in the registry. This is to prevent anyone with server access to see the SQL username and pwd. This is actually only relevant with using SQL authentication (vs. Windows Authentication), but not a policy that's likely to change any time soon. The possible remedy for the client side is to switch to ODBC Driver for SQL Server 18, which is supposed to encrypt by default. But removing 17 and adding 18 to a test server totally broke Laserfiche. To be continued...

0 0

replied on February 20

That is somewhat incomplete. Laserfiche Server 11 uses ODBC 17 for new repositories. Previously registered ones will keep using the driver version they were registered with (ODBC 11 in older versions of 11 and 10). The fact that uninstalling ODBC17 broke Laserfiche is confirmation that your repository registration was using v17. You can also see which driver is being used in Repository Settings in the LF Admin Console.

Laserfiche 12 uses ODBC driver v18.

Multiple ODBC driver versions can be installed side by side. You can switch the Laserfiche Server to use a newer version by unregistering and re-registering the repository and specifying the driver version at registration time.

All that said, I would rather look at what it would take to enable encryption requirements server-wide. With the number of server and applications you have, adding encryption piecemeal as connection string customization is kind of a nightmare to maintain long term.

One thing to keep in mind is that when using TLS, you need the server name to be specified as its fully qualified domain name (FQDN) so it matches the certificate. So I would pick a SQL server and go through its clients, verify the driver versions used and update connections strings to use FQDN. Once you do that, you can set up the certificate on SQL and enable "Force Encryption. Anything that's incorrectly set up will fail to connect immediately.

And as always, please please please, do not use self-signed certificates. Depending on how these SQL servers are accessed, an internal certificate authority and auto-renewal could be good enough.

This is a good intro to setting up auto-renewal for certificates: https://techcommunity.microsoft.com/blog/askds/first-issuance-manual-with-automated-renewals/4085859

2 0

replied on February 20 • Show version history

Thanks Miruna - I agree that these are not best practices, but the alternatives are all worse and the timeframe is super tight. That said, I think found the answer to this specific problem. Here is a screen that I have clicked by 1,000 without paying attention to all of the options:

That part where you can add ODBC options is staring me in the face. All I need to do here is to add the parameters, Encrypt=yes; TrustServerCertificate=yes; and I think we'll be done.

We already tested these options using Workflow data sources, so this should work at the server level as well.

Testing to follow...And testing seems to have worked. Note the encrypt_option column.

0 0

You are not allowed to follow up in this post.

Question

Question

Encrypting SQL without TLS

Replies

Sign in to reply to this post.