DSN-Less authentication and security

[osupratt]

I have the DSN-Less connection code as follows that I got from the mircosoft site......

Option Compare Database
'//Name : AttachDSNLessTable
'//Purpose : Create a linked table to SQL Server without using a DSN


'//Parameters
'// stLocalTableName: Name of the table that you are creating in the current database
'// stRemoteTableName: Name of the table that you are linking to on the SQL Server database
'// stServer: Name of the SQL Server that you are linking to
'// stDatabase: Name of the SQL Server database that you are linking to
'// stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'// stPassword: SQL Server user password
Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
On Error GoTo AttachDSNLessTable_Err
Dim td As TableDef
Dim stConnect As String

For Each td In CurrentDb.TableDefs
If td.Name = stLocalTableName Then
CurrentDb.TableDefs.Delete stLocalTableName
End If
Next

If Len(stUsername) = 0 Then
'//Use trusted authentication if stUsername is not supplied.
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
Else
'//WARNING: This will save the username and the password with the linked table information.
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
End If
Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
CurrentDb.TableDefs.Append td
AttachDSNLessTable = True
Exit Function
AttachDSNLessTable_Err:

AttachDSNLessTable = False
MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description
End Function


I then put this code in the on_open form.................


Private Sub Form_Open(Cancel As Integer)

Dim ServerName As String
Dim UserName As String
Dim Password As String

ServerName = "CPS23"
UserName = "myUserName"
Password = "myPassword"
'Attach the linked database tables if they aren't already attached

AttachDSNLessTable "dbo_AncillaryEquipUsed", "AncillaryEquipUsed", ServerName, "ABCFieldTicket", UserName, Password

End Sub


As far as connecting or linking the back-end SQL Server tables this code works great. My question is that I am having issues with access or security. Anyone that can find the .mdb file can get in and enter data into this system. How do I (or the SQL Administrator) set it up to where what security user group that is on the server is also on the front-end MS Access file? If someone is NOT in the user group then they should not be allowed to enter data into the system. So maybe have a window pop-up asking for a username and password? Not sure, but any pointers or any kind of help would be greatly appreciated.

[ItsMe]

You can lock down the app so users cannot click around and get access to Options and Objects. However, using code like that will not keep someone determined out. I would guess the best way is to use ADO or make links on the fly. You could hide the pass in your VBA and then distribute executables.

[osupratt]

so is there any kind of setting on the server side to where if the user is not part of the server side user group it will throw an error and not let them enter data?

[ItsMe]

You are going to need to connect to the sever. You will use a connection string like the one in post #1. If you save that string to a table or query that links to the server, you are creating vulnerabilities. So if the idea is just to keep people out in a casual way, go ahead and store the password in the string and in the linked tables. If the idea is you need to keep bad people out, you should not store the pass in the linked tables.

Once you figure out which of these two approaches you are going to take, you can go from there.

One option might be to store the pass in the linked tables but also encrypt the front end file. Then, each user would be responsible for their data on the server.

There are other options too.

[osupratt]

I still don't understand. I have the special keys and the navigation bar hidden. I have passwords to where users can't open the vba or look at my code. My question is that when they initially click on the file and open it up and see the switchboard.......they can click on 'enter field ticket data' and there is no security at that point. i would like there to be when a user or attempted user clicks on any switchboard controls there will be a window that pops up asking for their window authentication username and password or some form of security at the point just prior to data entry.

The username and password that are in thread #1 and that i show in red text are hitting the back-end sql database and is the username and password that the SQL Administrator gave me to use in this code. So I am guessing that if someone can open the file and the username and password are already existing in the code then they are 'IN' and can enter data. So I am trying to find other ways to secure this database without using the MS Access 'User and Group Permissions'.

[ItsMe]

...asking for their window authentication username...

You can automate that using the Environ() function. You can also use code to interrogate Active Directory or use a table to validate Windows UserName. So there is no need for the user to click anything. What I do in my apps is offer a pleasant little message box explaining they are not authorized to open the app. Similarly, you can control what forms and resources are available within a given app.

All you need to do is ask the Envron() function who is logged in and sync that up with a DB User and Pass.

[osupratt]

ok. maybe i'm going about this question the wrong way. after talking to the SQL Administrator he does not like the fact that i am circumventing the security he has set up on the server. When i place the username and password in the connection string ANYONE who finds the file and can open it up will have access to the server and database. So it seems the DSN-Less code is fine, but the code on the on_open of the switchboard form that has all my connection strings is where the issue lies. Is there a way to have connection strings that will require the user to enter a username and password rather than having it in code? Also is there a way to get rid of the Windows Authentication or trusted connection part of the DSN-Less code?

[ItsMe]

...Also is there a way to get rid of the Windows Authentication or trusted connection part of the DSN-Less code?

I do not know what you are asking here. The whole thing is rather simple. The hard part is implementation. The simple fact is that you need to provide a username and password to the SQL DB. The complicated part is how you do this. There are many options how to do it. As I mentioned before, you need to determine what is acceptable? Maybe it is acceptable to stick a post-it with the user and pass underneath the keyboard. So ... a discussion about the "how" can spiral into a sea of semantics.

If I do not understand what is acceptable, I cannot offer any advice. If you need it totally locked down, I believe the safest is to encrypt each front end and have the pass stored in the FE file somehow. If you cannot allow the person using the encrypted FE to know the user and pass to the SQL DB, you will not likely be able to use Access as an FE.

If you like, I will talk to your DBA over the phone. Maybe I can get an understanding of what is acceptable. Send me a PM if you would like to take me up on this offer.

[osupratt]

ok. i want the people we authorize to know the username and password. right now we have the username and password in the code and anyone that can open the MS Access FE file can access the server. We want authorized people to open the file and then have to use a username and password to enter data.

[ItsMe]

You do not have to include the UID and PWD arguments in the linked tables. If there is no PWD or UID info and a User tries to open a linked table, they will be prompted for a User Name and Password. This will establish a link that will timeout eventually.

Although that approach will connect the user, there are better ways to approach this. For instance, you can collect the user info before connecting, storing the info in a variable or a couple of textboxes and then connect when needed.

[ssanfu]

I'm hazy also, but try this (on a copy of your dB)

1) Create a new un-bound form
2) Name it something like "dbLogIn" or "LogMeIn"
3) Create two unbound text boxes:

Text box 1:
Name:"txtUID"
Caption: "User ID"
---
Text box 2:
Name: "txtPW"
Caption:"Password"

4) Create a button. Name it "btnLogIn" and the caption as "Log In"

Code for the button

Private Sub btnLogIn_Click()

    Dim ServerName As String
    Dim sUserName As String
    Dim sPassword As String  ' "Password" is a reserved word in Access


    ' if UID and PW controls have entries then
    ServerName = "CPS23"
    sUserName = Me.txtUID
    sPassword = Me.txtPW

    'add check that the UID and PW controls are not Null/empty
    If Len(Trim(sUserName) & "") > 0 And Len(Trim(sPassword) & "") > 0 Then

        'Attach the linked database tables if they aren't already attached
        AttachDSNLessTable "dbo_AncillaryEquipUsed", "AncillaryEquipUsed", ServerName, "ABCFieldTicket", sUserName, sPassword

        'more code here - close/hide log in form , open main form, etc
    Else
        MsgBox "Missing UID and/or Password"
        Exit Sub
    End If

End Sub

[ItsMe]

@Steve
According to osupratt, the DBA is not happy with storing the pass and user name in the FE file.

[ssanfu]

Since I don't have the dB, I can't see everything that happen in it, but I would expect to have the linked tables deleted when the user logs off.

In the code for the function "AttachDSNLessTable", there is this snippet of code:

For Each td In CurrentDb.TableDefs
   If td.Name = stLocalTableName Then
     CurrentDb.TableDefs.Delete stLocalTableName
   End If
Next

It first deletes the link (if there is a link), then later the code creates a new link to that table........ Like what you posted in your first post: "make links on the fly".

Right now I only use windows authentication..... it work for my requirements (right now). Might have to rethink this later.


(hmmm...what am I not seeing???? I'm trying to think, but nothing is happening!)

[ItsMe]

...but I would expect to have the linked tables deleted when the user logs off....

Something like this could work but what if the user never logs off? Since the thread title and OP is centered around security, I do not recommend linking tables like that. However, it is plenty good if all you are trying to do is maintain constraints and referential integrity. One approach to increase security is to keep your pass and user ID in the VBA code, calling it when needed. However, this is a lot of work and is not always necessary. As soon as you link a table, it is stored in the file.

Another approach that I have been considering trying out is to connect to a table using a 20-40 second timeout. Then use normal queries and linked tables (that do not have the pass). The idea would be, when the user needs access to the host, call a function that does nothing other than connect to a table of a dozen records or so. Then, let the user do what they need as long as it is under the timeout rule. Lately I have been looking into developing on Azure. Since the Chatty vs. Chunky services are taken into consideration with web services, I might as well look at things like managing high security with desktop apps.

As for the CurrentDb.TableDefs.Delete stLocalTableName thing, I believe the purpose of the function in post#1 is to change local tables into linked tables. I guess the idea is you can develop your app locally and then flip a switch. So I do not believe the original function is centered on creating the most secure link possible.