How encryption in SQL login authentication worksDatabase encryption and key management with pg_cryptoSQL server windows authentication through networkHow does one query a SQL Server Linked Server when SQL Server Authentication is specified?Restore Database w Master Key and storing password in plain textRestore db with encrypted column in SQL Server, without overwriting existing Service Master KeySQL Server, connecting without windows authentication?Create a User and Password for ADO.net and SQL SERVER AuthenticationSSMS Permission Denied, Login with Windows AuthenticationHow to stop decrypting data after opening master key in SQL Server?How to grant or allow a particular SQL Server Login/User to always view a Column Level Encrypted data?
How are Core iX names like Core i5, i7 related to Haswell, Ivy Bridge?
International Code of Ethics for order of co-authors in research papers
Was the Highlands Ranch shooting the 115th mass shooting in the US in 2019
What did Rocket give Hawkeye in "Avengers: Endgame"?
Early arrival in Australia, early hotel check in not available
Adding slope values to attribute table (QGIS 3)
How can I answer high-school writing prompts without sounding weird and fake?
Remove everything except csv file Bash Script
Does Lawful Interception of 4G / the proposed 5G provide a back door for hackers as well?
Exception propagation: When to catch exceptions?
Is a diamond sword feasible?
Is it a bad idea to replace pull-up resistors with hard pull-ups?
Will change of address affect direct deposit?
Was this a power play by Daenerys?
Why does a C.D.F need to be right-continuous?
On studying Computer Science vs. Software Engineering to become a proficient coder
Cropping a message using array splits
How are one-time password generators like Google Authenticator different from having two passwords?
How to slow yourself down (for playing nice with others)
As programers say: Strive to be lazy
How old is Captain America at the end of "Avengers: Endgame"?
Was there ever any real use for a 6800-based Apple I?
We are two immediate neighbors who forged our own powers to form concatenated relationship. Who are we?
How can this pool heater gas line be disconnected?
How encryption in SQL login authentication works
Database encryption and key management with pg_cryptoSQL server windows authentication through networkHow does one query a SQL Server Linked Server when SQL Server Authentication is specified?Restore Database w Master Key and storing password in plain textRestore db with encrypted column in SQL Server, without overwriting existing Service Master KeySQL Server, connecting without windows authentication?Create a User and Password for ADO.net and SQL SERVER AuthenticationSSMS Permission Denied, Login with Windows AuthenticationHow to stop decrypting data after opening master key in SQL Server?How to grant or allow a particular SQL Server Login/User to always view a Column Level Encrypted data?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
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?
sql-server sql-server-2014 encryption
asked May 1 at 8:11
ioquestionioquestion
362
add a comment |
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?
sql-server sql-server-2014 encryption
asked May 1 at 8:11
ioquestionioquestion
362
add a comment |
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?
sql-server sql-server-2014 encryption
asked May 1 at 8:11
ioquestionioquestion
362
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?
sql-server sql-server-2014 encryption
sql-server sql-server-2014 encryption
asked May 1 at 8:11
ioquestionioquestion
362
asked May 1 at 8:11
ioquestionioquestion
362
asked May 1 at 8:11
ioquestionioquestion
362
asked May 1 at 8:11
ioquestionioquestion
362
asked May 1 at 8:11
ioquestionioquestion
362
362
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
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).
answered May 1 at 11:21
Sean GallardySean Gallardy
17.7k22756
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
add a comment |
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.
answered May 1 at 14:13
Max VernonMax Vernon
53.2k13116234
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
add a comment |
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f237106%2fhow-encryption-in-sql-login-authentication-works%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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).
answered May 1 at 11:21
Sean GallardySean Gallardy
17.7k22756
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
add a comment |
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).
answered May 1 at 11:21
Sean GallardySean Gallardy
17.7k22756
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
add a comment |
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).
answered May 1 at 11:21
Sean GallardySean Gallardy
17.7k22756
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).
answered May 1 at 11:21
Sean GallardySean Gallardy
17.7k22756
edited May 1 at 12:43
answered May 1 at 11:21
Sean GallardySean Gallardy
17.7k22756
answered May 1 at 11:21
Sean GallardySean Gallardy
17.7k22756
answered May 1 at 11:21
Sean GallardySean Gallardy
17.7k22756
17.7k22756
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
add a comment |
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
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
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
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
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
add a comment |
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.
answered May 1 at 14:13
Max VernonMax Vernon
53.2k13116234
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
add a comment |
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.
answered May 1 at 14:13
Max VernonMax Vernon
53.2k13116234
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
add a comment |
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.
answered May 1 at 14:13
Max VernonMax Vernon
53.2k13116234
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.
answered May 1 at 14:13
Max VernonMax Vernon
53.2k13116234
edited May 1 at 16:55
answered May 1 at 14:13
Max VernonMax Vernon
53.2k13116234
answered May 1 at 14:13
Max VernonMax Vernon
53.2k13116234
answered May 1 at 14:13
Max VernonMax Vernon
53.2k13116234
53.2k13116234
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
add a comment |
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
1
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
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
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
1
True, @eckes - I should have noted the gotchas with NTLM
– Max Vernon
May 1 at 16:51
True, @eckes - I should have noted the gotchas with NTLM
– Max Vernon
May 1 at 16:51
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f237106%2fhow-encryption-in-sql-login-authentication-works%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
