Greetings,

There are a number of posts on this forum from users who were not able to install the 'bids' database on a real SQL2K Server. I haven't built or compiled or tested the ASP.NET code yet, but I do think I’ve got the database installed successfully on a Win2K3/SQL2K Server. I'll report back if I detect any additional problems and solutions with the ASP code, but here, in case anybody is interested, are the steps to get the database set up in this environment.

Open the DatabaseSetup.sql file in a text editor.

Save a copy of the original file as a backup.

Locate the following line near the top of the file:

CREATE DATABASE [bids] ON (NAME = N'bids', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL$NetSDK\Data\bids.mdf' , SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'bids_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL$NetSDK\Data\bids_log.LDF' , FILEGROWTH = 10%)

Bear in mind that this script was written specifically for the MDSE engine. The trouble with this is that SQL Server does not know where the path:

‘C:\Program Files\Microsoft SQL Server\MSSQL$NetSDK\Data\...'

is located, because it doesn’t exist. You could just replace '\MSSQL$NetSDK\' with '\MSSQL$MYSQLSERVERNAME\', but it turns out this isn't really necessary.

So, delete everything from this line except

CREATE DATABASE [bids]

In addition to placing the .mdf and .LDF files in the proper default location, SQL Server will also set the default size and filegrowth properties of the database for you, which are fine for our purposes, so all you really need here is 'CREATE DATABASE [bids]'.

Start SQL Server Query Analyzer, make sure you're connected to the master database, copy the entire script into a query window, and run it.

Be aware that you'll still get errors when the script tries to grant permissions for the user 'ROO\ASPNET'. This is because this user probably won't exist in your SQL Server (unless you already created it). But you don't really need or want that anyway.

If you're running ASP.NET in Windows Server 2003, the application service runs under an Identity called 'Network Service' by default (assuming you've left it that way). You can verify this by going into the IIS Manager console. Under the IIS Manager node, expand the server node, right click on the Application Pools folder, select Properties, and go to the Identity tab. If you've left it at it's installation defaults, the Predefined radio button will be selected, and the security account for the application pool should be set to 'Network Service'.

However, you cannot directly grant database access to this Identity. Rather, this Identity needs to be able to [u]impersonate</u> the user that IIS is running under, and before it can do [u]that</u>, you need to add that IIS user to the 'bids' database.

In the Win2K3 Application Manager console, expand the IIS node, the MYSERVERNAME node, expand the Web Sites node, right click on Default Web Site, and select Properties.

From here, select Directory Security and click the Edit button. If 'Enable anonymous access' is checked (which I believe it is by default,) you will see that the user that IIS is running under is 'IUSR_MYMACHINENAME', and that Win2K3 has created a default password for this user. DO NOT CHANGE THESE SETTINGS. The point of this part of the exercise was simply to verify what user IIS is running under. This is the user that ASP.NET's Identity will need to be able to impersonate in order to talk to the database.

For more information on this topic, see Imar Spaanjaars' excellent documentation here:

http://imar.spaanjaars.com/QuickDocId.aspx?QUICKDOC=263

and here:

http://imar.spaanjaars.com/QuickDocId.aspx?QUICKDOC=287

Once you've determined the user that IIS is running under, you need to add this user to the 'bids' database, and then grant this user all the permissions that the script was trying to grant to the nonexistent user, 'ROO\ASPNET'.

In SQL Server Enterprise Manager, expand the Databases folder, expand the 'bids' database, right click on the Users node, and select 'New Database User...'

In the Login name dropdown, select the IIS Anonymous Access User: 'IUSR_MYMACHINENAME'. Under 'Permit in Database Role', select 'public', and click OK.

Now, in the right hand pane, you should see the 'IUSR_ MYMACHINENAME' user added to the database.

Next, grant the permissions for this user on the necessary database objects:

In the right hand pane, right click on the user 'IUSR_ MYMACHINENAME', and select 'Properties...'

Click the Permissions button to view all the objects in the database (confirm that the 'List all objects' radio button is selected so that you can see all the objects in the database and the various permissions that can be granted on each object.)

The first five objects should be the data tables, 'Bid', 'Item', 'Person', 'Sale', and 'Seller'. Check the SELECT, INSERT, UPDATE, and DELETE permissions for these objects.

Next, scroll down to the stored procedures, all of which start with 'sp_', and check the 'EXEC' permission on all of these objects. When you've checked all these items, click OK | OK to exit back out to the Enterprise Manager.

I’m pretty sure this grants all the necessary permissions that the script was trying to grant to 'ROO\ASPNET', instead, granting them to IIS's Anonymous Access User.

Finally, you need to edit the web.config file.

First, enable the ASP.NET application's Network Service identity to impersonate the IIS Anonymous User. To do this, add the following element:

<identity impersonate="true" />

which needs to be somewhere within the <system.web> element. I typically place that line right underneath this one:

<compilation defaultLanguage="vb" debug="true" />

Also, as long as you're in there, set the customErrors element to "Off", so you can see detailed error messages in your browser when you try to execute the .aspx files.

Next, p. 395 of the book shows that they're adding a connection string element, which looks like this:

<appSettings>
    <add key="ConnectionString" value="server=(local)\NetSDK;database=bids;Trusted _Connection=true" />
</appSettings>

Of course, there is no server called 'NetSDK' in this case. So you need to change this to the name of your SQL Server. So the revised element looks like this:

<appSettings>
    <add key="ConnectionString" value="server=(local)\MYSQLSERVERNAME;database=bid s;Trusted_Connection=true" />
</appSettings>

If you're running Win2K3 Server and SQL Server on the same machine, be sure you're putting in the name of the SQL Server, not the name of the Win2K3 server or the machine name. You can confirm this in SQL Server Enterprise Manager by expanding the following:

Console Root | Microsoft SQL Servers | SQL Server Group

The server should show up as the following:

WIN2K3SERVERNAME\SQLSERVERNAME

The SQLSERVERNAME part is the part that needs to go where 'NetSDK' was.

That should complete the installation and configuration necessary to use the database in a Win2K3/SQL2K Server. Over the weekend, I'll be building the application using Visual Studio.NET Pro 2K3. I'll report back if I run into any additional issues.

Hope this helps.

The code works for me although because my installation of SQL server 2000 was as the default the connection string used was modified to

<appSettings>
    <add key="ConnectionString" value="server=(local);database=bid;Trusted_Conne ction=true" />
</appSettings>

note: take care with database=bids

In my case I needed to change this to database=Bid in the above Connection String before the login code worked.


Check the code in the book for reference.