MSSQL ODBC with Apache 4

[ndrhk]

How do we set a ODBC connection from our cPanel to a windows server?

I've searched every where but all I find is tutorials for plesk or websitepanel

How can we do that?

I've tried this:

<?php

$connection_string = 'DRIVER={SQL Server};SERVER=<servername>;DATABASE=<databasename>';

$user = 'username';
$pass = 'password';

$connection = odbc_connect( $connection_string, $user, $pass );

?>

* On servername I've placed my IP ADDRESS, is this correct?

From:

PHP: odbc_connect - Manual

But, no success...

I'm using WHM with EasyApache 4 (mssql and odbc extensions are installed)

I have no idea how to use it... On Plesk they say all you gotta do is add a odbc remote connection (throught a gui), but on cPanel I have no idea how to do it...

I know there is a odbc.ini file but I dont know how to use it, what to write on it.

Please, help me.

Thank you.

 

[Dave Smith]

Hi,
There is a 'workaround' here (look for the post by Eduardo):

EasyApache 4 PDO_DBLIB (mssql support)

Hope it helps you.

 

[ndrhk]

Thank you Dave, but that didn't worked for me.

See, I've never did an ODBC Connection before, so I must be skipping something.

I've just copy and paste the full code.
On odbc.ini I've replaced Server and Database values, for my own values.
On the php file I did a full copy and paste and create two strings $username and $password (which is the mssql user and pass right?)

I am getting this error:

SQLSTATE[IM002] SQLConnect: 0 [unixODBC][Driver Manager]Data source name not found, and no default driver specified

That's it? Or I need to do something else?

PS: I've also tried to chance the odbc.ini Driver value for:
SQL Server
Microsoft Access Driver (which is the one I need)

But none, neither the original one from Eduardo's post worked.

What am I missing?

Thank you.


Edit:

Oh, I think it's because I didn't installed the Driver for Linux.
Is there any strict tutorial? Because I'm a newbie with Linux and I don't know how to install it under SSH.
Can we install via the cPanel interface?

Is this installation really necessary? Because I have php odbc extension activated...

If it is... Will this work to connect via Microsoft Access Database?

Another observation: I already have a mssql connection, so no ports are blocked, I'm trying to install a new website that requires the odbc connection

 

[ndrhk]

Can't anyone help me?

I've read a lot today about the ODBC, since nobody seems to care.
So, I've found out that we need drivers to be able to run via unixODBC.

I've searched and found that everyone has trouble installing it.
Why dont cPanel doesnt simply give everyone the drivers needed?

So, I've tried to install this (Made by Eduardo):

EasyApache 4 PDO_DBLIB (mssql support)

Doesnt work... Error:

Checking for 64 bit Linux compatible OS ..................................... OK
Checking required libs are installed ........................................ OK
unixODBC utilities (odbc_config and odbcinst) installed ..................... OK
unixODBC Driver Manager version 2.3.0 installed ......................... FAILED
unixODBC Driver Manager configuration correct ...................... NOT CHECKED
Microsoft SQL Server ODBC Driver V1.0 for Linux already installed .. NOT CHECKED

I got:

Package unixODBC-2.3.1-11.el7.x86_64 already installed and latest version

So this proccess gives me a error... Should I continue and try to install?

---------------------------------

I've also searched for mdbtools... Tried installed it and seems it got succesfull installed... So I followed up a few tutorials and setted my odbc files this way:

odbcinst.ini:

[MDB]
Description = Microsoft Access Database
Driver = /usr/local/lib/libmdbodbc.so.0 ---> Also tried: /usr/local/lib/libmdbodbc.so
Setup =
FileUsage =
CPTimeout =
CPReuse =

No idea what to set on the blanked fields...

and odbc.ini:

[MyOdbcCN]
Description = MSA Database
Driver = MDB
ServerName = MYIPADDRESSGOESHERE
Port = 1433

And again, no idea how to connect to a server now...

Can you guys please advise me?

I need to connect to a Microsoft Access Database throught a remote connection on Windows Server.

PS: Is there any security issue by installing mdbtools? If I want to, how can I uninstall it later?

 

[Dave Smith]

Sorry I could not help. Must be 20 years since I used ODBC or Access...

It may be worth signing up to Stack Overflow and posting your question there? I learned a lot from Stack Overflow users over the years. Some users can be a bit pedantic and others dive right in to help you. Am sure you could get some help with your issue. Worth a try..

Regards.

 

[cPanelMichael]

SQLSTATE[IM002] SQLConnect: 0 [unixODBC][Driver Manager]Data source name not found, and no default driver specified

Hello,

Could you verify if the account you are creating the PHP script with is using a custom php.ini file? If so, could you try moving it out of the way to verify it's not causing an issue with the PHP configuration? Otherwise, please post the output from the following commands:

rpm -qa|grep freetds
rpm -qa|grep mssql
rpm -qa|grep odbc
rpm -qa|grep unixODBC

Thank you.

 

[ndrhk]

Hello Michael,

rpm -qa|grep freetds
outputs:

ea-freetds-1.00.27-1.1.1.cpanel.x86_64

rpm -qa|grep mssql
outputs:

ea-php55-php-mssql-5.5.38-16.16.3.cpanel.x86_64
ea-php54-php-mssql-5.4.45-32.32.3.cpanel.x86_64
ea-php56-php-mssql-5.6.30-9.9.3.cpanel.x86_64

rpm -qa|grep odbc
outputs:

ea-php70-php-odbc-7.0.18-3.3.3.cpanel.x86_64
ea-php54-php-odbc-5.4.45-32.32.3.cpanel.x86_64
ea-php55-php-odbc-5.5.38-16.16.3.cpanel.x86_64
ea-php56-php-odbc-5.6.30-9.9.3.cpanel.x86_64

rpm -qa|grep unixODBC
outputs:

unixODBC-devel-2.3.1-11.el7.x86_64
unixODBC-2.3.1-11.el7.x86_64



And thank you Dave for trying to help, I did what you told me to do, open up a request on StackOverflow, but I'm still waiting for someone to help me.

 

[cPanelMichael]

Hello,

I recommend first verifying that FreeTDS is working properly via the command line. The following document should help you with this:

http://www.freetds.org/userguide/confirminstall.htm

Note that "tsql" is located at /opt/cpanel/freetds/bin/tsql and "freetds.conf" is located at /opt/cpanel/freetds/etc/freetds.conf.

Thank you.

 

[ndrhk]

Compile-time settings (established with the "configure" script)
                            Version: freetds v0.91
             freetds.conf directory: /etc
     MS db-lib source compatibility: yes
        Sybase binary compatibility: no
                      Thread safety: yes
                      iconv library: yes
                        TDS version: 5.0
                              iODBC: no
                           unixodbc: no
              SSPI "trusted" logins: no
                           Kerberos: no

PS: I do not want to connect as a root server.
I want to connect as a cPanel user.
So I think the servername connection etc, its not for me...


I can connect using ea-php-mssql (automatic installed via WHM).
But I'm having troubles with ODBC.

I still must install a freetds extension while having the ea-php-mssql installed?

I've searched my /usr/lib64/ extensions and I have not found a usable freetds extension, only the "Setup" file.
Here are the files inside /usr/lib64/

libtdsS.so
libtdsS.so.2
libtdsS.so.2.0.0

And this is my odbcinst.ini

[MDBToolsODBC]
Driver=/usr/local/lib/libmdbodbc.so
Setup=
FileUsage=
CPTimeout=
CPReuse=

[MDBTools]
Driver=/usr/local/lib/libmdb.so
Setup=
FileUsage=
CPTimeout=
CPReuse=

But they dont work...
My odbc.ini:

[mdb]
Description = ODBC for Microsoft Access
Driver = MDBTools
Database =/pathtofile/file.mdb
Readonly = Yes

[mdbODBC]
Description = ODBC for Microsoft Access
Driver = MDBToolsODBC
Database = file.mdb
Server = IP
Readonly = Yes
Port = 1433
Trace = No

Things will always be that hard to config in cPanel?

Edit:

How do I add the mdbtools.so file to be load with all other libraries/extensions/idontknowwhatisthis in cPanel? I think they are not configured.

I've runned this code: (Is it dangerous? Should I remove it? How?)

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib/


Edit2:

I have found a workaround for the MDB local access...

By using:

$driver = "/usr/local/lib/libmdbodbc.so";
$db = new PDO("odbc:Driver=$driver;DBQ=$dbName", "", "");
or
$conn = odbc_connect("Driver=$driver;DBQ=$dbName", "", "");

I can open the .mdb file (if its on the cpanel user hosting)
But I still would like to fix it, so I can use:

$db = new PDO("odbc:mdb", "", "");
or
$conn = odbc_connect("mdb", "", "");

mdb is the datasource setted in odbc.ini

Still not able to connect via ODBC to another windows server...

 

[ndrhk]

Could cPanel help me fix this issue? Please?
I can't use ODBC!

Close this Thread...
cPanel will never even answer me...

I regret by not choosing P**sk... Their ODBC works...