Verification: a143cc29221c9be0

Php can not connect to mssql

Using the SQL Server Driver for PHP from Microsoft

Introduction

This short manual is for running Moodle 2.0 (and upwards) using the Microsoft SQL Server (MSSQL) RDBMS. The steps detailed below must be performed before installing Moodle itself.

First of all, the minimum required version of MSSQL has been stabilized to MSSQL 2005 (v.9).

Installation overview

1. Install Microsoft SQL Server including SQL Server Management Studio. (A free version, SQL Server Express Edition is available for testing.)

Make sure to choose mixed authentication (Windows and local accounts) to keep things simpler later. Define the "sa" account password when requested (it's the default System Administrator account which has full access to all databases by default).

2. Configure Windows for MSSQL.

By default, MSSQL listens to port 1433 for incoming TCP/IP connections and this port needs to be opened in the firewall. This is explicitly configured in the firewall installed (either Windows Firewall in the Control Panel or the configuration interface for other firewalls). If the port was changed when MSSQL was installed, then specify the correct port number to open in the firewall.
Confirm the TCP/IP protocol is enabled in: SQL Server Configuration Manager -> Network Configuration -> Protocols -> TCP/IP enabled

3. Create and configure a new database.

Open "SQL Server Management Studio" and create a new empty database.
  • Execute the following command to enable Row Versioning:

USE MASTER
GO
ALTER DATABASE  SET READ_COMMITTED_SNAPSHOT ON
GO

4. Install PHP and a web server.

  • PHP can be downloaded from www.php.net
  • If IIS is used as the web server, IIS 7.0 or later is recommended with FastCGI and WinCache.

5. Install the SQL Server Driver for PHP.

On the web server, install SQL Server Driver for PHP including all the pre-requisites listed on the download page. Note: it is critical to install the SQL Server Native Access Client version documented on the download page of the SQL Server Driver for PHP.
Configure PHP to use the appropriate SQLSRV driver. In php.ini, set the following:

For PHP 5.2.4 (or later)

[PHP_SQLSRV]
extension=php_sqlsrv_52_nts_vc9.dll

For PHP 5.3.2 (or later)

[PHP_SQLSRV]
extension=php_sqlsrv_53_nts_vc9.dll

The Microsoft documentation for the SQL Server Driver for PHP is available at: http://msdn.microsoft.com/en-us/library/ee229548(v=SQL.10).aspx

6. Install and configure Moodle.

Continue with the standard Moodle installation.

7. The Moodle config.php should include lines like these: $CFG->dbtype = 'sqlsrv'; // Required $CFG->dbhost = 'localhost'; // Assuming that MSSQL is on the same server, otherwise

                                   // use the actual name or IP address of your database server

$CFG->dbname = 'moodle'; // The name of the newly created Moodle database $CFG->dbuser = 'yourusername'; // Usually the 'sa' account (dbowner perms are enough) $CFG->dbpass = 'yourpassword'; $CFG->dbpersist = true; $CFG->prefix = 'mdl_'; // The prefix can be changed per individual preferences,

                                   // but NEVER leave this blank!

If the config.php file doesn't exist, it will be generated as normal from the Moodle installer. Alternatively, use the config-dist.php file that comes with the Moodle package to create a custom config.php file.

8. Restart or start your web server.

If Moodle still cannot communicate with the database server, turn display_startup_errors to "On" in the /PHP/php.ini file, then restart the web server and check for any errors that may indicate incorrect DLL versions or missing dependencies. These error reports, turned off by default in PHP, can be vital in locating a problem with new extension installations.

9. Test the database connection.

Try this PHP script, just add a text file called test.php from the example below and change ('localhost', 'db_user', 'db_password') to align with the config.php settings, and load from local host (http://localhost/test.php).

dbhost, array('UID' => 'db_user', 'PWD' => 'db_password')); if($link === FALSE) {

   echo 'Could not connect';
   die('Could not connect: ' . sqlsrv_errors(SQLSRV_ERR_ALL));

} echo 'Successful connection'; sqlsrv_close($link); ?>

10. Complete the rest of the Moodle installation as usual.

Overview

This article contains the basic information you need to connect from PHP to your MySQL database on your (mt) Media Temple service. The most common mistake is in the host name, so check there first:

  • Grid: internal-db.s00000.gridserver.com
  • VPS servers: localhost

NOTE:
Do not use your domain name or IP address as the host name. This can result in an unreliable database server connection.

Requirements

Before you start, you'll need the following:

  • Host: internal-db.s00000.gridserver.comlocalhost
  • Username: db00000 or db00000_usernameSet in Plesk.
  • Password: Your password.
  • Database: Your database name. This should look like db00000_dbname.

You can see a summary of all these settings which are specific for your own server in the AccountCenter, under the Server GuideServer Guide.

Instructions

The host is based on your access domain. You may have to check the advanced section of your database settings to be able to specify this. For external connections, use external-db.s00000.gridserver.com instead and make sure your IP address has been added to the list of allowed external IPs. Click here for details.

For VPS servers, localhost is the default host in most scripts and applications. You likely won't have to enter anything special for this parameter.

The default username with access to all of your databases is db followed by your site number, db00000. You can edit your database user password in the AccountCenter. See Database users on the Grid for instructions on updating passwords and adding database users. If you want to test your username and password, try logging into phpMyAdmin.

This article has instructions for adding a database with a username and password.

CAUTION:
If you update the password for an existing database user, you must update it in any files or applications that use the old password. If you're not sure how to do this, you may want to create a new database user for your current project. The new database user will have the format db00000_username.

Your database name will be of the format db00000_dbname. You must create the database through the AccountCenter first. See How do I create a database? if you need to create your database.

Sample connection script

Below is a sample PHP connection script which shows three different methods of connecting to MySQL. This script is adapted from the PHP manual page "Choosing an API." Be sure to replace the arguments with your own MySQL login credentials.

CAUTION:
As of PHP 5.5.0, mysql_connect() is deprecated and will be removed in the future. You will want to look into using mysqli or PDO.

Filename: mysql_connect.php


query("SELECT 'Hello, dear MySQL user!' AS _message FROM DUAL");
$row = $result->fetch_assoc();
echo htmlentities($row['_message']);

// PDO
$pdo = new PDO('mysql:host=internal-db.s00000.gridserver.com;dbname=db00000_dbname', 'db00000_username', 'password');
$statement = $pdo->query("SELECT 'Hello, dear MySQL user!' AS _message FROM DUAL");
$row = $statement->fetch(PDO::FETCH_ASSOC);
echo htmlentities($row['_message']);

// mysql
$c = mysql_connect("internal-db.s00000.gridserver.com", "db00000_username", "password");
mysql_select_db("db00000_dbname");
$result = mysql_query("SELECT 'Hello, dear MySQL user!' AS _message FROM DUAL");
$row = mysql_fetch_assoc($result);
echo htmlentities($row['_message']);

?>

query("SELECT 'Hello, dear MySQL user!' AS _message FROM DUAL");
$row = $result->fetch_assoc();
echo htmlentities($row['_message']);

// PDO
$pdo = new PDO('mysql:host=localhost;dbname=database', 'user', 'password');
$statement = $pdo->query("SELECT 'Hello, dear MySQL user!' AS _message FROM DUAL");
$row = $statement->fetch(PDO::FETCH_ASSOC);
echo htmlentities($row['_message']);

// mysql
$c = mysql_connect("localhost", "user", "password");
mysql_select_db("database");
$result = mysql_query("SELECT 'Hello, dear MySQL user!' AS _message FROM DUAL");
$row = mysql_fetch_assoc($result);
echo htmlentities($row['_message']);
?>

Overview

This article contains the basic information you need to connect from PHP to your MySQL database on your (mt) Media Temple service. The most common mistake is in the host name, so check there first:

  • Grid: internal-db.s00000.gridserver.com
  • VPS servers: localhost

NOTE:
Do not use your domain name or IP address as the host name. This can result in an unreliable database server connection.

Requirements

Before you start, you'll need the following:

  • Host: internal-db.s00000.gridserver.comlocalhost
  • Username: db00000 or db00000_usernameSet in Plesk.
  • Password: Your password.
  • Database: Your database name. This should look like db00000_dbname.

You can see a summary of all these settings which are specific for your own server in the AccountCenter, under the Server GuideServer Guide.

Instructions

The host is based on your access domain. You may have to check the advanced section of your database settings to be able to specify this. For external connections, use external-db.s00000.gridserver.com instead and make sure your IP address has been added to the list of allowed external IPs. Click here for details.

For VPS servers, localhost is the default host in most scripts and applications. You likely won't have to enter anything special for this parameter.

The default username with access to all of your databases is db followed by your site number, db00000. You can edit your database user password in the AccountCenter. See Database users on the Grid for instructions on updating passwords and adding database users. If you want to test your username and password, try logging into phpMyAdmin.

This article has instructions for adding a database with a username and password.

CAUTION:
If you update the password for an existing database user, you must update it in any files or applications that use the old password. If you're not sure how to do this, you may want to create a new database user for your current project. The new database user will have the format db00000_username.

Your database name will be of the format db00000_dbname. You must create the database through the AccountCenter first. See How do I create a database? if you need to create your database.

Sample connection script

Below is a sample PHP connection script which shows three different methods of connecting to MySQL. This script is adapted from the PHP manual page "Choosing an API." Be sure to replace the arguments with your own MySQL login credentials.

CAUTION:
As of PHP 5.5.0, mysql_connect() is deprecated and will be removed in the future. You will want to look into using mysqli or PDO.

Filename: mysql_connect.php


query("SELECT 'Hello, dear MySQL user!' AS _message FROM DUAL");
$row = $result->fetch_assoc();
echo htmlentities($row['_message']);

// PDO
$pdo = new PDO('mysql:host=internal-db.s00000.gridserver.com;dbname=db00000_dbname', 'db00000_username', 'password');
$statement = $pdo->query("SELECT 'Hello, dear MySQL user!' AS _message FROM DUAL");
$row = $statement->fetch(PDO::FETCH_ASSOC);
echo htmlentities($row['_message']);

// mysql
$c = mysql_connect("internal-db.s00000.gridserver.com", "db00000_username", "password");
mysql_select_db("db00000_dbname");
$result = mysql_query("SELECT 'Hello, dear MySQL user!' AS _message FROM DUAL");
$row = mysql_fetch_assoc($result);
echo htmlentities($row['_message']);

?>

query("SELECT 'Hello, dear MySQL user!' AS _message FROM DUAL");
$row = $result->fetch_assoc();
echo htmlentities($row['_message']);

// PDO
$pdo = new PDO('mysql:host=localhost;dbname=database', 'user', 'password');
$statement = $pdo->query("SELECT 'Hello, dear MySQL user!' AS _message FROM DUAL");
$row = $statement->fetch(PDO::FETCH_ASSOC);
echo htmlentities($row['_message']);

// mysql
$c = mysql_connect("localhost", "user", "password");
mysql_select_db("database");
$result = mysql_query("SELECT 'Hello, dear MySQL user!' AS _message FROM DUAL");
$row = mysql_fetch_assoc($result);
echo htmlentities($row['_message']);
?>

Example MS SQL Server Setup

We were able to set up MS SQL Server 2005 Express (free download) and connect to it via PHP by the following steps. There are several versions of MS SQL Server, so additional configuration may be required for other versions.

  1. Install MS SQL Server 2005 Express.
  2. Set up a database and table.
  3. Set up a SQL user.
  4. Enable Mixed Authentication.
  5. Add permissions on the database for the created user.
  6. Add permissions on the table for the created user.
  7. Access the Server according to the steps outlined below.

Connecting to a MS SQL Server database with PHP

Connecting to a MS SQL Server database with PHP is very similar to connecting to a MySQL database. The following example demonstrates how to connect to a MS SQL database from PHP. Note that the function names contain mssql, not mysql.


$host ="xxx.xxx.xxx.xxx"; 
$username ="username";
$password ="password";
$database ="database";

mssql_connect($host, $username, $password);
mssql_select_db($database);
?>