Network access

[Gismo]

Hi all,
Here is a weird question for you.
I have almost completed my access project but management want utmost security. I was suggested to link the access database security to our network security. on the network you need to reset password every 3 month which they would like to support on the access database. but I am not sure if this will be possible to have authentication through the network or even how I will be able to accomplish this. Any suggestions?

 

[Ranman256]

yes you can use Window authentication.
1st the user must be granted rights to the folder with the BE and FE databases.
2nd the user must be in the tUser table
3rd the user must use his/her windows login to get in.

The db FE would have a 'login' form. If correct the MainForm opens, else quit...

Dim sUser As String, sPass As String, sDom As String
dim vID, vDbID

sUser = txtUser
sPass = txtPass
sDom = txtDom

vID= Environ("Username")
vDbID = Dlookup("[userId]","tUsers","[UserID]='" & vID & "'")

if ucase(vID) = ucase(vDbID) then
  If WindowsLogin(sUser, sPass, sDom) and vID = vDbID Then
   mbSafe = True
   DoCmd.OpenForm "frmMainMenu"
   DoCmd.OpenForm "frmLogin"
   DoCmd.Close
  Else
    MsgBox "LOGIN INCORRECT", vbCritical, "Bad userid or password"
  End If
else
   MsgBox "You are not registered for this db.", vbCritical, "Contact Admin"
endif
End Sub

put this code into a module and save.

'-------------
Public Function WindowsLogin(ByVal strUserName As String, ByVal strpassword As String, ByVal strDomain As String) As Boolean
'-------------
        'Authenticates user and password entered with Active Directory.

        On Error GoTo IncorrectPassword
        
        Dim oADsObject, oADsNamespace As Object
        Dim strADsPath As String
        
        strADsPath = "WinNT://" & strDomain
        Set oADsObject = GetObject(strADsPath)
        Set oADsNamespace = GetObject("WinNT:")
        Set oADsObject = oADsNamespace.OpenDSObject(strADsPath, strDomain & "\" & strUserName, strpassword, 0)
        
        WindowsLogin = True    'ACCESS GRANTED
        
ExitSub:
        Exit Function
        
IncorrectPassword:
        WindowsLogin = False   'ACCESS DENIED
        Resume ExitSub
End Function

 

[The_Doc_Man]

You can surely use the domain password. However, you can also ask your management whether they think that it would be OK to have your DB "trust" your domain security and not require a repetition of the login to the DB. Instead, you would work with your security wonks to define group identifiers and make the database visible only to members of the appropriate Windows security group.

I ask that because there is an alternate viewpoint that forcing use of a password to get into the domain and then forcing a second login with the same password to get into the database doubles the chance that someone watching over-the-shoulder could steal that password. (Twice the usage = twice the risk of exposure.)

If you "trust" the domain login and use domain security features such as Windows Group IDs to drive the access control lists (ACLs), then you have a pretty secure setup. It was secure enough to be approved by the U.S. Dept. of Defense at the Navy Enterprise Data Center in New Orleans from about 2005 through 2016. (I retired in 2016 so have no idea if that ruling has changed - but I'm betting it has not.)

 

[pbaldy]

May be of interest:

http://www.accesssecurityblog.com/post/Securing-Access-databases-using-Active-Directory.aspx

 

[Pat Hartman]

"Utmost security" and Access in the same sentence is what we call an oxymoron.

Adding security after the fact is seriously poor planning. If all they want is log in security, then the previous suggestions will suffice. However, if they want you to control read/write/delete access to various forms/report, that's a whole new kettle of fish.

Management needs to know that NO Access database is secure. It is almost secure if your data is in SQL Server or other RDBMS but if the data is in an .accdb, I hope it doesn't contain anything that has any value on the open market because anyone can copy the BE and sell it. All Access databases can be cracked.

 

[The_Doc_Man]

Pat's comments are true.

The only thing you can do with an Access database is make it harder for someone to crack. But if you want absolute security, you need a true DB server. Access BE files are, of course, merely files and you use a file server protocol (Server Message Block or SMB) to read/write those files.

 

[Gismo]

I am having problems with the first part of the code.
The vDbID shows as error

Private Sub btnLogin_Click()
Dim sUser As String, sPass As String, sDom As String
Dim vID, vDbID
sUser = UserName
sPass = txtPassword
sDom = txtDom
vID = Environ("Username")
vDbID = Dlookup("[UserId]","tUsers","[UserID]='" & vID & "'"
If UCase(vID) = UCase(vDbID) Then
If WindowsLogin(sUser, sPass, sDom) And vID = vDbID Then
mbSafe = True
DoCmd.OpenForm "Switchboard"
DoCmd.Close
Else
MsgBox "LOGIN INCORRECT", vbCritical, "Bad userid or password"
End If
Else
MsgBox "You are not registered for this db.", vbCritical, "Contact Admin"
End If
End Sub

 

[pbaldy]

If that's a cut/paste, you're missing the closing parenthesis for the DLookup()

 

[Pat Hartman]

I am having problems with the first part of the code.

We have no idea what that means. It couldn't be a compile error because surely you would have mentioned that.

Please use the code tags when you post code so the alignment remains. It makes the code easier to read.

 

[Ranman256]

sorry, i dropped the right paren:

vDbID = Dlookup("[userId]","tUsers","[UserID]='" & vID & "'")

 

[Gismo]

Hi,

What does it mean when I get a compile error on
sDom = txtDom
It gives me a run-time error 94
invalid use of Null

I have the domain in my table under txtDom

 

[The_Doc_Man]

I would not have expected that particular compilation error on a simple assignment, but it is possible that for some reason VBA doesn't know what txtDom means. If it is part of a table, you need to have it available in one of a couple of ways.

1. Open a recordset to the table and qualify the reference with <recordsetname>.txtDom - which should pacify the compiler. (If there is still something wrong, it will become a run-time error.)

2. If this table is bound to your form and txtDom is also the name of a control bound to that field, then Me.txtDom would again qualify the reference to tell VBA where to find it. Again, if there is still something wrong, the qualified reference will push the error into the run-time domain.

By itself, txtDom is apparently "out of scope" (not visible to VBA in that context.) But I still would not have expected that error. Are you sure that is the line in question?

Oh, another possibility: Where is sDom defined and as what is it defined? Is it a string variable in your subroutine or function? Is it allocated in the declaration area of the form's class module or perhaps in a general module? Look at it this way: You've got three things on that line and there is no doubt as to the meaning of "=" in the assignment statement. So you've got two choices left. Syntactically, the statement is unambiguous, but semantically there must be something wrong with one or the other of the members of the statement.

There is one last possibility. Erase the entire line and retype it on the odd chance that you have a "phantom" NUL character (created by using CTRL/@) in the line because you fat-fingered something while typing. But even THAT should give you an "illegal character" error rather than "invalid use of null" in that context.

 

[Gismo]

Hi,

I retyped sDom
I have sDom defined as string
txtDom is linked to my form record source so yet it is available

Will txtDom work as "txtDom"
It now just gives me incorrect user name or password error.
Also not sure if my user name authentication to windows will be ID, UserId, User or UserName

Am I correct in saying, I don't need the windows password in my users table to be able to authenticate as it validates with the windows network password?

 

[The_Doc_Man]

Am I correct in saying, I don't need the windows password in my users table to be able to authenticate as it validates with the windows network password?

If you are in a managed domain where your IT staff enforces stuff like domain logins and group security profiles and system profiles, then you are in a secured domain. In that context, if you use a function such as Environ("Username"), that name comes from a managed part of Windows that is set via domain interactions and that is very hard to change as a non-admin user. As such, it is trustworthy.

In the Windows world, "trust" is important. There are trusted locations, trusted programs, and trusted members of a network. The implication is that in order to get to these things, you must go through Windows Domain Authentication. Once you are in, you can use the trusted elements because YOU have become trusted.

There ARE such things as non-domain logins even on a domain-based system (usually referred to as "local" accounts), and for those logins, it is like a kid outside in the snow looking in through the toy store window. No way to get to the goodies from there. So if you have a domain login, you can trust it. If you have only a local login, Windows won't trust you and will give you trouble.

Just ask your IT security staff if they even allow local logins. (Odds are they don't allow them for anyone except specific administrator functions that can be used when the computer has been isolated from the domain for security reasons.) Explain that you want to verify that the login has gone through proper authentication and that you are just dotting the i's and crossing the t's to assure that you can trust the name you receive in your application when you ask the appropriate question via software. They will not usually laugh at someone trying to assure that a particular security viewpoint is valid. That kind of question will make sense to them and typically will be seen as cooperative rather than disruptive.

 

[Ranman256]

it should have a close paren on the end
vDbID = Dlookup("[UserId]","tUsers","[UserID]='" & vID & "'")

 

[Gismo]

Hi All,

The above code as module, does it need to be named to anything specific and does any of the code need to be changed in relation to field names such as username, password or domain. we are using active directory. I just can not get through to validate my access to active directory. any suggestions on a sample dB?

 

[Gismo]

Hi All,

I have still not managed to solve my problem to authenticate using network credentials. Still get a invalid use or null error on below code on sUser and sDom. Does anyone know what the field name should be for user, password and domain for active directory?

Private Sub btnLogin_Click()
Dim sUser As String, sPass As String, sDom As String
Dim vID, vDbID
sUser = UserId
sPass = txtPassword
sDom = "txtDom"

vID = Environ("UserId")
vDbID = DLookup("[UserId]", "Users", "[UserId]='" & vID & "'")
If UCase(vID) = UCase(vDbID) Then
If WindowsLogin(sUser, sPass, sDom) And vID = vDbID Then
mbSafe = True
DoCmd.OpenForm "Switchboard"
DoCmd.Close
Else
MsgBox "LOGIN INCORRECT", vbCritical, "Bad Userid or Password"
End If
Else
MsgBox "You are not registered for this db.", vbCritical, "Contact Admin"
End If
End Sub

 

[Gismo]

Hi

what should this module be called?

'-------------
Public Function WindowsLogin(ByVal strUserName As String, ByVal strpassword As String, ByVal strDomain As String) As Boolean
'-------------
'Authenticates user and password entered with Active Directory.

On Error GoTo IncorrectPassword

Dim oADsObject, oADsNamespace As Object
Dim strADsPath As String

strADsPath = "WinNT://" & strDomain
Set oADsObject = GetObject(strADsPath)
Set oADsNamespace = GetObject("WinNT:")
Set oADsObject = oADsNamespace.OpenDSObject(strADsPath, strDomain & "" & strUserName, strpassword, 0)

WindowsLogin = True 'ACCESS GRANTED

ExitSub:
Exit Function

IncorrectPassword:
WindowsLogin = False 'ACCESS DENIED
Resume ExitSub
End Function

 

[isladogs]

Modules can be called anything you like but with no spaces or special characters.
The only issue is to ensure you do not use the same name for a module and procedure.

I prefer to group functions doing similar things into the same module and call them something meaningful with a mod prefix
e.g. modUpdateTables, modActiveDirectory

 

[Lightwave]

"Utmost security" and Access in the same sentence is what we call an oxymoron.

Security is tricky because you are trying to prevent access to something whose whole point is to give access - that's the oxymoron.

I think provided you take into consideration the characteristics of MS Access it can be made perfectly safe.

There's a lot you can do with RDP - Configuration looks however very complicated.

https://blog.rapid7.com/2017/08/09/remote-desktop-protocol-exposure/

As ever people are generally the weakness not the technology.
Quis custodiet ipsos custodes?