encrypting password ms access db

I am trying to make a password field encrypted, and have spent a couple of hrs looking for the solution, and can not!  I have a table with a field (password) and the datatype is nvchar(50).  Can someone point me in the correct direction to do a simple encryption.  I am looking for something that is going to not be complex.  I would even settle for 64bit encryption.  I do not believe the people using this software will know enough as to where to look for the password, but you never know.  Any help is greatly appreciated!!
Who is Participating?
>> do i need to write another fuction for decoding the hash also, or do i just call the same function. <<

With a hash scenario you don't (supposedly can't) decode the hash back to its original string.  The process by which you'd use the hash is this ..

Username (Text)
hPassword (Text) {this is the hashed password --- hPassword.  The result of a readable password (which I will call rPassword) string being hashed}
hUsernamePwd (Text) {Optional. But would be the result of (Username & rPassword) being hashed}

Then when enter information into tblUsers you would have a Form with two unbound text boxes, one for the username (control name of txtUsername) and one for the rPassword (control name of txtPassword).  Then you would have a command button that would append the new user into the system ...

Private Sub cmdSomeButton_Click()
    Dim strSQL = "INSERT INTO tblUsers (Username, hPassword, hUsernamePwd)" & _
                 " VALUES ('" & Me.txtUsername & "','" & Hash(Me.txtPassword) & "','" & Hash(Me.txtUsername & Me.txtPassword) & "')"
    CurrentDb.Execute strSQL, dbFailOnError
End Sub

Open in new window

Then to validate the user upon logon, you would have a form that looks similar with two text boxes (txtUsername, txtPassword) and a button with some code that looks something like this:

Public Sub cmdLoginButton()
    Dim strSQL As String
    strSQL = "SELECT * FROM tblUsers" & _
             " WHERE Username = '" & Me.txtUsername & "'" & _
             " AND hPassword = '" & Hash(Me.txtPassword) & "'" & _
             " AND hUsernamePwd = '" & Hash(Me.txtUsername & Me.txtPassword) & "'"
    With CurrentDb.OpenRecordset(strSQL)
        If .EOF Then
            MsgBox "Your login credentials were not valid. Please try again."
            'Logon succesful
            <initilization code goes here
        End If
    End With
    DoCmd.Close acForm, Me.Name
End Sub

Open in new window

So ... the idea is that the code and database work with hPassword and only the user provides you with the rPassword.

Did that make sense?

{disclaimer: code is AIR CODE meant to show the framework of a technique}
I would consider implementing a one-way hash algorith.  What that means is that the user puts in a password and it is one way hashed, then that hash result is stored.  Then when the user logs on you would ask for the pwd, the user would type it in, then you would hash their input and try to look up the hashed value in the table you speak of.

One such algorith can be adapted to Access/VBA can be found here ...

russell12Author Commented:
Thanks for the quick response, but I am writing this program in access, and have sessions created when users log in and the sessions form relies on the log in form, so I could not use this.  But this is the direction i am looking for!
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

>> Thanks for the quick response, but I am writing this program in access, and have sessions created when users log in and the sessions form relies on the log in form, so I could not use this.  <<

I know that you are in Access, and while the sample is in VB and VBScript, it ports to Access/VBA (IIRC) .... but if you don't want that kind of complication (I know I personally did not for my situation), then you may be able to use the following, very simple, hashing code ...

Public Function Hash(ByVal strHashThis As String) As Long

   Dim i As Long, h As Long
   Dim lngTemp As Long

   h = Len(strHashThis)
   For i = 1 To h
       lngTemp = (Asc(Mid$(strHashThis, h - i + 1, 1)) + Asc(Mid$(strHashThis, i, 1))) Xor lngTemp
   Next i

   Rnd -1

   Randomize lngTemp

   Hash = (Rnd() * &H7FFFFFFF) And &HFFFFFFFF

End Function

Open in new window

For your purpose it may work just fine, but please note that this routine will hash different strings to the same hash value.  While the occurrance of that phenomenon is in-frequent, it does occur.

Despite this, I personally have used the above code quite successfully for years with a UserName, hPassword type table.  I figure that I really don't care if two pwd's hash to the same value because I am doing a lookup based on UserName & Password and since the username is the key to a unique index, I have not been troubled by duplicate hash values.

In the event that it would become an issue, I would likely just add a third column to my table that is the result of a hash using the username and password (hUserPwd), then do a lookup on Username, hPassword, and hUserPwd where UserName is the readable string of username, hPassword is the hashed password, and hUsePwd is the username concatenated to the pwd then hashed.

I hope that made sense!! ...
Oh ...

Here is a site I just found that has some different encrypting code samples -- with VB6/VBA (in case you want stronger than my posted Hash() function.


Once there, you can d/l the original source code then take a look at the sample implementations of the functions.

Edit ...
Scratch that ... after looking at it longer it does not seem to be complete. :-/ ...
russell12Author Commented:
Thank you for the reply.  I am going to work on this program when i get off work tonight.  I do believe one of the other will be the answer, but looking at this, the only question i am going to have is do i need to write another fuction for decoding the hash also, or do i just call the same function.  Thanks for your help and like i said i will be rewarding u point after i try this.  I know one or the other will work.
russell12Author Commented:
Work perfect!!!!!!  Sorry for the extended time to give you points, I work in the I.T. department and I am over 6 stores, and the night I told you I was going to go home and give u points, we had 2 stores networks go down.  Spent many hrs working on the issues, and I just got the chance to utilize this.  So again I do appologize for the delay, but your code works perfectly.  The only thing I had to change was on:

With CurrentDb.OpenRecordset(strSQL)

It was throwing an error code of 3622 'You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column.'

After some research, this seemed to do the trick:

With CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

Thanks again!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.