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

Question

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.

Answer 1

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.

Answer 2

I do believe that the protocol allow the exchange of username and hashed password. This is probably used when connecting to linked server, but cannot be used when connecting from a third application.

For sure, the database engine internally store username and hashed password only. This is also confirmed in http://msdn.microsoft.com/en-us/library/bb669066.aspx . I don't believe that the clear password is stored anywhere "in the user's session".

The hashed password is used even when dumping all user details in SQL language. This is useful for cloning users to a different instance. So, the engine was forced to export the CREATE LOGIN accepting the hashed password as well.

Answer 3

You have to give username password(non hashed) of remote server when setting up the linked server.

When giving non windows username, password should be same on both servers to work!