Problem
We have a few SQL sandbox databases set up in our environment that allow our developers and some key end-users the ability to do some temporary development without expectations of performance or durability. Quite often these individuals will want to use Access as their front-end GUI tool for these databases. Realizing that this solution exposed the Database Administrator to endless phone calls asking for assistance, I developed a document that anyone could follow in order to get an SQL-via-Access implementation up and running on their own. This is the first of four tips focusing on connecting Microsoft Access to a SQL database.
The tips in this series are:
- Create an ODBC Data Source Name (DSN) using the standard SQL Server client.
- Create an ODBC Data Source Name (DSN) using the SQL Native Client (SNAC).
- Linking an Access table to SQL Server
- Creating a SQL Pass-Through query
This tip focuses exclusively on creating an ODBC Data Source Name (DSN) using the standard SQL Server client. This DSN will then be used to connect Access to SQL Server, allowing the end user the ability to create a form structure for presenting, querying, and reporting against SQL Server data; using the Microsoft Office suite of tools. These tools (Excel, Access, PowerPoint and Word) offer many of the same benefits for analyzing and presenting data that core programming languages and platforms such as C#, VB.net, and ASP do, but without the steep learning curve.
Solution
The initial step in creating any Data Source Name (DSN) is locating the Data Sources (ODBC) Control Panel icon. Navigate to the Administrative Tools via Start\Control Panel.
Selecting Data Sources (ODBC) will present you with the ODBC Data Source Administrator dialog box. There are considerable options for creating DSNs. Depending of the requirements of your environment you may choose to create a User, System, or File DSN. Let’s take a closer look at each of the three DSN types in order to allow you to make an informed decision.
User DSN
A User DSN is, you guessed it, user-specific. It is only available to the user who created it.
System DSN
Unlike a User DSN, the System DSN is available to all users of the workstation or server on which it has been created. Both SQL Server and Internet Information Systems (IIS) require a System DSN. Most-likely this will be the only DSN you will ever create.
File DSN
A File DSN is similar to a System DSN, in that it is not user-specific. Unlike User and System DSNs, a File DSN is stored in a .dsn file, not the Windows registry. This file can be emailed, shared, and installed on systems other than the one it was created on. It is important to note however that File DSNs are not ODBC-compliant.
Now that you have a general understanding of why we’ll be proceeding on with creating only System DSNs, let’s do just that. Navigate to the System DSN tab of the ODBC Data Source Administrator and select the ‘Add…’ button.
You’ll now be presented with the option of selecting the driver you’ll be utilizing for this DSN. Since this is MSSQLtips.com, we’re focusing on the SQL Server drivers. The next tip in this series will examine the differences in configuring the SQL Native Client (SNAC) that first made it’s appearance with SQL Server 2005. For the purpose of this tip, I’ll be selecting the SQL Server driver that has been associated with all previous releases of Microsoft SQL Server. Select this driver and then click Finish. This is a poorly-captioned button, since we’ve only just begun…
You’ll now be prompted for the basic information about your new SQL Server System DSN. Enter a name and description for the DSN then either type the name of the SQL instance you’ll be connecting to or select it from the instances available in the list. Two important things to remember when entering values on this dialog: you will be referring to the DSN name when utilizing Access, Excel, or other application that will be connecting to the database. Be sure that your name is not too cryptic and that it conforms to any naming conventions used in your environment. In regards to the selection of a target Server, remember that this value should be the SQL instance you desire to connect to, not the server itself. If you wish to connect to the SQL1 instance on ServerX, the value you would enter into the Server field on this form would be ServerX\SQL1. Enter the values required for your environment and click Next to continue.
At this point you are now going to be prompted to configure the security for the DSN. You should be familiar with the two options available: Windows NT authentication or SQL Server authentication. Windows authentication will pass the context of the current Win O/S login to the database, whereas SQL security uses a login/password model where both are stored as objects in the master database of the identified SQL instance. Below you will see both options presented. In either case I recommend selecting the check box authorizing a connection to the SQL instance to collect metadata about the login being passed within the DSN. This will allow you to determine if the user context of the DSN will be compatible with the security set up for this user within the SQL instance. It is my recommendation that you set up security in the SQL instance first, before creating any associated DSNs.
You’ll note that when working with the Windows method of authentication, you are unable to enter a login and password. This is specifically due to the fact that the login passed to the database for authentication will be dependent upon what login is active on the workstation/server at the time of use. Later in the DSN creation process you’ll be able to “test” the DSN. During this test, when using Windows security mode, the test will be performed with your active network login as the active user of the system at the time. Select Next to proceed.
Select the database you want to associate with this DSN. All databases that the user has any level of rights to is listed in the database combo box. If you do not see the database in this list it is because:
- If using Windows security, you as the DSN creator, do not have rights to the database
- If using SQL security, either the login\password combination is incorrect or the login does not have database rights.
After selecting the database, determine the connection-level settings for quoted identifiers and ANSI nulls, padding, and warnings the select Next.
On this form I strongly recommend that you leave all defaults in place and continue. There may be reasons to deviate from the defaults but those would most-likely be limited to situations where you’re serving data out globally to users that have different language or currency formats, or if you are not utilizing encryption schemes available to the relational database management system (RDBMS). Nine times out of ten, simply select Finish and move on.
I mentioned earlier that you would have the chance to test your DSN and this is the time. Select the ‘Test Data Source…’ button on the bottom left. The DSN settings are displayed below prior to proceeding with testing of both a Windows security and a SQL security based DSN.
If, for some reason your DSN test fails I suggest checking the following possible issues:
- The login\password does not match the login\password combination on the SQL instance you’re connecting to in the DSN
- The login specified does not have rights to the SQL instance or database
- The SQL instance is not online or is not accepting connections for another reason
- Networking issues between the workstation or server where you’re creating the DSN and the server hosting the SQL instance
Once you work out any possible configuration issues, you should receive results similar to the following screen.
Clicking OK will then return you to the ODBC Data Source Administrator dialog. If, at a later date you need to edit the DSN you just created, you would do so by selecting the DSN and then clicking the ‘Configure…’ button on the right side of the interface.
Next Steps
- Stay tuned for subsequent tips on creating SQL Native Client (SNAC) DSNs, linking Access objects to SQL database objects, and creating SQL Pass-Through Queries in Microsoft Access.
- Learn more about Named Instances at MSSQLtips.com
- Create your own DSN and configure it with different settings. What happens when you attempt to configure a DSN with incorrect security?
