MS-SQL ASP String Connection [Windows-Authentication] Tutorial:

Dr. Thomas E. Hicks
 Computer Science Department
Trinity University
 

The Initial Environment

1] The Microsoft SQL Server 2005 Express below is straight out of the box. As you can see from the MS-SQL Server Management Studio Express below, there are no additional users or databases (See Below!)

2] To make sure that our environment is the same, right-mouse on the SQLExpress Connection and select Properties. (See Below!) 

3] We want to make sure that our Security Authentication is Windows. Select the Security Page on the left and make sure that all of our choices are the same as those below.

4] We want to make sure that our Connections coincide. Select the Connections Page on the left and make sure that all of our choices are the same as those below.

5]  We want to make sure that our Database Settings coincide. Select the Database Page on the left and make sure that all of our choices are the same as those below.

6]  We want to make sure that our Permissions Settings coincide. Select the Permissions Page on the left and make sure that all of our choices are the same as those below.


 
Northwind Database

1]  If you have a database with which to connect, use it. If not, Export the Microsoft Access Northwind Database; tutorials for importing with Access 2003 and Access 2007 are linked below.

MSSQL-Server-From-2003-Access-DB      MS-SQL-Server-From-2007-Access-DB

2] This database, like many others, has many tables. Let us concentrate our efforts on the Customers Table. More Specifically, we would like to have an ASP web page which connects to the Northwind database and renders the CustomerID and CompanyName from the Customers table.

 
Create Page MS-SQL-ASP-String-Connection-Windows-Authentication.asp

1] We are going to create a page, called MS-SQL-ASP-String-Connection-Windows-Authentication.asp; Place it in your web directory; I am placing mine in folder C:\Inetpub\wwwroot\thicks\MS-SQL-ASP-String-Connection-Windows-Authentication. This page will simply be referred to as your Connection Page for the remainder of this tutorial.

2] Place some type of commercial, or banner, on the page for the moment:

3] Use your choice of browser to connect to the page. The actual URL would be


Things are not going to work correctly if the URL in the browser is C:\Inetpub\wwwroot\thicks\C:\Inetpub\wwwroot\thicks\MS-SQL-ASP-String-Connection-Windows-Authentication\C:\Inetpub\wwwroot\thicks\MS-SQL-ASP-String-Connection-Windows-Authentication.asp. You should use localhost, the machines IP, or the DNS entry for the system instead of c:\Inetpub\wwwroot!

 
Confirm ASP Functionality

1] Add the following block of code to the beginning of your Connection Page.

<%
Dim TestingASP
TestingASP = "<HR><H1><CENTER> Your ASP Is Functional </CENTER></H1><HR>"
Response.Write TestingASP 
%>

2] If you do not see the "Your ASP Is Functional" at the top of your page, your ASP is not working correctly. It is beyond the scope of this tutorial to fix your ASP problems. Get them fixed before you continue.

 
Rename Database

1] I am going to discuss multiple connection configurations and access strategies. Whereas you can use the same database throughout this entire tutorial, I will use a different database for each strategy. I exported four copies of the Northwind database into my system. I have named them Northwind1, Northwind2, Northwind3, and Northwind4.

 

ASP- Common Code For Most ASP Database Pages

1] Replace the TestingASP code snippet above with the following"

<%@ LANGUAGE = VBScript %>
<% Option Explicit %>  
<% Response.Expires = 0 %>
 
<%
'====================================================================================
'====================================================================================
'====             MS-SQL-ASP-String-Connection-Windows-Authentication.asp        ====
'====                                                                            ====
'==== Purpose    : Demonstrate a connection string to the MS-SQL Northwind       ====
'====              Database with Windows Authentication.                         ====
'====                                                                            ====
'==== Written By : Dr. Thomas E. Hicks                                           ====
'====================================================================================
'====================================================================================
'------------------------------------------------------------------------------------
'                              Declare ASP Database 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 

Const adModeReadWrite = 3
%>

2] The block/line of code
<%@ LANGUAGE = VBScript %>
sets the defalut scripting language. IIS has the ability to determine the default scripting; in the event that the scripting code is not specified, it might opt to use javascript, vbscript, or others. I recommend that each and every page specifically set the script for the page.

3] The block/line of code
<% Option Explicit %>
forces the programmer to declare each and every variable. When the programmer types a variable wrong and the system assumes blan, problems arise. I recommend that each and every page specifically force the user to declare all variables.

4] The block/line of code
<% Response.Expires = 0 %>
forces the browser to get a fresh copy of the data each and every time the page is refreshed. This is extremely important for dynamic pages..

5] The block/line of code
'====================================================================================
'====================================================================================
'====             MS-SQL-ASP-String-Connection-Windows-Authentication.asp        ====
'====                                                                            ====
'==== Purpose    : Demonstrate a connection string to the MS-SQL Northwind       ====
'====              Database with Windows Authentication.                         ====
'====                                                                            ====
'==== Written By : Dr. Thomas E. Hicks                                           ====
'====================================================================================
'====================================================================================
 is a comment box briefly describing the purpose of the page.

6] The block/line of code
 '------------------------------------------------------------------------------------
'                              Declare ASP Database 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
Const adModeReadWrite = 3
 declares the constants for all ASP database functionality in readable terms. I recommend including them at the top of each and every page that access data via ASP.

6] The page should still load:

Creating The ASP Database Connection Using A Connection String
 This Does Not Yet Work! Try It!
 
Add the following code immediately after declaring the ASP Database Constants (above)
1] Declare the variables
'------------------------------------------------------------------------------------
'                             Variable Declarations
'------------------------------------------------------------------------------------
Dim Conn, SQL, RecSet

2] Create a Connection Object and associate it with Conn
'------------------------------------------------------------------------------------
'                             Create The Connection
'------------------------------------------------------------------------------------
Set Conn = Server.CreateObject("ADODB.Connection")

3] Associate Northwind and open the Connection
'------------------------------------------------------------------------------------
'             Associate The Northwind Database, & Open The Connection
'------------------------------------------------------------------------------------
Conn.Open "Driver={SQL Server};Server=NET5\SQLEXPRESS;Database=Northwind1;"

4] Close the Connection
'------------------------------------------------------------------------------------
'                                Close The Connection
'------------------------------------------------------------------------------------
Conn.Close

5] Unfortunately, it does not work.

6] We know that the Server exists, so the access must be denied. The line generating the problem is :

Conn.Open "Driver={SQL Server};Server=NET5\SQLEXPRESS;Database=Northwind;"
 

Attempt To Change The Northwind1 Database Security To Enable Public Access
 This Does Not Yet Work! Try It!

1] The public does not have access to our database. The database properties panel will enable any authorized user to change access permissions.

2] Select the Permissions Page on the left. Push the Add button to enable a user permissions. (See Below!) 

3] If you know the username that you wish to enable it, you could type it in. For the moment, I choose to browse. Push the Browse button to identify the user accounts. (See Below!)

4] Select the public account. Push the OK button. (See Below!)

5] Push the OK button. (See Below!)

6] The Public user will need to connect in order to retrieve data from the database. Grant the Public user the Connect privilege. Push the OK button. (See Below!)

8] The Public user will need to select in order to query the database. Grant the Public user the Select privilege. Push the OK button. (See Below!)

9] We now get the following error message. The current user is unable to grant Public permission. (See Below!)

Who Is Logged In To Microsoft SQL Server Management Studio Express

1] The important question is "Who Is Logged In?" Answer : It is the BUILTIN\Administrators account.
The Systems Administrator sa Account

1] Expand the Security Tab in the Object Explorer. Expand the Logins tab to reveal the four user accounts created when the SQL Server 2005 was created. Let us take a brief look at Systems Administrator (sa) account.

2] Either double-click on the sa icon, or Right-Mouse click on the sa icon and select properties. (See Below!)

3] Make sure that you know the sa password. Keep it secure. . (See Below!)

4] Select the User Mapping page at the left. It will reveal the databases to which this person has access. Note that the sa account is the owner of the four initial databases created with Microsoft SQL Server 2005 Express: master, model, msdb, and the  tempdb. 

5] Suppose I would like to make the owner of the Northwind1 to the sa user account. Check the Northwind1 database Map. Check the db_owner role. Push the OK button. (See Below!)

6] The  BUILTIN\Administrators account can not make this change.
 

The BUILTIN\Administrators Account

1] Expand the Security Tab in the Object Explorer. Expand the Logins tab to reveal the four user accounts created when the SQL Server 2005 was created. We are interested in the BUILTIN\Administrators account.
 

2] Either double-click on the sa icon, or Right-Mouse click on the sa icon and select properties. (See Below!)

3] Suppose I would like to make the owner of the Northwind1 to the  BUILTIN\Administrators account. Check the Northwind1 database Map. Check the db_owner role. Push the OK button. (See Below!)

4] The  BUILTIN\Administrators account can not make this change.
 

Add the sysadmin Privilege To The BUILTIN\Administrators & sa Accounts

1] There are a number of pre-set server roles. It is beyond the scope of this tutorial to elaborate on those roles. For the sake of simplicity, let us assume that we would like to give the BUILTIN/Administrator account and the sa account privilege to do all server roles. We have seen above, that they already have sysadmin privilege, but let's check that out. Either double-click on the sysadmin icon, or right-mouse click on that icon and select properties.

2] Note that all four of the primary users have sysadmin privilege. Select OK.

Add the setupadmin Privilege To The BUILTIN\Administrators Account

1]  Either double-click on the setupadmin icon, or right-mouse click on that icon and select properties.

2]  Push the Add button.

3] Push the Browse button

4] Select BUILTIN\Administrators. Push the OK button. 

5] Push the OK button.

6] Push the OK button.

 

Add the serveradmin Privilege To The BUILTIN\Administrators Account

1]  Either double-click on the serveradmin icon, or right-mouse click on that icon and select properties. Perform the steps above to add the

 

Add the securityadmin Privilege To The BUILTIN\Administrators Account

1]  Either double-click on the securityadmin icon, or right-mouse click on that icon and select properties. Perform the steps above to add the

 

Add the processadmin Privilege To The BUILTIN\Administrators Account

1]  Either double-click on the processadmin icon, or right-mouse click on that icon and select properties. Perform the steps above to add the

Add the diskadmin Privilege To The BUILTIN\Administrators Account

1]  Either double-click on the serveradmin icon, or right-mouse click on that icon and select properties. Perform the steps above to add the

 

Add the dbcreator Privilege To The BUILTIN\Administrators Account

1]  Either double-click on the dbcreator icon, or right-mouse click on that icon and select properties. Perform the steps above to add the

 

Add the bulkadmin Privilege To The BUILTIN\Administrators Account

1]  Either double-click on the bulkadmin icon, or right-mouse click on that icon and select properties. Perform the steps above to add the

 

The BUILTIN\Administrators Account Is A SuperAdmin Account

1]  Once again, return to the BUILTIN/Administrators Account. Select the Server Roles page. Note that we have essentially assigned all privileges to this account. Although this is often undesirable from an administrative philosophy, it will enable this account to do all things.

Add the diskadmin Privilege To The BUILTIN\Administrators Accounts

1]  Select the User Mapping Page. Select the Northwind database. Since this account ja db_owner privilege, it can grant them to this, or any other, user. We are assigning all Northwind1 privileges to the BUILTIN/Adminstrator - who is currently logged in.

 

27]
 

28]
 

27]
 

28]
 

27]
 

28]
 

27]
 

28]
 

27]
 

28]
 

27]
 

28]
 

27]
 

28]
 

27]
 

28]
 

27]
 

28]
 

27]
 

28]
 

27]
 

28]
 

27]
 

28]
 

27]
 

28]
 

27]
 

28]
 

27]
 

28]
 

27]
 

28]
 

27]
 

28]
 

27]
 

28]
 

27]
 

28]
 

27]
 

28]
 

27]
 

28]
 

27]
 

28]
 

27]
 

28]
 

27]
 

28]
 

27]
 

28]
 

27]
 

28]
 

27]
 

28]
 

27]
 

28]
 

27]
 

28]
 

27]
 

28]
 

27]
 

28]
 

27]
 

28]
 

27]
 

28]
 

27]
 

28]
 

27]
 

28]
 

27]
 

28]
 

27]
 

28]