To connect to Sql Server from *nix environment, you can use JDBC (Java), Perl’s DBI and DBD packages, unixODBC, etc. You can also use another open source tool called FreeTDS.
TDS stands for Tabular Data Stream. It is a protocol that defines how data is transmitted between computers. Both Sybase and Sql Server implement TDS. The detailed implementation may be different now for Sybase and Microsoft, but I don’t think the basic principal has changed much over the years. FreeTDS is a tool that is written on the TDS protocol. It works both with Sql Server 2000 and Sql Server 2005. It also works with Sybase.
A few years ago I helped out a LAMP (Linux, Apache, MySql, PHP) project. The web application was working fine. Later on there was a need to generate web reports based on data from Sql Server 2000. So I helped setting up FreeTDS on RedHat Linux to solve that problem.
Ever since Sql Server 2005 came out, I’ve been meaning to test it to see if it works with Sql Server 2005. I finally did it today. I am happy to report that it does work. Here is my procedure on Cygwin:
1. wget http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz (Note: the link to the tar file may be different) 2. tar -xvf freetds-stable.tgz (Note: your file name maybe different) 3. cd freetds-0.6.4 (Note: your folder name maybe different) 4. ./configure --with-tdsver=8.0 (Note: it is important to have this tdsver=8.0 option) 5. make 6. make install
To verify you did it successfully, launch tsql on the command line. tsql is a command line utility that comes with FreeTDS. It does not have a lot of command line switches. The few it has are very similar to osql and sqlcmd.
For example, you can try:
tsql -S MySqlServer -U sa
and you will be prompted for password.
Once you are in, issue some command. For example, you can try
select name from sysdatabases (on Sql Server 2000) or
select name from sys.databases (on Sql Server 2005)
and see if you get results back.
20090928 Update: I tried to test FreeTDS for a named SQL Server 2008 instance from 2 virtual machines that I use, without success. I haven’t figured out why, perhaps due to my network was not done properly? I could ping those 2 boxes from each other, though. I also tried to connect to SQL Server instance port directly, and that didn’t work either. I couldn’t even telnet to the port that is supposed to be open on Windows.
5 responses to “Connecting to Sql Server from Unix or Linux with FreeTDS”
Hi,
Thanks for the details.
I tired installing freetds and was able to do it successfully. But once i logged in to sql server, i couldn’t execute any of the commands.
5> select name from sysdatabases
6> select name from sys.databases
7>
Can you please help.
Regards,
Javad
Hi,
Thanks for the details.
I tried installing freetds and was able to do it successfully. But once i logged in to sql server, i couldn’t execute any of the commands.
5> select name from sysdatabases
6> select name from sys.databases
7>
Can you please help.
Regards,
Javad
Javad,
Glad you found this helpful. There are a few things you can try:
1. You need to type “go” on a separate line in order for the statement to run;
2. Make sure you are in the right database and the login has permissions.
Hope this helps.
Hello,
I found your discussion on freetds and was wondering if you could help me with a quick question.
I have been able to connect to MS-SQL servers just fine, but there is a new MS-SQL server that I am trying to connect to. It is called ORCXPDB1C2\SQL, but the \ is causing me trouble. I was wondering if you had any insight on how to connect to a server with a \ in it.
Thank you,
Sean
Sean,
Sorry for the late reply. Been busy the last couple of days.
I can see FreeTDS might have trouble with this. When an instance has the back slash in it, it means it is a named instance. In your example, it resides on the server ORCXPDB1C2.
One thing I suggest you try is to connect to the named instance listening port directly on the box, therefore bypassing the back slash problem. So you will need to find out the port number this instance is at. Once you get that, try:
tsql -S ORCXPDB1C2:PortNumberHere -U sa
I have not done this in the past, but I think it should work. Hope this helps.