Getting Database Connection Information in Windows Azure Web Sites

Article
10/23/2012

A few weeks ago, I wrote a post that suggested you use app settings in Windows Azure Web Sites to store your database connection information. This approach allowed you to access database connection information as environment variables in PHP and Node.js applications. As I thought about it more, I wondered why I couldn’t directly get the database connection string as an environment variable. After all, the database connection string was a named key-value pair just like an app setting, so it seemed like I should be able to access it in the same way. Well, as it turns out, you can. I talked a bit with the Windows Azure Web Sites team and found that database connection strings are accessible as environment variables for PHP and Node.js apps, BUT the names of these environment variables have special prefixes, depending on the database:

Sql Server: SQLCONNSTR_
MySql: MYSQLCONNSTR_
Sql Azure: SQLAZURECONNSTR_
Custom: CUSTOMCONNSTR_

So let’s say you create a PHP website with a MySQL database and you name the connection string connectionString1 (which is the default name):

The connection string will be accessible as an environment variable with the name MYSQLCONNSTR_connectionString1. So in PHP, you can access the connection string with the getenv function like this:

 $conn_str = getenv("MYSQLCONNSTR_connectionString1");

In one way, this is nicer than storing values in app settings: the connection string will be hidden by default on the site’s CONFIGURE tab in the portal:

And, if you need a properly formatted MySQL connection string you’ve got it. If however, you need the various parts of a MySQL connection string, you may want a function that breaks them out. Here is such a function, though I’m not sure this is the best such function:

 function connStrToArray($conn_str){
  
     // Initialize array.
     $conn_array = array();
  
     // Split conn string on semicolons. Results in array of "parts".
     $parts = explode(";", $conn_str);
  
  
     // Loop through array of parts. (Each part is a string.)
     foreach($parts as $part){
  
         // Separate each string on equals sign. Results in array of 2 items.
         $temp = explode("=", $part);
  
         // Make items key=>value pairs in returned array.
         $conn_array[$temp[0]] = $temp[1];
     }
  
     return $conn_array;
 }

So suppose you access a MySQL connection string with the getenv function as shown above, and suppose it looks something like this:

 Database=bswandb;Data Source=us-cdbr-azure-east-b.cloudapp.net;User Id=b43c7d64f33b47;Password=e6e050a0

If you pass this connection string to the function above, the function will return an array that looks like this:

 Array ( [Database] => bswandb [Data Source] => us-cdbr-azure-east-b.cloudapp.net [User Id] => b43c7d64f33b47 [Password] => e6e050a0 )

Hopefully, you can use that array when connecting to a MySQL database. Let us know what you think.

Thanks.

-Brian

Note: We’re updating the Windows Azure Web Sites documentation to make sure it’s clear that you can access DB connection strings as environment variables with the prefixes I mentioned earlier.

Getting Database Connection Information in Windows Azure Web Sites

Additional resources