Accessing Windows Azure SQL Database From PHP

This example uses the unified ODBC support built in to PHP to interact with SDS. I’ve included a sample code snippet below which uses the Windows Azure SQL Database server database.windows.net:

<?php

$host = "database.windows.net";

$dbname = "database";

$dbuser = "user@server";

$dbpwd = "password";

$driver = "{SQL Server Native Client 10.0}";

// Build connection string

$dsn="Driver=$driver;Server=$host;Database=$dbname;Encrypt=true;TrustServerCertificate=true";

if (!($conn = @odbc_connect($dsn, $dbuser, $dbpwd))) {

die("Connection error: " . odbc_errormsg());

}

// Got a connection, run simple query

if ($qh = @odbc_exec($conn, "SELECT A, B FROM myTable")) {

// Dump query result

$rows = 0;

while ( $row = @odbc_fetch_object($qh) ) {

echo("$rows: $row->A $row->B\r\n");

$rows++;

}

@odbc_free_result($qh);

}

else {

// Error running query

echo("Query error: " . odbc_errormsg($conn));

}

// Free the connection

@odbc_close($conn);

In this example, I’m using the SQL Server native ODBC driver which ships out of band from the operating system. Other driver choices may be used as outlined in the table below.

Item	Comments
Server	Name of the virtual SQL Database data server to connect to. This takes the form of *servername.database.windows.net. In the example, I’m using a virtual server provisioned in the SDS pre-production domain database.windows.net*
Driver	ODBC driver to use: {SQL Server} – Legacy SQL Server driver {SQL Native Client} – SQL 2005 Native Client {SQL Server Native Client 10.0} – SQL 2008 Native Client
Database	Name of the target database to connect to. If left unspecified the connection will default to the master database.
Encrypt	Force use of encryption over TDS – if left unspecified, SQL Database will force encryption
TrustServerCertificate	Trust the certificate returned by the data service – this is currently required due to implementation restrictions that will be relaxed before we release
UID	Username of the user – form should be user@server. Can be specified in the connection string or as argument to odbc_connect()
PWD	Password of the user account. Can be specified in the connection string or as argument to odbc_connect()

For those familiar with ODBC you’ll note this pattern is also used to connect to a regular SQL Server instance. The only change in the code is the name of the server, use of encryption and the use of SQL standard security (username and password).

In addition to the ODBC driver patterns, there is an open source Native PHP driver for SQL Server. This driver is a native client library built over the SQL 2005 Native Client.

You see the patterns used to access SDS are identical to those you use to access SQL Server.