Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2004
    Posts
    5

    Red face Unanswered: Linked tables versus password protection

    Ok,

    I have a split database design with both the master front-end and back in different areas on the network. I copy local front-ends to the users desktops.

    The back-end is password-protected and encrypted through a workgroup with users and groups setup. If I create a shortcut to the front-end with the workgroup command-line options set, the MSAccess password dialog is displayed following what appears to be a normal login, except that it pings me with error messages that it couldn't connect to my links for some reason or another. Actually, I've tried so many variations that I don't recall the specifics of each attempt. This thing has a lot of code behind it.

    IDEALLY, I want to present a user with a login box in the front-end, then access the linked tables at will throughout the application.

    Seems pretty logical, right? So how is it done? User names and passwords are correct, I know Access statically holds the links open, and I've tried submitting passwords and usernames in the connection string to no avail. When I do it programmatically like this, it tells me I have no permissions in the database.

    Any ideas how I can one login dialog, Access or my own, and freely connect to the protected back-end when I want. This seems like one of those things that would be common to do. Yet, I can locate no answer anywhere I've looked, and can't find my own clue.

    dbHell

  2. #2
    Join Date
    Oct 2003
    Location
    US
    Posts
    343

    Re: Linked tables versus password protection

    You have to understand the MS Access security first. Here's what is happening in your case. When you locked your back end tables you used a different security file possibly the defualt system.mdw. But when you try to use the front end with commnad line set in your shortcut, it uses a different security file.

    What you need to do is to use the same security file for both front and back end. In that case if someone navigates to the network drive where you have the back end stored, he/she won't be able to open up the back end tables by just clicking on it, they'll actually need a shortcut which will use your specific security file to get into it.

    Hope that'll hwlp.


    Originally posted by dbHell
    Ok,

    I have a split database design with both the master front-end and back in different areas on the network. I copy local front-ends to the users desktops.

    The back-end is password-protected and encrypted through a workgroup with users and groups setup. If I create a shortcut to the front-end with the workgroup command-line options set, the MSAccess password dialog is displayed following what appears to be a normal login, except that it pings me with error messages that it couldn't connect to my links for some reason or another. Actually, I've tried so many variations that I don't recall the specifics of each attempt. This thing has a lot of code behind it.

    IDEALLY, I want to present a user with a login box in the front-end, then access the linked tables at will throughout the application.

    Seems pretty logical, right? So how is it done? User names and passwords are correct, I know Access statically holds the links open, and I've tried submitting passwords and usernames in the connection string to no avail. When I do it programmatically like this, it tells me I have no permissions in the database.

    Any ideas how I can one login dialog, Access or my own, and freely connect to the protected back-end when I want. This seems like one of those things that would be common to do. Yet, I can locate no answer anywhere I've looked, and can't find my own clue.

    dbHell

  3. #3
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Arrow

    fyi - One recommendation for extra security is to remove all rights for users/usergroups other than developers/admins to the tables themselves in the back-end as well as on the linked tables in the front-end.

    Then - in the front-end: ensuring that all access to the tables is from forms based on queries that run with Owner's permission. (ie. no direct lookup to tables, always either use queries, or set WITH OWNERACCESS OPTION as the last part of a VBA strSQL (or a control that uses its own select statement)

    Control which data the users can access simply through what is available from/in the forms/switchboards, hence by the user access to the forms/reports.

    Another issue:
    Do not distribute workgroup files with users that have not set their passwords (yet)... If a user copies the mdw file before all othes have entered their first time passwords, this user can subsequently have access point to the older version of the mdw file and log in as one of the users that have not set their pw at the time the mdw copy was made...

    I am about to check whether it is possible to use Windows NTFS file system permissions to make MDW files both readable but non-copyable and still fully working for Access. That would mean greater control over that part of Access security.

    Daniel

  4. #4
    Join Date
    Mar 2004
    Posts
    5
    Thank you khan and Daniel.

    Actually, I've done everything you suggested and more. I have ensured that the default admin account has been password protected and changed the name. All users that I created in the MDW have unique passwords. They don't know them of course, because I haven't gotten the security working on my test database.

    Nonetheless, I use only on MDW file, which is located in the same directory as the backend. The Frontend (at this time) is located one level up. When I used the User wizard, it was on the backend. This was when I cofigured my users and groups. --These are all located on the same server on the network.

    I have two shortcuts. One I use for the frontend, one I use for the backend. Both of these shortcuts assist me with testing, and both have command line options set to point to the single MDW that I created. When I use them exclusive of one another, I can get into the database just fine. On the backend, I can see all my data.

    However, it is my assumption that having both databases using the same MDW, and accessing the frontend via shortcut rediring to that MDW, that I would be able to access the "linked" backend tables by default. This is apparently not the case. I've tried to "preconnect" in my forms (after using the link to open the front end) using VB code to capture the username and password, then perform an explicit connection, passing both arguments. This fails to get me in also. I know there must be one tiny, simple thing I'm overlooking. That is always the case. This is my needle in the haystack.

    Here is what my statement looks like:

    cnn.Open "Provider=" & DATABASE_TYPE & ";" & "Data Source=" & DATABASE_PATH & DATABASE_NAME, strUsername, strPassword, adAsyncConnect


    One would think this should work. I have searched the ends of the world for an answer. Surely there is someone who has tried this before and can advise me.

  5. #5
    Join Date
    Mar 2004
    Posts
    29
    I've done somthing similar to this in my environment...

    Instead of securitizing the back-end at all really I just set a database password on it. Since I'm the only DBA they have and I'm not giving out the password it seems to work well enough.

    The only issue you run into is changing the database password which will break your links in the client, but if you have a good method for deploying / re-deploying the client such a sychronization then you should be fine.

  6. #6
    Join Date
    Jun 2003
    Location
    UK
    Posts
    68
    I'm with Coldendus, why have security when a database password will suffice. So much simpler just set the password in the BE link the FE and BE enter password when prompted during linking and its done.

    Paul

  7. #7
    Join Date
    Mar 2004
    Posts
    5
    Well Coldendus and Funster,

    This database is located in a public area. Simply applying a database password will require everyone to use the same password. This is a HelpDesk system for my department to track trouble calls. SO we have several users accessing it. Additionally, the Frontend will reside on each user's desktop (local machine). The organization has over 14000 employees with access to this thing if they want. Some oppose the system for obvious reasons (they do nothing and therefore can't stand the thought of working to justify their paycheck). Anyway, curious eyes and malicious intentions force the need to prevent users from seeing each others records, as well as action auditing. Each user needs a login to accomplish these tasks. So,a single database is not a feasible solution at this time. The system is rather complex and fully functional.

    I have several other security tricks to keep things on the level, but this is one area that I am having extreme difficulty with. The backend has to be much more secure than the front end do to the data it contains.

    Although, I could try setting just the backend password as you've suggested, then use the frontend to limit what the users have access to. That may require hard-coding the backend password and authenticating the frontend, but it's an idea.

    Thank you for your thoughts.

  8. #8
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369
    Originally posted by dbHell
    Nonetheless, I use only on MDW file, which is located in the same directory as the backend. The Frontend (at this time) is located one level up. When I used the User wizard, it was on the backend. This was when I cofigured my users and groups. --These are all located on the same server on the network.

    I have two shortcuts. One I use for the frontend, one I use for the backend. Both of these shortcuts assist me with testing, and both have command line options set to point to the single MDW that I created. When I use them exclusive of one another, I can get into the database just fine. On the backend, I can see all my data.
    hm. just to make sure:
    Can you not access the backend through the front end using that mdw even when logging on with the user registered as the owner of the db?

    Moreover ; I assume that that owner is the same for both the front and the back end and is member of the admins group ? Please confirm.

    Permissions need to be set for each group not only for the tables in the back-end, but also for the linked tables in the front-end. However, the recommendation is to ensure the owner access is working, then remove the rights for others on the tables and linked tables, then run all reguests through queries set to run with owners permissions.

    But of course, if just securing the back-end with a pw will do, then that is the easiest. If the front end then "works", you can establish the different roles / access levels on the front end as you would normally.

    D.

  9. #9
    Join Date
    Mar 2004
    Posts
    5

    Relief alas!

    OK, Everyone.

    I have found the error of my ways, and truly thank all who have provided advice.

    In the spirit of assistance, I am posting the answer which lies in the KB article at the link below.

    http://support.microsoft.com/default...b;en-us;191754


    Rather than use great detail, I must admit that my syntax was somewhat off when sending the Username and Password in the connection string. It should have been

    UserID:="username", Password:="password"

    I was sending the username and password alone, assuming that the arguments would be placed accordingly due to their parameter position for the function.

    Additionally, the path and filename of the workgroup security file has to be passed in the string preceding the userid and password. I did not do this. It goes something like this:

    "Jet OLEDBystem Database=C:\System.mdw"

    Correcting these two things have freed my life and saved some dents in the wall where my head frequently impelled it. I was relying on the command-line arguments in the shortcut and Access itself to figure out that it needed to look in that .mdw for the Username and Password. Silly me for thinking.

    Anyway, hope someone else can use this information.

  10. #10
    Join Date
    Mar 2004
    Posts
    29

    Thank you

    That was a great article. I've been looking for a solution like that myself while trying to migrate to ADO in preparation for upsizing to SQL Server 2000.

    The database password was wreaking havok on my connections and the VBA help... wasn't.

    In any case with 14,000 users you are definitely using the wrong backend. I would avoid Jet like the plague for that. I only have approx 400 but even that is pushing the limits of a single Jet DB.

  11. #11
    Join Date
    Nov 2004
    Posts
    14

    Unhappy

    I'm afraid I don't understand. I'm having a similar problem. I have an Access DB written by someone else which is in 2 parts. Application.mdb and tables.mdb. tables contains only the tables and application contains the forms, queries and reports. In order to stop users from circumventing the auditing I added to the application, I put a password on the tables.mdb, then relinked the tables in application.mdb. Now, if I hold down shift and double click application, it opens fine, and all the tables are visible, however, if I just double click on the application to go direct into the forms, I get an error message "Unable to Attach Tables Not a valid password".
    The article that dbHell linked is interesting, but I don't know how to apply it, where does this code go, or does this only apply to visual basic applications?

    I'm not an Access guru, in fact, before this current assignment, I'd never used it before other than in the simplest of ways, so please, speak slowly using short words

  12. #12
    Join Date
    Mar 2004
    Posts
    29
    Quote Originally Posted by ChrisW75
    I'm afraid I don't understand. I'm having a similar problem. I have an Access DB written by someone else which is in 2 parts. Application.mdb and tables.mdb. tables contains only the tables and application contains the forms, queries and reports. In order to stop users from circumventing the auditing I added to the application, I put a password on the tables.mdb, then relinked the tables in application.mdb. Now, if I hold down shift and double click application, it opens fine, and all the tables are visible, however, if I just double click on the application to go direct into the forms, I get an error message "Unable to Attach Tables Not a valid password".
    The article that dbHell linked is interesting, but I don't know how to apply it, where does this code go, or does this only apply to visual basic applications?

    I'm not an Access guru, in fact, before this current assignment, I'd never used it before other than in the simplest of ways, so please, speak slowly using short words
    1st Check: See if your linked tables in the Application.mdb have the password which I am assuming you just added, as part of the connection string. If you did this already then...

    2nd Check: You may want to check the clas module of any form that may be in the "Start up" section of your Application.mdb OR check for a macro with the name "Autoexec". These may be running code to link tables to your database programatically.

    WARNING: Because you did not write the code in your Application.mdb take care you are very carefull and fully understand the code that is operating before you attempt to alter it. Even better VBA programmers have issues at times with all the possible variations that can exist for your connection string.

  13. #13
    Join Date
    Nov 2004
    Posts
    14

    Talking

    In the end I hard coded the linking in.

    In my startup form I have:-

    Code:
        Dim sDBLocation As String
        Dim sConnect As String
            
        If optChoose.Value = 1 Then
            sDBLocation = glbPath
            sConnect = ";database=" & sDBLocation & "; pwd=mypassword"
        ElseIf optChoose.Value = 2 Then
            sDBLocation = txtPath
            SetAttr txtPath, vbReadOnly
            sConnect = ";database=" & sDBLocation
        End If
        
    ' link in tables.
        CreateLinkedTable "user_table", sConnect
        CreateLinkedTable....
    And the CreateLinkedTables function is:-

    Code:
    Private Sub CreateLinkedTable(sTableNm As String, sConnect As String)
    
             Dim dbLocal As Database
             Dim tbfNewAttached As TableDef
    
             Set dbLocal = CurrentDb()
             Set tbfNewAttached = dbLocal.CreateTableDef(sTableNm)
    
             With tbfNewAttached
               .Connect = sConnect
               .SourceTableName = sTableNm
             End With
    
             dbLocal.TableDefs.Append tbfNewAttached
    
    End Sub
    Works like a charm, it also allowed me to select which database I wanted to open.
    Last edited by ChrisW75; 02-23-05 at 03:09.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •