hi for the 2 years i've been here I believe we've had "encryption" turned off in what i only know as the way we tell ssms to communicate with our sql servers ...and i've always thought meant also the server itself relaxing how it communicates back to whatever client is asking for data.
recently an OO programmer asked why we are communicating to / from sql server without ssl. i told him its been like that and i'd investigate what the downside is either way knowing we havent heard of any breeches.
also recently, our dba turned on (for test purposes in response to the OO programmer's question) a service called TDE on a vm. All sorts of problems started happening including a need to trust the cert if contacting that server from a local instance of ssms and timeouts from/on one of our web apps (connection) trying thru java connectors to insert records onto a db on that server. shutting down that service made our current problems disappear.
so the questions are 1) what risk have we had all this time not encrypting, and 2) if we start encrypting, what other gotchyas will we see in an environment where ssis, ssrs, pbi , excel pivots etc are used bumping up against sql engines and cubes built off that engine?
So clarifying a few things here...
TDE is not a "service," it's a method to encrypt the database(s) both in-use and at-rest (ie, SQL Server service stopped.) This generally has a negligible impact on database performance, but does cause your backups to be much larger, as encrypted data doesn't compress down very well.
More likely your DBA enable the "Enforce Encryption" option on the SQL service, using a local or self-signed certificate, which would cause the issue of needing to trust the certificate you mentioned. I could also see if causing the connection issues from the app you mention, because the app isn't going to have a way to "trust" the certificate. There is, however, a solution, if you have the infrastructure to do it.
Create a server certificate from a "trusted" certificate authority. This could be a local certificate authority as part of an Active Directory Domain, or I believe you could purchase a certificate from a commercial authority (maybe.) Once you set up the SQL service with a trusted certificate, you should stop getting the problems you're seeing. Where I'm at, we're required to use SSL to encrypt the connections, and we've not had problems such as you're seeing, because we do have a local certificate authority as part of our domain that issues the certs, and thus, they're "trusted" by any device that belongs to that domain.
December 9, 2025 at 5:43 pm
Microsoft implemented a change requiring a cert that is trusted through the local hierarchy, or the client clicking "trust cert". Depending on which client versions were upgraded or servers changed, this likely caused things, as Jason noted.
December 9, 2025 at 8:06 pm
this is all good to know. What are the risks we've avoided in the last 2 yrs of not using encryption on these dw oriented servers? ssrs , ssms, pbi, excel and as i've shown, others, are requesting data from these servers. It seems to me if we are going to start using encryption that we'd better test the impact on these various technologies in a somewhat thorough fashion.
December 9, 2025 at 9:02 pm
I'd say the biggest downside / risk of not using encryption between the clients and the SQL Server would be the possibility of someone "sniffing" your network traffic, getting a SQL login / password, and using that to exfiltrate data.
As far as things like SSRS / SSMS / PBI / Excel talking to the SQL Server once you've got encryption set up with a trusted certificate, I've got all except Excel talking to my SQL Servers and none of them have run into any problems nor required setting up anything special.
But, absolutely yes, I'd say test for yourself. I'd also recommend confirming that I'm on the right track, thinking your DBA turned on the "Force Encryption" option for the SQL Server service, and not something else entirely. If I'm wrong, than you'll be barking up the wrong tree.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply