Setting Up SQL Server | Building Client/Server Applications Under VB .NET: An Example-Driven Approach

Before beginning, you need to add a new table to your Northwind database. The new table stores your usernames and passwords (well, sort of). Execute the SQL in Listing 12-1 to create the new table and associated stored procedures.

Listing 12-1: The UserList Table

 USE Northwind GO CREATE TABLE UserList (username VARCHAR(20) PRIMARY KEY,  userpass VARCHAR(100) NOT NULL) GO CREATE PROCEDURE usp_AddNewUser @uid varchar(20), @pwd varchar(100) AS INSERT INTO UserList (username, userpass)   VALUES (@uid, @pwd) GO CREATE PROCEDURE usp_ValidateUser @uid varchar(20), @pwd varchar(100), @valid int OUTPUT AS DECLARE @count int SET @count = (SELECT count(*)     FROM   UserList     WHERE   username = @uid     AND   userpass = @pwd) IF @count = 1    SET @valid = 1 ELSE    SET @valid = 0

As you can see, this table is very simple. The username column stores a unique username (this is the reason it is a primary key—so usernames cannot be duplicated), and the userpass column stores a hash of the user password. The AddNewUser stored procedure inserts new users into your UserList table, and the ValidateUser stored procedure verifies that the user has presented valid credentials to be logged on to the system.

Setting Up Security

The logon page is always the first entry into your private data. There can be other pages on the Web site with nonprivate data that do not need to be protected, but you are only concerned with the pages that need protection. After the user is authenticated against the Web site, you then need to figure out how to allow them access to the data. Right now you have SQL Server and IIS set up to allow only Windows Authentication—so you need to make some hard decisions when you determine if you want to continue using this scheme.

Caution

You must set the Directory Security on the Northwind virtual folder and Webservice1 virtual folder in IIS so that it allows Anonymous access. If you do not, this will not work! You can do this from the Directory Security tab in the Properties dialog box for the Northwind virtual directory in IIS.

Any discussion of setting up a database for possible external (to the company) access must include a discussion on security and best practices. So, you are going to take a little time out to discuss some of the security issues and how to best set up your database for your Web application.

Storing Passwords

I have seen several different schemes for storing passwords in a database. One is to store the username and password in clear text and then retrieve the password based on the username and compare them. Another method is to encrypt the password and store the encrypted password in the database and then decrypt it to authenticate the user (or encrypt the password the user gave and match it to the encrypted password in the system).

However, Microsoft recommends that a hash be made of the password and that only the hash gets stored in the system. When dealing with a hash, it is almost impossible to get the original value back out of the hash, and it is case sensitive. Using this method, no one, not even the Database Administrator (DBA) can get your password. It is easy to figure out if a system is configured this way because if you lose your password, the only solution is for a system administrator to reset your password to something such as "password" and require that you change it on your next logon. Note, though, that if you use this scheme, you need to make sure you supply the DBA with a hash of the word password and not the word itself!

Because Microsoft considers this a best practice approach, this is what this chapter demonstrates.

Now you need some way to log on to the database to verify a user's credentials. This becomes the second issue that you have to deal with when determining how to secure your application. If you continue to use the current setup (Windows Integrated Authentication), you need to grant access to SQL Server to a specific user account and use that for all of your connections to the database. That is not a bad idea, but not every Web site is using a database that allows Windows Integrated Authentication for the backend, and it could possibly introduce security holes.

Tip

You can use Windows Integrated Authentication to connect to the database instead of using a SQL Server account, but you must do this with caution. For an example of this type of successful setup, see the eWeek Web site (http://www.eweek.com) and search for OpenHack IV. Microsoft successfully used this setup to create a Web site that with-stood more than 80,000 attempted hacks!

So, what is the first line of defense? Always use components to connect to the database and make sure the components are independent of the user interface. But is that all you can do to make this more hacker proof?

Tip

One advantage of using individual user accounts is that you can control strict access to specific stored procedures, views, tables, and functions. Using a single account to connect to the database, you lose this ability. Also, you can update tables with the identity of the user who updated the record without having to pass the user's name in every time they call a stored procedure.

After all, if the connection is made entirely from within the firewall, why not use Windows Integrated Authentication there? The answer is that you can, except that as mentioned earlier, not everyone is using SQL Server as the backend. Also, numerous other security considerations have nothing to do with how you program your application, but with how the network administrators have configured your network.

You can take additional steps to secure yourself. The first, and easiest, way to do this is to place the SQL connection string in the web.config file on your component server (behind the firewall) and include the username and password there. Then, grant access to the SQL Server for that username/password combination. Now you have the problem of storing a username and password in clear text on a machine that can be reached by people internal to the company—and that is another bad idea. To mitigate the risk, you can take a slightly different step to store and retrieve the username and password for the database—use the registry. You can store the information in a key specifically created for this purpose. The information can be encrypted (if needed) and the appropriate Access Control List (ACL) placed on this key. This is probably the best solution because it keeps people both internal and external to the company from getting this information easily. Is it 100-percent hacker proof? Well, you cannot ever make that assumption because the first time you do, you will get burned, but it is better than storing database logon information in an easily accessible text file.

Tip

Generally, it is also a good idea to place the entire connection string in the registry because it protects the location of the database from a hacker. This can help protect against "man in the middle" attacks.

Because this is the best way to do this, you are going to create a registry key specifically for this purpose and place your logon information in it.

To start with, you need to change SQL Server from Windows authentication mode to Mixed Mode authentication (if it is not already set that way). To do this, take the following steps:

Open SQL Server Enterprise Manager.
Right-click the database instance that needs to be changed and select Properties.
Select the Security tab (as shown in Figure 12-3).

Figure 12-3: The SQL Server Properties dialog box's Security tab
Ensure that the SQL Server and Windows option button is selected.
Click OK.

When asked if you want to restart the server, select Yes.

Creating the User Account

Next you need to create a user account for accessing the Northwind database. To do this, execute the following SQL in SQL Query Analyzer:

 Use Northwind exec sp_addlogin N'nwAccount', 'password', 'Northwind' go sp_grantdbaccess N'nwAccount', 'nwAccount'

This creates a new account called nwAccount with the password password and sets the default database to Northwind. Then you give permissions for this account to actually access the Northwind database. Execute the following SQL to give your nwAccount permissions to execute all of the stored procedures you have created up to this point:

 Use Northwind GO GRANT  EXECUTE  ON [dbo].[usp_AddNewUser]  TO [nwAccount] GO GRANT  EXECUTE  ON [dbo].[usp_ValidateUser]  TO [nwAccount] GO GRANT  EXECUTE  ON [dbo].[usp_application_errors_save]  TO [nwAccount] GO GRANT  EXECUTE  ON [dbo].[usp_employee_delete]  TO [nwAccount] GO GRANT  EXECUTE  ON [dbo].[usp_employee_getall]  TO [nwAccount] GO GRANT  EXECUTE  ON [dbo].[usp_employee_getone]  TO [nwAccount] GO GRANT  EXECUTE  ON [dbo].[usp_employee_save]  TO [nwAccount] GO GRANT  EXECUTE  ON [dbo].[usp_employee_territory_delete]  TO [nwAccount] GO GRANT  EXECUTE  ON [dbo].[usp_employee_territory_insert]  TO [nwAccount] GO GRANT  EXECUTE  ON [dbo].[usp_region_delete]  TO [nwAccount] GO GRANT  EXECUTE  ON [dbo].[usp_region_getall]  TO [nwAccount] GO GRANT  EXECUTE  ON [dbo].[usp_region_getone]  TO [nwAccount] GO GRANT  EXECUTE  ON [dbo].[usp_region_save]  TO [nwAccount] GO GRANT  EXECUTE  ON [dbo].[get_menu_structure]  TO [nwAccount] GO GRANT  EXECUTE  ON [dbo].[usp_territory_delete]  TO [nwAccount] GO GRANT  EXECUTE  ON [dbo].[usp_territory_getall]  TO [nwAccount] GO GRANT  EXECUTE  ON [dbo].[usp_territory_getone]  TO [nwAccount] GO GRANT  EXECUTE  ON [dbo].[usp_territory_save]  TO [nwAccount] GO

Now that you have set up SQL Server, it is time to add a registry entry to hold your username and password information.

Setting Up the Registry

To begin, select the Start menu Run and type regedit32 and click OK. This brings up the Registry Editor, as shown in Figure 12-4.

click to expand
Figure 12-4: The Windows Registry Editor

To create the appropriate registry keys, take the following steps:

Expand the HKEY_LOCAL_MACHINE node.
Expand the SOFTWARE node.
Right-click the SOFTWARE node and select New Key.
When the new node is created, enter the name NorthwindTraders and press Enter.
Right-click the NorthwindTraders key that you just created and select New Key.
When the new node is created, enter the name dbLogon and press Enter.
Right-click the dbLogon node and select New String Value and enter the text username.
Right-click the dbLogon node and select New String Value and enter the text password.
Right-click the username entry and select Modify. Enter the username nwAccount.
Right-click the password entry and select Modify. Enter the password password.

Note

As mentioned earlier, this information can be encrypted, but that requires writing a separate program to store the information in an encrypted state. For your purposes you will not be doing this, but it is a simple matter to write information to the registry.

You have now created the registry keys and you are done with the registry. Later in the "Accessing the Registry" section, you will look at how to retrieve and use these pieces of information.

Setting Up the Web Site

To begin with, let's create the Web site using Visual Studio, which will serve as your "external" Web site. Start Visual Studio and select File New Project and select the ASP.NET Web Application template. Change the location name to read http://localhost/NorthwindWeb and click OK. Before you start building any pages, let's set up a secure folder and an empty form in that folder so that you have some place to redirect the user when they log on successfully. You will also set up an access denied page to redirect the user to when they fail a logon attempt.

Rename WebForm1.aspx to Logon.aspx (remember to switch to the code view afterward and rename the class from WebForm1 to Logon). Next, add a new WebForm and call it AccessDenied.aspx. Then create a new folder for the application and call it secure. Under this folder add one more WebForm called employees.aspx. When you are done, the Solution Explorer should look like Figure 12-5.

Figure 12-5: The NorthwindWeb Solution Explorer

The last thing you need to do to set up the Web site is to alter the web.config file so that you can use Forms Authentication. First, change the authentication section of the web.config file from <authentication mode = "Windows" /> to the following:

 <authentication mode="Forms">      <forms loginUrl="Logon.aspx" /> </authentication>

This specifies that you will be using Forms Authentication and that any unauthenticated users will be redirected to the Logon.aspx page. Next, you need to mark your secure folder as secure so that only authenticated users can get to that page. To do this you need to add a location tag to the web.config file. Add the code in Listing 12-2 to the third line of the web.config file (below the <configuration> tag and above the <system.web> tag).

Listing 12-2: The Location Tag

 <location path="secure">   <system.web>     <authorization>       <deny users="?" />     </authorization>   </system.web> </location>

This says that any unknown users will not be given access to the secure folder.

Tip

You can also accomplish this by placing another web.config file in the secure folder and setting the authorization to deny users="?".

Finally, go to the IIS Console and open the properties for the NorthwindWeb folder. Go to the Directory Security tab and edit the security settings by unchecking the Integrated Windows authentication. This sets up your Web site as though you would set up a Web site that allowed access to external users.