Configure ODBC Data Source Administrator with SQL Server

You might often need to connect to the SQL Server database say in your classic ASP Application using the ODBC System DSN (Data Source Name). To do this, we need to use the 32-bit application odbcad32.exe under the SysWOW64 folder. The steps are as below:
Go to the SystemDSN tab -> Click on Add

Select the SQL Server driver SQLSRV32.DLL:

Give a name and Server details:

Provide the SQL Login details. You can also use Windows Authentication if it serves the purpose.

Select the required database by checking “Change the default database to:”

Click on Finish and Test Connection.

To use this in Classic ASP, add the DSN details in your cnconst file as below:

DSNName = "testdb"
SystemDSN = "DSN=" & DSNName & "; UID=user; PWD=xxxx"

To connect, use the code below to fetch a recordset:

SET rsData = Server.CreateObject("ADODB.RecordSet")
strSQL = "SELECT LOCATION from Offices"
rsData.Open strSQL , SystemDSN
Dim OfficesArr, OfficeStr
OfficesArr = rsData.GetRows()