locked
Is the PasswordHash comparison happens at client level or DB level? RRS feed

  • Question

  • hi friends,

    i still have an issue with the user authentication process, which i though was solved by answers provided by Jesús López in the thread How to handle login requests

    But questions have risen so i need to clarify a few things about user authentication, because i want to know hows its done properly,

    I was told by someone that the authentication is a process that concerns about security. So the authentication process should be hidden from the client as possible and the client should receive only a flag value (1= password is correct and authenticated, -1= incorrect password) and/or user name of the authenticated user. So it should be a SQL operation that should occur in the back end. So the process in detail should be, client form sends user name and hashed /encrypted password to SQL, SQL authenticates and it sends 1/-1 with the user name to the client as the return value and the output.

    But when i search online, on user authentications, that was not the case, all the threads i have seen on web, either the app is windows or web, developers do two operations:

    1. In SQL server, the saltHash value of the password is searched by user name then return the saltHash value along with the PasswordHash to the client form / or to the business layer

    2. In the client or in BLL, Use the entered password and the returned saltHash value to generate a PasswordHash and then compare this passwordHash with the returned passwordHash. if match password is correct if not password is incorrect.

    Is this actually how developers write code to authenticate a user in a login event? If so basically the password comparison happens at the client side(either in the form or in the business layer) then wouldn't this be huge security risk by exposing the actual saltHash and passwordHash to the client?

    if some one could write me some code to demonstrate SQL and C# the login / authentication process, would be a delight!

    PS- when you reply please do not use LINQ, LINQ to SQL or entity framework. At the moment i dont know them. still learning them. Please reply from Ado .NET

    thanks


    I use Visual studio 2010 Ultimate and SQL server 2008 developer edition!

    • Moved by Kalman Toth Friday, June 20, 2014 9:01 PM Not T-SQL
    Thursday, June 19, 2014 3:15 AM

Answers

  • Somehow you got on the wrong track.

    What you are describing here and also what was the topic of that thread you refer to, was some kind of custom security mechanism which a developer implemented without using SQL Server Security mechanisms.

    With all the good and bad.

    If you leave authentication and permission to SQL Server it's completely different. SQL Server authenticates the specific account and handles everything at the server itself if it is a SQL Account" - if using "Windows Accounts for authentication a Domain Controller also comes into play.

    You can read more about SQL Server authentication mechanism and security here:

    Authentication in SQL Server


    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com

    • Proposed as answer by Kalman Toth Saturday, June 21, 2014 12:17 PM
    • Marked as answer by Elvis Long Monday, June 30, 2014 2:10 AM
    Saturday, June 21, 2014 12:08 PM
  • Is this actually how developers write code to authenticate a user in a login event? If so basically the password comparison happens at the client side(either in the form or in the business layer) then wouldn't this be huge security risk by exposing the actual saltHash and passwordHash to the client?

    It would be a security risk if "client" means the browser.  The layer that performs the salt/hash/compare operations can be implemented on the web server but is often implemented in a non-public facing mid-tier service as to follow layered security best practices.  If you implement this in the database tier, SQLCLR would need to be used to generate the salt with System.Security.Cryptography.RNGCryptoServiceProvider and all communication from the end user client to the database server needs to be encrypted to protect the clear text password.

    See http://www.codeproject.com/Articles/704865/Salted-Password-Hashing-Doing-it-Right for code examples.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com


    • Edited by Dan GuzmanMVP Saturday, June 21, 2014 12:39 PM spelling
    • Marked as answer by Elvis Long Monday, June 30, 2014 2:11 AM
    Saturday, June 21, 2014 12:38 PM

All replies

  • I am moving it to security to get an answer.

    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012






    Friday, June 20, 2014 9:01 PM
  • Somehow you got on the wrong track.

    What you are describing here and also what was the topic of that thread you refer to, was some kind of custom security mechanism which a developer implemented without using SQL Server Security mechanisms.

    With all the good and bad.

    If you leave authentication and permission to SQL Server it's completely different. SQL Server authenticates the specific account and handles everything at the server itself if it is a SQL Account" - if using "Windows Accounts for authentication a Domain Controller also comes into play.

    You can read more about SQL Server authentication mechanism and security here:

    Authentication in SQL Server


    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com

    • Proposed as answer by Kalman Toth Saturday, June 21, 2014 12:17 PM
    • Marked as answer by Elvis Long Monday, June 30, 2014 2:10 AM
    Saturday, June 21, 2014 12:08 PM
  • Is this actually how developers write code to authenticate a user in a login event? If so basically the password comparison happens at the client side(either in the form or in the business layer) then wouldn't this be huge security risk by exposing the actual saltHash and passwordHash to the client?

    It would be a security risk if "client" means the browser.  The layer that performs the salt/hash/compare operations can be implemented on the web server but is often implemented in a non-public facing mid-tier service as to follow layered security best practices.  If you implement this in the database tier, SQLCLR would need to be used to generate the salt with System.Security.Cryptography.RNGCryptoServiceProvider and all communication from the end user client to the database server needs to be encrypted to protect the clear text password.

    See http://www.codeproject.com/Articles/704865/Salted-Password-Hashing-Doing-it-Right for code examples.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com


    • Edited by Dan GuzmanMVP Saturday, June 21, 2014 12:39 PM spelling
    • Marked as answer by Elvis Long Monday, June 30, 2014 2:11 AM
    Saturday, June 21, 2014 12:38 PM