Fail to diable Force Encryption on SQL Server 2019

Stavros 1 Reputation point
2022-04-12T08:19:55.117+00:00

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

Windows Server 2019
Windows Server 2019
A Microsoft server operating system that supports enterprise-level management updated to data storage.
3,537 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,045 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. YufeiShao-msft 7,071 Reputation points
    2022-04-13T08:51:36.34+00:00

    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.

    0 comments No comments

  2. Stavros 1 Reputation point
    2022-04-13T14:30:57.68+00:00

    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

    0 comments No comments