I finally succeeded in encrypting the user passwords in my back end Sql Server database Login table using SHA2_512.
Can someone please point me in the right direction tp decrypt it via my front end Access interface? What would the query look like?
Below is what the data in the linked Sql Server table looks like.
I couldn't find any useful info in my web searches thusfar. Any help will be greatly appreciated!
You don't decrypt hashes--you would encrypt the user supplied password and see if it matches the hash you have stored.
Take whatever the user gives you as username to look up the hashed password of that user (DatabasePassword).
Use your existing encoding function to SHA2_512 the password the user used to sign on with (HashedSignOnPassword).
If HashedSignOnPassword equals DatabasePassword they used the right password.
For various reasons, as noted in the other replies, you don't decrypt passwords. You store the hash of the original password. Then at login you encrypt the offered password the same way. If the two hashes match, it's a match. SHA256 was what the U.S. Navy used for most of their password hashing in 2016 when I retired.
Hashing is a one-way encryption algorithm that is not designed to be reversible. Full-on keyed encryption using public/private key pairs is reversible and is used for file encryption/decryption via any of several Hermetian algorithms.
As a fine point: Please note also that even if we KNEW an algorithm that could reverse a hash, there is a legal issue that stops us from discussing it. Careless revelation of such an algorithm could result in shutting down this site for spreading hacker information.
Jon has worked hard to get this site to its current quality level and popularity. There should be no hacker tools involved here. In fact, if any of the moderators saw that someone directly posted hacker tools or direct links to same, I think we have a moral obligation to redact such posts.
Before we get too far into this, I just have a quick question. If you're using SQL Server for your backend, why do you feel like you have to create your own user password management when SQL Server already has one that you can use?
Not sure I understand your question DBguy. I need to validate the password that the user enters in the front end interface of my MS Access program - and I don't know how to do that. The password is encrypted SHA2_512 in a back end SqlServer table.
Not sure I understand your question DBguy. I need to validate the password that the user enters in the front end interface of my MS Access program - and I don't know how to do that. The password is encrypted SHA2_512 in a back end SqlServer table.
Well, in order to connect o SQL Server, don't your users already have to enter their username and password first? That is necessary even before you added your own username and password form. Right?
You generate the SHA-512 hash of whatever the user entered for the password when trying to log in. If it is identical to the stored hash in the database table, the user entered the correct password.
Nonetheless, @theDBguy has a valid point. Your users must authenticate to SQL Server anyway, so you should know their identity already and an additional user/pwd verification in the context of your application appears to be redundant.
So, I, and probably several other people, assumed you knew how to compute a hash.
SQL Server has the built-in Hashbytes function, which supports SHA-512.
But for password hashing I would recommend to substitute the clear-text password with the hash at the earliest opportunity. In this scenario this would be before it even gets send over the wire to SQL Server.
To compute the hash in VBA you can use my WinApi based hashing Module.
Yes. But how is that relevant?
I do know how to compute the hash. That's how I was able to encrypt it.
But I don't understand how to validate it from my Access front end. I downloaded your bas file (thank you for making this available!) but I must believe that there must be a way simpler way to validate. It would be easier for me to write my own encryption program which, actually, I already started and just about completed. But I still feel like I'm missing something very simple. I appreciate your help!
A user enters their username and password.
You compute the hash of what the user entered for their password.
You run a query against your user/password-hash table in the database using the entered user name and the computed password hash as criteria.
-> If there is a match, the user entered the correct password.
Please, stop!
Unless one is an experienced computer security engineer, nobody should write their own encryption algorithms for production use. This is most likely not going to end well and there is a number of solid and thoroughly vetted encryption algorithms already available.
I appreciate your time and advice with this sonic8!
It is necessary to validate the user again in the app to ensure that he has been permitted access to this specific Access application. Are you suggesting that a login screen along with its validation process is not necessary when using SqlServer as a back end? Eliminate the login screen completely? I hadn't considered that. Interesting.
You run a query against your user/password-hash table in the database using the entered user name and the computed password hash as criteria.
This is what I'm trying to learn how to do. How can I compare the newly entered password after it has been encrypted to the original? Is this done on the SqlServer side? If so, do I pass a value back to my app which identifies a match or no match? If so, how do I pass this value to my Access front end?
You need to study the concept of "trust" as applied to security considerations. If you are in a relatively tightly secured domain, you can look at the user's domain name because in order to HAVE that name, the user already presented a correct password. You can (here's how you use the word...) TRUST the domain's security to identify the user. Oh, you can still look up the username in a table that tells you what features he can use, and you can have a startup form that tests the username in its Form_Open routine to intercept unauthorized users by setting the Cancel parameter = 1 (or -1) to cancel opening your startup form. If you do that, you can include an Application.Quit as part of the termination code.
Look up this topic in the forum: "Secure a Database" (might also look up "Securing a Database") You will get quite a few articles on the subject of making the database harder to use if you aren't on the authorized user list. Our user Isladogs has published several articles on security as well.
Are you suggesting that a login screen along with its validation process is not necessary when using SqlServer as a back end? Eliminate the login screen completely?
A dedicated login dialog for the application may not be necessary.
If a Computer is only used by the one distinct person being logged into Windows, then any further login dialog would be redundant and can be replaced by just using the Windows user name without further user interaction. SQL Server is not necessarily required in this scenario, but it makes it even easier without being less secure because SQL Server inherently is aware of the Windows user identity, when using Windows authentication.
If a computer with active Windows login is shared by multiple persons, e.g. in a workshop setting, then you cannot rely on the Windows user name and still must identify the current user by other means, e.g. a application login screen.
This is what I'm trying to learn how to do. How can I compare the newly entered password after it has been encrypted to the original? Is this done on the SqlServer side? If so, do I pass a value back to my app which identifies a match or no match?
It can be done on the SQL Server side. However, this implies that the clear text password is passed to SQL Server first, which is not ideal because even if the connection is encrypted, the password may still be visible to administrators in SQL Server monitoring tools (e.g. SQL Server Profiler).
For this reason I recommend to compute the hash of the password as early as possible with VBA and only then pass the hash to the server for verification.
Assuming a login form with two textboxes txtUsername, txtPassword, and a button cmdLogin; the table tblUserPass must be linked into the current Access file:
Code:
Private Sub cmdLogin_Click()
Dim hashedPassword as String
hashedPassword = ComputeSha512HashString(Me.txtPassword.Value)
If DCount("*", "tblUserPass", "Username = '" & Me.txtUsername.Value & "' AND PasswordHash = '" & hashedPassword & "'") > 0 Then
MsgBox "Congratulations, you successfully authenticated yourself!"
Else
MsgBox "Sorry. Wrong User/Password combination. "
End If
End Sub
(This code was only written here in the post, it is only intended to illustrate the general approach.)
@sonic8 : Please take into account, that @TheSearcher uses a salt additionally, at least that's what it looks like, as he has listed it in his exemplary table.
So assuming that this salt is concatenated to the users password before hashing, VBA would need to read this salt if it should calculate the hash. If it will be done on the server, the password has to be transferred there.
So one of both 'secrets' has to be transmitted in clear text at all before hashing and a transport encryption would be advisable.
In my eyes, the lesser evil is to transfer the salt. But it has to be done in his scenario.
Of course, the computation of the hash during password verification must follow the same rules as the original hash computation.
I've got the impression, that in the current context of this thread it is more important to clarify the basic principle instead of addressing all potential details.
Whether the salt is a secret or not is debatable. - I'm not going to engage in that debate.
In any case, once an attacker got hold of the clear text password, the salt becomes irrelevant. So, protecting the salt is definitely of lesser importance than protecting the password itself.
Okay. I have everything in my interface working now thanks to all of you!
My problem now is that the SHA_512 hash returned by Sql Server is completely different than the SHA_512 hash returned by the online hash calculators. Example: For the string 'joseph' Both
and https://md5calc.com/hash/sha512return:
24d6edb8dbe2bca27cfefc1b0682bb7c34a0f5aa23ec6ea2724cc0dbfdbb9eb3d6bc90fea92b84c0820db784319ed53e6fb5de74b19c36fede0c594d374792cd
However, this line of code in my SqlServer stored procedure which creates the hash:
creates a hash value of:
0x866D9B1682FB5DB56DDD860C95D03447B7120B8E66481DA9FE65AC1FA85C0BEB36F0D1C653E3EE97E832C0D753263CBC8140CAD3F0881921A5E18DC8D93DE541
And to make matters more interesting, when I run this query in SqlServer it returns the same as the online calculators with an additional 0x at the beginning.