Fail to diable Force Encryption on SQL Server 2019

Question

Hi All,

I am trying to Disable the force encryption option on SQL Server 2019 and it fails.

I manage to enable force encryption option with no issues following the below steps:

In SQL Server Configuration Manager, expand SQL Server Network Configuration, right-click Protocols for <server instance>, and then select Properties.
In the Protocols for <instance name> Properties dialog box, on the Certificate tab, select the desired certificate from the drop-down for the Certificate box, and then select OK.
On the Flags tab, in the ForceEncryption box, select Yes, and then select OK to close the dialog box.
Restart the SQL Server service.

I execute the Query:

Select * from sys.dm_exec_connections
Where session_id = @@SPID ;

And the encrypt option is set to true.

Now after finalizing my test I want to revert the change and disable encrypt option.
From SQL Server Configuration Manager, under SQL Server Network Configuration, right-click Protocols for <server instance>, and then select Properties.
I remove the certificate and on Flags tab, in the ForceEncryption box, I select NO, and then OK to close the dialog box.

I restart the server I execute again the query :

Select * from sys.dm_exec_connections
Where session_id = @@SPID ;

To check that the encryption option is setup to FASLE again but to my surprise, the option is Yes (I get the error in logs also that encypt_oprion is enabled, not SQL logs but the logs from the app that I have configured against the SQL Database )

I followed exactly the same steps on SQL Server 2016 and I didn't face any issues at all.

Can you please assist me to disable encrypt option and help me to find out what I am doing wrong?

Thanks in advance,
Stavros

Answer 1

Hi @Stavros ，

Disable Force Encryption for a SQL Server instance, need to access to the Windows Server

Try to use Dbatools(a free PowerShell module) to stop:
like

PS C:\> Disable-DbaForceNetworkEncryption  

https://docs.dbatools.io/Disable-DbaForceNetworkEncryption
https://www.mssqltips.com/sqlservertip/6307/how-to-stop-and-start-sql-server-services/

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi Yufeishao-msft,

Thanks for your response.

For some reason today i followed the same steps like yesterday and the flag encrypt_option changed to False.

Didin't try yet to use your suggestion ( PS C:\> Disable-DbaForceNetworkEncryption), one extra reason was the message that i got that the repository is untrusted.

Thanks though for your responce, i will keep for future use if needed.

Regards,
Stavros