Tuesday, 30 April 2013

What is faster? PHP or ASP .Net?

How many times has this been asked on forums? It is an understandable question but ultimately betrays how narrow most people's understanding of software is. It's like someone who writes a rock-solid web application but puts it on a host that is unsecure and loses data - performance, like security, has many layers and the total performance is the sum of all such layers.

Take the above question, it seems fair but the intention of the question is "If I want my system to perform well, should I opt for PHP instead of .Net?" Of course, this is probably because of Microsoft's reputation (sometimes deserved) for writing bloated and slow software. But is it really?

The uninformed response is, "PHP is faster than .Net", a more informed but still incomplete response is "try it and see". Why is it incomplete? Well let's look at the variables.

  1. The web itself is often reasonably slow point-to-point. Why? Well, traffic loads, service-provider issues. Slow DNS lookups etc. at least this slowness is shared by both systems but it also might suggest that it is a more significant issue for performance than the application or server (but it might not be!).
  2. DNS is often overlooked as part of the performance of a site, in most senses, once you have done the site lookup, your ip address will be cached for a period of time but that is determined by your DNS settings (or rather by the TTL setup on the DNS record) so if that time is short, your users might make frequent trips to their DNS registries, which, again, can be significantly slow.
  3. The hosting centre who serve your site (or indeed your own data centre) has various limits on its performance and bandwidth, these might vary depending on traffic levels causes by other sites hosted at the centre which might vary their loading significantly during the day and which might be taking your CPU cycles on a shared VM host or otherwise the network bandwidth at any point on the hosting companies networks.
  4. Your actual server, which is likely to be virtual, has a whole host of issues that might mean it works well or not depending on factors like the OS you are running, the kind of loads you are putting on it, the speed and arrangement of disk arrays etc. Most of this is invisible to you but it certainly can add latency, either consistently or erratically.
  5. Your application then runs on some kind of operating system. This could be Linux or Windows as well as other stranger variations but depending on who installed and setup the server, it might not be optimised for web in one way or another. This might be because certain security that has been configured adds latency and these security measures may or may not be needed depending on where the system is hosted. Is your OS firewall simply a duplication of something that already exists on the boundary of your host network?
  6. Your web server software (Apache, nginx, IIS etc) is a variable. You can serve PHP, for instance, from most web servers but only IIS is optimised for ASP.Net (unless you count the slightly less well-featured Mono on Linux). So choosing one language over another can affect not only your web server software but also, by implication, the OS you have to run on. Also, Apache is available for multiple platforms but does it perform identically on each? Do you even have a way to tell?
  7. Any libraries (3rd party or core) you use that call into the OS may or may not be optimised, they may or may not perform well and this is hard to tell during development, what happens when the method is called 1000 times per-second? Of course, this might only manifest when a particular part of your code is being called, which may or may not be continuous/consistent.
  8. Then your choice of files will affect the performance of the system, and this is not necessarily consistent across all web servers. Web sites that feature heavy amounts of static content might fly on nginx but if you include a significant amount of dynamic content, Apache might perform better. Of course, in many scenarios, this is both not easy to determine and certainly isn't something that we can predict for the future very well.
  9. Then the application itself can be written well or badly at various levels. Basics such as calling into code that adds no additional functionality (perhaps it doesn't do anything, perhaps it is redundant because the functionality has already occurred earlier in code). An example might be re-checking form input against a Regular Expression even though it is already attached to a server control that does the validation for you. Of course, there are also more subtle optimisations which you may or may not have spotted. For instance, if you have a set of checks carried out on some input, do they run in an order which makes the most common failure be checked first to save calling other checks unecessarily?
  10. If you change languages are you as expert in the other language as you are in the one you currently use? If not, you might make the overall system less well performing even if the new language is potentially faster overall.
  11. If you use a database, how fast is that? Is it optimised for the type of data you use? Have you moved maximum processing into the web application so that the database can be light-weight and not require the large overhead of sharding or replication?
When you take the hoslitic view, it is clear that there is much that can affect performance and most of it is either outside of our visibility or outside of our expertise or ability to put right. My personal opinion along the axiom of simplicity is that it is preferable to have a system that is easy to understand, easy to maintain, using tried and tested principles, even if these make your system a few percent slower than it is to try and squeeze a few extra cycles out while at the same time pushing the system outside your expertise. Someone once said, "The first rule of optimisation is: Don't.", because we often optimise things that we think would be a problem even if they aren't. Who cares if a page takes 2 seconds instead of 1/2 second for something that is run once per day by 10 people if the alternative is complex coding or defect injection?

IT departments often complain about costs but how much does it cost to incur hours, days or weeks of downtime/work to fix something that wouldn't have happened if you stayed within your expertise or well-known design patterns/frameworks? Often, in cloud-based or well-written web applications, it is much easier to spend a few dollars more a month to get a larger host machine which will increase performance than it is risking moving your whole stack over from IIS/Windows/ASP.Net to Linux/Apache/PHP or back again.

Friday, 26 April 2013

Accessing Azure Role Configuration from PHP

This should be a simple thing, Azure recommends using Azure shared configuration for your instances which allows not only the values of these items to be hidden from PHP/.Net code but also allows them to be changed on the fly. How do you read them from PHP?

Firstly, the configuration items are declared in the .csdef and .cscfg in exactly the same way as they are in .Net. They can be given values in the cscfg files or left blank and set after deployment if that floats your boat.

You would think that you can just go and get https://github.com/windowsazure/azure-sdk-for-php which is the latest MS library for PHP Azure but for reasons which I can only assume are related to drink and drugs, it does not support access to the Role Environment of Azure where the configuration settings live.

Do not cry, just download the older (deprecated!) version that the MS one is based on from http://phpazure.codeplex.com/ and then once you have added this to your project in whichever way you fancy, use the following simple expression to get the value of a configuration variable from the Role Environment:

$myVariable = Microsoft_WindowsAzure_RoleEnvironment::getConfigurationSettingValue('ConfigItemName');

Easy as pie.

Encrypt web.config sections for Azure

Introduction

Encrypting web.config sections that contain passwords is good, firstly as defence in depth (if someone was able to access your config files) but also, actually, is good practice in development teams where everyone does not need to know what the passwords are, it just raises the risk of something happening and not knowing who leaked the information out!

Anyway, this is easy enough in ASP.net using aspnet_regiis.exe which somes with the framework and does various things including encrypting sections in web.config.

Quick Guide


There is a good set of articles starting here: http://blogs.msdn.com/b/sqlazure/archive/2010/09/07/10058942.aspx which describes having to do something slightly different when your site is hosted in Azure (the link is part 1 of 4 but there are no links to the next post so you need to click on September 2010 on the right to find the other parts). It involves downloading a custom encryption provider from http://code.msdn.microsoft.com/pkcs12protectedconfg, creating or buying an X509 (SSL) certificate which you should upload to Azure as you normally would (instructions in parts 1 and 2 of the guide). You then need to build the protectedconfig library and put it into the same directory as the web config that contains the sections you want to encrypt. You should also reference the dll in your project for use at runtime. You then modify the web config to add in the custom provider configuration like this:

<configuration>
  <!-- everything else -->
 <configProtectedData>
    <providers>
      <add name="CustomProvider" thumbprint="ABC123etc..." type="Pkcs12ProtectedConfigurationProvider.Pkcs12ProtectedConfigurationProvider, PKCS12ProtectedConfigurationProvider, Version=1.0.0.0, Culture=neutral, PublicKeyToken=34da007ac91f901d" />
    </providers>
  </configProtectedData>
  <!-- anything else -->
</configuration>

which tells the protected config which certificate to use for encryption/decryption (put the relevant thumbprint in the config section). This will need to be present both on the local machine and on the Azure service that you will be uploading to. Note, you do NOT need to declare this configuration section at the top of the web.config.
You then need to run the following command from a Visual Studio command prompt in the same directory as the web.config. Remember to make sure the pkcs12protectedconfig.dll is in the same directory: aspnet_regiis -pef "connectionStrings" "." -prov "CustomProvider" 
Note that you need to specify the section you need to encrypt in this command, otherwise the other options are the same as above.

Things to Note

If you want to encrypt your own custom section, you have extra work. You need to install the dll that defines the configuration section into the GAC of your development machine, even if it is included in the project because aspnet_regiis will not look in the project to find it. You will then need to change the declaration of the section in web.config to include the GAC fields (culture, version, publickey token). After you have encrypted the section, you can uninstall the library from the GAC, change the config section back to a local reference and then re-build your project afterwards, otherwise the project will start to think the reference that was local is now global and it will break when you upload it. If you want to find the public key token of your dll, run sn -T name.dll from a Visual Studio command prompt.

You cannot encrypt all sections, if you try, you might get an illogical error like "the configuration section 'system.net' was not found". In this specific example, you cannot encrypt the top-level entry but you CAN encrypt further down, for example: aspnet_regiis -pef "system.net/mailSettings/smtp" "." -prov "CustomProvider"

Wednesday, 17 April 2013

Checking for duplicate when creating new object from MVC

I am creating new objects using the wonderful MVC4 scaffolding from my models and one issue I noticed is where I don't have a primary key or unique constraint on a table column but where I still want that column to be unique. Of course, the logic could be more complex, such as you are only allowed to duplicate the "name" if the previous object of the same name has expired. This might be kind of easy to code but here is an example anyway. In my controller method for Create(model), you can add additional logic to check before the item is added to the entity list and saved. There are loads of linq expressions at your disposal but .Any<> is probably the one most useful for this:

[HttpPost]
        public ActionResult Create(DiscountCodeModel discountcodemodel)
        {
            if (ModelState.IsValid && !db.DiscountCodeModels.Any( m => m.CodeHash == discountcodemodel.CodeHash))
            {
                db.DiscountCodeModels.Add(discountcodemodel);
                db.SaveChanges();
                return RedirectToAction("Index");
            }
            if (db.DiscountCodeModels.Any(m => m.CodeHash == discountcodemodel.CodeHash))
            {
                ViewBag.ErrorMessage = "Code already exists";
            }

            return View(discountcodemodel);
        }

This (not very tidy modification) will not save the entity if the hash of the entered code matches the hash of any existing item and if so, it sets a viewbag message and returns the current view again. The same code is used if there are validation errors therefore I guard the error message with another of the same if statements. I'm sure you could all re-factor this to make it tidier!

MVC4 Decouple View Data from Database (e.g. for encryption)

The problem: I want to display human-readable data to my MVC View but I want the database to save the encrypted version and likewise when reading from the database. I need to have 2 pairs of getters and setters!

The solution is pretty easy. Create two properties. Mark one as NotMapped and use this for display, including encrypting/decrypting the human-readable data into a member variable and then have another property which is mapped to a column in the database but which is not displayed in the view. Something like this:

private string _code;   // Encrypted version of the code

        [NotMapped]
        [Display(Name = "Product Code")]
        [MaxLength(12)]
        [Required]
        public string Code 
        {
            get 
            { 
                if (_code == null) 
                    return null; 
                else return Owasp.Esapi.Esapi.Encryptor.Decrypt(_code); 
            }
            set 
            { 
                _code = Owasp.Esapi.Esapi.Encryptor.Encrypt(value.ToLower().Trim());
            }
        }

        [Column("code")]
        public string EncryptedCode
        {
            get { return _code; }
            set { _code = value; }
        }

Thursday, 11 April 2013

Making AES256 encryption work the same in PHP as .Net

Further to my previous post, I had data which I had encrypted using AES256 and which I needed to decrypt in PHP. This made me shudder when I realised that I had used a third-party library in .Net and which could do a load of things that might not be possible in PHP. Fortunately, these things really are standard and I'm please to report that it was possible to do this in PHP.

The examples here are only for decryption since I do all the encryption in .Net but the basic principle is the same. This is the function I eventually used:

protected function Decrypt($ciphertext)
{
    try
    {
        $key = $this->pbkdf2(Microsoft_WindowsAzure_RoleEnvironment::getConfigurationSettingValue("Password"),
                Microsoft_WindowsAzure_RoleEnvironment::getConfigurationSettingValue("Salt"),1000,32);
        $cipherTextBytes = base64_decode($ciphertext);
        $ivBytes = substr($cipherTextBytes,0,16);
        $cipherTextBytesOnly = substr($cipherTextBytes,16);
        $decrypted = mcrypt_decrypt(MCRYPT_RIJNDAEL_128, $key, $cipherTextBytesOnly, MCRYPT_MODE_CBC, $ivBytes);
        # mcrypt pads the decrypted data with a character whose value is equal to the number of padding characters added
        $dec_s = strlen($decrypted); 
        $padding = ord($decrypted[$dec_s-1]); 
        if ( $padding <= 16 )
        {
            # string might not be padded
            return substr($decrypted, 0, -$padding); 
        }
        else
        {
            return $decrypted;
        }
        
    }
    catch (Exception $e)
    {
        return null;
    }
}

Firstly, a correct key must be derived, in my case from a password and the master salt which are configuration variables in Azure (this password/salt system is based on the owasp implementation of ESAPI). The method being called is shown below and matches the functionality of the .Net Rfc2898DeriveBytes class.

private function pbkdf2( $p, $s, $c, $kl, $a = 'sha1' ) {
    $hl = strlen(hash($a, null, true)); # Hash length
    $kb = ceil($kl / $hl);              # Key blocks to compute
    $dk = '';                           # Derived key

    # Create key
    for ( $block = 1; $block <= $kb; $block ++ ) {
        # Initial hash for this block
        $ib = $b = hash_hmac($a, $s . pack('N', $block), $p, true);
        # Perform block iterations
        for ( $i = 1; $i < $c; $i ++ )
            $ib ^= ($b = hash_hmac($a, $b, $p, true));  # XOR each iteration

        $dk .= $ib; # Append iterated block
    }
    return substr($dk, 0, $kl);
}

I did not write the pbkdf2 function but found it on a github demo project (thanks!)

The original encrypted data is base64 encoded to allow it to be sent over non-binary safe channels so firstly this is reversed. Then, the standard way to store the initialisation vector is as the first x bytes of the stored data - 16 in the case of AES256. This is taken from the front of the data and the remainder becomes the actual encrypted data.

The function then uses mcrypt_decrypt using the RIJNDAEL_128 algorithm. This is because the 128 here is the block size of the cipher, whereas the 256 in the AES is the key length (in bits).

Another by-product of using mcrypt_decrypt is the use of padding in PKCS7 format. This means that the decrypted data will be padded to the next 16 byte block using a character whose ascii code is the same as the number of padding characters that have been added so "help" would be decrypted as "help\f\f\f\f\f\f\f\f\f\f\f\f" since \f is 12 in ascii form. The remainder of the function then checks that the last digit is a padding character (<= 16) and if it is, removes these from the decrypted string. There is the possibility if you are decrypting binary data that the last character of data is actually less than 0x10 which would make the padding code think there are padding characters to remove but this won't happen with strings (which is what I am using).

SHA512 hashing in PHP

I am currently trying to use PHP and .Net to work with the same data. One of the effects of this is having to hash and decrypt in the same way as I have done in .Net. This means two things. Firstly, I get a reference version with which to compare my PHP version so I know when I've done it correctly. Secondly, because of this, I get to catch things I cocked up in the .Net version!

Hashing

I was trying to do something pretty standard. SHA512 with a fixed salt value. There was nothing tricky in the .Net code and I assumed it would be quick and easy. I tried this:

$hash = openssl_digest($plainText.$hashsalt, "sha512");
return base64_encode($hash);

You can see that I have a $plaintText and a $hashsalt but despite trying this (and peeling through all the other variables I might have got wrong in my project) the hash wouldn't match the one I produced in .Net. I (eventually) reread the docs for openssl_digest and realised there was an optional parameter which tells the function to override its default behaviour which is to binhex encode the result of the hash so it is safe to send over non-binary channels. I personally think this default behaviour is poor because it is not obvious (apart from the manual) that this happens by default. Anyway, all I had to do was change it to this:

$hash = openssl_digest($plainText.$hashsalt, "sha512", TRUE);
return base64_encode($hash);

and the optional parameter tells it to return the raw hash (which I was then base64 encoding anyway).

I am pleased to say that this works and the result is identical to what happens in .Net when you hash with SHA512 and base 64 encode!

Creating a REST web service using Yii and PHP

This is the flavour of the month for budding web developers! Developing REST is pretty easy actually for two reasons. Firstly, a REST web service is actually only a web application since web applications already support most of what you want to call your web service with. The only things that might help are an MVC framework which allow you to abstract between the url that is called from the outside and the controller that handles the action. Secondly, REST uses standard HTTP syntax which is as old and well supported as the hills!

For instance, a common pattern is http:///api/model/action (although the action is sometimes inferred from the HTTP verb (like GET, POST, DELETE etc.). This might mean you have something like /api/user/put, /api/user/delete, /api/user/get which in traditional web development would point to three different directories/files but which you probably want to point to the same controller, say a UserController!

Anyway, in Yii it's really easy. There is a longer example here but I will just cover the outline:

  1. Download Yii as normal, you may or may not want to scaffold any database tables at this point (I just call into a separate database and didn't need to)
  2. Setup the rules in the url manager in your config so that they redirect the incoming /api/model/action into the relevant controller:
    'urlManager'=>array(
                'urlFormat'=>'path',
                'rules'=>array(
                                array('user/get', 'pattern'=>'api/user', 'verb'=>'GET,POST'   ),
                                array('userdata/get', 'pattern'=>'api/userdata', 'verb'=>'GET,POST'),
                       ),
            ),
  3. Remove any other default routes that you do not need from here (such as the post/posts routes)
  4. Note, I am not using the action in the url, it is implied because I have a very simple REST service that only "GET"s.
  5. The routes above will automatically redirect to UserController::getAction and UserDataController::getAction the bits in bold are added by the framework when resolving the request (btw this is standard Yii behaviour). Note you can also restrict which HTTP verbs are allowed. If you call the route with an incorrect verb you will get a "no route found" error. In my case I allow GET and POST but I will return a specific error if the user tries to use GET.
  6. Add a relevant method to your controller classes:
    public function actionGet()
        {
            if ( Yii::app()->request->requestType != "POST")
            {
                $this->_sendResponse(400, CJSON::encode("Invalid verb. Method requires POST"));
                Yii::app()->end();
            }
    // etc....
  7. Since this is a REST API, it is almost certain that you will be returning JSON encoded data, including with the errors. Note the helper functions for these are defined in the longer tutorial linked above and are added to components\Controller.php (the base class for controllers)
  8. Remove any default functionality that you don't want/need like login pages and contact forms.
  9. Optionally redirect/customise standard errors and modify the url manager to not include index.php in the url if required.
  10. That's it!


SQL Server Azure from Azure PHP using Yii

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.

Running Yii on Windows Azure for PHP

This is pretty straight-forward as it happens and most of you will probably have been successful. Basically, I copied the Yii code (protected, themes, css, index.php etc) into the root of my WebRole in the same structure as normal, leaving only the web configs and the bin directory from the default role code and that's all it really takes. It should all deploy as per normal and all work. The only thing you might have to play around with is the database connection string in Yii, especially if you are working with SQL Server Azure, I will cover this in my next post.

As a side note, if you download netbeans and point it at the Yii code in your web role, you can edit the PHP in netbeans and then deploy your project from powershell with no file copying or anything else silly like that!

Query Azure Configuration Setting from PHP

I'm quite excited about the fact that Microsoft are starting to embrace PHP as a standard programming language for web apps but as with many new things, the help is sparse and things seem to change between SDK releases. The first of my posts here is the last thing I had to do on my project and involves querying the value of an Azure configuration setting from PHP in code.

Why might you do this? In my case, I have connection string data and encryption passwords and I don't want them to swim around in code. In my case, by putting them into an Azure configuration setting, not only could they be changed on the fly, but they are hidden from developers who are either maintaining or reviewing your code and who you don't want to see these values.

I will cover other PHP in future posts but assuming you have a PHP app up and running on azure and you simply want to retrieve a value from the Configuration Settings, the first thing to note is that currently (April 2013(, the latest github version of the Azure PHP SDK does NOT support the RoleEnvironment, which is pretty rubbish. It as a class and a function but these throw exceptions, both locally and on the cloud. The good news is that you can still obtain the older SDK from http://phpazure.codeplex.com/ and this works just fine. Currrently, I am only using this for configuration settings so obviously other things might not work as well.

  1. Download the zip file from codeplex and unpack the library folder to a relevant place in your web root. I am using Yii so I copied it into protected/vendors/microsoft.
  2. Use whatever you need to in order to locate this package from within your app, again for Yii, i had to use the line: Yii::import('application.vendors.*'); (note that the word application is required here even though it is not part of the path). If you have a vanilla PHP app, you will not need this.
  3. require_once('microsoft/windowsazure/WindowsAzure/RoleEnvironment.php'); (or whatever the path is!)
  4. Access the config setting thus: Microsoft_WindowsAzure_RoleEnvironment::getConfigurationSettingValue("SettingKey");
That's it! You can deploy away and this is all the work required. This also works locally so you can test/debug before you deploy.

A quick note on adding these settings. Normally, you would do this in Visual Studio (and I don't know if this is possible just for the azure project in VS when using PHP) but basically, you need to edit the csdef file and add a section called and this will contain one or more entries. The values are not set here. The ConfigurationSettings element is directly under the WebRole element. You then edit the cscfg files for each of your local and cloud configurations. These should already contain an empty ConfigurationSettings element. Open this out and add the same Setting elements as in the csdef but this time add values for them (value="something"). Naturally, you can use different values for local and cloud.

Friday, 5 April 2013

Calling SQL Server from Yii on Ubuntu

I hit an issue today that I didn't think would still exist. There is no native way to call SQL Server from Linux despite both their existence for a long time. It seems Microsoft have created a Native driver but it only exists on Windows (not surprisingly).
Anyway, there is a way and it uses ODBC (generic database connections) + TDS (some old-school database protocol that SQL Server supports). More specifically, we will use free-tds, a Linux project.
Most of the information at this blog post is correct but the example you are supposed to copy did not exist in my installation (Ubuntu 12.10) and the Yii specific part was missing (although I assume this would mostly be the same as per other database technologies and frameworks). So it is in a nutshell:

  1. Install freetds-bin, freetds-common, tdsodbc, odbcinst, php5-odbc and unixodbc from the package manager. Some of these will pull in some of the others as dependencies.
  2. Edit /etc/odbcinst.ini and add in the following lines:

    [mssql] # note this must match the driver name in the connection string
    Description = MS Sql Server
    Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
    Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
    UsageCount = 1
    FileUsage = 1
  3. Restart your web server: sudo service apache2 restart
  4. Set your Yii connection string thus:

    'connectionString' => 'odbc:Driver=mssql;Port=1433;Server=192.168.1.115;Database=MyDatabase;UID=username;PWD=password123'
  5. Note the colon between odbc and Driver, it is not a semi-colon.
  6. It should all work fine
  7. Note this is not a great solution because TDS is old-school and no doubt not optimised. Also odbc is an abstraction level which may or may not help performance. I think I eventually might run the php on a Windows box which can access a native PHP PDO driver.