Block unencrypted connection to SQL Server via ODBC

new9mtju 于 2023-06-28 发布在 SQL Server

关注(0)|答案(2)|浏览(148)

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?

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/enable-encrypted-connections-to-the-database-engine?redirectedfrom=MSDN&view=sql-server-ver16

sql-server

来源：https://stackoverflow.com/questions/73497721/block-unencrypted-connection-to-sql-server-via-odbc

2条答案

按热度按时间

watbbzwu1#

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.

赞(0）回复(0）举报 2023-06-28

j13ufse22#

You can block that specific connection with Trigger of Logon. But you need to know what are you doing, if you write some wrong SQL so nobody connect anymore, so you will need to disable the trigger with administrative console SQLCMD on local machine. I sugest first you learn about, how to use that triggers.

赞(0）回复(0）举报 2023-06-28

我来回答

Block unencrypted connection to SQL Server via ODBC

2条答案

相关问题

热门标签

最新问答