How does "Be made using the login's current security context" pass the users password to remote server

Say you have a SQL Server with a "Linked Server" to another server - said "Linked Server" is set to use "Be made using the login's current security context" as its authentication model.

How does the SQL Server provide the correct password (when dealing with non-windows credentials) to the other server when all the server has is a Hash.

I have transferred logins from server to server before and i know that the hash alone is stored in SQL and this is often presented as a hex value hex when creating logins with passwords, for example:

create login test with password = '0x22A9AE652CFC38938D56A9C3872B266B192D16E4' hashed

If SQL only has a hash available - can it logon to the remote server using the hash? Or is the original (un-hashed) password kept in the user’s session / memory for the entire duration of their connection which SQL can then retrieve and pass to the remote server for login?

If the server can login with a hash instead of the password – can this be done in normal logins or is it purely an internal feature of linked servers?

Purely a curiosity question – like to understand how these things work.

Whilst liasing with Microsoft on a different issue (paid support request) i happened to ask them about this & they confirmed the unhashed password is passed to the remote server but the mechanism in which the SQL Engine does this is "hidden and cannot be captured" - but suffice to say, the login is not done using the hash.

Here is there full response:


How does the SQL Server provide the correct password (when dealing with non-windows credentials) to the other server when all the server has is a Hash?


Answer: If connected to the local server using SQL Server
Authentication, login name and password will be used to connect to the
remote server. In this case a login with the exact same name and
password must exist on the remote server.


Be made using the login's current security context:


Specify that a connection will be made using the current security context of the
login for logins not defined in the list. If connected to the local
server using Windows Authentication, your windows credentials will be
used to connect to the remote server. If connected to the local server
using SQL Server Authentication, login name and password will be used
to connect to the remote server. In this case a login with the exact
same name and password must exist on the remote server.


How does the SQL Server provide the correct password (when dealing with non-windows credentials) to the other server when all the server has is a Hash?


Answer: Login name and password is passed to the remote server, this
mechanism/task is handled by the SQL Engine which is hidden and cannot
be captured.


If SQL only has a hash available - can it logon to the remote server
using the hash? Or is the original (un-hashed) password kept in the
user’s session / memory for the entire duration of their connection
which SQL can then retrieve and pass to the remote server for login?
If the server can login with a hash instead of the password – can this
be done in normal logins or is it purely an internal feature of linked
servers?


Answer: HASH cannot be used to login to the SQL server, SQL Engine is
responsible for handling login process and we don’t have much
documentation around this.

StackExchange Database Administrators Q#41136, answer score: 5