Tutorial: MSSQL - Using ODBC And Windows Authentication To Connect To A MSSQL
Database Using ASP
Dr. Thomas E. Hicks
Computer Science Department
Trinity University
The purpose of this tutorial is to show you how to use the free Microsoft ODBC Connector and enable ASP web pages to query data from Microsoft SQL Server Express databases using Windows Authentication.
1] I am starting with the H-Northwind Database. You may download and install it
if you like. (See Below!)
2] Use the tutorial below to help with the install (if you
need).
3] If you open the Employee table of the H-Northwind database, you can see that there are 9 records in the table. (See Below!)
1] One can use an ASP connection string to connect to a MSSQL database; one can
also use an ODBC connection. It is the purpose of this tutorial to walk through
the ODBC Connection. Go to your Administrative Tools and Select ODBC. (See Below!)
2] Select the System DSN tab at the top. Using the mouse, select/push the Add button. (See Below!)
3] Select the SQL Server data source connection type. Using the mouse, select/push the Finish button. (See Below!)
4] Enter the ODBC source Name - H-Northwind. Use the same for the description. Depending upon the network status, you might be able to find the server listed in the dropdown. My computer name is Net1. I am using the free version of Microsoft SQL Server Express. Entering NET1\EXPRESS is much faster than searching the network. I also have Microsoft SQL Server; had I been using it on Net1, the Server would simply be Net1. (See Below!)
5] Select the desired authentication type. My students start out with Windows Authentication and graduate to SQL Server Authentication. Using the mouse, select/push the Next button. (See Below!)
6] Select the database; the most common problem my students have is leaving this associated with the Master database. If I leave it associated with the Master database, then all of my queries for Employees are going to fail to find the table Employeees. Using the mouse, select/push the Next button. (See Below!)
7] Using the mouse, select/push the Finish button. (See Below!)
8] It is always a good idea to test the Data Source. Using the mouse, select/push the Test Data Source button. (See Below!)
9] It tested OK. Using the mouse, select/push the OK button. (See Below!)
10] We are finished. Use the mouse to select/push the OK button. (See Below!)
11] Use your favorite web site design tool, or text editor, to create a file called DisplayEmployees.asp; store the file in directory c:\inetpub\wwwroot.
<%@ LANGUAGE = VBScript %>
<% Option Explicit %>
<% Response.Expires = 0 %>
<%
'-----------------------------------------------------------------
' ASP Connection Constants
'-----------------------------------------------------------------
'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adopendynamic = 2
Const adOpenStatic = 3
'---- LockTypeEnum Values ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4
'-----------------------------------------------------------------
' Declarations
'-----------------------------------------------------------------
Dim Conn, RecSet, SQL
'-----------------------------------------------------------------
' Initialization
'-----------------------------------------------------------------
'-----------------------------------------------------------------
' Create Connection Object & Open ODBC Database
'-----------------------------------------------------------------
Set Conn = Server.CreateObject ("ADODB.Connection")
Conn.Open "H-Northwind"
'-----------------------------------------------------------------
' Create Query
'-----------------------------------------------------------------
SQL = "Select * from Employees"
'-----------------------------------------------------------------
' Create RecordSet Object % Fill With All Employees
'-----------------------------------------------------------------
Set RecSet = Server.CreateObject( "ADODB.Recordset" )
RecSet.Open SQL, Conn, AdOpenDynamic, adLockOptimistic
%>
<table style="width: 100%">
<tr>
<td>First</td>
<td>Last</td>
</tr>
<%
'-----------------------------------------------------------------
' Start Of Loop Through Employees
'-----------------------------------------------------------------
Do While NOT RecSet.EOF
%>
<tr>
<td><% response.write RecSet.Fields("First")%> </td>
<td><% response.write RecSet.Fields("Last")%></td>
</tr>
<%
'-----------------------------------------------------------------
' End Of Loop Through Employees
'-----------------------------------------------------------------
RecSet.MoveNext
Loop
%>
</table>
|
12] If you are not sure your ASP is configured properly, you might want to
review the following:
1] Right mouse click on your file c:\inetpub\wwwroot\DisplayEmployees.asp and select Properties.
2] We are interested in the Security Permissions. Select the Security Tab. Note
the presence of the Internet Guest Account; the info in parentheses will vary. I
initially recommend that you give the Internet Guest Account Modify permissions;
you can tighten this security later. (See Below!)
3] I am user thicks. If I am to add files, delete files, modify files, etc., it is best that I have full control of the Inetpub folder. Make sure you have full control.
4] Everyone, i.e. the other users of this computer, should generally have Read, Execute, and List permissions. (See Below!)
5] The administrators should generally have full control of the entire web site. (See Below!)
6] The permissions above are good starting permissions for Inetpub and wwwroot. File c:\inetpub\wwwroot\DisplayEmployees.asp must also have the correct permissions. Check to make sure the Internet Guest Account has access. (See Below!)
1] In order to access the H-Northwind Database via a web browser, the database has to grant Connect and Select permissions to the guest account. (See Below!)
2] To set these permissions, return to Microsoft SQL Server Management Studio Express. Hold down the mouse on the H-Northwind database and select Properties. (See Below!)
3] Select the Permissions Page (on the left). Add the Guest user and grant Connect and Select permissions to this guest account. (See Below!)
1] Load DisplayEmployees.asp with your browser. (See Below!)
