When you start to connect Access or Office to SQL Server, you will face a number of choices in setting up and configuring a Data Source Name, or DSN. This article will help you to make the most suitable choices.

Security

Before you start to setup a connection with SQL Server, you need to decide what kind of security you intend to use.

We suggest four alternatives which you can consider.

• Use windows authentication, which means that you will need to manage you users on the SQL Server. This offers simple traceability, seamless operation and excellent security. Each user will require to be enrolled in the SQL Server and granted appropriate individual access to the database. You will either need to use the the Management Studio or build some application components to manage security.
• Use windows authentication but manage the logins on the SQL Server for one or more windows groups, this makes managing the security on SQL Server simpler (as the windows groups need only be setup once), but you loose the ability to easily identify individual users on the SQL Server (as common SQL Server functions like suser_sname() now return the group name and not individual users name, this can be overcome in the application by identifying individual users with for example API calls).
• Use one or more SQL Server login accounts, these are one-off setup accounts which will be used by all your users, this is suitable if you don’t care about tracking individual users or possible have your own front-end or table driven security system. When a user attempts to touch the SQL Server they will be prompted for this single login name and password.
• Follow the above but embed the SQL Server login and password in all connection strings. This offers very low security; these details are not encrypted and will be visible to those who know their way around Access. But is very easy to use.

Top of Page

Drivers

If you look at the ODBC Data Source Administrator in the Windows Administration Tools, you will see an interface like the following.

This is the same interface that you get in Excel or Access, when you try and create a link to ODBC data. On most machines you are likely to find you will have the ‘SQL Server’ driver, which makes it the universal preferred choice as nothing needs to be installed on client workstations. A machine with SQL Server 2005 installed will also have the ‘SQL Native Client’ drivers, and 2007 has yet another new set of drivers.

So which drivers do you want to use? Whatever choice you make, you will need to ensure that those drivers are available on the client machines, which in most cases means that if you choose anything other that ‘SQL Server’ you will need to download and install the drivers on every client machine which uses your software.

The main reason for choosing a different driver is because you want to work with newer features of SQL Server. For example to use Access VBA to read XML you must use the newest drivers, or to use MARS which returns multiple result sets you need the latest driver. If you decide to use one of the newer drivers then searching the Microsoft site will get you a download package including the newest drivers which can then be installed on client machines.

Top of Page

DSN

In Access only System or File DSN’s can be used. The DSN is what associates the specific SQL Server, driver and database together, which is saved as a connection string to get at the server.

First take a look at creating a system DSN. When building any DSN you will need to select the driver, enter the server name, select the database and decide whether to use Windows Authentication or a SQL Server login.

The example below shows an Access 2003 set of dialogs, but all versions are very similar.

In a blank Access database, call up the File- Get External Data – Linked Tables dialog and select as shown below.

Then select New as shown below.

You may also have a further choice to make depending on your windows environment.

All going well you will end up with the following dialog, having successfully created a system dsn.

Pressing Ctrl-G and using the immediate window we can see the connection string (the example below has irrelevant details removed).

The above example we are using Windows Authentication (Trusted Security). Now you can see the reference to the DSN (NorthwindSystemDSN) in the connection string.

In corporate environments these DSN’s will be centrally deployed to workstations. One of the ideas behind this is that if a server name for example had to be changed then a new DSN could be deployed to the desktop without requiring any changes to be made in applications which refer to the dsn.

Avoiding using System DSN’s saves you the trouble of setting up the DSN (or transfer appropriate files) on every workstation. This is achieved by creating a File DSN instead of a System DSN, this requires a similar set of steps that used when creating the system dsn.

Now following the same steps we end up with the following.

Once completed, we can examine a tabledef's connection string.

A database which used a File DSN links does not need the DSN created on each target workstation, all it requires is the appropriate ODBC driver to be available.

Below is a comment from one of our customers (John Newmarch) and we thought that this is an additional appropriate clarification.

In creating a File DSN, you have to create a .DSN file, however the connection that is created is "DSN-less". If you move the Access application with the connection in place to a users machine, then they don't need the .DSN file, however it had to exist on the machine when the connection was originally created.

Also read our article (Relinking tables and views between Access and SQL Server) which explains how these connections are managed in program code.

Top of Page

Embedding Passwords

You may have noticed, when linking tables that you have an option to save the username and password in the connection string. If you answer yes, then as expected these credentials get embedded in the connection string (this is not very secure as the details are held as plain text in the query or tables connection string information). This only applies when using SQL Server security, if using windows authentication then no additional details are saved.

Top of Page

Ports and SQL Browser

The last word to add on DSN’s concerns ports. In many organisations it will be a c ompany policy to switch off the “SQL Browser” task on the SQL Server which listens for connections on all ports and instead allocate a SQL Server to a specific port; thus improving security. In this case you need to add ‘;Port=xxx’ to your connection string where the number like xxx will be given to you by the database administrators.

MUST+SQL allows you to upsize into multiple schemas splitting up an application into manageable chunks or bringing together multiple databases and simplifying security. Click below to improve your development productivity.

Top of Page

by Andrew Couch Office Access MVP, Director ASC Associates andy@ascassociates.biz