How to Create a Read-Write Desktop Database from Your Access Web App

Access will automatically create an Access Reports Desktop Database for you that is Read-Only but you are not limited to just that option. With a little code you can easily change those linked tables to Read-Write tables. You can enjoy all the rich features and flexibility we all have come to love with Access. It is good to know that using an Access Web App does not limit you to having that as the only tool in your developer tool belt.

Since Web Apps are new to many of us I am going to start from the beginning and show step-by-step how to create an Access Desktop Database with Read-Write linked tables to your Web App’s Azure SQL Database. You begin with your Web App opened in Access.

 

Step 1: Click the File Tab

Click the File Tab

 

Step 2: Click on the Manage Connections Button

Click on Manage Connections

 

Step 3: Enable Connections

I always enable connections from any location. The long and complex UID and Password should provide sufficient security. And you can always change the Password.

Click on Manage Connections

Now you can see that “Connections” is brightly colored and a light color now appears around the “From Any Location” Icon.

Color is added to Icon

 

Step 4: Click on “Enable Read-Write Connection”

Click on Enable Read-Write Connection

Now you can see that a light color surrounds the “Enable Read-Write Connection” Icon indicating the connection is enabled.

Color is added to Icon

 

Step 5: Click “View Read-Write Connection Information”

Click on View Read-Write Connection Information

 

Step 6: Copy and Paste all the connection information and save it in a secure file.

Copy Connection information

 

Step 7: Click the “Create Reports Button”

"Click Create Reports Button"

Access will create a Desktop Database for Reports in the folder you select.

 

Step 8: Open the Reports Database

The database will have linked Read-Only tables. You can create Reports to view the data but you cannot edit, insert, or delete any of the data. When you hover the cursor over the linked tables in the Navigation Pane you can see the Read-Only Connection String. But you can change the Connection String so the tables will be Read-Write with just a little code.

Read-Only Connection String

 

Step 9: Click on the “DATABASE TOOLS” Tab

Click DATABASE TOOLS Tab

 

Step 10: Click the “Visual Basic” Button

Click Visual Basic Button

 

Step 11: Click “Insert” and then “Module”

Click Insert then Module

 

Step 12: Copy the Code Below and Paste it in the Code Window

'---------------------------------------------------------------------------------------
' Procedure : ConvertTablesToReadWrite
' Author    : Patrick Wood - Gaining Access Technologies - http://gainingaccess.net/
' Purpose   : Convert Read-Only Access Web App Linked Tables to Read-Write.
' Arguments : strReadWritePWD - Your Read-Write Password.
' Example   : Call ConvertTablesToReadWrite("MyReadWritePWD")
'           : You are welcome to use this code in your Applications and share it if you
'           : keep this header with the code. There is no warranty expressed or implied.
'---------------------------------------------------------------------------------------
'
Public Sub ConvertTablesToReadWrite(strReadWritePWD As String)
On Error GoTo ERRHANDLE

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strConnect As String
    Dim strReadOnlyPWD As String
    Dim strErrors As String
    Dim lngStart As Long
    Dim lngEnd As Long

    Set db = CurrentDb

    'Loop through the TableDefs Collection.
    For Each tdf In db.TableDefs
    
        'Verify the table is an ODBC linked table.
        If Left$(tdf.Connect, 5) = "ODBC;" Then
        
            ' Skip System tables.
            If Left$(tdf.Name, 1)  "~" Then
                Set tdf = db.TableDefs(tdf.Name)
    
                ' Get the Table's Connection String.
                strConnect = tdf.Connect
                
                ' Get the Read-Only Password from the Connection String.
                lngStart = InStr(1, strConnect, "PWD=")
                lngEnd = InStr(lngStart, strConnect, ";")
                lngStart = lngStart + 4

                strReadOnlyPWD = Mid$(strConnect, lngStart, lngEnd - lngStart)
                
                ' Replace the Read-Only UID with the Read-Write UID.
                strConnect = Replace$(strConnect, "_ExternalReader", "_ExternalWriter")
                
                ' Replace the Read-Only Password with the Read-Write Password.
                strConnect = Replace$(strConnect, strReadOnlyPWD, strReadWritePWD)
                tdf.Connect = strConnect
                
                ' Apply the changes to the Table.
                tdf.RefreshLink
            End If
        End If
        DoEvents ' Enable other Windows processes to run.
    Next tdf

    ' Show a message about the Results.
    If Len(strErrors & "") = 0 Then
        MsgBox "All ODBC Tables were converted from Read-Only to Read-Write.", vbInformation
    Else
        Debug.Print vbCrLf & "Errors in Procedure ConvertTablesToReadWrite:" & vbCrLf & strErrors
        MsgBox "There are Error Messages listed in the Immediate Window!", vbExclamation
    End If

EXITHERE:
    ' Release Memory.
    On Error Resume Next
    Set tdf = Nothing
    Set db = Nothing
    Exit Sub

ERRHANDLE:
    ' Collect all Error info and continue to run the Procedure.
    strErrors = strErrors & "Error " & Err.Number & ": " & Err.Description & vbCrLf & vbCrLf
    Resume Next
    Resume
End Sub

 

The code should look like this in the Code Window.

The Code Window

 

Step 13: Run the Code

The Code Window

In the Immediate Window type in “Call ConvertTablesToReadWrite(“”)” Then enter the Read-Write Password you copied and saved between the quotation marks. To Run the code click just to the right of the Parentheses and press the “Enter” key on the Keyboard. When the code is finished running a Message Box should appear.

 

If there were no Errors you should see this message:

Confirmation Message

 

If there were Errors you should see this message:

Error Message

The Errors information is there to help you fix any problems. But an error does not necessarily mean the conversion to Read-Write failed. To check if the tables are now Read-Write you need to do the next step.

 

Step 14: Completely close Access to clear the Cache then open your database and try it out

Because Access Caches the connection string, it has cached your Read-Only connection string. You must first close Access and open it again to use the new Read-Write connection string. Note: you must close Access itself and not just the database to clear the cache.

 

Access Web Apps Hybrid Possibilities

Congratulations! You now have a new Access Web App Hybrid Application! And there are many other means of adding new functionality to Access Web Apps. We do not have to see Access Web Apps as silos all alone unto themselves. We can take the broader view of seeing Access Web Apps as a part of a dynamic and feature laden set of Applications. We can use other Apps and Applications with our Access Web Apps. We can use our Web Apps with SQL Server Management Studio, Visual Studio, Office Web Apps, and other Apps and Applications to create a broad collection of powerful and versatile Applications. We can build solutions based on Access Web Apps. Or we can build solutions to which we add Access Web Apps to enable our existing Applications to be used on the Web. We have not reached the limit of what can be achieved with Access Web Apps.

 

Exploring Grow-up Paths for Access Web Apps

There are also a number of Grow-up paths for Access Web Apps. You can migrate your Web App’s Azure SQL Database Tables and data to a SQL Server Database or a free-standing Azure SQL Database not associated with a Web App. Or you can easily convert your linked tables to local Access Desktop tables. You can learn about this and additional helpful and informative resources for Access Web Apps from Access MVPs on the web page entitled The Free Access Web App Reference from Access MVP Crystal Long.

 

Conclusion

With your new Read-Write desktop Access Database with Linked Tables you can now use the rich variety of features of Access. You can create Forms and Queries that will write to your Web App’s Azure SQL Database. Your Web App is not in a silo by itself and you can do almost everything that can be done with an Access desktop database. You can create new Access local tables. You can send emails to just about anyone. You can use automation to work with Office and other applications. You can use the broad and rich features of Windows API’s. With Access Web Apps you can have a multitude of tools in your developer tool belt.

 

Free Downloads:
Us or UK/AU Pop-up Calendar
Report Date Dialog Form in US or UK/AU Version.
Free Church Management Software with Contributions management.
Code Samples

 

Get the Access and Outlook Appointment Manager to manage all of your Outlook Calendar Appointments and Access dated information.

 

Happy computing,
Patrick (Pat) Wood
Gaining Access
http://gainingaccess.net


Return to Top

 

The Two Best Tools for Microsoft Access I Have Ever Used

Some time ago Luke Chung, the President and Founder of FMS, gave me two FMS Tools for Microsoft Access and asked me to let him know how they worked for me. I am so pleased with the FMS applications I decided to make my report public and this is it. Without a doubt the Total Access Analyzer and the Total Access Detective are the two best tools I have ever used to develop better Access applications. They both come with Free Technical Support for 30 days, after which you may purchase a support subscription, and printed User Guides which are actually small books. I never needed Technical Support because the User Guides are so helpful. Disclaimer: I am not being compensated in any way by FMS to write this article.

The Total Access Analyzer

The Total Access Analyzer helps me find errors I could never discover with other tools. It thoroughly examines the structure of my databases and every property and setting of every object in the database including the code. It shows me any errors and provides Suggestions for best practices and Performance Tips. It also reveals any issues with code and documents how my code works, and can produce diagrams. It even helps find Embedded Macros and TempVars! It is easy to see why the Total Access Analyzer has been the Winner of every Best Access Add-In Award since 1994.

The Total Access Detective

The Total Access Detective is also very useful and has helped me to document all the differences between two Access databases or between two Access objects. It works quite quickly when comparing two objects. I produce several editions of some databases and it is important that they all work the same. The Total Access Detective checks every property and setting, including code, and shows you every difference. It has helped me to produce much higher quality Access databases.

Highly Configurable

Both the Total Access Analyzer and the Total Access Detective are highly configurable. This is one of the best features of this software and I have not found comprehensive information and configurations like this available in any other tool. You can see as much data or as little as you want. You can focus on specific objects or entire databases. This versatility is very useful.

Generates Very Comprehensive and Detailed Information

The applications can take a long time to gather the data needed from your Access databases. But this is not a weakness but rather it is a strength. This meticulous and painstaking attention to the smallest detail is one of the things that sets them apart from all other Access utilities and tools. These FMS tools have earned the word “Total” in their names.

Produces a Variety of Report File Types

Another valuable feature of these tools is the ability to create and configure the hundreds of Reports available and export them in the different file types provided by Access including PDF, XPS, Excel, Word RTF file, Text file, and HTML. Plus you can save reports as tables in your database.

Conclusion: Awesome!

It is not often we can so enthusiastically recommend Tools for Access. But the Total Access Analyzer and the Total Access Detective are simply the best tools of their kind I have ever used. You can produce Access databases of the highest professional quality by using them. They make you look good. They are worth every penny you invest in them.

Get our Free Downloads:
Us or UK/AU Pop-up Calendar
Report Date Dialog Form in US or UK/AU Version.
Free Church Management Software with Contributions management.
Code Samples

Get the Access and Outlook Appointment Manager to manage all of your Outlook Calendar Appointments and Access dated information.

Happy computing,
Patrick (Pat) Wood
Gaining Access
http://gainingaccess.net


Return to Top

 

Using Microsoft Access to Manage SQL Azure Database Users and Roles

One of the best things about SQL Azure is how easy it is to manage User security. In this article we will show you how you can use Microsoft Access to add individual Users to Database Roles which makes managing User security much easier.

The Benefits of Using Flexible Database Roles

If you grant and deny permissions to individual database Users instead of using Database Roles you will have to grant and deny all the permissions to your database objects over and over again each time you create a Database User. It is much easier and efficient to grant and deny all the permissions just one time to a Database Role that you create. Then you just have to add new Users to the Role and all the granted and denied permissions are automatically applied to the User.

There are a number of additional advantages to using Database Roles. You can create as many Database Roles as you need and you can add as many Users as you need to a Role. You can add Users to more than one Role. This allows you complete control over all the permissions granted to all the Database Users. You can change a Role’s Permissions at any time and the changes will immediately apply to all Users assigned to the Role. If you no longer want to allow access to a View, you just deny the Role all permissions to the View and immediately all the Database Users that are members of that Role can no longer access that View.

This is very helpful when you develop an application for multiple users which have differing access and security requirements. You can set up one Role with Permissions for data entry, another for workers in the financial department, and another for managers. Controlling Permissions with Database Roles enables you to make sure the financial information is only accessed by authorized users, and the managers can see the Reports they need.

Understanding Flexible Database Roles

Before you begin creating SQL Azure Database Roles there are a few things that you need to know. First, SQL Azure has two types of Database roles: fixed Database Roles which have fixed permissions and flexible Database Roles which you can create and for which you can grant and deny permissions as you choose. You can learn more about fixed database roles from these articles: Permissions of Fixed Database Roles (Database Engine) and Understanding SQL Server fixed database roles.

We will be working with the flexible Database Roles which you create and are able to control the permissions granted and denied to the Role. Using this method you need to create the Role before we work with Permissions. And in this case we will also add users to the Role before granting and denying Permissions.

How to Create Flexible Database Roles

The syntax for creating a Database Role is CREATE ROLE role_name [ AUTHORIZATION owner_name ]. If the optional owner_name is not used the User that created the Role will be the owner of the Role. Normally I use the SQL Azure Administrator account to create roles so I do not usually bother with the Authorization statement. So the T-SQL we will use with SQL Azure to create the Role looks like this:

CREATE ROLE MyRoleName

Using a Form to Create Flexible Database Roles

That is just about as simple as you can get. But we can also create a simple Form to make it easier to create a new Role. To do this, I added a textbox named “txtNewRole” with the label caption “Enter New Role Name”. I also added a command button named “cmdCreateRole”. After making some improvements to the appearance the form looked like this:

 

Create Roles Form

 

The code that runs when the “Create New Role” button is clicked first Validates there is a value in the txtNewRole text box, builds a SQL Statement and passes it to the ExecSQLAzureSQL Function. The Function returns a value of “True” to the Sub if the Role was successfully created or “False” if it fails. The Sub then informs us of the success or failure to create the Role.

 

Private Sub cmdCreateRole_Click()

    Dim strSQL As String

    'Verify a Role name has been entered.
    If Len(Me.txtNewRole & vbNullString) = 0 Then
        MsgBox "Please enter a name for the new Role.", vbCritical
    Else
        'Create the T-SQL to be passed to SQL Azure.
        strSQL = "CREATE ROLE " & Me.txtNewRole
        
        'Create the new Role.
        '"BO_sR&ai>xc%ZwE_UdVe~9^DPBp1m" is used like a Password to safeguard the ExecSQLAzureSQL Procedure.
        If ExecSQLAzureSQL(strSQL, "BO_sR&ai>xc%ZwE_UdVe~9^DPBp1m") = False Then
            MsgBox "The Role was not created. Please try again.", vbCritical
        Else
            MsgBox "The Role """ & Me.txtNewRole & """ was created.", vbInformation
        End If
    End If
    
End Sub

 

How to Execute T-SQL in SQL Azure Using a Pass-through Query and VBA

The ExecSQLAzureSQL Function uses a temporary Pass-through query in code to “pass” your SQL, without modifying it, directly to the SQL Azure Database specified in the Connection String. This Function can be used any time you need to execute a T-SQL statement in SQL Azure that does not return records. It returns “True” if the SQL is executed successfully and “False” if it fails. The comments in the Function explain what the code is doing.

*Update: My good friend known as “The Smiley Coder” pointed out that this procedure needed to be made secure. He was absolutely right. So I have modified the procedure below. To secure the connection string and other code whenever I provide databases to clients for users I give them a mde or accde database file so they cannot see the code. Plus I often pass the connection string as a Procedure Argument. Then to provide even more security I use a Procedure Argument like a password the way the “strIn” Argument is used in ExecSQLAzureSQL below.

 

'Public Function ExecSQLAzureSQL
'Place this Procedure in a Standard Module.
'Executes the SQL using a Pass-through Query.
'The SQL is executed in the SQL Azure database in the connection string.
'This procedure is for "Action Queries" SQL that does not return records.
'
Public Function ExecSQLAzureSQL(strSQL As String, strIn As String) As Boolean
On Error GoTo ErrHandle

    Dim db As DAO.DATABASE
    Dim qdf As DAO.QueryDef

    ExecSQLAzureSQL = False 'Default Value

    'This line ensures the code only runs if the "Password" Argument "strIn" is correct.
    If strIn = "BO_sR&ai>xc%ZwE_UdVe~9^DPBp1m" Then

        Set db = CurrentDb

        'Create a temporary unnamed Pass-through QueryDef. This is a
        'practice recommended in the Microsoft Developer Reference.
        'The order of each line of code must not be changed or the code will fail.
        Set qdf = db.CreateQueryDef("")

        'Use a function that returns the Connection string to the SQL Azure database.
        'Change the obfuscatedFunctionName's name for Security.
        qdf.Connect = obfuscatedFunctionName("Wb_gR%/PD\-k&yZq~j>l")

        'Set the QueryDef's SQL as the strSQL passed in to the procedure.
        qdf.SQL = strSQL

        'ReturnsRecords must be set to False if the SQL does not return records.
        qdf.ReturnsRecords = False

        'Execute the Pass-through query.
        qdf.Execute dbFailOnError

        'If no errors were raised the query was successfully executed.
        ExecSQLAzureSQL = True
    End If

ExitHere:
    'Cleanup for security and to release memory
    On Error Resume Next
    Set qdf = Nothing
    Set db = Nothing
    Exit Function

ErrHandle:
    MsgBox "Error " & Err.Number & vbCrLf & Err.Description _
    & vbCrLf & "In procedure ExecSQLAzureSQL"
    Resume ExitHere

End Function

 

How to Get the Connection String to a SQL Azure Database

The obfuscatedFunctionName Function returns the Connection String. The SQL is executed in the SQL Azure Database specified in the Connection String: “DATABASE=MySQLAzureDatabaseName;”.

 

'It is best to change the name of this procedure for better security for your use.
'The strIn Argument value, "Wb_gR%/PD\-k&yZq~j>l", is used like a Password to keep
'unauthorized users from getting your Connection String. You should also change it
'to suit you before you use it in a distributed application.
Public Function obfuscatedFunctionName(strIn As String) As String
    If strIn = "Wb_gR%/PD\-k&yZq~j>l" Then
        obfuscatedFunctionName = "ODBC;" _
            & "DRIVER={SQL Server Native Client 11.0};" _
            & "SERVER=tcp:MyServerName.database.windows.net,1433;" _
            & "UID=MyUserName@MyServerName;" _
            & "PWD=MyPassword;" _
            & "DATABASE=MySQLAzureDatabaseName;" _
            & "Encrypt=Yes"
    Else
        obfuscatedFunctionName = vbNullString
    End If
End Function

 

How to Use a Form to Add a User to a SQL Azure Database Role

We can add Users to the Role we have created using the sp_addrolemember System Stored Procedure in this manner:

 

EXEC sp_addrolemember 'MyRoleName', 'MyUserName';

 

We can modify the Form we created earlier to add Users to Roles. I added a text box named “txtRoleName” and another named “txtUserName”. After adding a command button named “cmdAddUserToRole” this is how the Form appears:

 

Manage Users and Roles Form

 

The following code was added to the cmdAddUserToRole button’s click event to add the User to the Role:

 

Private Sub cmdAddUserToRole_Click()

    Dim strRole As String
    Dim strUser As String
    
    'Verify a Role name has been entered.
    If Len(Me.txtRoleName & vbNullString) = 0 Then
        MsgBox "Please enter a name for the Role.", vbCritical
    Else
        strRole = Me.txtRoleName
        
        'Verify a User name has been entered.
        If Len(Me.txtUserName & vbNullString) = 0 Then
            MsgBox "Please enter a name for the User.", vbCritical
        Else
            strUser = Me.txtUserName

            'Now that we have both a Role and a User Name we
            'can call a Function to add the User to the Role.
            Call AddUserToSQLRole(strRole, strUser, "X~K<2%fM>Tn5ejxJ$R&ZEyUzh")
        End If
    End If
    
End Sub

 

Using a Function to Add a User to a SQL Azure Database Role

The command button’s code calls the AddUserToSQLRole Function which we need to place in a Standard Module. This Function passes the necessary T-SQL to the ExecSQLAzureSQL Function we used earlier. One of the basic principles of good programming practice is DRY (Don’t Repeat Yourself). It is better to write one Procedure that does the same job repeatedly and reliably than it is to write the same code over and over again. Using the DRY principal you write less code and make fewer mistakes. It also makes your code easier to debug, manage, understand, and reuse in other applications.

*Update: To provide better security we have added an Argument to this Procedure to use like a Password as we did in the ExecSQLAzureSQL and obfuscatedFunctionName Procedures.

 


Function AddUserToSQLRole( _
    strRole As String, _
    strUser As String, _
    strIn As String _
    ) As Boolean

    Dim strSQL As String
    
    AddUserToSQLRole = False 'Default Value

    'This line ensures the code only runs if the "Password" Argument "strIn" is correct.
    If strIn = "X~K<2%fM>Tn5ejxJ$R&ZEyUzh" Then

        'Build the T-SQL that will add the User to the Role.
        strSQL = "EXEC sp_addrolemember '" & strRole & "', '" & strUser & "';"
    
        'Call the Function to execute the SQL.
        If ExecSQLAzureSQL(strSQL, "BO_sR&ai>xc%ZwE_UdVe~9^DPBp1m") = True Then
            AddUserToSQLRole = True

            'Inform the User of Success
            MsgBox "User """ & strUser & """ was added to the """ _
                & strRole & """ Role.", vbInformation
        Else
            'Inform the User of Failure.
            MsgBox "User """ & strUser & """ was NOT added to the """ _
                & strRole & """ Role." & vbCrLf & vbCrLf _
                & Space(30) & "Please try again.", vbCritical
        End If
    End If

End Function

 

If your code fails the cause may be a failure to make the connection to SQL Azure. Often the failure may just be an intermittent and temporary issue that can occur for a number of reasons. So it is best to encourage the user to try again because the second attempt will often succeed.

Summary

We have examined how to create SQL Azure Database Roles and add Users to those Roles. We are now prepared to control with precision which users have access to the objects in SQL Azure by using SQL Azure Permissions. We will address that crucial subject in another article.

Get the Code

You can download the code used in this article from our Free Code Samples page.

More Free Downloads:
Us or UK/AU Pop-up Calendar
Report Date Dialog Form in US or UK/AU Version.
Free Church Management Software with Contributions management.
Code Samples

Get the Access and Outlook Appointment Manager to manage all of your Outlook Calendar Appointments and Access dated information.

Happy computing,
Patrick (Pat) Wood
Gaining Access
http://gainingaccess.net


Return to Top

 

How to Create SQL Azure Database Users With Microsoft Access VBA

Why do we need a SQL Azure Database User Account? An Access Database cannot access SQL Azure Objects such as Tables, Views, or Stored Procedures without one. That is, unless you use your SQL Azure Administrator account which would be living very dangerously if you were distributing your database to others. So before your database can do anything with SQL Azure, a Database User Account must be created that it can use. Also permissions must be granted to use the necessary SQL Azure Tables, Views, and Stored Procedures. We are going to show you how you can use a pass-through query in Access to create SQL Azure Database Users using Access VBA.

Database users must be created in the database in which they will exist because the “USE” statement can only work for the current database in SQL Azure. So to create a Database User we must use a query that runs in the Database in which we want to create the User. And since it would be confusing, to me at least, to log in using “JoeDeveloper” and work as Database User “SamCodeSlinger” my normal practice is to create a Database User with the same name as a Login Name.

If we were using SQL Server Management Studio (SSMS) or the Windows Azure Management Portal we could create a Database User as shown with the following Transact-SQL (T-SQL):

CREATE USER MyLoginName FOR LOGIN MyLoginName

Or:

CREATE USER MyLoginName FROM LOGIN MyLoginName

But you can easily create Database Users with Microsoft Access using the following two procedures, passing the Login Name to the CreateSQLAzureDBUser Function:

 

'Example usage: Call CreateSQLAzureDBUser("MyLoginName")
Public Function CreateSQLAzureDBUser(strLoginName As String) As Boolean
On Error GoTo ErrHandle

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String

    CreateSQLAzureDBUser = False 'Default Value

    strSQL = "CREATE USER " & strLoginName & " FOR LOGIN " & strLoginName

    'Create the Database User
    Set db = CurrentDb
    Set qdf = db.CreateQueryDef("")
    'Change obfuscatedFunctionName to the name of a Function
    'that Returns your SQL Azure Database Connection String
    qdf.Connect = obfuscatedFunctionName 
    qdf.SQL = strSQL
    qdf.ReturnsRecords = False
    qdf.Execute dbFailOnError

    'If no errors the Database User was Created
    CreateSQLAzureDBUser = True

ExitHere:
    'Cleanup for security and to release memory
    On Error Resume Next
    Set qdf = Nothing
    Set db = Nothing
    Exit Function

ErrHandle:
    MsgBox "Error " & Err.Number & vbCrLf & Err.Description _
    & vbCrLf & "In procedure CreateSQLAzureDBUser"
    Resume ExitHere

End Function

 

The Database User must be created in the Database in which it is to be used and not in the master Database. You can do this by changing MySQLAzureDatabaseName in the obfuscatedFunctionName Function to the name of the database in which you want to create the users.

 

'It is best to change the name of this procedure for better security for your use. 
'The strIn Argument value, "Wb_gR%/PD\-k&yZq~j>l", is used like a Password to keep 
'unauthorized users from getting your Connection String. You should also change it 
'to suit you before you use it in a distributed application.
Public Function obfuscatedFunctionName(strIn As String) As String

    If strIn = "Wb_gR%/PD\-k&yZq~j&gt;l" Then
        obfuscatedFunctionName = "ODBC;" _
            & "DRIVER={SQL Server Native Client 10.0};" _
            & "SERVER=tcp:MyServerName.database.windows.net,1433;" _
            & "UID=MyUserName@MyServerName;" _
            & "PWD=MyPassword;" _
            & "DATABASE=MySQLAzureDatabaseName;" _
            & "Encrypt=Yes"
    Else
        obfuscatedFunctionName = vbNullString
    End If

End Function

 

For better security you can keep the Login Name, Password, and User Name hidden in the code without exposing it to the Access user.

We now have the ability to create Database Users but we still need to Grant Permissions before our Access Database can use this User Account to access any data in SQL Azure. We plan to show how you can do that in the articles to come.

You can download the code used in this article from our Free Code Samples page.

More Free Downloads:
Us or UK/AU Pop-up Calendar
Report Date Dialog Form in US or UK/AU Version.
Free Church Management Software with Contributions management.
Code Samples

Get the Access and Outlook Appointment Manager to manage all of your Outlook Calendar Appointments and Access dated information.

Happy computing,
Patrick (Pat) Wood
Gaining Access
http://gainingaccess.net

How to Plug Microsoft Access accde and mde Security Leaks

I was shocked. I had heard that Constants and Variables could be seen by opening an accde or mde file with a hex editor but when I opened my file I was stunned to see so much of my data, objects, and code in plain text. I am not an Access Security expert, but I decided to take on the challenge of this security issue and when I was through I was very pleased with the results. In this article we will examine the security risks of accde and mde files and present some ways you can greatly improve the security of your files.

The Bad News About accde and mde File Security

You don’t have to be an Access expert to break the security of an Access Database. Anyone can do it with a free hex editor easily downloaded from the internet. The hex editor revealed the data in every local table in my accde database and worse.

1) A Simple Hex Editor Reveals Much Information

Here is a partial list of what I was able to read with a free hex editor:

  • Names of all Tables both Local and Linked
  • All Field Names in Local Tables
  • All Values in Tables Except for Hyperlinks
  • All Query Names and Query SQL
  • All Global Constants
  • All Module Names
  • All Procedure Names
  • Most Procedure Arguments
  • Some Code in Procedures
  • Usually Two or More Variable Names in Each Procedure
  • All Form RecordSource Queries and SQL
  • All Control Names and Property Values
  • All List Box and Combo Box RowSource Queries and SQL Statements
  • All References with File Names and Full Paths

I found the code of a couple of procedures virtually intact. As we shall see, this revealed data can lead to even further exploitation.

2) Your Forms and Controls are Vulnerable

If you have controls on a form that hold sensitive data such as confidential Customer information a user can very easily get that data if they can open the database. A control with the name txtSQLServerConnection may give someone access to the company database. Who knew we were so vulnerable?

3) Those Who Know How Can Read Your Constants and Run Your Procedures

For those who know how, it is very easy to read your Constants and run your Subs and Functions. I will not reveal how it is done here, but for experienced developers it is a piece of cake.

I know of no way of safeguarding your Constants short of encrypting your database and that option is not always available. So I think it best not to use Constants for any sensitive data like Connection Strings or Passwords.

Most procedures can easily be run by those who know how. This can reveal a lot of sensitive data and if you use queries in code your database is especially vulnerable. Your data may easily be read, altered, or deleted. That is depressing enough without going further, so I will share some good news.

The Good News About accde and mde File Security

1) Encrypting Your File May Provide Greater Security

Encrypting your database with a password may provide better protection in keeping unauthorized users from getting information from your database. I wrote “may provide” because there are some cases where this does not help. Unfortunately it can’t keep the authorized users from giving their password to others or taping it on a note on their monitor or trying to hack the database themselves. The security of your database is only as strong as your weakest user. In addition, encrypting a database is not always an option for distributed applications, especially when users have different versions of Access because they can only be opened using the version of Access by which they were encrypted.

2) Obfuscate the Names of Procedures, Arguments, and Variables

Variables strA, strB, etc. are a lot better at concealing important information than strAccountNumber and strPassword. Fortunately your comments all get removed when the accde or mde file is created. So if you don’t want to purchase software to obfuscate your code for you, you can do it yourself and use comments in your original accdb or mdb file to document your code so you can always know what your code is doing.

3) Use a Procedure Argument As a “Password”

It is so very easy to run the procedures in an accde or mde file once you know how. Give me your unencrypted accde or mde file and I can be running procedures in it in less than 5 minutes without any preparation in advance. So I am not going to share that secret here. But I will share how you can easily foil this type of attack.

If we can use Passwords to open files then we can use them to protect our code from hackers who would run our procedures. When I write an important procedure I add an additional Argument that is used like a Password. When the Procedure runs it checks for that Argument and if it is not correct then the Procedure does nothing. The following example shows how this works.

'The PartColorNotes Procedure is named to mislead hackers.
'In this case it returns the SQL Azure Database Connection String
Public Function PartColorNotes(strIn As String) As String

    If strIn = "Wb_gR%/PD\-k&yZq~j&gt;l" Then
        PartColorNotes = "ODBC;" _
            & "DRIVER={SQL Server Native Client 10.0};" _
            & "SERVER=tcp:MyServerName.database.windows.net,1433;" _
            & "UID=MyUserName@MyServerName;" _
            & "PWD=MyPassword;" _
            & "DATABASE=MySQLAzureDatabaseName;" _
            & "Encrypt=Yes"
    Else
        PartColorNotes = vbNullString
    End If

End Function

First, if I was a hacker I could not care less about the PartColorNotes. And I would definitely give up trying to run this procedure long before I discovered the “Password” and that is the point. We cannot keep the bad guys totally out of our database but we can make it so difficult to get our information it is not worth the effort. Some good news about this method is that after using my other security measures I did not see any string values in my accde and mdb files so this security measure should be very effective.

Below is a procedure that can produce the “Passwords” for your Procedure Arguments and which you can also use to create SQL Azure and SQL Server Passwords.

'---------------------------------------------------------------------------------------
' Procedure : GetPWDCharacters
' Author    : Patrick Wood  http://gainingaccess.net
' Date      : 8/10/2011
' Purpose   : Get Random Characters (These are also safe to use for ODBC Passwords)
' Arguments : lngCount is the number of characters you want returned
' Example   : strPassword = GetPWDCharacters(15) - Returns a string of 15 characters
'---------------------------------------------------------------------------------------
Function GetPWDCharacters(lngCount As Long) As String

    Dim i As Long
    Dim j As Long
    Dim arrCharacters As Variant
    Dim strCharacters As String

    'Build an array of Characters including all letters, numbers, and safe special characters
    arrCharacters = Array( _
         "o", "S", "v", "2", "y", "Z", "I", "%", "t", "x", "6", "W", "q", "_", "j", "u", _
         "8", "k", "L", ">", "m", "C", "R", "/", "P", "9", "d", "h", "^", "w", "B", "f", _
         "+", "Q", "i", "E", "&", "a", "X", "U", "=", "g", "b", "F", "$", "O", "Y", "J", _
         "3", "0", "K", "p", "~", "s", "M", "4", "H", "l", "#", "D", "G", "N", "\", "V", _
         "c", "z", "1", "5", "T", "e", "7", "n", "<", "A", "r")

    'Loop the number of times entered
    For i = 1 To lngCount
        'Get a random number to use with the array
        j = Int((UBound(arrCharacters) - LBound(arrCharacters) + 1) * Rnd + 0)

        'Get a Random character and add it to the string
        strCharacters = strCharacters & arrCharacters(j)
    Next i

    GetPWDCharacters = strCharacters

End Function

4) Use SQL Server Or SQL Azure As a Back End

The only way to secure the data in your tables is to place your tables in a secure back end database such as SQL Azure or the Free Express version of Microsoft SQL Server. SQL Server Express has excellent security features that even meets HIPA security requirements. SQL Server’s fine grained security features also enable you to Grant and Deny Permissions to SQL Server objects or individual users to Database Roles. And SQL Server Express is free for you to download and distribute.

There is no foolproof way to keep data stored in Access tables secure. Nothing I did could keep the table values from being read as plain text in a hex editor. Even if you did go through the trouble of encrypting your data before storing it in your tables, it is still too easy for someone to delete or change your data. That is why it is so great to have the free Express versions of SQL Server available to us.

If you are using SQL Azure or SQL Server linked tables in Access you can write a function that returns the Connection String such as the PartColorNotes Function above. This enables you to safely use code to access your tables. To avoid the security risks involved with using ODBC linked tables see my article entitled Building Safer SQL Azure Cloud Applications with Microsoft Access.

5) Decompile Your Database Immediately Before Creating Your accde or mde File

This is a very crucial step. This did more to keep my sensitive information from being read with a hex editor than anything else I tried, with the exception of encrypting the database with a Password. This is most effective if you use linked tables for important information which prevents a hex editor from reading the table values.

Databases need to be Decompiled because Compacting and Repairing does not remove all the old code you no longer use. It leaves behind a large amount of sensitive information that can be exploited. I found that Decompiling my database and then immediately creating an accde or mde file removed a huge amount of text, code, and other sensitive information from my file. This was so effective it restored my faith in using accde and mde databases.

Decompiling your database should be the last step you take before creating the accde or mde file. FMS provides an article with instructions that shows you how to decompile your database as well as create a shortcut that makes decompiling much easier. After decompiling your database it will usually be much smaller and less likely to have unexplained errors.

Decompiling is however an undocumented and unsupported feature so to be safe you should never decompile without making a backup copy first. It is best to keep your original file in a safe place and decompile the copy of the database.

Summary

We have looked at some of the serious security issues with accde and mde files and have presented a number of steps to greatly improve the security of your databases. You should now be able to distribute your accde and mde files with greater confidence in their security.

You can download the code used in this article from our Free Code Samples page.

Get the free Demonstration Application that shows how effectively Microsoft Access can use SQL Azure as a back end.

More Free Downloads:
Us or UK/AU Pop-up Calendar
Report Date Dialog Form in US or UK/AU Version.
Free Church Management Software with Contributions management.
Code Samples

Get the Access and Outlook Appointment Manager to manage all of your Outlook Calendar Appointments and Access dated information.

Happy computing,
Patrick (Pat) Wood
Gaining Access
http://gainingaccess.net

Return to Top

 

Follow

Get every new post delivered to your Inbox.

Join 84 other followers