Access 2010 user level password security

IF ... you are still in MDB format inside of A2010, then ULS is still supported - but not in ACCDB format.

So ... in A2010 - you are left with only the revamped Database Password.

If you do move to the .accdb format:

What was the purpose of ULS? If it's for form navigation (i.e. UserA can open FormB but not FormC) then you'd have to build your own navigation scheme. Peter's Software has LASsie (http://www.peterssoftware.com/las.htm) that can be used for things like this, or you can "roll your own".

If you need data security, then you should use the builtin features of SQL Server for this.

If I use the SQL Security client, it only verifies that the user has access right to the SQL DB,
Can it be made to ask for the user password from windows?

SQL Server security does much, much more than just determine access rights. A user can have the ability to connect to a database, but only read certain tables/views (for example). Or they can have Update but not Insert rights.

This is something you can configure, but it takes some time to do, and generally you create an application AFTER deciding how to secure the data, not the other way around. I realize you don't have much choice in this, since the Access app is already done, but doing it "backwards" results in much more work, generally speaking.

Can it be made to ask for the user password from windows?

SQL Server can use "mixed authentication", where you can have a mix of Trusted Users and SQL Authentication. With SQL Authentication, the user is required to log into the server with their username and password (which can be the same as their Windows credentials). There is a builtin prompt for this - you can simply remove all the linked tables from the database, and then relink them - but do NOT check the "save password" box when doing so. This creates the link, but everytime the user opens the app they'd have to enter their password.

SQL is already check the user ID's, but the Access frontend need to also check the user ID and password which is different from windows.  Otherwise any user that has users right to the SQL backend tables will also have access to other user Data if the login with that other user ID.  So I need to have it check a second passsword in the frontend program?

I have the two tables in the Frontend access with password and user ID's.
I have it checking the User ID = valid, now just need to add the Password part to validate, or if need to change password.

So you're storing these values in a table somewhere?

If so, you can validate the password like this:

Dim sPass As String
sPass = Nz(DLookup("YourPasswordField", "YourTable", "YourUserName='" & TheUserName & "' AND YourPassword='" & ThePassword & "'"), "")

If sPass = "" Then
  '/ the password did not match
Else
  '/ the password did match
End If

Obviously you'd have to change table, field and control names to match your project.

See here for more information on DLookup: http://office.microsoft.com/en-us/access-help/dlookup-function-HA001228825.aspx

This is the code I am running:  Checking the User HMF id works perfect, just when I get to the Password section it does not verify the hmf id  and password ?
**************************************************************
Private Sub CMDenter_Click()
    Dim rst    As DAO.Recordset
    Dim rstp    As DAO.Recordset
   
    '/ did the user enter anything in the username and password field?
    On Error GoTo CMDenter_Click_Error

    If IsNull(Me.Txt_User) Then
        MsgBox "Enter User Name", vbCritical, "Access Denied"
        Me.Txt_User.SetFocus
        Me.Txt_User.Undo
    Else
        '/ user entered a name in txt_user, so validate it
        Set rst = CurrentDb.OpenRecordset("SELECT * FROM Users WHERE hmf_id='" & Me.Txt_User & "'", dbOpenDynaset, dbSeeChanges)

        If (rst.EOF And rst.BOF) Then
            '/ no match found
            MsgBox "Invalid User Name", vbCritical, "Access Denied"
            DoCmd.GoToControl ("Txt_User")
            Me.Undo
            Me.[Txt_User] = ""
        Else
            If rst("Is_Active") = 0 Then
                '/ found a match, but user is not Active
                MsgBox "Account Deactivated"
                DoCmd.GoToControl ("Txt_User")
                Me.Undo
                Me.[Txt_User] = ""
        Else
****************************************************************************
above works fine

Below From here is where is is not finding and test the User id and password
****************************************************************************
  Set rstp = CurrentDb.OpenRecordset("SELECT * FROM Users WHERE password='" & Me.txtPassword & "'", dbOpenDynaset, dbSeeChanges)
   
            'Check for correct password
    If rst([HMF_ID]) = ("Txt_User") And rstp([Password]) = ("txtPassword") Then
        'Check if password needs to be reset
            If rstp("PWReset") = True Then
                DoCmd.OpenForm "frmPasswordChange", , , "[hmfID] = " & ("txt_User")
           
            'DoCmd.RunMacro "CleanHMFTBLS"
             'DoCmd.OpenForm "frm_Main"
            'Me.Visible = False
        Else
            MsgBox "Password does not match, please re-enter!", vbOKOnly
            Me.txtPassword = Null
            Me.txtPassword.SetFocus
            End If
************************************************************************
To here
****************************************************************************            
    End If
        End If
            '/ found a match, so run your macro
               'DoCmd.RunMacro "MKHMFTBL"
               DoCmd.RunMacro "CleanHMFTBLS"
            End If
   

        Set rst = Nothing
    End If

CMDenter_Click_Exit:
    On Error Resume Next
    Exit Sub

CMDenter_Click_Error:
    Select Case Err.Number
            'Case 0
            'Case 1
        Case Else
            MsgBox "Error " & Err.Number & ": " & Err.Description & vbCrLf & vbCrLf _
                   & "Occured in Module1:CMDenter_Click", vbOKOnly + vbExclamation, "Error"
            Resume CMDenter_Click_Exit
    End Select

End Sub




Any suggestions?

I also tried to check the Password with this:

Private Sub txtpassword_AfterUpdate()
 Dim rst    As DAO.Recordset
 Dim rstp    As DAO.Recordset
 
     Set rst = CurrentDb.OpenRecordset("SELECT * FROM Users WHERE hmf_id='" & Me.Txt_User & "'", dbOpenDynaset, dbSeeChanges)
     Set rstp = CurrentDb.OpenRecordset("SELECT * FROM Users WHERE password='" & Me.txtPassword & "'", dbOpenDynaset, dbSeeChanges)
   
            'Check for correct password
    If rst([HMF_ID]) = ("Txt_User") And rstp([Password]) = ("txtPassword") Then
        'Check if password needs to be reset
            If rstp("PWReset") = True Then
                DoCmd.OpenForm "frmPasswordChange", , , "[hmfID] = " & ("txt_User")
           
            'DoCmd.RunMacro "CleanHMFTBLS"
             'DoCmd.OpenForm "frm_Main"
            'Me.Visible = False
        Else
            MsgBox "Password does not match, please re-enter!", vbOKOnly
            Me.txtPassword = Null
            Me.txtPassword.SetFocus
            End If
           
    End If
End Sub


*******************************

But stops at the First IF statement not finding the First field?

YEs we did , and thanks again for the first part.  Now I need to add the Password check.
I'll try this code in my vb this morning.

Tried it and get the following error message:

Runtime error 2465
LEAP can't fine the field'|1' referred to in your expression

Same message I was getting before

Try this:

 If rst("HMF_ID") = Me.Txt_User And rst("Password") = Me.txtPassword Then

Tried it but does not work.  Atatched is the code that is working to a point and I get a error 3075,  it find the Field values but gives me this error?
See attached.
3075-error-issues-with-password-.docx

Add the word "AND" as below:

Set rstp = CurrentDb.OpenRecordset("SELECT * FROM Users WHERE hmf_id='" & Me.Txt_User & " AND password='" & Me.txtPassword & "'", dbOpenDynaset, dbSeeChanges)

Tried it but still getting a 3075 error missing operator?
my current code:

 Set rstp = CurrentDb.OpenRecordset("SELECT * FROM Users WHERE hmf_id='" & Me.Txt_User & " AND password='" & Me.txtPassword & "'", dbOpenDynaset, dbSeeChanges)
            'Check for correct password
      If rstp([HMF_ID]) = Me.Txt_User And rstp([Password]) = Me.txtPassword Then
         
        'Check if password needs to be reset
            If (rst([PWReset]) = 1) Then
                DoCmd.OpenForm "frmPasswordChange", , , "[hmfID] = " & ("txt_User")
           
            MsgBox "Password does not match, please re-enter!", vbOKOnly
            Me.txtPassword = Null
            Me.txtPassword.SetFocus
            End If
           
    End If

Try this:

 Set rstp = CurrentDb.OpenRecordset("SELECT * FROM Users WHERE hmf_id='" & Me.Txt_User & "' AND password='" & Me.txtPassword & "'", dbOpenDynaset, dbSeeChanges)

Note too that we do expect you to try to resolve these sorts of issues on your own - for example, Text values must be enclosed in single or double quotes, so "missing operator" would generally give you a clue that a delimiting marker is missing.

I have been working and trying everything I can think of. I have been researching the internet for answers,  I am not as good as you are in this area.
And I appreciate all the help you have been given me.

I have been working on this, but now it gets stuck on invalid password?
If I enter in the wrong password it displays invalid password message.  Hit enter and goes back to the login form, then if I enter the correct password it comes back again with the invalid password message?

This is my current code:
*****************************************
Private Sub CMDenter_Click()
    Dim rst    As DAO.Recordset
    Dim rstp    As DAO.Recordset
    Dim pfoundmatch As Boolean
   
 
   
    '/ did the user enter anything in the username and password field?
    'On Error GoTo CMDenter_Click_Error

    If IsNull(Me.Txt_User) Then
        MsgBox "Enter User Name", vbCritical, "Access Denied"
        Me.Txt_User.SetFocus
        Me.Txt_User.Undo
    Else
        '/ user entered a name in txt_user, so validate it
         Set rst = CurrentDb.OpenRecordset("SELECT * FROM Users WHERE hmf_id='" & Me.Txt_User & "'", dbOpenDynaset, dbSeeChanges)

        If (rst.EOF And rst.BOF) Then
            '/ no match found
            MsgBox "Invalid User Name", vbCritical, "Access Denied"
            DoCmd.GoToControl ("Txt_User")
            Me.Undo
            Me.[Txt_User] = ""
        Else
            If rst("Is_Active") = 0 Then
                '/ found a match, but user is not Active
                MsgBox "Account Deactivated"
                DoCmd.GoToControl ("Txt_User")
                Me.Undo
                Me.[Txt_User] = ""
        Else
 
      Set rstp = CurrentDb.OpenRecordset("SELECT * FROM Users WHERE hmf_id='" & Me.Txt_User & "' AND password='" & Me.txtPassword & "'", dbOpenDynaset, dbSeeChanges)
  pfoundmatch = False
       
      If (rst.EOF And rst.BOF) Then
            'Check for correct password
        If (rstp("HMF_ID") = ("Txt_User") = True And (rstp("Password") = ("txtPassword"))) Then
            pfoundmatch = True
        End If
       
         If pfoundmatch = True Then
   
            '/ found a match, so run your macro
              DoCmd.RunMacro "CleanHMFTBLS"
            Set rst = Nothing
            Set rstp = Nothing
              End If
        Else
            MsgBox "Invalid password", vbCritical, "Access Denied"
                DoCmd.GoToControl ("Txtpassword")
                Me.Undo
                Me.[txtPassword] = ""
        End If
     
       
     
        'Check if password needs to be reset
           'If rstp("PWReset") = True Then
            '    DoCmd.OpenForm "frmPasswordChange", , , "'hmf_id='" & ("txt_User")
        'Else
         '   MsgBox "Password does not match, please re-enter!", vbOKOnly
          '      Me.txtPassword = Null
           '     Me.txtPassword.SetFocus
        'End If
       
   
         
        End If
           
    End If
   
End If
 
End Sub

Can any help issue?  Just need this one issue fixed, I can not seem to find the issue, Already fix the RST to RSTP, but it still have at the Invalid password message.  I do not think it is really checking the Password value?

This line is still wrong:

 If (rstp("HMF_ID") = ("Txt_User") = True And (rstp("Password") = ("txtPassword"))) Then

Essentitally, you'are asking is some value named "TXT_User" is equal to True, and that will never occur.

I think it should be:

 If (rstp("HMF_ID") = Me.Txt_User And (rstp("Password") = Me.txtPassword)) Then

Tried that and now I get error 3021 no current record:

Current code:
****************************
Private Sub CMDenter_Click()
    Dim rst    As DAO.Recordset
    Dim rstp    As DAO.Recordset
   
    '/ did the user enter anything in the username and password field?
    On Error GoTo CMDenter_Click_Error

    If IsNull(Me.Txt_User) Then
        MsgBox "Enter User Name", vbCritical, "Access Denied"
        Me.Txt_User.SetFocus
        Me.Txt_User.Undo
    Else
        '/ user entered a name in txt_user, so validate it
         Set rst = CurrentDb.OpenRecordset("SELECT * FROM Users WHERE hmf_id='" & Me.Txt_User & "'", dbOpenDynaset, dbSeeChanges)

        If (rst.EOF And rst.BOF) Then
            '/ no match found
            MsgBox "Invalid User Name", vbCritical, "Access Denied"
            DoCmd.GoToControl ("Txt_User")
            Me.Undo
            Me.[Txt_User] = ""
        Else
            If rst("Is_Active") = 0 Then
                '/ found a match, but user is not Active
                MsgBox "Account Deactivated"
                DoCmd.GoToControl ("Txt_User")
                Me.Undo
                Me.[Txt_User] = ""
        Else
       
       
        If IsNull(Me.txtPassword) Then
            MsgBox "Enter Your Password", vbCritical, "Access Denied"
            Me.txtPassword.SetFocus
            Me.txtPassword.Undo
        Else
         Set rstp = CurrentDb.OpenRecordset("SELECT * FROM Users WHERE hmf_id='" & Me.Txt_User & "' AND password='" & Me.txtPassword & "'", dbOpenDynaset, dbSeeChanges)
         
         If (rstp.EOF And rstp.BOF) Then
******************************************************************
Somewhere here is where it goes off
****************************************************************        
       
  'Check for correct password
      'If (rstp("HMF_ID") = ("Txt_User")) And (rstp("Password") = ("txtPassword")) Then
       If (rstp("HMF_ID") = Me.Txt_User And (rstp("Password") = Me.txtPassword)) Then
             
             MsgBox "Invalid password", vbCritical, "Access Denied"
                DoCmd.GoToControl ("Txtpassword")
                Me.Undo
                Me.[txtPassword] = ""
         Else
             
             DoCmd.RunMacro "CleanHMFTBLS"
           
        'Check if password needs to be reset
           'If rstp("PWReset") = True Then
            '    DoCmd.OpenForm "frmPasswordChange", , , "'hmf_id='" & ("txt_User")
          'Else
          '  MsgBox "Password does not match, please re-enter!", vbOKOnly
          '      Me.txtPassword = Null
          '      Me.txtPassword.SetFocus
         'End If
       
       
            '/ found a match, so run your macro
               'DoCmd.RunMacro "MKHMFTBL"
              ' DoCmd.RunMacro "CleanHMFTBLS"
            End If
    End If
    End If
End If

        Set rst = Nothing
        Set rstp = Nothing

CMDenter_Click_Exit:
    On Error Resume Next
    Exit Sub

CMDenter_Click_Error:
    Select Case Err.Number
            'Case 0
            'Case 1
        Case Else
            MsgBox "Error " & Err.Number & ": " & Err.Description & vbCrLf & vbCrLf _
                   & "Occured in Module1:CMDenter_Click", vbOKOnly + vbExclamation, "Error"
            Resume CMDenter_Click_Exit
    End Select
End If
End If
End Sub

This line:

 If (rstp.EOF And rstp.BOF) Then

should be

 If NOT (rstp.EOF And rstp.BOF) Then

Otherwise, you're trying to read a recordset that is at EOF and BOF ... and you can't do that.

Put that in but now It Seems that it is not seeing the Password value correctly,
If I type in the Correct password it comes back with invalid password message?

If I type in the wrong password does nothing?

I have it working with the correct password now,
But if I type in the wrong password it does not show the message?

This is what I have now working with the correct password, but if I type in the wrong password the message does not show?  Do not know why it is not showing?

***********************************************************************

Set rstp = CurrentDb.OpenRecordset("SELECT * FROM Users WHERE hmf_id='" & Me.Txt_User & "' AND password='" & Me.txtPassword & "'", dbOpenDynaset, dbSeeChanges)
         
       
         If Not (rstp.EOF And rstp.BOF) Then

'*****************************************************************************************************
  'Check for correct password
         If (rstp("HMF_ID") = Me.Txt_User And (rstp("Password") = Me.txtPassword)) Then
            DoCmd.RunMacro "CleanHMFTBLS"
         Else
            MsgBox "Invalid password"
                DoCmd.GoToControl ("Txtpassword")
                Me.Undo
                Me.txtPassword = ""
           End If
                 
    End If
    End If
End If

        Set rst = Nothing
        Set rstp = Nothing

CMDenter_Click_Exit:
    On Error Resume Next
    Exit Sub

CMDenter_Click_Error:
    Select Case Err.Number
            'Case 0
            'Case 1
        Case Else
            MsgBox "Error " & Err.Number & ": " & Err.Description & vbCrLf & vbCrLf _
                   & "Occured in Module1:CMDenter_Click", vbOKOnly + vbExclamation, "Error"
            Resume CMDenter_Click_Exit
    End Select
End If
End If
End Sub

Put a breakpoint on this line:

 If (rstp("HMF_ID") = Me.Txt_User And (rstp("Password") = Me.txtPassword)) Then

And then run the code. You'll be thrown into the debugger and can step through to see what's wrong.

To check the values in your recordset, type this in the Immediate window:

?rstp("HMF_ID")

and then press the Enter key. You should see the value of that recordset field presented in the immediate window.

All it does, is a macro windows opens

I don't understand your comment? What do you mean by "it"?

When you run the code - that is, when you use the Access interface to execute that bit of code, like a user would - you should be thrown into the debugger.

If you try to "run" that code from the VBA Editor, then the macro interface will appear. You should not try to "run" the code from the VBA Editor - you should run it from the interface, just like a user would do.

I'll try it again this morning

No, tried that and When I hit the Enter key, just sits there and does nothing.  I can keep hitting the Enter and nothing, until I put the correct password then it will run the DOCMD. and then the program will open like it should.

No debugger comes up, no MEssage box come up?

So it does not get to the ELSE statement for the messagebox?

Then put your breakpoint earlier in the code, perhaps on this line:

   If IsNull(Me.Txt_User) Then

Ok I get the Message after here to enter a password I hit OK and then I get an error message:
*******************************************
error 2046 Gotocontrol isn't availiable now.

   On Error GoTo CMDenter_Click_Error

    If IsNull(Me.Txt_User) Then
        MsgBox "Enter User Name", vbCritical, "Access Denied"
        Me.Txt_User.SetFocus
        Me.Txt_User.Undo
    Else
        '/ user entered a name in txt_user, so validate it
         Set rst = CurrentDb.OpenRecordset("SELECT * FROM Users WHERE hmf_id='" & Me.Txt_User & "'", dbOpenDynaset, dbSeeChanges)

        If (rst.EOF And rst.BOF) Then
            '/ no match found
            MsgBox "Invalid User Name", vbCritical, "Access Denied"
            DoCmd.GoToControl ("Txt_User")
            Me.Undo
            Me.[Txt_User] = ""
        Else
            If rst("Is_Active") = 0 Then
                '/ found a match, but user is not Active
                MsgBox "Account Deactivated"
                DoCmd.GoToControl ("Txt_User")
                Me.Undo
                Me.[Txt_User] = ""
        Else
  ********************************************      
   GEt to here and shows the msgbox " enter you password" , hit the enter key and then I get the attached error message
**************************************
        If IsNull(Me.txtPassword) Then
            MsgBox "Enter Your Password"
            DoCmd.GoToControl ("Txtpassword")
            Me.Undo
            Me.txtPassword = ""
        Else
***********************************
Stops some where below here ************************************      
         Set rstp = CurrentDb.OpenRecordset("SELECT * FROM Users WHERE hmf_id='" & Me.Txt_User & "' AND password='" & Me.txtPassword & "'", dbOpenDynaset, dbSeeChanges)
         
       
         If Not (rstp.EOF And rstp.BOF) Then

'*****************************************************************************************************
  'Check for correct password
         If (rstp("HMF_ID") = Me.Txt_User And (rstp("Password") = Me.txtPassword)) Then
            DoCmd.RunMacro "CleanHMFTBLS"
         Else
         'If (rstp("HMF_ID") = Me.Txt_User And Not (rstp("Password") = Me.txtPassword)) Then
            MsgBox "Invalid password"
                  DoCmd.GoToControl ("Txtpassword")
                Me.Undo
              Me.txtPassword = ""
           End If
         End If
    End If
    End If
End If

        Set rst = Nothing
        Set rstp = Nothing

CMDenter_Click_Exit:
    On Error Resume Next
    Exit Sub

CMDenter_Click_Error:
    Select Case Err.Number
            'Case 0
            'Case 1
        Case Else
            MsgBox "Error " & Err.Number & ": " & Err.Description & vbCrLf & vbCrLf _
                   & "Occured in Module1:CMDenter_Click", vbOKOnly + vbExclamation, "Error"
            Resume CMDenter_Click_Exit
    End Select
End If
'End If
End Sub

I am lost, do not know why it is not showing the Msgbox for invalid password?

Stops some where below here

If you debug the code, you can determine the exact line where the code fails.

Are you familiar with debugging? Here's a MSFT article about it: http://office.microsoft.com/en-us/access-help/debug-your-visual-basic-code-HP005186717.aspx

It runs and does the Verify  for the ID and the Password.  If they match then it will run the Docmd.runmacro "CleanHMFTBLS"
 
****************************************************
  'Check for correct password
         If (rstp("HMF_ID") = Me.Txt_User And (rstp("Password") = Me.txtPassword)) Then
            DoCmd.RunMacro "CleanHMFTBLS"
***********
But if they both do not match, it is suppose to run the Else and bring up the  MsgBox "Invalid password"
This is where it does not execute the else and pop up the  MsgBox "Invalid password"
Why it will not bring oup the MSGbox ?
     
***************
  Else
         'If (rstp("HMF_ID") = Me.Txt_User And Not (rstp("Password") = Me.txtPassword)) Then
            MsgBox "Invalid password"
                  DoCmd.GoToControl ("Txtpassword")
                Me.Undo
              Me.txtPassword = ""
           End If
         End If

Again:

You need to use the debugging methods described in the link above to determine the exact line where the process stops.

So set the breakpoint on the line just after the 'Check for correct password' comment, and then enter an invalid password, and determine if your code is following the correct path.

If it's not, then verify the values of the various items. To do that, while you're in Debug mode, you can type this in the Immediate window:

?Me.txt_User

And then press the Enter key, and you should see the value of txt_User in the immediate window.  You can do this for all the various elements, including the recordset:

?rstp("HMF_ID")

Would show you the value in that field.

I have tried this for three weeks and still can not see why it is not poping up the msgbox, Just sits there
I do not have time anymore messing with, so I give up and quit!
Everything is working outside of the msgbox
The debug gets to the line I mentioned and just sits there does nothing!  Can not see why it is not working.
I guess I have looked at this so much, I just can not see it!

Have you performed maintenance on your database? If not, MAKE A BACKUP OF YOUR DATABASE and try these suggestions:

1) Decompile the database. To do this, create a shortcut with this as the target:

"full path to msaccess.exe" "full path to your database" /decompile

2) Compact your database
3) Compile your code. To do this, open the VBA Editor and click Debug - Compile. Fix any errors, and keep doing this until the Compile menuitem is disabled (which means it's compile).
3) Compact again.

You might also consider creating a new, blank database and Importing everything from the old into the new database, using the External Data - Import - Access button.

Still have not resolved this issue, had to put this on temp hold

Well, LSMconsulting has help me to a point, everything works, except the One pop window for the Password is still not working correctly, but for now I am going to use it the way it is,

So, I am requesting that LSMconsulting get 300 Points for their effort, and requesting to close this question.

Thanks

We fixed part of the Solutions never completed the last part.