Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Is the local server using windows integrated authentication? –  Chris Travers Apr 29 '13 at 10:33
    
Hi Chris, both servers are using mixed mode authentication - but as stated in my question - this is in relation to a non-windows credential (i.e. a sql credential) connection - cheers –  HeavenCore Apr 29 '13 at 11:05
add comment

3 Answers

up vote 3 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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!

share|improve this answer
4  
You dont have to give a username & password of the remote server when setting up a linked server at all? thats the whole point of the @useself parameter on sp_addlinkedsrvlogin - hence my question :) –  HeavenCore Apr 29 '13 at 12:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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