Creating the Database Project

To allow our database design and stored procedures to be managed via source control, and enable it to be shared more easily with other developers, we can create a database project within VS .NET. Just as it was when we created the Security database, this step is optional, but it's often a good way to simplify the management of the database scripts.

Tip 

The database we created is included in the code download for the book, within a database project.

Open a new Blank Solution by choosing File New Blank Solution in VS .NET. Name the solution ProjectTracker. This solution will contain the business application code that we'll create through the rest of the book. Then choose File Add Project New Project to add a new database project to the solution. Name it PTrackerDB as shown in Figure 6-24.

Figure 6-24: Adding the PTrackerDB project

When prompted for a database connection, choose the PTracker database. (If it isn't in the list, use the dialog box to add a new connection, and then choose it.)

Once the project has been created, we can populate it with scripts from our database by simply dragging and dropping the Tables node from the Server Explorer to the Create Scripts node in the PTrackerDB project. This will bring up a dialog box that asks what should be copied . Indicate that we want to copy everything as shown in Figure 6-25.

Figure 6-25: Dialog box that controls what's copied to the database project

Tip 

In some installations of VS .NET, a Create Scripts node isn't automatically created in database projects. If this is the case in your installation, you'll need to right-click the database project in Solution Explorer and choose Add Folder to add a Create Scripts folder to the project.

When we click OK and confirm the destination directory, SQL Server will create SQL scripts for all of our database objects, including tables, keys, relationships, and stored procedures. The scripts will be placed into the PTrackerDB project.

Database Security

The last step in creating the database is to set up security. This can't be done from within VS .NET, so we'll use Enterprise Manager to do the work.

When we build the Windows Forms UI in Chapter 8, we'll make use of a remote DataPortal , so the data-access code will run under the ASP.NET account. In the case of the Web Forms UI that we'll create in Chapter 9, and the web-services interface we'll create in Chapter 10, the UI code and data-access code will all run in the same process under the ASP.NET account. This means that the only account that will need access to our database is the ASP.NET account.

In a production application, this isn't ideal, since it means that any application running under the ASP.NET account would have access to our database. Basically though, we're stuck with the same four options that we laid out in the table in Chapter 5; the ideal solution will vary depending on your environment and specific security requirements, as listed in Table 6-5.

Table 6-5: Database Security Options and Consequences

Database Access Option	IIS Configuration	Consequences
Integrated security	Anonymous access (no impersonation)	All database access is done under the ASP.NET account. We get database-connection pooling, but the ASP.NET account needs access to our databases. No password values are stored in clear text.
Integrated security	Windows security (impersonation)	All database access is user specific, so each client identity is used to access the database. We get no real database-connection pooling, and database permissions must be granted to the end- user identities that are using the application. No password values are stored in clear text.
Specific user IDs and passwords in connection strings	Either anonymous access or Windows security	Database access is done under the specific user IDs. We get database-connection pooling and have better control over which applications can access which databases. Password values are stored in clear text.
Integrated security	Anonymous access (impersonating specific user)	Database access is done under the specific user ID we provide. We get database connection pooling and have better control over which applications can access which databases. Configuration of this option is complexsee Microsoft's Knowledge Base article 306158. [*]
[*] Microsoft Knowledge Base article number 306158, "Implementing Impersonation in an ASP.NET Application," March 31, 2004. See http://support.microsoft.com.

For the purposes of this book, we'll give the ASP.NET account access to the database so that we can use integrated security in our database-connection string. Open Enterprise Manager and navigate to the PTracker database. Right-click the Users node under the database, and choose New User. In the dialog box, select the ASP.NET account for your server as shown in Figure 6-26.

Figure 6-26: Setting database security for the ASP.NET account

When we click OK, the ASP.NET account will be added as a valid user for the database. However, this act alone doesn't provide the account with access to any database objects. Double-click the ASP.NET account in the right-hand pane of Enterprise Manager to bring up its Database User Properties dialog box, and click the Permissions button. As shown in Figure 6-27, the account needs SELECT access to the Roles table, and EXEC access to all our stored procedures.

This gives the ASP.NET account, and therefore all of our data-access code, the access rights that it needs to read data from the Roles table and to call all the stored procedures we just created.

Figure 6-27: Granting permissions to the ASP.NET account