The following Steps will explain how to setup an ODBC (Open Database Connectivity) Connection to a SQL Server Database.
Note: The used Operating system in the following steps is “XP” and the version of SQL server is “SQL Server 2005″.
1- Go to your “Control Panel”, double-click “Administrative Tools” and then double-click “Data Sources (ODBC)”;
2- In the “Data Sources Administrator” dialog box, select the “System DNS” tab. This lists all the ODBC connections. Click the “Add” button;
3- A list of drivers will be presented, scroll down until you find the driver for the database which is “SQL Server”, then click “Finish”;
4- The “Data Source SQL Server Setup” window appears, enter a Name for your ODBC connection (This can be any name you desire and will be used later) i.e. <SQL Data Source>, then enter a Description for the data source (this can be any descriptive information you want).
5- Then, enter the server name for the SQL Server, click on “NEXT”;
6- Click the appropriate radio button for your connection type;
If you access your SQL Server Database using your standard Windows login, keep the default option for “With Windows NT Authentication…”, otherwise, if you access your SQL Server Database using a SQL Server Login, click on the radio button option for “With SQL Server Authentication…” Then, you must enter your SQL Server Login ID and Password.
7- Once you finish, click on “Next”;
8- Click on the box for “Change Default Database To:”, then, in the drop-down menu, select your SQL Server database name. Then click on NEXT;
9- In the next window, keep the default options as they are, so do not make any changes and click on the “Finish” button;
10- In the “ODBC Microsoft SQL Server Setup” Window, click on the Test Data Source button to test connection;
11- If your connection setting is correct, you should see a message “TESTS COMPLETED SUCCESSFULLY.”, if the test failed, you will need to click the “BACK” buttons to determine which setting is wrong and then repeat the above steps.