MS SQL certificates and force encryption question

Kimball, Carol (DMH) 21 Reputation points
2021-03-23T20:03:39.76+00:00

Our situation involves three servers. Server A is a SQL 2019 in AWS. Server B is a SQL 2005 warehouse on prem, and server c is another SQL 2005 server on prem. We assigned certificates to encrypt the data from server A to Server B )between the cloud and on prem) and set the server to enforce encryption. The results worked fine. The problem is Server C does not need encryption but does not have a certificate. Is there any way to enforce encryption between AWS and B but allow C to talk to B? We had to turn off encryption reinforcement on B because C failed. Thank you in advance

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,639 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,547 questions
0 comments
Accepted answer
  1. CathyJi-MSFT 21,081 Reputation points Microsoft Vendor
    2021-03-24T07:02:16.857+00:00

    Hi @Kimball, Carol (DMH) ,

    > Server B is a SQL 2005 warehouse on prem, and server c is another SQL 2005 server on prem.

    SQL server 2005 is out of supported for a long time. Suggest you upgrading your SQL server to a newer version. Don't let your infrastructure and applications go unprotected.

    > Is there any way to enforce encryption between AWS and B but allow C to talk to B?

    No. If you want to connect to SQL server instance(enable encrypted connections)on server B from server C, you need to copy either the original certificate or the exported certificate file from server B to server C.

    Refer to MS document Enable encrypted connections to the Database Engine to get more detail information.

    If the response is helpful, please click "Accept Answer" and upvote it, thank you.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Brad Edmondson (MA EOHHS) 1 Reputation point
    2021-03-25T13:45:40.62+00:00

    Thanks @CathyJi-MSFT --

    We're trying to confirm whether the on-prem SQL 2005 box, with a certificate installed but "force" encryption set to No, will allow incoming connections to request encryption even though the server will not force it. So, yes, in a sense: accepting both encrypted and unencrypted connections, and allowing the client (or other server initiating the connection) to choose.

    This third-party page, if accurate, describes it well:

    When the ForceEncryption option for the Database Engine is set to Yes, all client/server communication is encrypted and clients that cannot support encryption are denied access.

    When the ForceEncryption option for the Database Engine is set to No, encryption can be requested by the client application but is not required.

    The SQL 2019 box is using a Linked Server object to connect to the 2005 box (the one with cert installed but force encryption=no). So we're looking at how to configure the connection for the Linked Server object (on the 2019 box) to use encryption when it reaches back to the 2005 box.

    Thanks,
    Brad

    0 comments