SQL Azure Security

Building Safer SQL Azure Cloud Applications with Microsoft Access

The Security Risks of Distributing SQL Azure Applications Using Microsoft Access

When we distribute Microsoft Access applications that use SQL Azure it is vital that we make our applications as secure as possible. Whether we use a DSN (Data Source Name) to link tables and pass-through queries to SQL Azure or if we use DSN-less linked tables and pass-through queries we need to be aware of the serious security risks involved with both of these methods. We need a better way to ensure that our applications are not hacked by company employees or unauthorized users.

We can make a database more secure if we encrypt it. However, if we encrypt an Access 2010 database it cannot be opened by customers using Access 2007. Distributing an encrypted database is not secure since users can expose or even share the password with others. And once the password has been entered the connection string can be easily accessed by the user. Moreover, you cannot use that database as an accde file and conceal the connection string in the code because an accde file cannot be encrypted.

Security Risks with Linked Tables and Pass-through Queries Using a File DSN

SQL Azure does not support Windows Authentication. However, we can easily use a file DSN which contains our SQL Azure connection string to distribute an application that uses linked tables and pass-through queries. But a DSN file stores our connection string in plain text. This means our SQL Azure server name, user name, and database name are easily accessed once the file is located. A DSN file is easily opened by Notepad.

Now that is a serious breach of security!

However, in order to make our linked tables work with SQL Azure we have to expose our password as well. When linking OCDB tables in Microsoft Access, in order for them to work seamlessly with our application we must check the "Save password" checkbox in the Link Tables Dialog. This saves the user name and password in the linked table's Connect property.

A dialog warns that users will be able to see your user name and password. Even if we hide everything and use accde or mde files the danger still remains as we shall soon see.

Security Risks When Using DSN-less Tables and Pass-through Queries

If we decide to use DSN-less tables and pass-through queries the same user name and password information is saved in the TableDef and QueryDef Connect property. This makes it very easy for an unauthorized user to use the following code to get our connection string from our ODBC linked tables whether we use a DSN or not:

Sub ListRemoteTdfProps()
    Dim dbRemote As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strDbPath As String

    strDbPath = "C:\MyDatabases\MySQLAzureApp\MySQLAzureDB.accde"
    Set dbRemote = DBEngine(0).OpenDatabase(strDbPath)

    For Each tdf In dbRemote.TableDefs
        Debug.Print tdf.Name
        'The TableDef Connect Property Value is the Connection String
        'to our SQL Azure Database!
        Debug.Print tdf.Connect
    Next

    Set dbRemote = Nothing
End Sub

That is just too easy! Similar code returns the pass-through QueryDef Connect property value. The unauthorized user can easily gain access to all the same objects as the user in the connection string. Since we need to enable our applications to update, append, or delete records these same powers can now fall in the hands of the unauthorized user and the outcome could be disastrous. Worse, if we have foolishly used our original SQL Azure login the entire database could be deleted.

How We Can Build A More Secure SQL Azure Application

We can make our SQL Azure database much more secure by using code to make the needed connections and saving our databases as accde or mde files to protect the code. We can make the ODBC connection by using a nameless pass-through query that is not appended to the QueryDef collection. We can then open a recordset and get the data to fill unbound forms. Using a QueryDef that has no name is a technique recommended in Microsoft Access Developer References. Here is some sample code like that which I use in my demonstration application. There are plenty of comments to help explain what the code is doing.

Sub FillMyControls()
On Error GoTo ErrHandle

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset

    Set db = CurrentDb

    ' Create a temporary QueryDef with no name so it is
    ' not automatically appended to the QueryDefs Collection
    ' This technique is recommended in Microsoft Access Developer References
    Set qdf = db.CreateQueryDef("")

    ' Setting a connection string automatically makes this a pass-through query
    ' This includes your server name, username, password, and database
    ' I believe it is safer to use a function rather than a constant to get the
    ' connection string. I use a name that obfuscates what the function does
    qdf.Connect = obfuscatedFunctionName

    ' In my demonstration application I fill my forms with one record when
    ' they open. I use the pass-through query to call a Stored Procedure
    If IsNull(myAddressID) Or myAddressID = 0 Then
        ' This form is now being opened
        ' Use a Stored Procedure to select the top 1 record
        qdf.SQL = "EXEC [MySchema].[MyStoredProcedureTop1]"
    Else
        ' The Find record Combo box has been used
        ' Get the selected record with a Stored Procedure
        qdf.SQL = "EXEC [MySchema].[MyStoredProcedure] " & myAddressID
    End If

    ' If the QueryDef returns records set the QueryDef
    ' ReturnsRecords property to True, else set it to False
    qdf.ReturnsRecords = True

    ' Create a new recordset using the QueryDef OpenRecordset Method
    ' Since pass-through queries are not updatable use dbOpenSnapShot
    Set rst = qdf.OpenRecordset(dbOpenSnapshot)

     With rst
        .MoveFirst
        Do While Not .EOF
            ' In this case we are using unbound controls on the form.
            Me.txtAddressID = !AddressID
            Me.txtCustomerID = !CustomerID
            Me.txtCustomerName = !CustomerName
            Me.txtAddressType = !AddressType
            Me.txtAddressLine1 = !AddressLine1
            Me.txtAddressLine2 = !AddressLine2
            Me.txtCity = !City
            Me.txtStateProvince = !StateProvince
            Me.txtPostalCode = !PostalCode
            Me.txtCountryRegion = !CountryRegion
            .MoveNext
        Loop
    End With

ExitHere:
    On Error Resume Next
    rst.Close
    Set qdf = Nothing
    Set rst = Nothing
    Set db = Nothing
    Exit Sub

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

I use a Function to return the connection string because the names of Constants and Variables in accde and mde databases can be read using some types of software. To connect to SQL Azure the Function needs to return a connection string like this:

Public Function obfuscatedFunctionName() As String
    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"
End Function

SQL Azure provides the ability to encrypt the connection string as it passes over the internet to the Server which is a very nice feature.

You can also use local tables to temporarily hold records which enables you to use regular queries as the recordsource for forms. This is how I made the Master form and Subform in the demonstration application work smoothly and quickly in the Sales Order Form. I also load the local tables with the initial record for the Master form and the form's Combo box when the application first opens. This helps the Sales Order form to open more quickly the first time. I also use local tables to hold the records for the Combo boxes which we use to find records. In a larger database I would use other means to help the user find records. Here is an example of the code.

Sub FillComboTable()
On Error GoTo ErrHandle

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    Dim rstCbo As DAO.Recordset
    Dim strSQL As String

    ' First clear out the local table
    strSQL = "DELETE * FROM Table1;"
    Set db = CurrentDb
    db.Execute strSQL, dbFailOnError

    ' Prepare the RowSource for the Combo box
    strSQL = "SELECT Field1, Field2, Field3" _
        & " FROM Table1" _
        & " ORDER BY Field3, Field2;"

    Set rstCbo = db.OpenRecordset("Table1", dbOpenDynaset)

    'Get the records from SQL Azure
    Set qdf = db.CreateQueryDef("")
    qdf.Connect = obfuscatedFunctionName
    qdf.SQL = "EXEC [MySchema].[MyStoredProcedureCbo]"
    qdf.ReturnsRecords = True
    Set rst = qdf.OpenRecordset(dbOpenSnapshot)

    ' Now use the two recordsets to copy the data to the local table
     With rst
        .MoveFirst
        Do While Not .EOF
            rstCbo.AddNew
            rstCbo!Field1 = !ProductID
            rstCbo!Field2 = !ProductName
            rstCbo!Field3 = !CategoryName
            rstCbo.Update
            .MoveNext
        Loop
    End With

    ' Since this Sub is called from the Form Load Event
    ' we do not need to requery the Combo box
    Me.MyFindProductCbo.RowSource = strSQL

ExitHere:
    On Error Resume Next
    rst.Close
    rstCbo.Close
    Set qdf = Nothing
    Set rst = Nothing
    Set rstCbo = Nothing
    Set db = Nothing
    Exit Sub

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

End Sub

After using code like this we can save our databases as accde files for distribution. But there are even more steps we can take to significantly tighten security.

We Can Use SQL Azure to Strengthen Security

There are a lot of advantages to using the robust features of SQL Azure to do as much of the work as possible to increase the speed and security of our application. That is why I make heavy use of Stored Procedures and use the pass-through queries to call them. And when we need to insert, append, or delete data we can use Stored Procedures that ensure the parameters entered are strictly limited, adding another vital layer of protection. Using Stored Procedures keeps things neat, simple, fast, and secure.

Another source of strong security is the Roles and Permissions that are available for us to use in SQL Azure. We can Grant the Microsoft Access application only the permissions to use those objects that are absolutely necessary to get the job done. We never give the application permission of any type to a table. That way even if someone was able to hack the application's connection information they would be strictly limited to only the Objects in SQL Azure granted to the application. We are just skimming the surface of the security provided by SQL Azure.

Summing Things Up

My goal with this article is to help you more safely develop SQL Azure applications using Microsoft Access. And while we cannot develop perfect security, we can make it much harder to break our security. No doubt there are improvements that can be made to the code used here. I welcome your feedback and your constructive criticism. Hopefully together we can improve on using Microsoft Access as a front end to SQL Azure. You are most welcome to make comments here.

We have a simple demonstration application that uses the techniques mentioned here that you can download from this page.

Return to Access and SQL Azure Information Center

Return to Top