|Syntactic Confectionery Delight|
Accessing Microsoft SQL Server
Recently, I made yet another attempt to get Perl to access Microsoft SQL Server using DBD. Usually, when I want to connect to a Microsoft SQL Server, it is from Perl on Windows. So I take the easy route and use DBD::ODBC and use an ODBC connection. This time though, I wanted to connect to Microsoft SQL Server 2000 from a Linux box. Having no ODBC to fall back on, I looked for native DBD driver of some sort.
It took me several hours of struggling to make it work. I almost gave up several times, so I am writing outline to help anyone else trying to accomplish this same task.
In the end, we will use the DBD::Sybase perl module from
CPAN to access the Microsoft SQL Server. Before we can do that however, we
must first compile the freetds library.
Download and compile freetds from
Now we have the freetds compiled, but we still have configure it. This is the part that threw me off and is so different from other DBD drivers. The DBD::Sybase driver will ultimately be affected by the contents of the /usr/local/freetds/etc/freetds.conf file. If that file is not configured correctly, your DBD::Sybase connection will fail.
Okay, now that we have established there is a relationship between the freetds.conf file and the DBD::Sybase module, let's edit the freetds.conf file.
The strategic modifications I made to the freetds.conf file were:
1) uncomment the following lines and modify if necessary:
Note: this forces the module to attempt a database login instead of a domain login. I could not get domain login to work, though I will admit I did not try very hard.
2) uncomment the following line and modify if necessary:
This supposedly sets the default tds version to establish a connection with. I have only SQL Server 2000 servers, and they won't talk at any lower version. So I set it to 7.0. If for some reason you had older SQL Servers, you might leave it at the default 4.2.
3) create a server entry for my server sql1:
Note: My server here is sql1. Ping sql1 worked, so I am sure I can resolve it using DNS. You can also specifcy an ip address instead of the host name. The sql1 in the brackets is just a descriptor. It could be 'superduperserver' and it would still work as long as my 'host =' is set correctly. I tried 'tds version 7.0' for my SQL Sever 2000 and it worked. Version 5.0 though resulted in an error. You might want to verify your SQL Server is listening on port 1433 with a 'netstat -a -n' run from the command line on the SQL Server.
At this point you can verify your configuration.
You will then be prompted for a password and if everything is well, you will see a '1)' waiting for you to enter a command. If you can't get the 1) using tsql, I doubt your DBD::Sybase perl code is going to work. Please note that sqluser is not an Active Directory/Windows Domain user, but an SQL Server user.
Now that we have the freetds library prerequisite for DBD::Sybase installed and configured, we can compile the DBD::Sybase perl module. Obtain it from www.cpan.org if you haven't already.
once you have untarred it and are in the directory, run:
Note: The export line is to let the compilation process
know where to find the freetds libraries.
You are now ready to test your DBD::Sybase module.