Zarafa DB to LDAP user plugin conversion

From Zarafa wiki

Revision as of 17:50, 25 February 2009

The following script allows you to convert the mailboxes from a local Zarafa DB plugin to an Active Directory environment.

Requirements:

- Make sure all local Zarafa users are also available in the Active Directory
- Install the perl module MIME::Base64 and DBI
- Make sure you have a successful backup of the Zarafa database

#!/usr/bin/perl -w

use strict;
use MIME::Base64;
use DBI;

if(@ARGV != 7) {
        print "Usage: $0 <mysqluser> <mysqlpass> <database> <ldaphost> <ldapbinduser> <ldapbindpass> <ldap_base_dn>\n";
        exit(1);
}

my ($dbuser, $dbpass, $db, $ldaphost, $ldapuser, $ldappass, $ldapbase) = @ARGV;

my $dbh = DBI->connect("DBI:mysql:database=$db;host=localhost",
                                $dbuser, $dbpass,
                                {'RaiseError' => 1});

open INPUT, "ldapsearch -x -H ldap://$ldaphost -b \"$ldapbase\" -D \"$ldapuser\" -w \"$ldappass\"|";

my %names;
my $objectsid;

while(<INPUT>) {
        if(/sAMAccountName: (\S+)/) {
#               print "name: $1\n";
                $names{$1}->{"objectsid"} = $objectsid;
        }
        if(/objectSid:: (\S+)/) {
#               print "objectSid: " . $dbh->quote(decode_base64($1)) . "\n";
                $objectsid = decode_base64($1);
        }
}

my $name;

foreach $name (keys %names) {
        my $q = $dbh->prepare("SELECT objectid FROM objectproperty WHERE propname='loginname' AND value='$name'");
        $q->execute;

        my $rows = $q->rows;

        if($rows == 1) {
                # Found a user with username in the database, so now we know the user id
                $names{$name}->{"userid"} = ($q->fetchrow_array())[0];
        }

my ($dbuser, $dbpass, $db, $ldaphost, $ldapuser, $ldappass, $ldapbase) = @ARGV;

my $dbh = DBI->connect("DBI:mysql:database=$db;host=localhost",
                                $dbuser, $dbpass,
                                {'RaiseError' => 1});

open INPUT, "ldapsearch -x -H ldap://$ldaphost -b \"$ldapbase\" -D \"$ldapuser\" -w \"$ldappass\"|";

my %names;
my $objectsid;

while(<INPUT>) {
        if(/sAMAccountName: (\S+)/) {
#               print "name: $1\n";
                $names{$1}->{"objectsid"} = $objectsid;
        }
        if(/objectSid:: (\S+)/) {
#               print "objectSid: " . $dbh->quote(decode_base64($1)) . "\n";
                $objectsid = decode_base64($1);
        }
}

my $name;

foreach $name (keys %names) {
        my $q = $dbh->prepare("SELECT objectid FROM objectproperty WHERE propname='loginname' AND value='$name'");
        $q->execute;

        my $rows = $q->rows;

        if($rows == 1) {
                # Found a user with username in the database, so now we know the user id
                $names{$name}->{"userid"} = ($q->fetchrow_array())[0];
        }
}

foreach $name (keys %names) {
        print "# $name\n";
        print "UPDATE users SET externid=" . $dbh->quote($names{$name}->{"objectsid"}) . " WHERE id=" . $names{$name}->{"userid"} . ";\n";