encrypting password ms access db

Posted on 2012-08-21
Last Modified: 2012-08-27
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!!
Question by:russell12
    LVL 11

    Expert Comment

    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 ...
    LVL 2

    Author Comment

    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!
    LVL 11

    Expert Comment

    >> 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!! ...
    LVL 11

    Expert Comment

    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. :-/ ...
    LVL 2

    Author Comment

    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.
    LVL 11

    Accepted Solution

    >> 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}
    LVL 2

    Author Closing Comment

    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!!

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now