Using ODBC to MS SQL

ejennings_98 · 08-04-2005, 04:11 PM

I am trying to connect from my Mandrake 10.2 box to an MS SQL server. After going through the number of tutorials on using unixODBC and freeTDS, I am still at a loss.

I have unixODBC 2.2.1 installed as well as freeTDS 0.63. I have setup the the connections as described in the tutorials, but am not able to connect using the isql command.

I am able to connect to the ms sql server from another windows box. I am able to telnet to the sql server from the linux box.

When I use isql, the ODBC client, I get the following.

$ isql -v MyDSN me mypassword
[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[ISQL]ERROR: Could not SQLConnect

I added the following to the freetds.conf file:

[TDS]
host = myserver
port = 1433
tds version = 7.0
--------------------------------

The odbc.ini file reads as follows:

[MyDSN]
Description = TDS MSSQL
Driver = TDS
Servername = myserver
Database =
UID = me
PWD = mypassword
Port = 1433
----------------------------

If anyone knows of a good resource or if there are any suggestions, it would be much appreciated.

Thanks, Eric

paulsm4 · 08-04-2005, 04:33 PM

Hi -

1. I would definitely verify that freeTDS can talk to SQL Server first. I've always
had good experience with the folks on the freeTDS mailling list - they should
be able to help you.

2. The second step is to get unixODBC working. I'm afraid I can't help you here.

3. This article might be of interest:

http://www.phpbuilder.com/columns/alberto20000919.php3?

'Hope that helps .. PSM

ejennings_98 · 08-04-2005, 04:54 PM

paulsm4:

I was able to confirm that the freeTDS and connect to the server. Its the ODBC stuff that is not working.

The article that you reffered to was the first one I read. It is not intuitive and must be missing something because it does not work. I have followed it fairly carfully.

There is a second article from Linux Journal which was much more straight forword, except the author forgot the step about first installing the unixODBC stuff.

Eric

paulsm4 · 08-04-2005, 05:43 PM

Cool - I'm glad you were able to localize the problem to the unixODBC component.

Unfortunately, my experience is only writing Windows clients to Windows servers, and Linux clients to Linux servers. Two possible suggestions:

1. See if there are any viable alternatives to unixODBC

2. Consider using freeTDS (ie. have your client make dblib API calls) instead of ODBC.

I'm pretty sure you also have other alternatives besides those two, but unfortunately I can't help you there.

Good luck .. PSM

ejennings_98 · 08-04-2005, 05:57 PM

paulsm4:

To test the freeTDS I used the following:
[root@localhost]# /usr/local/bin/tsql -S myserver -U username
Password: <password>

Then I got the prompt
1>

So, all seems okay with the freeTDS. I got this from the Linux Journal article http://www.linuxjournal.com/article/6636

I am trying to use PHP on Linux to connect to MS SQL, and that is where the challenge is. I have been developing PHP apps with a variety of databases; using both Linux and Windows. This is the time where I need to connect a Linux server to MS SQL.

Thanks for your input. Anyone else??

paulsm4 · 08-04-2005, 05:59 PM

Would it be possible to run PHP (under either Apache or IIS) on the SQL Server host?

ejennings_98 · 08-05-2005, 11:09 AM

paulsm4:

For the short term I guess I will have to setup IIS with PHP. This is not what I had planned as I am at a critical point with my company, trying to move them over to Linux. If they loose confidence now, due to a driver issue, I will be forced to supply Windows solutions in the future. I have recently had some great successes with Linux.
(Okay stepping off of the soap box now)

Here is what I have found so far:
Apparently I do not need and ODBC connection from Linux to the MS SQL server. I can us the freeTDS along with adding the ms-sql extension into PHP. I dont know how to do that, but I am checking into it.

I have found an RPM for setting this up:
http://phprpms.sourceforge.net/mssql
Since I am using Mandriva, which supports most RPM installs, I will either have to guess at which one to use or build my own from scratch with the source files they supply. I am not looking forward to that.

If anyone else has had success using MS SQL with PHP on Linux I would really appreciate some assistance.

Thanks, Eric

ejennings_98 · 08-05-2005, 12:10 PM

Okay, I think I may have figure it out.

The old PHP site came to the rescue. http://ca.php.net/mssql

A post from marcos1979ar at yahoo dot com dot ar, 10-Jun-2004 06:07, wrote a mini tutorial. I will post it here just in case someone runs into the same stuff as I did.

----------------------
Red Hat 9.0 Freetds 0.62.3 Apache 2.0.49 PHP 5.0.ORC3
Instucction, that premit connect a server Linux with a server Windows 2000 Server with SQL Server.

INSTALLING FREETDS

1-. Download freetds -> www.freetds.org
2-. tar -zxvf freetds-stable-tgz
3-. cd freetds-0.62.3
4-. ./configure --prefix=/usr/local/freetds --with-tdsver=8.0 --enable-msdblib --enable-dbmfix --with-gnu-ld

Note: tdsver=8.0 if you use SQL 2000, tdsver=7.0 if you use SQL 7.0

5-. make
6-. make install
7-. /usr/local/freetds/bin/tsql -S <ip of the server> -U <User SQL>

Note: For default User SQL is sa and the it have not password
For example: /usr/local/freetds/bin/tsql -S 198.168.100.2 -U sa

8-. Add the next text in freetds.conf ( /usr/local/freetds/etc )
[TDS]
host = <ip of the Server with Sql>
port = 1433
tds version = 8.0

Note: If you use SQL 2000 then tds version = 8.0
if you use SQL 7.0 then tds version = 7.0

9-. Add the next text in the file /etc/ld.so.conf
/usr/local/freetds/lib

INSTALLING APACHE

1-. Download apache www.apache.org
2-. tar -zxvf httpd-2.0.49.tar.gz
3-. cd httpd-2.0.49
4-. ./configure --prefix=/etc/httpd --enable-so
5-. make
6-. make install
7-. Configure the file -> httpd.conf ( /etc/httpd/conf/httpd.conf )
8-. Probe the apache: /etc/httpd/bin/apachectl start
/etc/httpd/bin/apachectl stop

INSTALLING PHP

1-. Download the PHP in this site (www.php.net)
2-. tar -zxvf php-5.0-ORC3.tar.gz
3-. cd php-5.0-ORC3
4-. ./configure --with-apxs2=/etc/httpd/bin/apxs --enable-versioning --with-mssql=/usr/local/freetds --disable-libxml
5-. make
6-. make install
7-. cp php.ini-DIST /usr/local/lib
8-. Add the next line in /etc/httpd/conf/httpd.conf
AddType application/x-httpd-php .php

TESTING

<html>
<body>
<?php
$con = mssql_connect ("<ip of the server SQL>", "sa", "");
mssql_select_db ("<Data Base>", $con);
$sql= "SELECT * FROM <Table>";
$rs= mssql_query ($sql, $con);
echo "The field number one is: ";
echo mssql_result ($rs, 0, 0);
mssql_close ($con);
?>
</body>
</html>