I recently enabled the encrypted connection to one of my SQL Server instances. I have software that DSN ODBC must connect to this instance.
The database is on a W10 PC while the client is a W11 PC.
I would like the data that the client sends to the server and the server sent to the client to be encrypted. Following the official Microsoft guide (I leave the link below) I managed to configure the connection but I saw that if I create a new ODBC connection without activating the "use strong encryption for data" flag I can still read and write data to the Database. In my opinion it makes no sense, then anyone could connect to the database, even without having the certificate.
How can I prevent / block connection to the database from a PC that does not have the certificate installed?
CodePudding user response：
Data will be encrypted using the server's certificate regardless of the client ODBC DSN setting "Use strong encryption for data" when you set force encryption on the database server. Below is the excerpt from the ODBC driver documentation.
----------------- -------------------------- ------------------------- ---------------------------------------------------------------------------------------------------------------------
| Encrypt Setting | Trust Server Certificate | Server Force Encryption | Result |
----------------- -------------------------- ------------------------- ---------------------------------------------------------------------------------------------------------------------
| No | No | No | Server certificate isn't checked. Data sent between client and server isn't encrypted. |
| No | Yes | No | Server certificate isn't checked. Data sent between client and server isn't encrypted. |
| Yes | No | No | Server certificate is checked. Data sent between client and server is encrypted. |
| Yes | Yes | No | Server certificate isn't checked. Data sent between client and server is encrypted. |
| No | No | Yes | Server certificate is checked. Data sent between client and server is encrypted. |
| No | Yes | Yes | Server certificate isn't checked. Data sent between client and server is encrypted. |
| Yes | No | Yes | Server certificate is checked. Data sent between client and server is encrypted. |
| Yes | Yes | Yes | Server certificate isn't checked. Data sent between client and server is encrypted. |
| Strict | - | - | TrustServerCertificate is ignored. Server certificate is checked. Data sent between client and server is encrypted. |
----------------- -------------------------- ------------------------- ---------------------------------------------------------------------------------------------------------------------
In my opinion it makes no sense, then anyone could connect to the database, even without having the certificate.
You may be confusing authentication with encryption. The encryption key exchange occurs during the initial database connection and is not related to authentication. Use Windows authentication for additional security since that ultimately uses certificates to verify user identity.