Programming

Microsoft SQL server is a fork of Sybase SQL server. This is because their co-operation at their early stages during end of 80s and beginning of 90s. For that reason the client protocol to access both servers is precisely the same TDS. There is an excellent open-source library of FreeTDS to access these SQL-servers from Linux. According to me and number of other sources in The Net, this library can also access Windows Azure SQL server.

During my own projects I was building a Linux-image for Azure. My development boxes are spread around geographically, and in this case the simplest solution was to open access into a firewall to allow incoming IPv6 TCP/1433 requests.

My tests with this setup failed. IPv6-access was ok, firewall was ok, a socket would open without problems but my application could not reach my development SQL-box. Bit of a tcpdumping revealed that my Hyper-V hosted Linux-box attempted to reach my SQL-box via IPv4. What?! What?! What?!

A quick browse into FreeTDS-code revealed that it had zero IPv6-related lines of code. According to Porting IPv4 applications to IPv6, there should be usage of struct sockaddr_in6 and/or struct in6_addr. In the latest stable version of FreeTDS there is none.

After a lot of Googling I found a reference from FreeTDS developers mailing list that in January 2013 Mr. Peter Deacon started working on IPv6-support. Naturally, this was good news to me. Another message in the ML said from February 2013 said that the IPv6-support would be working nicely. Yet another good thing.

Now all I had to do is find FreeTDS source code. I found somebody's Subversion copy of it, but with Google, no avail. The IPv6-patch nowere to be found, nor the actual source code. The mailing list itself seems to be having some sort of technical difficulties. My attempts to ask for further information seemed to go nowhere. I pretty much abandoned all hope when Mr. Frediano Ziglio was kind enough to inform me that the IPv6-support would be in the latest GIT-version of FreeTDS.

FreeTDS source code can be found from Gitorious at http://gitorious.org/freetds/freetds

I can confirm that the current Git-version does work with IPv6. However, for example PHP's PDO or Perl's DBI do not support entering IPv6-addresses into connect string. With FQDN I could confirm everything being IPv6 from Wireshark, but all my attempts of entering native IPv6-addresses into connect strings failed on both libraries and FreeTDS's CLI-tool tsql.

Anyway, here is what I did to test the thing. First I confimed that there is basic connectivity:

tsql -H myownserver.here -p 1433 -U sa
Password:
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1> sp_help MyCoolTable
2> go
1> quit

Then I took a simple example from Perl Monks site and modified it to work (the original code was quite crappy):

#!/usr/bin/perl -wT --
# vim: tabstop=4 shiftwidth=4 softtabstop=4 expandtab:

use DBI;
use Data::Dumper;   # For debugging
use strict;
use utf8;

my $dsn = 'DBI:Sybase:server=myownserver.here;database=MyCoolDatabase';
my $dbh = DBI->connect($dsn, "sa", 'lemmein!') or
    die "unable to connect to server. Error: $DBI::errstr";

my $query = "SELECT * FROM MyCoolTable";
my $sth = $dbh->prepare($query) or
    die "prepare failed. Error: $DBI::errstr";
$sth->execute() or
    die "unable to execute query $query. Error: $DBI::errstr";

my $rows = 0;
while (my @first = $sth->fetchrow_array) {
    ++$rows;
    print "Row: $rows\n";
        foreach my $field (@first) {
                print "field: $field\n";
        }
}
print "$rows rows returned by query\n";

Also I did some complex testing with PHP DBO and had no issues. I even made sure from my firewall settings, that I could not accidentally access the SQL Server via IPv4. It just works perfectly!

If you need my src.rpm or pre-compiled packages, just drop a comment.

Getting Parallels Plesk Panel to do something without admin's interaction is not tricky. My favorite method of remote-controlling Plesk is via its RPC API. I am a co-author of Perl-implementation API::Plesk, which is available in CPAN.

All XML RPC -requests should be directed towards your Plesk-server at URL
https://-your-plesk-box-here-:8443/enterprise/control/agent.php

Raw XML

First we'll need to get the internal site ID of a domain. A request to get all the subscriptions looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<packet version="1.6.3.5">
    <webspace>
        <get>
            <filter/>
            <dataset>
                <gen_info/>
            </dataset>
        </get>
    </webspace>
</packet>

Note: It would have been possible to filter a specific subscription by domain name, but in this case we just wanted a list of all.

A response to it will contain domain names and their Ids:

<?xml version="1.0" encoding="UTF-8"?>
<packet version="1.6.3.5">
    <webspace>
        <get>
            <result>
                <status>ok</status>
                <filter-id>1</filter-id>
                <id>1</id>
                <data>
                    <gen_info>
                        <name>www.testdomain.org</name>
                    </gen_info>
                </data>
            </result>
        </get>
    </webspace>
</packet>

The response packet contains internal ID and name. We'll be using the internal ID of 1 to get all the DNS-records of the zone:

<?xml version="1.0" encoding="UTF-8"?>
<packet version="1.6.3.5">
    <dns>
        <get_rec>
            <filter>
                <site-id>1</site-id>
            </filter>
        </get_rec>
    </dns>
</packet>

A response packet will look like this:

<?xml version="1.0" encoding="UTF-8"?>
<packet version="1.6.3.5">
    <dns>
        <get_rec>
            <result>
                <status>ok</status>
                <id>111</id>
                <data>
                    <site-id>1</site-id>
                    <type>CNAME</type>
                    <host>www.testdomain.org.</host>
                    <value>testdomain.org.</value>
                    <opt/>
                </data>
            </result>
        </get_rec>
    </dns>
</packet>

There seems not to be any other way of picking a specific record. A filter with type/name would be welcome. Any further operations would be done with the domain record's ID. In this case it is 111.

Perl-code

With a software library, the access is much easier. The same requests would be something like this in Perl:

my $plesk_client = API::Plesk->new('api_version' => '1.6.3.5',
  'secret_key' => $plesk_api_key,
  'url'=>'https://-your-plesk-box-here-:8443/enterprise/control/agent.php',
  'debug'        => 0);

$res = $plesk_client->webspace->get();
die "Subscriptions->get() failed!\n" . $res->error . "\n" if (!$res->is_success);

my @domains = @{$res->results()};
my $cnt = $#domains + 1;
for (my $idx = 0; $idx < $cnt; ++$idx) {
    my $domainId = $domains[$idx]{"id"};
    $domainId += 0;    # toInt

    my $res = $plesk_client->dns->get('site-id' => $domainId);
    die "DNS->get() failed!\n" . $res->error . "\n" if (!$res->is_success);
    my %dns = %{@{$res->results()}[0]};
    print Dump::Dumper(%dns);
}

That is pretty much it.

Update (2nd Nov 2013)

To get all of the domains will require a two-step process (order does not matter): 1) get all the subscriptions (kind of main domains) and 2) get the other domains under subscriptions.

In my Perl-code I do it like this:

# NOTE: This is from the above code
# 1st round:
# Get all the subscriptions.
# There we have the "main" domains
$res = $plesk_client->webspace->get();
die "Subscriptions->get() failed!\n" . $res->error . "\n" if (!$res->is_success);

# NOTE: New one:
# 2nd round:
# Get all the sites.
# There we have the "non-main" domains
$res = $plesk_client->site->get();
die "Sites->get() failed!\n" . $res->error . "\n" if (!$res->is_success);
@domains = @{$res->results()};

In my case, the $res-hash is fed into a ExtractDomains()-function to get the details I need from them. If only the name is required, then no further processing is necessary.