5

I have a new SQL Server 2019 on-premises instance. Assuming the client is the SQL Server Management Studio or any other application:

  1. When the client connects to the server using SQL Server authentication, does the connection request go from the client to the server as plain text? In other words, are the authentication credentials exposed (plain text) over the wire? In other words, can an attacker see the username/password on the wire?

  2. Subsequent to the authentication, and assuming TLS is not configured, then is the query (example: SELECT) and its output visible as plain text over the wire?

2 Answers 2

10

are the sql authentication credentials exposed over the wire?

No, login traffic is always encrypted over the wire. From this documentation page:

SQL Server always encrypts network packets associated with logging in. If no certificate has been provisioned on the server when it starts up, SQL Server generates a self-signed certificate which is used to encrypt login packets.

I understood "exposed" to mean not encrypted, exposed as plain text over the wire. The documentation link above addresses the larger security questions about using self-signed certificates:

By default, encryption of all network traffic for a connection requires that a certificate be provisioned on the server. By setting your client to trust the certificate on the server, you might become vulnerable to man-in-the-middle attacks. If you deploy a verifiable certificate on the server, ensure that you change the client settings about trust the certificate to FALSE.

5
  • 1
    I think that's a strawman: OP asks "are the sql authentication credentials exposed over the wire?" you answer "No, login traffic is always encrypted... SQL Server generates a self-signed certificate" so it's definitely still exposed, because a self-signed certificate is trivial to MITM, unless the certificate has been pinned, which is unlikely Jan 15 at 21:41
  • @Charlieface, self-signed or not, certificate verification is not in the UI for a connection (SSRS or Visual Studio). I doubt that the average application verifies the certificate either.
    – grahamj42
    Jan 16 at 10:22
  • @grahamj42 Can't say for other client drivers, but .NET's SqlClient has a specific TrustServerCertificate which defaults to false, you need to set it to true to use a self-signed cert otherwise you get a certificate error. You can use that as a custom connection property in SSMS and Visual Studio, not sure about SSRS. There is a similar setting in DataGrip so I assume JDBC also has a similar option. Screenshot of that option in Visual Studio Jan 16 at 13:53
  • So the conclusion is that an attacker cannot see the plaintext username/password of the sql authentication but the attacker is able to see the subsequent SQL query (example: SELECT query) and its output? Correct @Charlieface? @Dan?
    – variable
    Feb 3 at 17:03
  • Yes correct, however it's very easy to MITM the connection if there is only a self-signed certificate, as the client cannot verify it. The data comes through unencrypted over TDS, which an attacker would probably need a parser for. I believe Wireshark can do some of this Feb 3 at 17:10
1

Subsequent to the authentication, and assuming TLS is not configured, then is the query (example: SELECT) and its output visible as plain text over the wire?

Query output is not encrypted, but except for text columns, is not sent as text. The network protocol is TDS. - David Browne - Microsoft

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.