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.
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
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
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.
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:
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.
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
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:
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
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.
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,
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.
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
We use Microsoft® SQL Azure™ to provide safe and affordable 24/7 internet access to your company's vital information.
Contact us for a Free Consultation, feedback, or more information:
Page layouts based on Matthew James Taylor's Perfect multi-column CSS liquid layouts
© 2008-2010 Patrick Wood