5

I am going through the below link

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/authentication-in-sql-server

and in that it says

When SQL Server logins are used, SQL Server login names and encrypted passwords are passed across the network, which makes them less secure.

how are the password encrypted and how does sql server decrypt it? What kind of encryption is involved in it?

7

how are the password encrypted and how does sql server decrypt it? What kind of encryption is involved in it?

Super high level, if you have encryption setup for the instance then the certificate that is configured can be used. If you don't have it enabled, the self-signed certificate created on SQL Server startup will be used.

Regardless of which is used, the SSL/TLS setup for this is done during the pre-login negotation phase. If, however, encryption is either not set or enforced, the TLS/SSL portion will only be active for the length of the credential transfer and will immediately go back to plain text (no SSL/TLS) once this is completed for the life of the connection.

SQL Server itself doesn't encrypt or decrypt the data, it's encrypted and decrypted in transit only. Depending on your client driver and application, this may or may not exist in plain text in your process memory space. Additionally, SQL Server uses hashes to check the passwords and doesn't store the original plain text password for SQL logins (unless you want to be pedantic about proxy accounts and linked servers).

  • Actually that depends on the driver, by default jTDS for example does not use partial TLS for the handshake. So there is only a Minor Obfuscation or NTLM (and unfortunately LM Hashes) which gets send (in clear) for that driver. (And short than turning Force encryption on it seems not posssible to reject this) – eckes May 1 at 16:21
  • 1
    1) Hence why I said it depends on your driver. 2) The official TDS specification calls out what has to happen, at a minimum. Violation of that could cause other issues. For example, there is also FreeTDS and I have no idea if it follows spec. Pass the hash is a different issue, and we could do down a long dark road about owning the server and decrypting all of the SSL/TLS traffic. This is why I kept it "HighLevel" :) @eckes – Sean Gallardy May 1 at 16:39
3

I agree with all the points Sean made in his answer.

However, I think the point the Docs page is trying to make is that Kerberos authentication via Windows never1 passes the password across the network. SQL authentication does, which means there is a chance that someone capturing packets might be able to decrypt that password and login to the SQL Server.

Windows Authentication is based on tokens. When you login, your account is given a token by the login authority that can be passed around across the network and confirmed by the login authority without the possibility of compromising the account password.

As a result, SQL Server authentication is less secure than Windows Authentication.

  • 1
    Yes, this is a great point and I believe that's the basis for the text in the Docs page. If you and the OP believe this, let me know and I'll edit the Docs page to be less ambiguous. – Sean Gallardy May 1 at 14:50
  • Actually NTLM might pass a hash which can be brute forced (or stolen for v1). It only uses tokens when Kerberos (or the newer Azure AD Token stuff) is used. – eckes May 1 at 16:23
  • 1
    True, @eckes - I should have noted the gotchas with NTLM – Max Vernon May 1 at 16:51

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.