Connection to MS SQL database with PHP: data source name not found, and default driver not specified

Question

I got stuck in this problem for a while and I just can't get any further, I did a lot of searches, but nothing works ... I am trying to connect to a Microsoft SQL database with php using odbc.

Everything is configured as follows (the values ​​between "" are correct in the file):

file / etc / odbc.ini:

[CRMCONNECT] Description = "CRMConnect" Driver = FreeTDS Trace = No Servername = CRMSERVER Database = "dbname" UserName = "username" Password = "password" [Default] Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so 

/etc/odbcinst.ini:

 [FreeTDS] Description = tdsodbc Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so CPTimeout = 5 CPReuse = 5 FileUsage = 1 

/etc/freetds/freetds.conf:

 [CRMSERVER] host = xxx.xxx.xxx.xxx port = 1433 tds version = 8.0 

I checked the host twice, and rightly so. I also tried tds version 7.0, but no luck.

I can successfully connect to the server using isql:

 root@crmart-web004 :/# isql -v CRMCONNECT "user" "pass" +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> 

But with php, I just can't get it to work, I get the following error:

 [unixODBC][Driver Manager]Data source name not found, and no default driver specified 

My connection string:

 $connection = odbc_connect("Driver={CRMCONNECT};Server=xxx.xxx.xxx.xxx;Database=dbname;","username","password"); 

All parameters are double and correct.

Why can I successfully connect to isql but it fails in php?

My php version:

 PHP Version 5.4.4-14+deb7u5 

Odbcinst configuration:

 unixODBC 2.2.14 DRIVERS............: /etc/odbcinst.ini SYSTEM DATA SOURCES: /etc/odbc.ini FILE DATA SOURCES..: /etc/ODBCDataSources USER DATA SOURCES..: /root/.odbc.ini SQLULEN Size.......: 8 SQLLEN Size........: 8 SQLSETPOSIROW Size.: 8 

Hope someone has an idea what might be wrong.

Thank you in advance

Hello

UPDATE:

I changed the connection string to:

 $connection = odbc_connect("CRMCONNECT;Database=dbname;","user","pass"); 

which leads to another error:

 [unixODBC][Driver Manager]Driver SQLAllocHandle on SQL_HANDLE_HENV failed 

I will look at it, thanks again vinodadhikary

Sincerely.

UPDATE 2:

My connection string was wrong, it should have been:

 $connection = odbc_connect("CRMCONNECT","user","pass"); 

Thank vinodadhikary

Hello and happy holidays.

Answer 1

Since you already have a CRMCONNECT DSN , you can use the following connection method:

 $connection = odbc_connect("CRMCONNECT","username","password"); 

Also in your connection string is Driver={CRMCONNECT}; . CRMCONNECT , as you have defined, is not a driver; it is the name of a data source. The driver in your case will be FreeTDS

Answer 2

I had the same error in Laravel 5.5 with PHP 7.1.9 on Debian 7.11. Fixed by removing curly braces from driver name in DSN:

Failed: "odbc:Driver={fail};Server=host;Database=db;"

Works: "odbc:Driver=success;Server=host;Database=db;"

Another working option was to move the data source configuration from this line to odbc.ini and then access it: "odbc:odbc_ini_data_source_name"

The funny thing is that the same data source with Driver={SQL Server} (with curly braces) worked correctly in Windows 10.

Answer 3

The same goes for PDO. $conn = new PDO("odbc:CRMCONNECT"); and be sure to install the default driver in the odbc.ini file

[Default] Driver = IBM i Access ODBC Driver 64-bit in our case