[Duckington]

I think i'm confusing myself at the moment, so i would like to just go back to the beginning and start again..

Basically, I would like to be able to connect to a remote Microsoft SQL server from PHP (5.3).

Now historically I've always done this by installing the odbc extension, and it worked fine, however that was always done on WAMP which was easy - select it from list, restart apache, pretty much done....and I'm now trying to do it using Linux (Redhat) and have failed miserably. So next I decided to forget those extensions and use PDO instead, but i'm somewhat unsure of:

a) What drivers I need to install, as the PHP manual lists MS SQL Server twice (dblib & sqlsrv), as well as ODBC.

Actually...how to download & install them on linux. I've previously been doing things like "yum install php-odbc" when I was messing around with that, but as far as I can see the PHP manual only talks about how to do it on Windows, not linux.

I've already done "yum install php-pdo" and get the latest version, however I only seem to have: mysql, odbc, sqlite as my available PDO drivers.


Is there some kind of basic tutorial/example on how to set this up? I realise this is fairly beginner stuff, but i've only just recently started using Linux at all really, having always developed on Windows and it's confusing me.


Thanks.

This post has been edited by Duckington: 29 August 2012 - 06:26 AM

[RudiVisser]

You use ODBC, MS have a native client available to download here.

Install that on your server platform, and you should be able to use the ODBC PDO driver with this SQL Server Native Client as your ODBC driver. Full instructions are included on that download page of how to install it onto the server, so it shouldn't prove too difficult

This post has been edited by RudiVisser: 29 August 2012 - 06:39 AM

[Duckington]

Hi, thanks.

I've tried that and it got as far as installing unixODBC again, however then it's failed saying it can only be installed on a 64-bit Linux operating system, and I believe ours is 32 bit.

is there another download link for the 32 bit version? I can only see 64 bit on there and my search in their downloads site hasn't been veyr fruitful.

Cheers.

[RudiVisser]

Quote

Is the driver 64 bit or 32 bit?
The first version of the driver is a 64 bit. A 32-bit driver may be available in a future release.

Unfortunately not, at least not now.

I haven't seen a 32bit server in about 10,000 years, in fact I haven't seen a 32bit system of any kind in about that amount of time also! I think you need to up your servers unless there's some serious compatibility issue with that

[Duckington]

Nothing I can do about it unfortunately, this is just what the other guys setup, and it's already got a load of stuff on so they're not going to want to do it again with only a few days left until everything needs to be ready.

I have made a little bit of progress, continuing back along the lines of what I was doing here:

http://www.dreaminco...-name-too-long/

In that I've managed to install FreeTDS (had to install another repoistory) and I've got ti setup so it's sort of trying to connect now, but i'm getting "Adaptive Server is unavailable or does not exist"... so now got to work out what that issue is... Only thing I can think of at the moment is it can't use the port i've specified... But i could be very wrong..

[RudiVisser]

Sorry, I forgot to suggest FreeTDS.

What version of FreeTDS are you using, as some of them only support certain database servers?

Also, what are your connection parameters? I assume you're testing this from the command line and not PHP at the moment like in your other post?

[Duckington]

I think it's version 4.2 according to the freetds.conf file.

Yes i'm still in command line atm, this is what i've got in the various files:

freetds.conf

#   $Id: freetds.conf,v 1.12 2007/12/25 06:02:36 jklowden Exp $
#
# This file is installed by FreeTDS if no file by the same 
# name is found in the installation directory.  
#
# For information about the layout of this file and its settings, 
# see the freetds.conf manpage "man freetds.conf".  

# Global settings are overridden by those in a database
# server specific section
[global]
        # TDS protocol version
;	tds version = 4.2

	# Whether to write a TDSDUMP file for diagnostic purposes
	# (setting this to /tmp is insecure on a multi-user system)
;	dump file = /tmp/freetds.log
;	debug flags = 0xffff

	# Command and connection timeouts
;	timeout = 10
;	connect timeout = 10
	
	# If you get out-of-memory errors, it may mean that your client
	# is trying to allocate a huge buffer for a TEXT field.  
	# Try setting 'text size' to a more reasonable limit 
	text size = 64512

# A typical Sybase server
[egServer50]
	host = symachine.domain.com
	port = 5000
	tds version = 5.0

# A typical Microsoft server
[egServer70]
	host = ntmachine.domain.com
	port = 1433
	tds version = 7.0


[bksb-connector]
  host = 10.1.6.65\bksbportal
  port = 1433
  tds version = 8.0

odbcinst.ini

[FreeTDS]
Description = ODBC For MS SQL
Driver = /usr/lib/libtdsodbc.so.0
UsageCount = 1
Threading = 2

odbc.ini

[bksb-connector]
Description = MS SQL Connection to BKSB Database
Driver = FreeTDS
Database = ****
Server = 10.1.6.65\bksbportal
UserName = ****
Password = ****
Trace = No
TDS_Version = 8.0
Port = 1433

And i'm testing it by doing: echo "select 1" | isql -v bksb-connector


Now I have checked to see if the MS server is listening on port 1433 by using telnet on my windows machine:

telnet > 10.1.6.65 1433
telnet > 10.1.6.65\bksbportal 1433
etc...

And it always says that it can't connect... So that leads me to believe (and hope) that it's just a port issue and nothing else...however that raises additional problems, as the guy who set up the MS server is not the...easiest to get a straight answer out of.

When connecting to it in php before when we used mssql_* we never needed to specify a port in the connection string/function, so haven't a clue what it is.

Is there an easy way to find out what port the server uses? I do have remote access to the server, so I can login to it if that helps? Or I suppose I could go and attempt to ask the guy.....

[RudiVisser]

You would go onto the server with SQL Server Configuration Manager, and take a look at the TCP Ports that it allows, if any.

If none are specified (or simply the whole TCP/IP network is disabled) then you can simply enable it, fix up the firewall, and you should be good to go.

Telnet to that port would always be the first port of call before using something else though, remote connectivity is possibly the most important thing in a scenario like this

[Duckington]

Finally managed to get it working, here are the instructions I wrote down, in case it helps anyone else in the future:

Quote

Connecting Redhat Linux Server, running PHP 5.3 to Microsoft SQL Server 2005


Issues:

- PHP 5.3 doesn't support the mssql functions, so can't use them

- We were using a 32 bit server, which doesn't support the drivers required for PDO ODBC


So we have to use standard odbc_* functions.


Helpful Resources

http://www.asteriskd...uring_odbc.html

http://developer.min...nstall_unixODBC

http://dev.mysql.com...n-dsn-unix.html



Step One

Install unixODBC development package - yum install unixODBC



Step Two

Install the odbc mysql connector (this is for MySQL obviously, but I found it better to test that first and get it working on local MySQL before moving onto remote server)

yum install mysql-connector-odbc


If those two successfully installed, we should now be able to run odbcinst -j to see a list of configuration files, which should look something like:

odbc_ss1.png


Step Three

Install FreeTDS

Now doing a yum install or a yum search for this on Redhat found nothing, so I had to install an extra repoistory, the "EPEL (Extra Packages for Enterprise Linux) repoistory.

To do this I found a copy of the repository by going to here:

https://fedoraproject.org/wiki/EPEL

Then scroll down to "How can I use these extra packages" and selecting EL6 and copying the link to the rpm file, which was:

http://mirrors.corei...-6-7.noarch.rpm

To actually install the repository, I used that URL and ran: rpm -Uhv http://mirrors.corei...-6-7.noarch.rpm

And that installed the repo. I was then able to check it was installed by running cd /etc/yum.repos.d and it was listed there.

So now that the repository is installed, freeTDS can be installed by running yum install freetds



Step Four

Before moving onto the MS SQL connection, I found it best to make sure I could get it to work on the local MySQL connection first, so we're going to need to edit some of those config files now.



In the drivers file (/etc/odbcinst.ini), add in a MySQL driver with the following info:

[MySQL]

Description = ODBC For MySQL

Driver = /usr/lib/libmyodbc5.so

Setup = /usr/lib/libodbcmyS.so

FileUsage = 1

Check that those library files actually exist and that you're using version 5, as you may be using different version.



Next edit the /etc/odbc.ini file and add this at the top:

[ODBC Data Sources]

local = Local MySQL Connection

Followed by:

 
[local]

Description = MySQL Connection

Driver = MySQL

Database = YOUR_DB_NAME_HERE

Server = localhost

Username = root

Password = YOUR_ROOT_PW_HERE

Port = 3306

Socket = /var/lib/mysql/mysql.sock

I had issues getting it to work with any user other than root, so had to use root.

The "Driver" value is the [Name] we specified for our MySQL connection in the Drivers config file (odbcinst.ini)



Now let's test that connection, we can do this by running isql -v local, where "local" is the [name] we defined in the odbc.ini file

If it's all installed & setup properly that should connect us and we can then run normal SQL statements.



Step Five

If that worked, then we can move onto the MS SQL connection.

Add another driver to the odbcinst.ini file:

[FreeTDS]

Description = ODBC For MS SQL

Driver = /usr/lib/libtdsodbc.so

Setup = /usr/lib/libtdsS.so

UsageCount = 1

Threading = 2

Should note that the options like UsageCount and Threading... I'm not really sure what they're for and haven't look them up, but this is what worked for me.


Now let's add our connection to the odbc.ini file. I called mine "bksb" because I was trying to connect to the BKSB software on the MS SQL server, you can call it whatever you like as far as I am aware, though i'd avoid spaces in the name.

[bksb]

Description = MS SQL Connection to BKSB Database

Driver = FreeTDS

Database = YOUR_DB_HERE

Server = YOUR_SERVER_HERE

UID = DB_USER_HERE

PWD = DB_USER_PW_HERE

Trace = No

TDS_Version = 8.0

Port = PORT_HERE

Next edit the /etc/freetds.conf file:

[bksb]

host = YOUR_SERVER_HERE

port = PORT_HERE

tds version = 8.0

Note: The default port for MS SQL Server is 1433 I believe, but our BKSB server was running on a different one. This was difficult to find out, in the end I had to remote into the server and go to:

Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Configuration Manager -> "Network Configuration" -> "TCP/IP" -> Properties -> IP Addresses

And the port number was at the bottom under "TCP Dynamic Ports"

Whether this means it will change and mess things up, i'm not sure. If so, will need to get a static port set i would assume.



Now we can test this connection as well. Now I couldn't get it to work using just the data source, I had to specify the user & password in the command as well:

isql -v bksb username password

And if it has worked properly, it should connect you like before.

If not, you can test the freeTDS connection by doing:

tsql -S server -U username

I had to specifcy the port when I did this, e.g. tsql -S 1.2.3.4:1433 -U myuser

If that works, you should get "1>" in the terminal, you can then enter a query to run, e.g.

1> SELECT * FROM sysobjects WHERE type='u'
2>GO


Step Six

The last step was just to get it working in PHP. You use odbc_connect() with the data source name as the first parameter, then username and password, eg.

$conn = odbc_connect('bksb', '***', '***');

Alternatively, I also managed to get PDO working with ODBC, for instance:

$db = new PDO("odbc:bksb", "***", "***");


So in theory I think that should work if someone has folllowed these instructions as well, as I don't recall installing anything else in particular for PDO to work, other than obviously just yum install php-pdo in general.

Attached image(s)

• 

This post has been edited by Duckington: 30 August 2012 - 05:35 AM

[Duckington]

Okay forget that, the PDO doesn't really work. I can connect, but it doesn't work properly. If I specify the wrong connection details it ignores the try catch and gives "Connection closed by remote server", same goes for any attempt to execute a statement.

[Duckington]

....And cancel the whole thing, even the odbc_* methods aren't working properly.

I can connect, but then when I attempt to use odbc_execute on a prepared statement, all I get is:

SQL error: Failed to fetch error message, SQL state HY000 in SQLExecute

E.g.

$db = odbc_connect('bksb', '**', '**') or die('connection error: ' . odbc_error()); 
$st = odbc_prepare($db, "select top 1 * from bksb_Sessions where userName = ?") or die('prepared error: ' . odbc_error());
$result = odbc_execute($st, array('myname')) or die('execute error:' . odbc_errormsg());

Quote

Warning: odbc_execute() [<a href='function.odbc-execute'>function.odbc-execute</a>]: SQL error: Failed to fetch error message, SQL state HY000 in SQLExecute in /var/www/html/moodle/test.php on line 7

execute error:Failed to fetch error message

Ugh....