The West Wind Web Store application works with SQL Server 2000, Sql Server 2005, Sql Server Express 2005 or MSDE. You can create the Web Store tables and stored procedures directly through this Web interface. The interface looks like this:

Before you begin
Make sure that you have the following rights and permissions set up for your Sql Server or MSDE Server:

• Server configured with Sql Server Authentication or Mixed Mode Authentication
  
• Access to an Administration account with access to the Master database to create a new database
  

For this example I'll log in as the sa account to create the database.

Note for MSDE users
If you're using MSDE you should use the DB2kMgr utility described below. MSDE installs with Windows Authentication by default, so you will need to switch it into Mixed Mode authentication. For more info see MSDE Configuration.

Note for SQL Express users
If you're using SQL Express note that it is configured for Windows Authentication with the Network Service account. Unless you enable Mixed Mode authentication it's probably easiest to set up new databases using integrated security. To do so leave the Admin Login and New Login fields blank.

Here's what you need to set:

Server Name:
This is the machine that the machine lives on. This can be the local machine (local) or an IPAddress or NetBios name. Examples are:

• (local) - local machine, default SQL Server Instance
  
• . - same as (local)
  
• .\SQLEXPRESS - Named instance, in this case SQLEXPRESS
  
• RASNOTEBOOK - default instance on the machine specified
  
• RASNOTEBOOK\SQLEXPRESS - named instance on the machine specified
  

Admin Login:
The name of a SQL Server security account that has rights to create a database. In order to create the database an adminstrative SQL Server account is required. This can be the sa account as in the example above or any other admin account that has access to the master database and can create new databases.

In ISP scenarios the ISP will often create a database for you. If that's the case you won't need a global admin account, but rather you can use the account the ISP provides you. If so make sure to check the Add tables and rules to an existing database checkbox.

Database to create:
This is the name of the database to create or - if you've checked the Add tables to an existing database - an existing database that the Web Store will create its tables and rules in. In either case, remember that whatever account you specified above is the account used to create these tables so this account must have rights to create tables and stored procedures in this database.

Data File Location:
This is an optional field and allows to specify the physical location of the SQL Server database file that is created. If you leave the filename blank the filename will be the same as the database. The new database file is always created in the App_Data directory of the Web site.

Create new Login and add to Database
When checked this option tries to create a new Database Login and adds it to the new Database. This allows you to create a username and password that you can then use in the connection string to the database when the application runs. Note the account that is created is flagged as dbo.owner and has full rights to the new database. If this is too loose you have to manually edit the Login information in the database later.

Alternately you can add an existing Sql Server Login by not checking the Create new Login checkbox.

Go do it!

When you've filled in these values click on Create Database. If you're successful you will see a message like the one in the screen shot above. Otherwise an error message will provide info on what went wrong.

If no errors occur the button for Step 2 - Web Store Configuration becomes available.

Manual Configuration

If you want more control over the database creation you can also manually perform the configuration steps using SQL Management Studio or using a utility like DBAMGR2K SQL Admin utility that works without the SQL Server Client tools.

The steps to set up the database are:

• Make sure you log on to the database as an Admin account
  
• Create a new DataBase in the SQL Server Management Console
  
• Find the SQL Script at: <installDir>\DataTemplates\WebStore_SqlServer.sql
  
• Open and copy the text into the query window of Query Analyzer/Sql Management Studio
  
• Execute the full Sql Script
  
• Create a new global SQL Server Login
  
• Add the new Login for this user to your new database and configure access permissions
  

Permissions Required for the User Account

The Web Store requires fairly complete access to the WebStore database, so when you set up an account make sure you do one of the following:

• Configure the user as DBO for the database
  DBO has full access and this is the easiest to set up but is less secure if your server should get compromised for whatever reason.

• Set DataReader/DataWriter access and Configure Stored Procedure access
  Reader and Writer access allows access to the database. In addition you need to make sure you allow access to execute the Web Store Stored Procedures. Specifically the ww_NewIDSafe Stored Procedure is required for new ID generation.
  

The latter is the minimum set of permissions required to run the store.

See also

MSDE Configuration