• Home
• About me

Configuring SQL Server 2005 for database-driven web pages

SQL Server 2005 has enhanced security that prevents it initially from serving data for web pages. Here are the configuration settings to check before trying to create database driven web pages in Visual Studio, Dreamweaver, or any other web design application. Note that some of these settings reduce the security level of SQL Server 2005.

SURFACE AREA CONFIGURATION

1. Go to Programs –> Microsoft SQL Server 2005 –> Configuration Tools –> SQL Server Surface Area Configuration
2. Click on Surface Area Configuration for Services and Connections
3. Expand Database Engine and Click on Remote Connections
4. Select local and remote connections and TCP/IP and named pipes.
FIREWALL CONFIGURATION

1. Make sure your firewall allows TCP port 1433.
WIRELESS ROUTER CONFIGURATION (if you have one!)

1. Go to port forwarding and forward port 1433 to the internal IP address of the machine running SQL Server 2005 (e.g. 192.168.1.1).
SQL SERVER CONFIGURATION

1. The simplest (but not most secure) method of authenticating a web page query to a database is to use SQL Server authentication. This requires your server to allow Mixed authentication. The more secure method of authentication is windows authentication. For an excellent description on how to set up and use windows authentication with ASP.NET, visit http://www.spaanjaars.com/QuickDocId.aspx?quickdoc=395. If you want to use mixed authentication: 

2. Open Microsoft SQL Server Management Studio
3. Authenticate in the usual way (usually Windows Authentication)
4. In the Object Explorer, right click on the name of the server and select properties.
5. Click on the security page and select SQL Server and Windows authentication.

CREATE A NEW SQL SERVER LOGIN (if using SQL Server authentication)

1. Open Microsoft SQL Server Management Studio
2. In object explorer, expand the security folder and right-click on Logins.
3. Select New Login
4. Type in your new username and select SQL Server authentication.
5. Type and confirm a password.
6. [Optional] Uncheck enforce password policy and User must change password at next login.
7. Choose the default database.
8. Click OK
9. In the object explorer, click on the name of your database, expand the folder, and expand the security folder.
10. Click on users and if the user you created is not listed, right-click on users and select new user.
11. The login name needs to be the user you just created (click on … to select a user from a list of users). The username can be the same if you wish.
12. Select the relevant database roles (e.g. db_datareader and db_datawriter).
13. Click OK

Hope this helps!
Grant

Did you enjoy this post? Why not leave a comment below and continue the conversation, or subscribe to my feed and get articles like this delivered automatically to your feed reader.