Ok, because we all hate Microsoft with a passion, we're going to assume that you're a bit pissed that you are even attempting to let your unix box communicate with mssql. That being said, we want the process of communicating with it to be as easy as possible :)

as of the writing of this document, the following packages work very nicely to allow gentoo to speak with mssql:

Python or PHP are languages that we will use to query the database and manipulate the returned data. Apache is the server that can "run" either of these two languages via the appropriate mod_foobar package. unixODBC is an api that either of these languages will use to communicate with Freetds, the piece that then talks to mssql for us. (adodb is a database abstraction layer for php that can make life a bit slow, but really easy :)

So, let's summarize:
A user browses to a web page, apache tells php to tell adodb to tell unixODBC to tell Freetds to ask mssql a question. Makes sense right?

Because of Gentoo and it's beautiful portage, getting this stuff installed is cake. Simply make sure your /etc/make.conf has the following two use flags: freetds odbc

Here is the USE flag portion of my make.conf file:

USE="-X -gnome -kde -gtk -qt postgres gd png jpeg xml freetds odbc ssl"
        

Once you have these use flags, all you need is to do:

root# emerge sync
root# emerge -p apache mod_php
root# emerge -v apache mod_php
        

The first command will update your portage tree, the second will show you what is about to happen, the third will actually to the installation

Because of your use flags, apache and mod_php will be installed, along with a whole slew of other neat packages, most importantly PHP, unixODBC, and Freetds. But, we will have one serious problem that needs to be addressed straight away.

This part is extremely important, or your setup will never work:
As of this writing, the Freetds ebuild doesn't compile Freetds with all the stuff we need, and you will thus wind up cursing because you can'd find /usr/lib/libtdsodbc.so which is needed later. The soluion is to make sure your current ebuild has the following line: econf --with-unixodbc

Here is the src_compile() function that I use:

src_compile() {
    econf --with-tdsver=7.0
    econf --with-unixodbc
    emake || die
}
        

The inclusion of --with-unixodbc tells the compiler to include the necessary pieces to allow unixodbc to use Freetds, namely /usr/lib/libtdsodbc.so. Because we have already installed everything, the fix is a quick update of the ebuild to look like the above, and then reinstall it like so:

root# emerge -v freetds
        

This will update Freetds including the unixODBC stuff we need

Now that we have everything installed nicely, it's time to configure everything. The first piece is the "Freetds registration". This is done via the file /etc/freetds.conf
Here is what I added to the very bottom of the file:

# A typical Microsoft SQL Server 2000 configuration
[elk]
        host = sql.internal.rockfloat.com
        port = 1433
        tds version = 7.0
        

This gives your system a Freetds server by the name of elk that points to the hostname of sql.internal.rockfloat.com, using port 1433 and tds vesion 7.0

Now that you have a Freetds server, we need a unixODBC driver that can talk to it. This is done via the file: /etc/unixODBC/odbcinst.ini
Here is what I wrote in my file:

[TDS]
Description = v0.61 with protocol v4.2 and v7
Driver = /usr/lib/libtdsodbc.so
        

Here TDS is the name of our new unixODBC driver, and /usr/lib/libtdsodbc.so is the binary that is actual binary driver (remember this is the piece that required a reinstall of Freetds after modifiying the ebuild)

Now that we have a server, and a driver.. it's time for a datasource that brings this all together. This is done via the file: /etc/unixODBC/odbc.ini
Here is what i wrote in my file:

[alpha]
Driver          = TDS
Description     = Rockfloat alpha mssql server
Trace           = No
Servername      = elk
Database        = rockfloat_alpha
UID             = rockfloat_alpha
        

Here let's go thru this file from top to bottom, explaining what stuff is:
alpha is the name of our new datasource, and is known as a "dsn" to php or python.
Thinkflat alpha mssql server is a silly description of what this datasource points to.
Trace = No is something I haven't looked into :)
elk is the name of our Freetds server created above.
rockfloat_alpha is the name of our mssql database that we want to connect to.
rockfloat_user is the name of the mssql user that has permissions to query this database.

All done with the configuration, that wasn't so bad!

The adodb for php library is a really nice database abstraction layer for php. It makes things much simpler if you were ever to move from mssql to mysql or postgres (the latter being my favorite.) For this we will go into a working website and install the files:

root# cd /path-to-website/htdocs
wget http://phplens.com/lens/dl/adodb420.tgz
tar zxvf adodb420.tgz
rm adodb420.tgz
        

Now we will have a directory named adodb in the root of your website's functional root. For those who don't know what this means, basically do: http://your-website/adodb and apache would look for a file by the name of index.html inside the directory you just created.
Anyway...

Assuming things went well, create a file called gentoo-mssql-setup.php inside of htdocs with the following content:

<?
// include the adodb abstraction library
include('adodb/adodb.inc.php');

// create a connection object (here you could use odbc, postgres, mysql, ect.)
$objConn =& ADONewConnection('odbc');

// tell adodb that we want the recordset to include an associative array of fieldnames
$objConn->SetFetchMode(ADODB_FETCH_ASSOC);

// tell the adodb connection object to connect to our shiny new datasource
$objConn->Connect('alpha', 'rockfloat_user','PASSWORD');

// create a simple query
$tmp_str_sql = "select * from VALID-TABLE";

// execute the query
$objRS = &$objConn->Execute($tmp_str_sql);

// if we have a recordset returned (true) then...
if($objRS){
    print('Recordcount: '.$objRS->RecordCount().'<br />');
    
    // loop thru the recordset
    while(!$objRS->EOF){
        print('data returned: '.$objRS->fields['VALID-FIELDNAME'].'<br />');
        $objRS->MoveNext();
    }
    
    // clean house
    $objRS->close();
}
?>
        

Now pull up the page in your favorite browser (most likely opera) and go to:

http://your-website/gentoo-mssql-setup.php which hopefully will post something like (but with your data):

data returned: Foo
data returned: Bar

Haven't written this part yet :/

- http://phpbuilder.com/columns/alberto20000919.php3?page=2
- http://linuxjournal.com/article.php?sid=6636&mode=thread&order=0
- http://www.unixodbc.org/doc/FreeTDS.html
- http://php.weblogs.com/ADODB
- http://www.microsoftsucks.com/