Topic: Connecting to MS-SQL Server 2000 from SME 7.4

[mad-mooo]

My objective is to connect to an SQL Server DB on the LAN from the SME Box.
The web site runs php on the SME and needs to connect using (I would assume) mssql_connect.

On ye olde posts, I've seen suggestions that MySql be used, however this is not an option due to other dependencies.

So far, I've enabled the unixODBC packages available through server-manager.
FreeTDS appears, from my google trawling, to be the missing link.

At this point I run out of direction.
 Which RPM / tgz should I be using for CentOS?
 which version to use, 6.4 or 8.x?
 How to make the templates etc (never did understand that bit)

Once I've managed (with help of course) to resolve, I'd like to produce a step by step 'How to' for future reference and
other desperate but determined dilettantes like myself.

Cheers & Beers Hamish

[cactus]

A good source for finding RPMs is http://rpm.pbone.net/ , unfortunately it does not list a RPM for freetds for SME Server (CentOS/RHEL), so you will have to compile it yourself from scratch.

As it is not adviced to compile on your SME Server as you need to install some applications that are harmful o the security of your system you should condigure a CentOS 4.x box (for SME Server 7.x) and compile the drivers there. Then install them on SME Server.

Another thing you could consider is running a Windows server (either on a seperate server or on VMWare) that will host the application for you, it might save you the fuss of trying to get SME Server ready for MSSQL odbc connection.

[mad-mooo]

Cactus

I've tried building the stable version on the SME box (From link below) and as you suggest, it requires additional tools to do it.
I'll look at the option of building an RPM on a separate CentOS box.
ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz

The SQL Server is already running on a separate Win2K server.
I assume the application you referred would be the php/Apache Web pages on the SME box. The prospect of using an MS platform to serve web pages is not a joyfull one!

I appreciate your input.

[cactus]

I've tried building the stable version on the SME box (From link below) and as you suggest, it requires additional tools to do it.

Even if you sue CentOS you might need to install additional packages. Most systems do not install development packages and libraries by default.

The prospect of using an MS platform to serve web pages is not a joyfull one!

I can imagine that...

I appreciate your input.

You're welcome.

[Stefano]

My objective is to connect to an SQL Server DB on the LAN from the SME Box.
The web site runs php on the SME and needs to connect using (I would assume) mssql_connect.

hi.. read here.. it's in italian, b ut you can translate with google..

HTH
Ciao
Stefano

[mad-mooo]

Hi Stefano

I'm currently trying to work from the same post, translated by google as suggested.
Having followed to the letter, no joy, php does not recognise the mssql functions.

Starting again, I have installed the freetds RPM and set up the templates for freetds.conf and odbcinst.ini
I have yet to establish a connection to the MS-SQL Server through the freetds driver using a DNS-less configuration using the tsql utility.

The tsql command can see the MS server but returns 'There was a problem connecting to the server'
My immediate task is to find a way of isolating the problem. http://www.freetds.org/userguide/confirminstall.htm

Suggestions and links to relevent resources all very welcome.
Thanks for you input.

[mad-mooo]

Update - Still working on this one.

tsql will connect to and query MS box when either ip address is used instead of server name or server MS box is added to /etc/hosts.

No sucsess with isql however.
here are the configuration files:

# cat freetds.conf
[global]
        # TDS protocol version
        tds version = 4.2

[mypool]
        host = 127.0.0.1
        port = 5000
        tds version = 4.2

# Microsoft SQL Server 2000 configuration
[MSSQL01]
        host = drogo1
        port = 1433
        tds version = 8.0

# cat odbcinst.ini
[FreeTDS]
    Description = FreeTDS Driver (MS-SQL access)
    Driver      = /usr/lib/libtdsodbc.so

# cat odbc.ini
[ODBC Data Sources]
    MSSQL01     = Connection to the JESE DB

[MSSQL01]
    Driver      = /usr/lib/libtdsodbc.so
    Description = Connection to the JESE DB
    Trace       = No
    Server      = 192.168.2.10
    Database    = JESE
    Port        = 1433
    TDS_Version = 8.0

[Default]
    Driver      = /usr/lib/libtdsodbc.so

# odbcinst -j
unixODBC 2.2.11
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
USER DATA SOURCES..: /root/.odbc.ini

# odbcinst -q -d
[FreeTDS]

# odbcinst -q -s
[MSSQL01]
[Default]
#


when i run isql, this is what happens:
# isql -v MSSQL01 sa 12345678
[IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified
[ISQL]ERROR: Could not SQLConnect
#

using strace, i get the following:
# strace -e connect isql  MSSQL01 sa 12345678
connect(3, {sa_family=AF_FILE, path="/var/run/nscd/socket"}, 110) = -1 ENOENT (No such file or directory)
connect(3, {sa_family=AF_FILE, path="/var/run/nscd/socket"}, 110) = -1 ENOENT (No such file or directory)
[ISQL]ERROR: Could not SQLConnect


On investigation, I determine that the directory nscd is not present in /var/run.
I add the directories nscd/socket to /var/run/ and re-run the strace:

# strace -e connect isql MSSQL01 sa 12345678
connect(3, {sa_family=AF_FILE, path="/var/run/nscd/socket"}, 110) = -1 ECONNREFUSED (Connection refused)
connect(3, {sa_family=AF_FILE, path="/var/run/nscd/socket"}, 110) = -1 ECONNREFUSED (Connection refused)
[ISQL]ERROR: Could not SQLConnect
#

I'm looking for suggestions that may help to narrow the problem domain.....
All input most welcome.

[Per]

Hi.
I'm following this post closely as I'm trying pretty much the same thing.
As I'm having a test server in server-only-mode I took the liberty to compile freetds directly on the server.
But then I'm stuck. I can't connect properly by tsql and PHP says that it don't have mssql_connect function.
I have added the line extension=php_mssql.dll in php.ini (by custom template).
I've googled around but no answers (simple enough for me to understand).
If any help, I have my temporary notes here: http://bends.se/cms_mek/index.php?PHP:MsSQL:FreeTDS
/Per

[Confucius]

Saw this 2 days ago, was interested and got stuck at 1 point. Worked on it a bit more today and I think the answer is pretty simple.

using sa to log in gave me trouble, I discovered that in MS SQL 2005 (9.0) the sa account had no access from outside, this could be a default setting in the security from MS SQL 2005. I decided to leave this setting as it is (safety 1st) and created a new account in MS SQL. Today I had no trouble connecting anymore using isql (as others mentioned, tsql was working from the start). After some fiddling in the rights on a harmless database I was able to do queries and I'm moving on to the next step.

Allthough I don't speak italian, the guide given by Stefano was simple enough to make all work. Good luck.

Harro

[cactus]

using sa to log in gave me trouble, I discovered that in MS SQL 2005 (9.0) the sa account had no access from outside, this could be a default setting in the security from MS SQL 2005.

Yes, and that is what you should keep it indeed. This is the super admin account and should not be used for normal database use, you should indeed create different users for that with the proper privileges on the proper entities.

Here is some more information on the sa account: http://www.sql-server-performance.com/faq/system_administrator_role_p1.aspx

[mad-mooo]

sa Yes, fair point for working solution.
I've added a new account logon to the MS server and the problem remains the same.

Regarding the Post from Per, I've compiled the v0.82 and got tsql working.
In order for it to work:
unixODBC need be installed first
./configure options are required. I used --sysconfdir=/ect --with-tdsver=8.0
tsql -C should report unixODBC : Yes

having got tsql to work, I ran in to the same problem.
using osql, the following was reported:
error: no potential directory strings in "/usr/bin/isql"
+ /lib/ld-linux.so.2

I'd apreciate an clues to help expand on the above.


I've ordered and am waiting on a new machine to experiment with.
The one I've been testing with and just locked myself out of is a 300Mhz machine zzzzzzzzz

As I'd rather not risk killing the live box, I'll wait for the new one before I start over.
Please keep the sugestions coming.

[Confucius]

I've added a new account logon to the MS server and the problem remains the same.

Just to make sure.... You created a Windows account or a MS-SQL account ? The MS-SQL account is what you need and there is no need to create a Windows account since you're directly accessing the SQL server/service and windows is only hosting SQL.

Harro

PS: based on your earlier messages it might be usefull to uninstall all involved modules and install them again. I had problems aswell because unixODBC was needed by an earlier zabbix installation and caused me trouble at 1st till I re-installed all modules for this process.

[Per]

Ah! unixODBC was needed, I overlooked that in your first post.
So I installed unixODBC.i386 0:2.2.11-1.RHEL4.1 via server-manager.
Installed and then configured freeTDS with:
./configure --sysconfdir=/etc --with-tdsver=8.0
But something with unixODBC seem to have failed (last line):

[root@server22 freetds-0.82]# tsql -C
Compile-time settings (established with the "configure" script)
                            Version: freetds v0.82
             freetds.conf directory: /etc
     MS db-lib source compatibility: no
        Sybase binary compatibility: no
                      Thread safety: yes
                      iconv library: yes
                        TDS version: 8.0
                              iODBC: no
                           unixodbc: no

Any suggestions?
This is building up to a good howto in the wiki when we have sorted things out a bit.

[mad-mooo]

Confucius, thanks.
 I confirm, it was a SQL account, not a windoze domain account.
 In any case,  I'll be starting from scratch on the new hardware.

Per
Something I may have omitted to mention... Ooops
I installed unixODBC as well as unixODBC-devel, but not unixODBC-kde.
Obviously, this then requires freetds.conf to be placed in /etc
I would still expect tsql to work though.
 If you have not already tried: tsql -S xxx.xxx.xxx.xxx -U account -P password
 where xxx is the ip address of your MS-SQL server box, account is the SQL server log on.
 If the above does not work, what is being reported?

The above got tsql working for me but not osql or isql.
Other ./configure options may also be required, such as --prefix=/usr.
Was not able to verify test this before I killed the old test box.
I'm hopeful that the new hardware will arrive today which will speed up testing.
As ever, I'll post updates as I discover more.

[Per]

Ah! Thanks. I do a "Revert to snapshot" and then try again.
Good luck with your new engine!

[cactus]

As ever, I'll post updates as I discover more.

That would be very nice. I think many people will value a step-by-step howto in the wiki as well if you could spare the time once you have found out how to do it. Thanks in advance.

[Per]

Are there a reason not to keep the default dir /usr/local ?
In /etc are a lot of other stuff (of course).

[Per]

I finally got connected by tsql to my SQL Server (Express edition).
The final key was to add the line

instance = db_kalle

in freetds.conf, where db_kalle is the name of the instance of SQL Server you are trying to connect to.
Before that the SQL Server must be configured to allow outside connections (disabled by default in Express edition) alongside with a user with enough permissions.
And now it's time to start wrestling with php support...

PS. Does anybody know where David Crittens howto (from 2007 I think) can be found now? DS

[mad-mooo]

The big break through
The answer is always too simple eh! In all my trawling for clues, I never found anything about tabs in the formatting of the odbc.ini
I use mcedit as my preferred editor on the SME box. As per all the examples, i typed the entries out

[TestDB]
<Tab> Driver =<Tab>/usr/lib/libtdsodbc.so


Formatting the file thus will cause odbcinst -q -s to respond correctly. However, this will upset isql!
Remove the leading <Tab>s ( In line Tabs don't cause a problem) and suddenly isql comes to life

With the above caveat, The "How to" written by Nick Critten and the one posted on http://www.calio.it/docs/doku.php/sqlserver should be good.

So where from here.
Per has started writing up a "How to"
Once I've verified the php bit, I intend to roll an RPM for FreeTDS-0.82 to remove the requirement for installing gcc and make yum friendly
If anyone has already done so, I'd bee keen for any input.

More Updates soon.

[Per]

GREAT!!

Yup. The wiki HowTo are located here: http://wiki.contribs.org/ODBC
and I've added your tip about spaces.

Feel free everyone to contribute to the HowTo. 

[Per]

Hmm. tsql works fine. isql does not. Tabs are removed. Any clues?

Solved: Sorting out FQDN and DSN fixed it, howto updated accordingly.

[mad-mooo]

Latest Update

Freetds V0.82
Using the ./configure options
 --prefix=/usr
 --with-tdsver=8.0
 --sysconfdir=/etc
 --enable-msdblib

freetds.conf - not implimented
odbcinst.ini  - not implimented
odbc.ini as follows:
[TestDB]
Driver          = /usr/lib/libtdsodbc.so
Description   = Test Database Connection
Trace          = No
Server         = 192.168.x.x
Port            = 1433
TDS_Version = 8.0
_____________________________________________
tsql works
isql works

php almost works.....

$TestDB = odbc_connect('TestDB', 'TestUsr', 'Secret');  // Good this far
if(!isset($TestDB)) die("Database connection failed");
$Query = 'select * from TABLE1';
$RecSet = odbc_exec($TestDB, $Query);                    // This is where it goes pear shaped.

From Message log
Jun 19 14:51:57 test1 httpd: PHP Warning:  odbc_exec(): SQL error: àZ   xð   8rú¿�Xú, SQL state 00000 in SQLExecDirect in /home/e-smith/files/ibays/tests/html/ticket.php on line 54

I built up separate SME box in parallel and installed FreeTDS 0.63. The above problem did not occur, all ran as smooth as a baby's b...
Installing FreeTDS 0.64 over the FreeTDS 0.82 installation clears the problem (not very scientific I know)

At this time I conclude that one of two possibilities are likely.
There is a bug in FreeTDS 0.82
I've not configured FreeTDS 0.82 correctly for it to work with PHP

Having spent 15+ days working on a the problem and arrived at a repeatable + workable solution (using v0.63),
I will return to testing when the next stable version of FreeTDS is released or when anyone can suggest
anything that stands a reasonable chance of progress with v0.82.

I'll continue to liaise with Per, who is writing up a new 'How To'

[Per]

I'll try your setup today to verify the problem and then I update the howto accordingly.
Do you remove freetds.conf and odbcinst.ini or you just ignore them?

Is it possible to reverse engineer the precompiled 0.63/0.64-version to find out used settings?

I'ts probably not neccesary to add --enable-msdblib as that is a default according to docs: "As of version 0.63, this option specifies just the default behavior".

If the problem persists, should we post something on the FreeTDS mailing list?

[Per]

I've successfully connected to SQL Server 2005, both regular and express, using the current HowTo at: http://wiki.contribs.org/ODBC.

Hence I removed the 'Under construction' -warning 

But it's neither complete or fully tested yet so, please folks, do feel free to correct, edit and stuff to make it better!

[Per]

mad-mooo

having got tsql to work, I ran in to the same problem.
using osql, the following was reported:
error: no potential directory strings in "/usr/bin/isql"
+ /lib/ld-linux.so.2

Just added instructions on how to troubleshoot with osql in the HowTo, but when I tested myself (on my working connection) I also got the same response as you.

I found som info here: http://lists.ibiblio.org/pipermail/freetds/2008q3/023579.html

When I do:

[root@server22 apps]# strace -e connect isql -v remoteserver dbuser dbpass
connect(3, {sa_family=AF_FILE, path="/var/run/nscd/socket"}, 110) = -1 ENOENT (No such file or directory)
connect(3, {sa_family=AF_FILE, path="/var/run/nscd/socket"}, 110) = -1 ENOENT (No such file or directory)
connect(3, {sa_family=AF_FILE, path="/var/run/nscd/socket"}, 110) = -1 ENOENT (No such file or directory)
connect(3, {sa_family=AF_FILE, path="/var/run/nscd/socket"}, 110) = -1 ENOENT (No such file or directory)
connect(3, {sa_family=AF_INET, sin_port=htons(1433), sin_addr=inet_addr("192.168.0.112")}, 16) = -1 EINPROGRESS (Operation now in progress)
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

To me it seems to work despite the error message recived with the osql script.

Should I bother with further investigation or simply accept the world as not perfect?

[mad-mooo]

Per

I think it may be fair to mention osql as tool available for testing the connection but with some considerations:
To my knowledge osql was introduced from v0.63. When I've installed 0.6x RPMs, osql has not been present or worked.
Did you get osql to work with v0.6x?

Running osql through strace does appear to show attempts to look for files, assumedly to make a remote socket / connection.
In my case, this turned out to be a red herring, the real and repeatable problem was found to be caused by the leading TABs in the odbc.ini file.

Form my experience, a strong trouble shooting section is to be reccommended but strace my be on the side of mission creep.
I would encourage readers of this forum to add details of any problems found together with their solutions to add to the troubleshooting section.

I still have to find out what is preventing odbc_exec working with v0.82 and publish the problem. I've read of others having the same problem but not yet learnt what was causing their problem.

Additional comment or corrections always welcome.

[Per]

Yes I've added that to the HowTo. I've also posted on the FreeTDS mailing list to find out more.

To be continued.