Results 1 to 3 of 3
  1. #1
    Datech is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    40

    Security with SQL Server 2005

    I just converted my backend to SQL Server 2005, but I don't know enough about SQL Server to know anything about security. I've attached an old form of my DB that uses the same login form that we want to use now.



    Are there any good links to preferred methods, or one method that everyone likes that I haven't stumbled on yet?

    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 be not be domain authenticated, and would require a unique ID and password for each user. The management console within the Access DB 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
    Datech is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    40
    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?

  3. #3
    Datech is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    40
    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?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Import .TXT file into SQL Server 2005 Table
    By taimysho0 in forum SQL Server
    Replies: 1
    Last Post: 05-23-2012, 03:02 PM
  2. access on server security
    By bigmac in forum Security
    Replies: 1
    Last Post: 03-11-2012, 06:02 AM
  3. Replies: 0
    Last Post: 03-08-2012, 03:12 AM
  4. PHP/MySQL vs SQL server 2005
    By mimi in forum Import/Export Data
    Replies: 0
    Last Post: 02-28-2010, 09:13 AM
  5. SQL Server 2005 Connection
    By Patrick.Grant01 in forum Import/Export Data
    Replies: 2
    Last Post: 06-18-2009, 11:22 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums