Difference between revisions of "ODBC"
m (Added tip) |
m (Minor text changs) |
||
(2 intermediate revisions by the same user not shown) | |||
Line 4: | Line 4: | ||
Adding support in SME Server for a universal connection to foreign datebase servers using Open Database Connectivity (ODBC) with focus on PHP. | Adding support in SME Server for a universal connection to foreign datebase servers using Open Database Connectivity (ODBC) with focus on PHP. | ||
− | We will assume that you have an | + | We will assume that you have an Microsoft SQL Server 2000/2005 running somewhere. The SQL Server should allow remote access and support connections using TCP/IP. As of my knowledge, connections using named pipes are unsupported. Of course; you will want a login id and password. |
It should be similar with Sybase and other versions of SQL Server, consult FreeTDS and unixODBC documentation for details. | It should be similar with Sybase and other versions of SQL Server, consult FreeTDS and unixODBC documentation for details. | ||
Line 52: | Line 52: | ||
wget http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-0.82.tar.gz | wget http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-0.82.tar.gz | ||
− | |||
gzip -d freetds-0.82.tar.gz | gzip -d freetds-0.82.tar.gz | ||
− | |||
tar -xvf freetds-0.82.tar | tar -xvf freetds-0.82.tar | ||
− | |||
cd freetds-0.82 | cd freetds-0.82 | ||
− | |||
./configure --sysconfdir=/etc --prefix=/usr --with-tdsver=8.0 | ./configure --sysconfdir=/etc --prefix=/usr --with-tdsver=8.0 | ||
− | |||
make | make | ||
− | |||
make install | make install | ||
− | |||
make clean | make clean | ||
− | |||
signal-event post-upgrade; signal-event reboot | signal-event post-upgrade; signal-event reboot | ||
− | |||
{{Warning box|msg=Leaving gcc on a production server is considered a security risk, if you do not really need it you should not install it, if you need it you should seriously consider uninstalling gcc when you no longer need it. Removing gcc can be done using the following command: | {{Warning box|msg=Leaving gcc on a production server is considered a security risk, if you do not really need it you should not install it, if you need it you should seriously consider uninstalling gcc when you no longer need it. Removing gcc can be done using the following command: | ||
yum remove gcc}} | yum remove gcc}} | ||
Line 140: | Line 131: | ||
[FreeTDS] | [FreeTDS] | ||
− | Description = Access | + | Description = Access Microsoft SQL Server with FreeTDS driver |
Driver = /usr/lib/libtdsodbc.so | Driver = /usr/lib/libtdsodbc.so | ||
Line 252: | Line 243: | ||
On some systems (RISC) the drivers in /usr/local/lib/ may have the extension .sl rather than .so for shared libraries. | On some systems (RISC) the drivers in /usr/local/lib/ may have the extension .sl rather than .so for shared libraries. | ||
− | ==== | + | ====Microsoft SQL Server==== |
If your SQL Server are using instances, replace <port = 1433> with <instance = MyInstanceName> in your freetds.conf template fragment. Maybe adding the instance name after host in freetds.conf like this: <Server = xp3c\MyInstanceName> would aid too. | If your SQL Server are using instances, replace <port = 1433> with <instance = MyInstanceName> in your freetds.conf template fragment. Maybe adding the instance name after host in freetds.conf like this: <Server = xp3c\MyInstanceName> would aid too. | ||
From SQL Server 2000 TDS version 8.0 should be used. Add the line <tds version = 8.0> in your template fragment for freetds.conf | From SQL Server 2000 TDS version 8.0 should be used. Add the line <tds version = 8.0> in your template fragment for freetds.conf | ||
− | + | Microsoft SQL Server might not allow the db system user "sa" to connect from outside the server, create another account with proper permissions. | |
Express edition have remote access disabled by default. Can be changed in the menu "Surface Area Configuration". [http://support.microsoft.com/kb/914277 More info here] | Express edition have remote access disabled by default. Can be changed in the menu "Surface Area Configuration". [http://support.microsoft.com/kb/914277 More info here] |
Latest revision as of 08:01, 27 June 2009
Description
Adding support in SME Server for a universal connection to foreign datebase servers using Open Database Connectivity (ODBC) with focus on PHP.
We will assume that you have an Microsoft SQL Server 2000/2005 running somewhere. The SQL Server should allow remote access and support connections using TCP/IP. As of my knowledge, connections using named pipes are unsupported. Of course; you will want a login id and password.
It should be similar with Sybase and other versions of SQL Server, consult FreeTDS and unixODBC documentation for details.
Used packages
unixODBC - is a driver manager with built-in support for many different databases. unixODBC official site
unixODBC-devel
php-odbc - is simply an extension that allows you to use unixODBC to connect to database servers using PHP.
FreeTDS - adding drivers for Sybase and Microsoft using TDS protocol. FreeTDS official site
Optional: GCC - The GNU Compiler Collection with libraries. GCC official site
Installation
Install basic packages in server-manager, instructions here:
unixODBC.i386
unixODBC-devel.i386
php-odbc.i386
gcc.i386 (only needed if you are going to install from source)
Alt 1: Install FreeTDS precompiled
It save some work and you don't need gcc but usually contain an older version. Testing suggests it still works for SQL Server 2005.
Install FreeTDS from Dag repository, (you must add Dag repo first, instructions here):
yum --enablerepo=dag install freetds freetds-devel
If Dag repo does not work or you want another rpm package, this will do, modify path as needed:
wget http://dag.wieers.com/rpm/packages/freetds/freetds-0.64-1.el4.rf.i386.rpm
wget http://dag.wieers.com/rpm/packages/freetds/freetds-devel-0.64-1.el4.rf.i386.rpm
yum localinstall freetds-*.rpm
In any case, reconfigure the server afterwards:
signal-event post-upgrade; signal-event reboot
Alt 2: Install FreeTDS from source
This will give you full control and the latest version (or any version, modify path as you like).
wget http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-0.82.tar.gz gzip -d freetds-0.82.tar.gz tar -xvf freetds-0.82.tar cd freetds-0.82 ./configure --sysconfdir=/etc --prefix=/usr --with-tdsver=8.0 make make install make clean signal-event post-upgrade; signal-event reboot
Test with tsql
TSQL is a simple database client included in FreeTDS package for testing and troubleshooting. "man tsql" give more info.
Check compile settings:
tsql -C
Should output something similar to this (if you compiled 0.82 version with above options):
Compile-time settings (established with the "configure" script): Version: freetds v0.82 MS db-lib source compatibility: no Sybase binary compatibility: no Thread safety: yes iconv library: yes TDS version: 8.0 iODBC: no unixodbc: yes
Test connection to the foreign database, overriding settings in freetds.conf (modify to suit your conditions). IP and not FQDN should be used. If on a NT domain, replace <dbuser> with <'DOMAINNAME\dbuser'>.
tsql -H 192.168.0.112 -p 1433 -U dbuser -P dbpass
This should output something like this indicate that you are connected, logged in and the server are ready for SQL commands:
locale is "sv_SE" locale charset is "ISO-8859-1" 1>
Exit tsql console with "quit". If connection was made but you didn't log in indicates that you have a problem in your remote server, check settings and permissions.
Configuration
Since these configuration files reside in /etc they may be overwritten by other server modifications. Therefore we use a templating system to make sure key information survives. More info here
freetds.conf
This file is provided by FreeTDS and contains the settings that tell freetds about each server it will connect to.
Host should be a FQDN (Fully Qualified Domain Name).
Make directory to hold template fragments to be included in the final file located in /etc/freetds.conf
mkdir -p /etc/e-smith/templates-custom/etc/freetds.conf
Copy the original file to be included as a template fragment when we regenerate the file:
cp /etc/freetds.conf /etc/e-smith/templates-custom/etc/freetds.conf/10original
Make additional fragment for your foreign server, "20xp3c" is my example name and can be modified:
pico /etc/e-smith/templates-custom/etc/freetds.conf/20xp3c
Add text about your foreign database server (modify to suit your conditions):
# Microsoft SQL Server 2005 Express on a Win XP machine [remoteserver] host = xp3c port = 1433 tds version = 8.0
Exit with ctrl-x, save with y and enter. Regenerate the complete file:
expand-template /etc/freetds.conf
The generated file can now be viewed with:
pico /etc/freetds.conf
If you want to edit the text, edit the template fragments, not this file.
odbcinst.ini
This file is provided by unixODB and tells unixODBC about the FreeTDS driver.
mkdir -p /etc/e-smith/templates-custom/etc/odbcinst.ini
cp /etc/odbcinst.ini /etc/e-smith/templates-custom/etc/odbcinst.ini/10original
pico /etc/e-smith/templates-custom/etc/odbcinst.ini/20sqlserver
Add driver path (modify to suit your conditions):
[FreeTDS] Description = Access Microsoft SQL Server with FreeTDS driver Driver = /usr/lib/libtdsodbc.so
expand-template /etc/odbcinst.ini
odbc.ini
This file is provided by unixODB and defines the DSN's (Data Source Names) for each ODBC connection. A DSN is simply a label for a given connection.
mkdir -p /etc/e-smith/templates-custom/etc/odbc.ini
cp /etc/odbc.ini /etc/e-smith/templates-custom/etc/odbc.ini/10original
If your original file are empty, there is no need to copy it, but it doesn't hurt either.
pico /etc/e-smith/templates-custom/etc/odbc.ini/20xp3c
Add text (modify to suit your conditions):
[remoteserver] Driver = FreeTDS Description = Connection to database DB_TEST on server XP3C Trace = No Server = xp3c Database = db_test Port = 1433 TDS_Version = 8.0
expand-template /etc/odbc.ini
Hosts
If the DNS does not resolve the ip of the machines running the remote SQL server (try ping the host name), add them to your hosts file (change ip and the machine name ...):
mkdir -p /etc/e-smith/templates-custom/etc/hosts
pico /etc/e-smith/templates-custom/etc/hosts/20xp3c
192.168.0.112 xp3c
expand-template /etc/hosts
The original content of the hosts file are generated elsewhere, hence not included here.
Test again
tsql
Test connection, using input (DSN and driver) in the configuration files we just edited, the result should be the same as the previous tsql test:
tsql -S remoteserver -U dbuser -P dbpass
isql
ISQL is a simple database client included in unixODBC package for testing and troubleshooting. The command "isql" give more info.
Generally speaking, isql is a bit more fussy about correct configuration settings than tsql, therefore we include also this test.
At the command line enter <isql -v DSN USERNAME PASSWORD>. If on a NT domain, replace <dbuser> with <'DOMAINNAME\dbuser'>:
isql -v remoteserver dbuser dbpass
+---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL>
If you do not get output similar to the above, then check your config files, pay special attention to upper/lowercase, for example REMOTESERVER is not the same as remoteserver.
Check PHP
Sample php code to check above could be (add standard html code):
if (function_exists('odbc_connect')) { echo "Function odbc_connect exists!"; } else { echo "Function odbc_connect does NOT exist!"; }
and
phpinfo();
Section Configure Command, should contain: --with-unixODBC=shared,/usr. There should also be a "odbc"-section further down.
So far the php-functionality have been provided by unixODBC and php-odbc and should have worked even before we installed the FreeTDS package.
To test FreeTDS drivers in PHP you can test a connection to your remote database:
$con = odbc_connect("remoteserver", "dbuser", "dbpass"); if (!$con) { print("There is a problem with SQL Server connection."); } else { print("The SQL Server connection object seems to work."); odbc_close($con); }
Additional information
FreeTDS user guide with troubleshooting section.
unixODBC official site
Input from, and credit to, Nick Critten unofficial copy of his HowTo and calio.it translated
Troubleshooting
There are some ifs and buts involved in theese config files, please consult the docs at FreeTDS and UnixODBC if you are running into trouble. Feel free to contribute to this HowTo with your experiences.
Tab spaces in config files may cause troubles, use single space before and after the <=>.
If on a NT domain, replace <dbuser> with <'DOMAINNAME\dbuser'> (with a single quotation mark before and after).
Escaping the backslash with another backslash <DOMAINNAME\\dbuser> would also work.
The host name xp3c used in the examples above works in a workgroup environment, it could (should?) be a full FQDN like xp3c.mydomain.com in a NT domain environment.
In the source directory freetds-0.82/src/apps is the shell script "osql" that attempts to automatically verify the ODBC setup. It checks odbc.ini, odbcinst, and, optionally, freetds.conf, then execute isql (assume it's unixODBC's isql). Syntax: osql -S server -U user -P password.
cd freetds-0.82/src/apps ./osql -S xp3c -U dbuser -P dbpass
On some systems (RISC) the drivers in /usr/local/lib/ may have the extension .sl rather than .so for shared libraries.
Microsoft SQL Server
If your SQL Server are using instances, replace <port = 1433> with <instance = MyInstanceName> in your freetds.conf template fragment. Maybe adding the instance name after host in freetds.conf like this: <Server = xp3c\MyInstanceName> would aid too.
From SQL Server 2000 TDS version 8.0 should be used. Add the line <tds version = 8.0> in your template fragment for freetds.conf
Microsoft SQL Server might not allow the db system user "sa" to connect from outside the server, create another account with proper permissions.
Express edition have remote access disabled by default. Can be changed in the menu "Surface Area Configuration". More info here