Koozali.org: home of the SME Server

Connecting to MS-SQL Server 2000 from SME 7.4

Offline mad-mooo

  • 9
  • +0/-0
Connecting to MS-SQL Server 2000 from SME 7.4
« on: June 02, 2009, 12:34:59 AM »
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
« Last Edit: June 02, 2009, 09:37:10 AM by mad-mooo »
All's well that ends

Offline cactus

  • *
  • 4,880
  • +3/-0
    • http://www.snetram.nl
Re: Connecting to MS-SQL Server 2000 from SME 7.4
« Reply #1 on: June 02, 2009, 09:01:18 AM »
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.
Be careful whose advice you buy, but be patient with those who supply it. Advice is a form of nostalgia, dispensing it is a way of fishing the past from the disposal, wiping it off, painting over the ugly parts and recycling it for more than its worth ~ Baz Luhrmann - Everybody's Free (To Wear Sunscreen)

Offline mad-mooo

  • 9
  • +0/-0
Re: Connecting to MS-SQL Server 2000 from SME 7.4
« Reply #2 on: June 02, 2009, 09:33:08 AM »
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.
All's well that ends

Offline cactus

  • *
  • 4,880
  • +3/-0
    • http://www.snetram.nl
Re: Connecting to MS-SQL Server 2000 from SME 7.4
« Reply #3 on: June 02, 2009, 10:40:06 AM »
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.
Be careful whose advice you buy, but be patient with those who supply it. Advice is a form of nostalgia, dispensing it is a way of fishing the past from the disposal, wiping it off, painting over the ugly parts and recycling it for more than its worth ~ Baz Luhrmann - Everybody's Free (To Wear Sunscreen)

Offline Stefano

  • *
  • 10,836
  • +2/-0
Re: Connecting to MS-SQL Server 2000 from SME 7.4
« Reply #4 on: June 03, 2009, 08:50:59 AM »
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

Offline mad-mooo

  • 9
  • +0/-0
Re: Connecting to MS-SQL Server 2000 from SME 7.4
« Reply #5 on: June 03, 2009, 09:51:07 AM »
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.
All's well that ends

Offline mad-mooo

  • 9
  • +0/-0
Re: Connecting to MS-SQL Server 2000 from SME 7.4
« Reply #6 on: June 11, 2009, 03:39:40 PM »
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.

All's well that ends

Offline Per

  • *
  • 63
  • +0/-0
    • Bends - better on the internet than in reality
Re: Connecting to MS-SQL Server 2000 from SME 7.4
« Reply #7 on: June 13, 2009, 02:33:35 PM »
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
NO! I refuse to put some clever latin sentence here!

Offline Confucius

  • ****
  • 235
  • +0/-0
Re: Connecting to MS-SQL Server 2000 from SME 7.4
« Reply #8 on: June 16, 2009, 07:19:58 PM »
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

Offline cactus

  • *
  • 4,880
  • +3/-0
    • http://www.snetram.nl
Re: Connecting to MS-SQL Server 2000 from SME 7.4
« Reply #9 on: June 16, 2009, 07:38:59 PM »
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
Be careful whose advice you buy, but be patient with those who supply it. Advice is a form of nostalgia, dispensing it is a way of fishing the past from the disposal, wiping it off, painting over the ugly parts and recycling it for more than its worth ~ Baz Luhrmann - Everybody's Free (To Wear Sunscreen)

Offline mad-mooo

  • 9
  • +0/-0
Re: Connecting to MS-SQL Server 2000 from SME 7.4
« Reply #10 on: June 16, 2009, 08:44:01 PM »
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 :roll:

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.
All's well that ends

Offline Confucius

  • ****
  • 235
  • +0/-0
Re: Connecting to MS-SQL Server 2000 from SME 7.4
« Reply #11 on: June 16, 2009, 11:50:46 PM »
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.
« Last Edit: June 17, 2009, 12:02:40 AM by Confucius »

Offline Per

  • *
  • 63
  • +0/-0
    • Bends - better on the internet than in reality
Re: Connecting to MS-SQL Server 2000 from SME 7.4
« Reply #12 on: June 17, 2009, 08:30:40 AM »
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):
Quote
[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.
NO! I refuse to put some clever latin sentence here!

Offline mad-mooo

  • 9
  • +0/-0
Re: Connecting to MS-SQL Server 2000 from SME 7.4
« Reply #13 on: June 17, 2009, 09:08:01 AM »
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.
All's well that ends

Offline Per

  • *
  • 63
  • +0/-0
    • Bends - better on the internet than in reality
Re: Connecting to MS-SQL Server 2000 from SME 7.4
« Reply #14 on: June 17, 2009, 09:35:58 AM »
Ah! Thanks. I do a "Revert to snapshot" and then try again.
Good luck with your new engine!
NO! I refuse to put some clever latin sentence here!