Connecting to SQL Server
SQL Server is a much more sophisticated and powerful database than MS Access. With its power comes more knowledge to work with it. So there are more variations on how to connect to the server and pass authorization credentials. SQL Server has two ways of connecting to it: ODBC DSN (Open DataBase Connectivity using a Data Source Name) and OLE DB (Object Linking and Embedding). The method that you use depends on your technical knowledge and how your computer is set up.
Using an ODBC connection requires that an administrator set up your computer in advance to be able to talk to the database Of course, if you have the technical skills and you know the security information, you could do this yourself. Once the connection has been created on your computer, any program (including Crystal Reports) can reference the ODBC connection name and retrieve the database records.
The benefit to having an ODBC connection is that the connection details are hidden from the user. All the user needs to know is the name of the ODBC connection and they can retrieve the data. This helps keep the database secure. The drawback to using an ODBC connection is that it must be set up on your computer in advance. If you work in a company with hundreds of computers, it literally takes a full tech support team to go to every computer and set this up manually. This isn’t very efficient. So Microsoft later decided that they needed a better way of connecting to databases and they came up with the OLE DB connection type to simplify the process.
OLE DB connections let you save the SQL Server authentication information within the actual program. Unlike ODBC, the authentication information isn’t in a hidden place on your computer. It is within the application and stays with the application. You can copy the program to any computer in the company and it can connect to the database. No tech support teams are needed to run around the company setting up each individual computer. The drawback to using OLE DB is that sometimes, depending upon how the database is set up, it requires the user to have knowledge of the database’s authentication information, and giving out the User ID and Password to users can present a security risk.
The type of connection you use is often determined by corporate policy. If your computer is set up to connect to the database using an existing ODBC connection, then that is what you use. If you were given a User ID and Password to access the database directly (or your windows login is authenticated to do so), then you can use an OLE DB connection to retrieve the data. If you are uncertain about which applies to you (and this whole discussion has lost you), consult your company tech support for advice on how they wish you to proceed.
