Setting up DSN Connections

Setting up a Data Source Name (or DSN) in Windows is something akin to falling off a log, once you’ve found the appropriate log, falling off it is child’s play!

Windows comes with a number of Open DataBase Connectivity (ODBC) drivers already installed. Things like Access and SQL server are easily contactable from the get go, its only when you look at database systems from other suppliers that you need to think about downloading drivers.

Products such as Oracle, MySQL, PostgreSQL and the like will require their own drivers installing before they become an option for DSNs. When you think about it, it kind of makes sense. Why should Microsoft try to write the driver when the one from the manufacturer will do the job much better and will probably go through many incarnations before the next Windows release?

I’ll talk you through how to set up a vanilla, ready salted, sorbet of a DSN setup that will hopefully point you in the right direction if you’re using other clients.

OK, here goes:

  • Go to Control Panel
  • Double click Administrative Tools
  • Open Data Sources (ODBC)

There are a number tabs available to you at this juncture, I’ll be concentrating on System DSN in these instructions. Please see my notes at the end of this post for a little further information about the others.

  • Click the System DSN Tab
  • Click the Add… button
  • At this point you must choose the ODBC drive you’re going to use for the DSN, select SQL Server for now
  • In the Name box type the name you want the DSN to have
  • Type a meaningful description in the Description box
  • Drop down the list at the bottom to select the server you wish to connect to
  • Click Next

At this point you’ll be asked about how you’d like to connect to the server, you’ve got two options:

Windows Authentication uses the credentials of the user that’s currently logged in to the PC. Its OK from most connections as long as the database administrator has set up the user on the SQL server.

SQL Server Authentication is used when you’d like to connect to the server as a SQL Server user  account. It doesn’t care who you’re logged in as, you’ll authenticate as this user. In order for this to work, your dba will need to have created a SQL user and be running the server in question in Mixed Mode Authentication mode.

  • Leaving the authentication as Windows for now, we’ll move on. Click next
  • Now you have the option to change the database you’ll automatically connect to and some other settings to do with ANSI identifiers and nulls, ignore the lot and click Next
  • At this screen you get the options for logging, encryption and languages, leaving these as they are you click Finish and away you go.

This is the most basic form of connection you can have. Using this you can create linked tables in Access, reports in Crystal Reports, use pass through queries, the lot!

 

Notes

Types of DSN – There are three major types of DSN: User DSNs, System DSNs and File DSNs.
User DSNs are used to create data sources that only that user will need. If you have roaming profiles enabled, they’ll follow the user about too.
System DSNs are machine specific and don’t care who’s logged in. They’re handy for things like reception desks and so such where the user can frequently change but the information available must always be there.
File DSNs are simply files that hold DSN information.

Share
April 15th, 2008 in Databases