Setting up SQL Server 2008 for an ASP.NET website on IIS 7.0

Setting up SQL Server 2008 for an ASP.NET website on IIS 7.0 

 

SQL Server 2008 Express edition with Tools has finally been released (It was launched on August 11th, but only today are the management tools available).  As I’ve been building web servers today, I’ve put together this comprehensive guide to setting up your website to work with SQL Server 2008.

 

 Step 1: Prerequisities

1. Firstly you need Windows 2008 Server and IIS 7.0 installed. Installing Windows Server 2008 is pretty straight-forward and I won’t cover it here (there is an excellent article here), although be aware that as you are using ASP.NET and SQL Server you cannot install server core.  Installing the IIS 7.0 server role is also quite easy, (another great article here)
2. Install Windows PowerShell from the Features section in the Windows Server 2008 Server Manager.
3. Install .NET Framework 3.5 SP1 from here.
4. Install Windows Installer 4.5 from here.
5. If you have AJAX enabled sites running on ASP.NET 2.0 then Install ASP.NET AJAX Extensions 1.0 from here.
6. Download SQL Server 2008 Express with Tools from here.  This version includes:
   • SQL Server database engine – create, store, update and retrieve your data
   • SQL Server Management Studio Basic – visual database management tool for creating, editing and managing databases

While this guide concentrates on the Express version of SQL Server 2008, you need to ensure that it meets the requirements in your specific environment.  The SQL Server 2008 blog has an article which discusses this issue (SQL Server Express and Hosting)

Step 2: Installing SQL Server 2008

1. Start the install from your CD or downloaded file.
2. Select New SQL Server stand-alone installation of add features to an existing installation
3. The setup program will check that you have the necessary pre-requisities in place for the SQL Server setup support files.  Click OK.
4. Click Install to install the SQL Server setup support files.
5. SQL Setup will then check that you have all the necessary pre-requisities in place for the SQL Server installation.  I received a warning that Windows Firewall needs port 1433 open for remote TCP/IP connection to SQL Server.  Click Next.
6. Nothing to do on the next step if you are using the Express Edition, other you need to enter a valid product key.  Click Next.
7. Accept the license terms and click Next.
8. Select the features you want to install and cick Next.  At a minimum I recommend installing the Database Engine Services and the Management Tools.
9. Select an instance to install your database server into and a location.  In the web.config file for your ASP.NET web site, if SQL is not installed in the default instance then you need to set Data Source = SERVERNAME\INSTANCENAME for your database connection string.
10. SQL Setup will confirm you have enough disk space. Click Next.
11. Configure your service account and collation settings and click Next. Use the built-in System account and don’t change the default collation unless you have databases with special requirements.
12. Configure Mixed Mode authentication and provide a password for the sa account.  This allows you to use either windows or sql authentication depending on your requirements.  Choose which accounts have administrative privilages to the SQL Server database engine.  Click Next.
13. Tick the checkboxes if you would like to send error reports and usage data to Microsoft, and click Next.
14. Setup will then check if the installation process will be blocked.  Check the report and click Next.
15. Verify the SQL Server features to install and click Install. 
16. Once the setup finishes, check Windows Update to see if there are any patches that need to be installed.

Step 3: Allowing TCP/IP Connections to SQL Server

By default, only the Named Pipes protocal is allowed to connect to the SQL Server.  To allow TCP/IP connections, perform the following steps:

1. Open the SQL Server Configuration Manager under the Microsoft SQL Server 2008, Configuration Tools start menus.
2. Expand the SQL Server Network Configuration node and select Protocols for <InstanceName>.  Double click on the TCP/IP Protocol and change it to Enabled.
3. Restart SQL Server for the changes to take effect.

Step 4: Attaching a Database

1. The SQL Server 2008 Management tools are almost identical to the SQL Server 2005 ones.  Open up the Management Tools and login to your database.  You will then see the following:
2. Right-click on the Databases node and select New Database to create a new database, or Attach to attach an existing one.   If you are attaching a database then click the Addbutton, browse for your .mdf file and click OK twice to attach the database.  You will be asked to also add the location of any Full-Text catalogs if they exist.

Step 5: Creating a Login for your database

By default, only Administrators have access to your database.  You need to now configure your database to allow Internet users access to read and (if required) write to your data.  You can either use Windows authentication or SQL Server authentication.  Windows authentication uses your local server or Active Directory user account to connect, and doesn’t require a password to be kept in the web.config file.  SQL Server authentication is easier to seperate your user accounts for different databases and applications easily.

To set up Windows Authentication:

On a standalone Windows 2008 Web Server, the ASP.NET service runs as the NT AUTHORITY\NETWORK SERVICE account.  You need to map this account to SQL Server:

1. In the SQL Server Management Tools, Right click on Logins under the Security Nodeand select New Login.
2. Type NT AUTHORITY\NETWORK SERVICE in the Login Name box and select Windows Authentication.  Click Ok.

To set up SQL Authentication:

1. In the SQL Server Management Tools, Right click on Logins under the Security Nodeand select New Login.
2. Type the name of your database user in the Login Name box and select SQL Authentication.  Set a password and untick Enforce Password Policy. Click Ok.

Step 6: Assigning the user permissions to your database

1. Now you need to assign this login the appropriate permissions in your database. To do this, expand the Databases node and then expand Security. Right-click the Usersnode and choose New User.
2. Give your Login whatever name you like and in the Login Name box type in either the name of your database user if using SQL authentication or NT AUTHORITY\NETWORK SERVICE if you are using Windows authentication (or browse to select your account).  Under Database Role Membership select db_datareader (and db_datawriter if your users are performing INSERT and UPDATE SQL queries).
3. Click OK.

Step 7: Configuring your database connection string

You can set your database connection string by either manually editing the web.config file, or using the IIS built in Connection Strings applet.

1. Open Internet Information Services (IIS) Manager from the Administrative Toolsstart menu.
2. Browse to your website and double-click on Connection Strings in the right hand pane.
3. Either Add a new connection string or right click an existing one to Edit.
4. Set the name of your server and database appropriately, and then choose the credentials to match your database user.  If you are using SQL Authentication then you need to set the username and password you configured.

That’s it!  I hope this guide helps, and please let me know if I’ve missed anything!