2,411
edits
Changes
From SME Server
Jump to navigationJump to search
mLine 1:
Line 1: − {{Warning box| Under construction, some content may be totally wrong and misleading.}} − + − + Line 16:
Line 15: − + − + Line 53:
Line 52: − − − − − − − − − Line 79:
Line 69: − + − + − + − + − + − + − A response something like this would indicate that connection was successful but login failed (wich is normal at this stage):+ − − Msg 18456, Level 14, State 1, Server XP3C\SQLEXPRESS, Line 1 + − Login failed for user 'dbuser'.+ − Msg 2002, Level 9, State -1, Server OpenClient, Line -1 − Adaptive Server connection failed − There was a problem connecting to the server Line 145:
Line 131: − + Line 156:
Line 142: + Line 163:
Line 150: − + Line 186:
Line 173: − + − − This should output something like this: − locale is "sv_SE" − locale charset is "ISO-8859-1" − 1> − 1> indicate that you are connected, logged in and the server are ready for SQL commands. Exit tsql console with: − quit − + + + Line 213:
Line 195: − + Line 223:
Line 205: − + − + − + − + Line 239:
Line 221: − + + + − + + + + + + + + + + + + + + − + − + − + − Ms SQL Server might not allow the db system user "sa" to connect from outside the server, create another account with proper permissions.+ − +
Minor text changs
{{Languages}}
{{Languages}}
{{Level|medium}}
{{Level|medium}}
===Description===
===Description===
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 Ms 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.
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.
===Used packages===
===Used packages===
'''unixODBC''' - is an API that provides a common interface to many different databases exept Sybase and SQL Server. [http://www.unixODBC.org unixODBC official site]
'''unixODBC''' - is a driver manager with built-in support for many different databases. [http://www.unixODBC.org unixODBC official site]
'''unixODBC-devel'''
'''unixODBC-devel'''
'''php-odbc''' - is simply an extension that allows you to use unixODBC to connect to database servers using PHP.
'''php-odbc''' - is simply an extension that allows you to use unixODBC to connect to database servers using PHP.
'''freetds''' - adding drivers för Sybase and Microsoft using TDS protocol. [http://www.freetds.org/ FreeTDS official site]
'''FreeTDS''' - adding drivers for Sybase and Microsoft using TDS protocol. [http://www.freetds.org/ FreeTDS official site]
Optional: '''gcc''' - The GNU Compiler Collection with libraries. [http://gcc.gnu.org/ GCC official site]
Optional: '''GCC''' - The GNU Compiler Collection with libraries. [http://gcc.gnu.org/ GCC official site]
===Installation===
===Installation===
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}}
tsql -C
tsql -C
Should output something similar to this:
Should output something similar to this (if you compiled 0.82 version with above options):
Compile-time settings (established with the "configure" script):
Compile-time settings (established with the "configure" script):
Version: freetds v0.64
Version: freetds v0.82
MS db-lib source compatibility: yes
MS db-lib source compatibility: no
Sybase binary compatibility: unknown
Sybase binary compatibility: no
Thread safety: yes
Thread safety: yes
iconv library: yes
iconv library: yes
TDS version: 4.2
TDS version: 8.0
iODBC: no
iODBC: no
unixodbc: yes
unixodbc: yes
Test connection to the foreign database, overriding settings in freetds.conf (modify to suit your conditions, ip could be replaced with FQDN):
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
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 is "sv_SE"
locale charset is "ISO-8859-1"
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===
===Configuration===
[FreeTDS]
[FreeTDS]
Description = Access Ms SQL Server with FreeTDS driver
Description = Access Microsoft SQL Server with FreeTDS driver
Driver = /usr/lib/libtdsodbc.so
Driver = /usr/lib/libtdsodbc.so
cp /etc/odbc.ini /etc/e-smith/templates-custom/etc/odbc.ini/10original
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
pico /etc/e-smith/templates-custom/etc/odbc.ini/20xp3c
[remoteserver]
[remoteserver]
Driver = FreeTDS
Driver = FreeTDS
Description = Connection to database DB_TEST on server xp3c
Description = Connection to database DB_TEST on server XP3C
Trace = No
Trace = No
Server = xp3c
Server = xp3c
===Test again===
===Test again===
====tsql====
====tsql====
Test connection, using input in freetds.conf:
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
tsql -S remoteserver -U dbuser -P dbpass
====isql====
====isql====
ISQL is a simple database client included in unixODBC package for testing and troubleshooting. The command "isql" give more info.
ISQL is a simple database client included in unixODBC package for testing and troubleshooting. The command "isql" give more info.
At the command line enter "isql -v DSN USERNAME PASSWORD" e.g.:
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
isql -v remoteserver dbuser dbpass
SQL>
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!
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===
===Check PHP===
and
and
phpinfo();
phpinfo();
Section Configure Command, should contain: --with-unixODBC=shared,/usr
Section Configure Command, should contain: --with-unixODBC=shared,/usr.
There should also be a "odbc"-section further down.
There should also be a "odbc"-section further down.
So far the php-functionality have been provided by unixODB and php-odbc and should have worked even before we installed the FreeTDS package.
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 you can do a connection to your remote database, "remoteserver" contain the DSN built in odbc.ini:
To test FreeTDS drivers in PHP you can test a connection to your remote database:
$con = odbc_connect("remoteserver", "dbuser", "dbpass");
$con = odbc_connect("remoteserver", "dbuser", "dbpass");
if (!$con) { print("There is a problem with SQL Server connection."); }
if (!$con) { print("There is a problem with SQL Server connection."); }
else { print("The SQL Server connection object is ready.");
else { print("The SQL Server connection object seems to work.");
odbc_close($con); }
odbc_close($con); }
FreeTDS [http://www.freetds.org/userguide/ user guide] with [http://www.freetds.org/userguide/troubleshooting.htm troubleshooting section].
FreeTDS [http://www.freetds.org/userguide/ user guide] with [http://www.freetds.org/userguide/troubleshooting.htm troubleshooting section].
[http://se2.php.net/manual/en/book.uodbc.php php.net ODBC]
unixODBC [http://www.unixODBC.org official site]
[http://php.net/manual/en/book.uodbc.php php.net ODBC]
Input from, and credit to, Nick Critten [http://bends.se/it/smeserver/filer/HowTo_NickCritten_freetds.html unofficial copy of his HowTo] and [http://www.calio.it/docs/doku.php/sqlserver calio.it] [http://translate.google.se/translate?u=http%3A%2F%2Fwww.calio.it%2Fdocs%2Fdoku.php%2Fsqlserver&sl=it&tl=en&hl=sv&ie=UTF-8 translated]
Input from, and credit to, Nick Critten [http://bends.se/it/smeserver/filer/HowTo_NickCritten_freetds.html unofficial copy of his HowTo] and [http://www.calio.it/docs/doku.php/sqlserver calio.it] [http://translate.google.se/translate?u=http%3A%2F%2Fwww.calio.it%2Fdocs%2Fdoku.php%2Fsqlserver&sl=it&tl=en&hl=sv&ie=UTF-8 translated]
===Troubleshooting===
===Troubleshooting===
Tab spaces in config files may cause troubles, use single space before and after =.
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).
<br>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.
====Ms SQL Server====
====Microsoft SQL Server====
If you are using instances, replace "port = 1433" with "instance = db_kalle" in your freetds.conf template fragment (replace db_kalle with the name of your instance).
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]
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]
----
----
[[Category:Howto]]
[[Category:Howto]]
