Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2012
    Posts
    14

    Unanswered: Security with MS Access 2010 and MSSQL Server 2005

    I just converted my MS Access 2010 application's backend to SQL Server 2005, but I don't know enough about SQL Server to know anything about security. I've attached an old version of my DB that uses the same login form that we want to use now (gotta have Access to open it).

    Are there any good links to preferred methods, or one method that everyone likes that I haven't stumbled on yet? Is there specific literature on how to manage user security in SQL? (I'm sure there is)

    Right now I would like to keep a similar method of entry and management as there is in the test DB. The log in would not be domain authenticated, and would require a unique ID and password for each user. The management console I created within the Access interface would allow admins to add users to the SQL Server user list for that DB, and give them immediate access through just this DB interface. Is that possible? Are there better ways of doing it that I don't know about?

    My guess is that I need to create a user groups with the permissions I want for each user type server side, then change the user creation code in Access to add a user to that group on the server. Then the log in code would need to be changed to link to the ODBC connection string, which would then give them access to the DB's info. This is my first crack at the problem, but any relevant reading material would be much appreciated!
    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm moving your thread from the MS-SQL to the MS-Access forum. You ought to get more useful answers to MS-Access application development questions here than you would in the SQL Server forum.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2012
    Posts
    14
    Thank's Pat, I wasn't sure which would be the best. I'm familiar with the Access side of things, and just need most of the knowledge about the SQL side of things.

  4. #4
    Join Date
    Jun 2012
    Posts
    14
    Alright, I've made a little progress. I've got my login screen linking to my SQL server with the right ODBC connection string with unique UID and PWD. I've got a For loop that refreshes all of the table links too, and everything seems to work fine at those steps.

    However, whenever I try to open anything after the log in, it says the connection failed because there is no user info. If I do the normal SQL Server Login method with the same Login ID and Password, it will stay connected to the SQL server until I close the Access file.

    Here's the code I'm using the Refresh all the tables upon login. This takes a few seconds longer than just the SQL login, so is this the correct method for a ODBC connection without a DSN?

    Code:
    For Each tdf In CurrentDb.TableDefs
        ' Only attempt to refresh link on tables that already
        ' have a connect string (linked tables only)
        If Len(tdf.Connect) > 0 Then
            strTable = tdf.Name
            
            ' Set the tables connection string
            tdf.Connect = strConnect
            
            ' Give feedback to user
            strMsg = "Refreshing link to ...  " & strTable
            Me.lblmsg.Caption = strMsg
            Me.Repaint
            
            tdf.RefreshLink
            
        End If
    Next
    Of course, strConnect is the connection string that is defined elsewhere in the code (and it is correct). The feedback part is just a little text box that lets the user know the login screen is actually doing something between the time you hit Log In and the main menu pops up.

    Basically, if that code works and seems to connect correctly, why doesn't it stay connected?

  5. #5
    Join Date
    Jun 2012
    Posts
    14
    Found the fix. For anyone who hasn't noticed, I'm using a DSN-less connection without Trusted Connections (not all our users will be on the domain, complicated reasoning). That means we have to use SQL Server Security, and therefore the UID and PWD have to be saved by something. The original code doesn't save it, but if you add 'tdf.Attributes = DB_ATTACHSAVEPWD' you're golden. I'm sure someone can point out some sort of security flaw with this, but I think I'm ok with it for now. Here's the log in code when you have a log in screen with a User ID (quserid) and Password (qpwd) field:

    Code:
    Private Sub cmdLogIn_Click()
    On Error GoTo Err_cmdLogIn_Click
    '-----------------------------------------------------------------------------------------------------------------------------
    ' This code is used to validate users with SQL Server Security connect to SQL server.
    ' If the wrong user name or password is provided access is denied.
    ' Created by: Daniel VanBeek with code from Danny Lesandrini, www.databasejournal.com
    ' Date Created: 08 Jul 2012
    ' Date Modified: 08 Jul 2012
    '-----------------------------------------------------------------------------------------------------------------------------
     
    Dim strTable As String
    Dim strSuccess As String
    Dim tdf As TableDef
    Dim strMsg As String
    Dim strUID As String
    Dim strPWD As String
    Dim strConnect As String
    Dim stDocName As String
    Dim stLinkCriteria As String
    
    
     
     ' Check for existence of User Name and password.
     ' If missing, inform user and exit.
        If IsNull(Me.quserid) Then
            strMsg = "Enter user login.  (Example: bsmith)" = ""
            MsgBox strMsg, vbInformation, "Missing Data"
            Me.quserid.SetFocus
        ElseIf IsNull(Me.qpwd) Then
            strMsg = "Enter your password."
            MsgBox strMsg, vbInformation, "Missing Data"
            Me.qpwd.SetFocus
        Else
            strUID = Me.quserid
            ' Password may be NULL, so provide for that possibility
            strPWD = Nz(Me.qpwd, "")
            
            ' Prepare connection string
            strConnect = "DRIVER={SQL Server}" _
                    & ";SERVER=###.###.###.###" _
                    & ";DATABASE=myDB" _
                    & ";Uid=" & strUID _
                    & ";Pwd=" & strPWD & ";"
            'Debug.Print strConnect
        End If
        
        ' Refresh Access Linked Tables
    For Each tdf In CurrentDb.TableDefs
        ' Only attempt to refresh link on tables that already
        ' have a connect string (linked tables only)
        If Len(tdf.Connect) > 0 Then
            strTable = tdf.Name
            
            ' Set the tables connection string
            tdf.Connect = strConnect
            tdf.Attributes = DB_ATTACHSAVEPWD
            
            ' Give feedback to user
            strMsg = "Refreshing link to ...  " & strTable
            Me.lblmsg.Caption = strMsg
            Me.Repaint
            
            tdf.RefreshLink
            
        End If
    Next
    
    
    ' Give feedback to user
    'strSuccess = IIf(Err.Number = 0, "Successful", "NOT successful.")
    'strMsg = "Finished.  Connect was " & strSuccess
    'Me.lblmsg.Caption = strMsg
    
    
    
    
    stDocName = "frmMainMenu"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.Close acForm, "LogInForm"
    
    
    Exit_cmdLogIn_Click:
        Exit Sub
    
    
    Err_cmdLogIn_Click:
        MsgBox Err.Description
        Resume Exit_cmdLogIn_Click
        
    End Sub
    As you can see, this will only log you in and take you to the main menu if the SQL Server connection string works. If not, you'll get a Connection failed error from the SQL Server Login box. It then gives you another chance to log in through the SQL Server Login box.

    Can anyone help me with the error handling so that it catches the SQL Server Login box and routes you back to my login form with an error?

Posting Permissions

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