Single Sign-On Between SugarCRM and Request Tracker

As mentioned before, over the last few month I’ve been involved in quite a few integration projects, using mostly SugarCRM and Request Tracker.  One of the interesting challenges was the Single Sign-On (SSO) between the two.

The interesting bit comes from these facts:

• Different technologies: SugarCRM is written in PHP, while Request Tracker is in Perl.
• SugarCRM uses PHP sessions, which are not very useful for other languages.
• Request Tracker supports multiple external authentication mechanisms, but nothing that fits well with SugarCRM.

Firstly, let’s see which mechanisms Request Tracker supports for external authentication.  For that, we need the documentation for RT::Authen::ExternalAuth module.  The choices are:

1. LDAP
2. Database
3. SSO Cookie

LDAP was not an option in this project, so it was dismissed pretty much immediately.  SSO Cookie sounded like the best option, but after some poking around was also dismissed.  Firstly, SugarCRM does not set the SSO cookie, as it uses sessions, and modifying it to do so didn’t sound elegant.  Secondly, I couldn’t quite make all the sense out of the rather brief documentation for the RT::Authen::ExternalAuth::DBI::Cookie module.  So I settled on the external MySQL database option.

RT::Authen::ExternalAuth::DBI module is well documented and provides some examples of how to handle the passwords.  The nice feature of Request Tracker authentication that not many people know about is that you can send user and pass GET parameters to pretty much any RT page.   If the user is not authenticated, than these parameters will be used to authenticate him.  If the user is already authenticated, than these will be ignored.

That sounds like a nice and easy way to link to RT from SugarCRM, where the user is already logged in.  But we need to look into more details here, and also consider the security aspect.  Even if RT instance runs on HTTPS, there is still a chance of one user sending a URL with credentials to another by email or chat.

Before we get into that though, let’s look at the value of password.  We can fetch the password value stored in the database for the current SugarCRM user.  But the password is not stored in clear text – it’s in a form of a hash.  So we can send an encrypted hash value as a parameter, and nobody will guess what the password is, right?

Well, maybe.  Especially after SugarCRM 6.5 introduced stronger password hashes.  But maybe we still shouldn’t send those all over.  Let’s encrypt the hash once over anyway.

If we are to use encryption, we need to find one that we can use both in PHP and Perl.  PHP will be encrypting the password hash, and Perl will be decrypting it, before comparing to the value in the database.

Tinkering with different encryption methods and their parameters took me a while.  Security in general, and cryptography in particular aren’t my strongest suits.  This blog post, however, was quite useful.  For simplicity sake, here’s what I did:

• Blowfish algorithm with CDC mode was chosen as the cryptography mechanism.
• Created a random string of 32 bytes long, which was added to the configuration of both SugarCRM and Request Tracker.  I call it seed, just because I don’t know any better.
• First 24 bytes of the string were used as the key.  Last 8 bytes of the string were used as IV.
• Encrypted string was base64 encoded to avoid binary data in the URLs.
• Base64-encrypted string was URL-encoded on top.

Here’s the sample PHP code:

<?php
/**
* Encryption helper
*/
class EncryptHelper {

  protected static function getKeyFromSeed($seed) {
    $result = substr($seed, 0, 24);
    return $result;
  }

  protected static function getIvFromSeed($seed) {
    $result = substr($seed, -8, 8);
    return $result;
  }

  public static function encrypt($seed, $string) {
    $key = self::getKeyFromSeed($seed);
    $iv = self::getIvFromSeed($seed);

    $result = mcrypt_encrypt(MCRYPT_BLOWFISH, $key, $string, MCRYPT_MODE_CBC, $iv);
    $result = base64_encode($result);
    $result = urlencode($result);

    return $result;
  }

  public static function decrypt($seed, $string) {
    $key = self::getKeyFromSeed($seed);
    $iv = self::getIvFromSeed($seed);

    $result = urldecode($string);
    $result = base64_decode($result);
    $result = mcrypt_decrypt(MCRYPT_BLOWFISH, $key, $result, MCRYPT_MODE_CBC, $iv);

    return $result;
  }
}
?>

On the Request Tracker side, I followed the documentation to configure external database authentication.  Which also provided a handy p_check function, where I could decrypt the password hash before checking.  Here’s configuration snippet from etc/RT_SiteConfig.pm:

Set($ExternalSettings, {
  'My_MySQL' => {
    'type' => 'db',
    'dbi_driver' => 'mysql',
    'server' => 'localhost',
    'port' => '3306',
    'user' => 'root',
    'pass' => '',
    'database' => 'sugarcrm',
    'table' => 'users',
    'u_field' => 'user_name',
    'p_field' => 'user_hash',
    'p_check' => sub {
      my ($hash_from_db, $password) = @_;

      $password = uri_unescape($password);
      $password = decode_base64($password);

      my $seed = "my_seed_string_of_at_least_32_bytes_goes_here";

      my $key = substr $seed, 0, 24;
      my $iv = substr $seed, -8, 8;

      my $cipher = Crypt::CBC->new(
        -key => $key,
        -cipher => 'Blowfish',
        -iv => $iv,
        -header => 'none',
        -padding => 'null',
        -literal_key => 1,
        -keysize => length($key),
      );
      my $password = $cipher->decrypt($password);

      return $hash_from_db eq $password;
    },
    'attr_match_list' => [
      'Gecos',
      'Name',
      'EmailAddress',
    ],
    'attr_map' => {
      'Name' => 'user_name',
      'Gecos' => 'user_name',
      'Nickname' => 'user_name',
      'HomePhone' => 'phone_home',
      'WorkPhone' => 'phone_work',
      'MobilePhone' => 'phone_mobile',
      'Address1' => 'address_street',
      'City' => 'address_city',
      'State' => 'address_state',
      'Zip' => 'address_postalcode',
      'Country' => 'address_country',
    },
  }
});

Now the above sorts the problem of us sending around the password hash value from the database. It’s well encrypted now, and we can authenticate just fine.

But the problem of one user sending the URL with the credentials to another user – accidental or not – is still not solved. So, what can we do here?

As I mentioned earlier, we can’t really read the PHP session from Perl. But we can read the session cookie. SugarCRM starts the user session and stores the ID of the session in PHPSESSID cookie. So, we can use that as part of our seed to make sure that user and pass parameters only work properly if they are used in the same browser during the same session. Session ID by itself is too short to be our seed string, but we can complete it with random seed characters, like the above examples show, or we can throw in some more strings related to the current session – like the user’s IP address, or browser’s User-Agent, etc. I’ll leave this for you to figure out.

The next problem, is “how can we read the session cookie from RT_SiteConfig.pm?”. It’s not a true CGI script. We don’t have any global request variables or anything like that. This part took the most time to figure out in this whole setup.

Dumping out the environment variables, I didn’t see any web request server variables, except QUERY_STRING. No cookies, no user-agent, no remote IP address. This was weird. But the QUERY_STRING was there, so that gave me a lead. Digging through the Request Tracker source code, I found the place, where my problem was.

Gladly, Request Tracker provides an easy way to modify it. Simply copy the lib/RT/Interface/Web/Handler.pm to local/lib/RT/Interface/Web/Handler.pm (created the folders, if needed), and add additional variables as needed. Here’s an example:

# HTML::Mason::Utils::cgi_request_args uses $ENV{QUERY_STRING} to
# determine if to call url_param or not
# (see comments in HTML::Mason::Utils::cgi_request_args)
$ENV{QUERY_STRING} = $env->{QUERY_STRING};
# Remote IP address
$ENV{REMOTE_ADDR} = $env->{REMOTE_ADDR};
# Cookie header
$ENV{HTTP_COOKIE} = $env->{HTTP_COOKIE};

With this now, you can update the p_check subroutine in the etc/RT_SiteConfig.pm file, to match the key and IV to the ones used in SugarCRM.

As always, if you know of a better or more elegant way to solve the above, please let me know.