question

KimballCarolDMH-5780 avatar image
0 Votes"
KimballCarolDMH-5780 asked BradEdmondson-MassGov answered

MS SQL certificates and force encryption question

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-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft commented

Hi @KimballCarolDMH-5780,

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.



· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Working with OP here on the problem.

@Cathyji-msft Would the answer be different if we asked to allow encryption from clients that request it (while also supporting unencrypted for clients that don't request it), rather than enforcing encryption from some clients but not others?

Point taken on migrating away from 2005; this question is coming up as part of an effort to do just that.

Thanks!

-Brad

0 Votes 0 ·
Cathyji-msft avatar image Cathyji-msft BradEdmondson-MassGov ·

Hi @BradEdmondson-MassGov,

Would the answer be different if we asked to allow encryption from clients that request it (while also supporting unencrypted for clients that don't request it), rather than enforcing encryption from some clients but not others?

Did you mean one instance support encrypted connection and unencrypted connection at one time? The answer is no.

If I misunderstood, please let me know.

0 Votes 0 ·
BradEdmondson-MassGov avatar image
0 Votes"
BradEdmondson-MassGov answered

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




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.