Well this post kind of covers any use of PDO to access SQL Server but you need to know certain things.

Firstly, the proper PDO SQL Server driver only exists for Windows (and therefore works on Azure), if you use Linux, please refer to my older post here: http://lukieb.blogspot.co.uk/2013/04/calling-sql-server-from-yii-on-ubuntu.html

Secondly, the syntax for the connection string on Yii is slightly different than a raw PDO connection. Specifically, when using PDO directly in PHP, I could put the uid and pwd into the connection string and it was happy. Doing the same on Yii throws an error and requires you add the username and password in the config array and not have them in the connection string. The following instructions can be followed if you are not using Yii, the only difference is how you declare and access the database connection.

  1. Download the microsoft native SQL PDO drivers and unpack them to a relevant extensions directory. In the case of azure, this is under \webrole\bin\php\ext. I unpacked all the dlls for the sheer hell of it but you obviously only need the relevant one for your version of PHP. Also note there are thread safe and non-thread safe versions. I am using the thread safe version but I find the whole choice confusing and it is related to performance and the threading model of the web server (a multi-threaded web server needs the slower thread safe versions whereas other web servers that use multi-process can use the non-thread safe ones but I think IIS is an exception!).
  2. Edit (or add) a php.ini in webrole\bin\php to load the extension like this: extension=php_pdo_sqlsrv_53_ts.dll This will be added to any other php.ini configuration on the server, it won't replace the whole file. Note that the directories I am using are the defaults, if you try and use different directories for ext and php, you might have problems.
  3. If you are trying to test this locally without using the Azure emulator, you will need to do as above into your normal PHP folder but then restart IIS before the changes will take effect. I think IISreset does it but it takes a little while to catch up!
  4. For your sanity, create a test.php file with echo phpinfo(); in it, run up the azure emulator (or local IIS for your local machine version of PDO), go to this file and search for PDO in it. If you have installed it correctly, you should see the sql server pdo driver listed (it simply says enabled). Ignore the build flag at the top which says -no-mssql or something and doesn't mean anything.
  5. Obviously you can write some simple PDO to test it is basically working and this should help your sanity since there are all kinds of firewall and permission issues that you could hit before it works. Also, you might want to set display_errors = On in php.ini which should help you find the problems.
  6. In yii, the connection string array looks like this:
    'db'=>array(
                        'class' => 'CDbConnection',
                       'connectionString' => 'sqlsrv:Server=tcp:azuredatabase.database.windows.net,1433;Database=dbname;',
                       'username' => 'username',
                       'password' => 'password123'
                    ),
  7. Accessing the database in Yii is pretty standard stuff: $connection=Yii::app()->db;
  8. create a command. In my case, everything is stored procedures and the create command is not like .Net where you bind params and they just work, you have to specify them in the createCommand function: $command=$connection->createCommand("EXECUTE procDoSomething :parameterName, :anotherParameterName);
  9. Then you bind each of the params: $command->bindParam(":parameterName", $variableName); (Note, you do NOT use the @ syntax anywhere in PDO)
  10. Then you call a relevant function depending on what your proc or statement does: $results = $command->queryRow();
  11. Most of this is just Yii/PDO specific and doesn't relate to SQL Server but I thought I would add it as an example of something that definitely works.
  12. Be REALLY careful with the bindParam statement. If you specify a type as an optional parameter, it sometimes seems to cause an error where SQL thinks it is an output parameter that you didn't flag as an output! This might only be the case for strings, since that is all my parameters are.