Setting Up a Database for a PHP Application on IIS

by Eric Woersching

Set Up a SQL Server Database from Microsoft SQL Server Management Studio Express

SQL Server is a database built and supported by Microsoft. Many PHP applications support SQL Server as their back end data repository. These instructions assume that you already have SQL Server installed and running. For this walkthrough, we used SQL ServerĀ® 2005. You can also use MySQL, SQL ServerĀ® Express or others.

Before starting the install, create a database on your database server. Also create a user and grant this user ownership permission to the database. If you are using SQL Server 2005, then open Microsoft SQL Server Management Studio and click "New Query" button. Enter the following script into the query window (this script creates the database and user).

USE [master]
GO

CREATE DATABASE my_DB
GO

CREATE LOGIN [my_DBuser] 
WITH PASSWORD=N'pass@word1',
     DEFAULT_DATABASE=[my_DB],
     CHECK_EXPIRATION=OFF,
     CHECK_POLICY=OFF
GO

USE [my_DB]
GO

CREATE USER [my_DBuser] FOR LOGIN [my_DBuser] WITH DEFAULT_SCHEMA=[dbo]

In addition, grant the newly created user the db_owner permissions to my_DB database:

Screenshot shows the Database User dialog box for my_D B user, with d b_owner selected as a Role Member.

Setup a MySQL Database from the Command Line

MySQL is a popular database supported by most PHP applications as their back end data repository. These instructions assume that you already have MySQL installed and running.

Before starting the installation procedure for your PHP application, create a database on your server. Also create a database user and grant this user ownership permission to the database. First, log on to your MySQL server with a command similar to the following (you must log on with an account with admin privileges; in our case 'root'):

c:\> mysql  -u root -p

You then must provide the password to your root or admin account on the MySQL server.

Next, create a database for the your application at the MySQL prompt after logging in:

mysql>CREATE DATABASE my_DB;

To create the database, download the MySql GUI-Tools from: https://dev.mysql.com/downloads/workbench/. Then you can create the database using a simple create database cslh command using the MySQL Query Browser.

Next, create the account that will be used with your PHP application to access this particular database:

mysql> GRANT ALL PRIVILEGES ON my_DB.* TO 'my_DBuser'@'%' IDENTIFIED BY 'my_DBpass';

This command creates the account, sets the password, and sets the privileges all in one line:

  • GRANT ALL PRIVILEGES ON my_DB.* - This statement says to allow all privileges for the account but only for the 'my_DB' database and all of its tables.
  • TO 'my_DBuser'@'%' - This statement specifies the account name and which machines can login with this account. In this case, 'my_DBuser' is the name of the account that will be created and '%' means that you can login to the account from anywhere. If '%' was replaced with localhost or a specific IP address you could only login to the database from that location.
  • IDENTIFIED BY 'my_DBpass'; - This statement sets the password for the account to 'my_DBpass' and ends the entire command with a semi-colon.

Note

Refer to the MySQL documentation for more complete information on managing user accounts and privileges.