Re: Looking up hostname from IP address and connection problem MS SQL Server from php

OK, 

If I understood well, I have to get the following configurations in these files :

 In /etc/odbc.ini script : 
============================================================
 [ODBC Data Sources]
 MyMSSQLServerName=MSSQL Server
 [MyMSSQLServerName]
 Driver = /usr/lib64/libtdsodbc.so.0
 Description = MSSQL Server
 Trace = No
 Server = 192.168.1.32
 Database = mydatabase
 Port = 1433
 TDS_Version = 7.2
============================================================



In /etc/odbcinst.ini  file :
==============================================================
# Example driver definitions
# Driver from the postgresql-odbc package
# Setup from the unixODBC package
[PostgreSQL]
Description = ODBC for PostgreSQL
Driver  = /usr/lib/psqlodbc.so
Setup  = /usr/lib/libodbcpsqlS.so
Driver64 = /usr/lib64/psqlodbc.so
Setup64  = /usr/lib64/libodbcpsqlS.so
FileUsage = 1

# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[MySQL]
Description = ODBC for MySQL
Driver  = /usr/lib/libmyodbc5.so
Setup  = /usr/lib/libodbcmyS.so
Driver64 = /usr/lib64/libmyodbc5.so
Setup64  = /usr/lib64/libodbcmyS.so
FileUsage = 1

[FreeTDS]
Driver = /usr/lib64/libtdsodbc.so.0
Setup = /usr/lib64/libtdsS.so
Description = MSSQL Server
Trace = No
 ================================================================================



The  /etc/freetds.conf file :  
=====================================================================
#   $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 ## I added theses lines below
# MSSQL ServerName
[MyMSSQLServerName]
host = 192.168.1.32
port = 1433
tds version = 7.0
# client charset = UTF-8
===============================================================================================


> These 4 lines here are useless and confusing, odbcinst.ini is just for
> drivers, not connections.

OK.

> It's better to set Setup for unixODBC with same value as Driver
> (FreeTDS provide a Setup).

I don't understand everything, could you give me an example how should I make the script in odbcinst.ini, odbc.ini and freetds.conf please ?


> You should use DSN here to refer the entry you configured in odbc.ini.
> Also you should try with isql tools to connect.
> 
> Something like "DSN=MyMSSQLServerName;UID=user;PWD=password;"
> replacing user and password. You need either to provide a
> username/password or setup Kerberos.
> 

In my case, the php file refers the odbcinst.ini. Can you tell me how could I must my php page refers to the odbc.ini configuration , 
if I should use :
$dsn = "DSN=MyMSSQLServerName;UID=user;PWD=password;"; 
$cnx = odbc_connect($dsn,'','');
?
Sorry, I use the odbc connection and odbc functions (not php mssql functions)  for my web server .

I know nothing about Kerberos. The value of UID, is the authentication SQL Server login or Windows login  ?
Should I modify something in my MS Windows Server or SQL Server ? What should I do about this ?   

Thank you very mutch.

Best regards.















> Date: Sun, 1 Mar 2015 08:11:48 +0000
> From: [email protected]
> To: [email protected]
> Subject: Re: [freetds] Looking up hostname from IP address and connection problem MS SQL Server from php
> 
> 2015-02-27 14:14 GMT+00:00 Marc-André Legrand <[email protected]>:
> >
> >
> >
> > Hello,
> >
> > I installed FreeTDS on CentOS 6.6 and I have a connection problem between
> > PHP Server and MS SQL Server 2014. I have the following message :
> > Warning: odbc_connect(): SQL error: [unixODBC][FreeTDS][SQL Server]Unable to connect
> > to data source, SQL state S1000 in SQLConnect
> > After FreeTDS installation I made a test by :
> > # tsql -C
> > 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: yes
> >                       Thread safety: yes
> >                       iconv library: yes
> >                         TDS version: 4.2
> >                               iODBC: no
> >                            unixodbc: yes
> >               SSPI "trusted" logins: no
> >                            Kerberos: yes
> >
> >
> > It is a FreeTDS RPM version that I have got .
> > Here is Linux Server feature :
> > CentOS 6.6
> > Php 5.3.3
> > Apache 2.2.15
> > unixodbc 2.2.14
> > freetds 0.91
> > My Windows Server :
> > Windows Server 2012 R2
> > MS SQL Server 2014 Standard Edition
> > Here is the command line that I executed  :
> > # osql -S MyMSSQLServerName -U user -P password
> >
> > My result of this command :
> > checking shared odbc libraries linked to isql for default directories...
> > strings: '': No such file
> >  trying /tmp/sqlH ... no
> >  trying /tmp/sqlL ... no
> >  trying /etc ... OK
> > checking odbc.ini files
> >  cannot read "/root/.odbc.ini"
> >  reading /etc/odbc.ini
> > [MyMSSQLServerName] found in /etc/odbc.ini
> > found this section:
> >  [MyMSSQLServerName]
> >  Driver = /usr/lib64/libtdsodbc.so.0
> >  Description = MSSQL Server
> >  Trace = No
> >  Server = 192.168.1.32
> >  Database = mydatabase
> >  Port = 1433
> >  TDS_Version = 7.2
> >
> > looking for driver for DSN [MyMSSQLServerName] in /etc/odbc.ini
> >   found driver line: " Driver = /usr/lib64/libtdsodbc.so.0"
> >   driver "/usr/lib64/libtdsodbc.so.0" found for [MyMSSQLServerName] in odbc.ini
> > found driver named "/usr/lib64/libtdsodbc.so.0"
> > /usr/lib64/libtdsodbc.so.0 is an executable file
> > "Server" found, not using freetds.conf
> > Server is "192.168.1.32"
> > looking up hostname for ip address 192.168.1.32
> > osql: warning: no DNS hostname found for "192.168.1.32"
> > Usage: host [-aCdlriTwv] [-c class] [-N ndots] [-t type] [-W time]
> >             [-R number] [-m flag] hostname [server]
> >        -a is equivalent to -v -t ANY
> >        -c specifies query class for non-IN data
> >        -C compares SOA records on authoritative nameservers
> >        -d is equivalent to -v
> >        -l lists all hosts in a domain, using AXFR
> >        -i IP6.INT reverse lookups
> >        -N changes the number of dots allowed before root lookup is done
> >        -r disables recursive processing
> >        -R specifies number of retries for UDP packets
> >        -s a SERVFAIL response should stop query
> >        -t specifies the query type
> >        -T enables TCP/IP mode
> >        -v enables verbose output
> >        -w specifies to wait forever for a reply
> >        -W specifies how long to wait for a reply
> >        -4 use IPv4 query transport only
> >        -6 use IPv6 query transport only
> >        -m set memory debugging flag (trace|record|usage)
> > osql: no IP address found for ""
> 
> I think this is just a problem of osql, FreeTDS is able to use ip
> directly in server.
> 
> >
> > Here is my ODBC configuration
> > ====================================================================
> > /etc/odbc.ini for osql
> > [ODBC Data Sources]
> > MyMSSQLServerName=MSSQL Server
> > [MyMSSQLServerName]
> > Driver = /usr/lib64/libtdsodbc.so.0
> > Description = MSSQL Server
> > Trace = No
> > Server = 192.168.1.32
> > Database = mydatabase
> > Port = 1433
> > TDS_Version = 7.2
> 
> Looks good
> 
> > [Default]
> > Driver=/usr/lib64/libtdsodbc.so.0
> > ================================================================
> > /etc/odbcinst.ini for php
> > # Example driver definitions
> > # Driver from the postgresql-odbc package
> > # Setup from the unixODBC package
> > [PostgreSQL]
> > Description = ODBC for PostgreSQL
> > Driver  = /usr/lib/psqlodbc.so
> > Setup  = /usr/lib/libodbcpsqlS.so
> > Driver64 = /usr/lib64/psqlodbc.so
> > Setup64  = /usr/lib64/libodbcpsqlS.so
> > FileUsage = 1
> >
> > # Driver from the mysql-connector-odbc package
> > # Setup from the unixODBC package
> > [MySQL]
> > Description = ODBC for MySQL
> > Driver  = /usr/lib/libmyodbc5.so
> > Setup  = /usr/lib/libodbcmyS.so
> > Driver64 = /usr/lib64/libmyodbc5.so
> > Setup64  = /usr/lib64/libodbcmyS.so
> > FileUsage = 1
> >
> > [FreeTDS]
> > Driver = /usr/lib64/libtdsodbc.so.0
> > Setup = /usr/lib64/libtdsS.so
> > Description = MSSQL Server
> > Trace = No
> 
> > Server = 192.168.1.32
> > Database = mydatabase
> > Port = 1433
> > TDS_Version = 7.2
> 
> These 4 lines here are useless and confusing, odbcinst.ini is just for
> drivers, not connections.
> It's better to set Setup for unixODBC with same value as Driver
> (FreeTDS provide a Setup).
> 
> > =====================================================================
> > /etc/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 ## I added theses lines below
> > # MSSQL ServerName
> > [MyMSSQLServerName]
> >     host = 192.168.1.32
> >     port = 1433
> >     tds version = 7.0
> >     # client charset = UTF-8
> > ====================================================================================
> > ========
> >
> > My connection string in php file :
> > Driver=FreeTDS;Server=192.168.1.32;Database=mydatabase;
> >
> 
> You should use DSN here to refer the entry you configured in odbc.ini.
> Also you should try with isql tools to connect.
> 
> Something like "DSN=MyMSSQLServerName;UID=user;PWD=password;"
> replacing user and password. You need either to provide a
> username/password or setup Kerberos.
> 
> > Another information about CentOS, I disabled SELinux .
> > The osql searches DNS hostname from the IP address that I given but it didn't find.
> >
> > I don't know if the connection problem from php to SQL Server is due by the lookup DNS problem from the IP address.
> >
> > I would like to know what should I do to resolve this connection problem ?
> >
> > Thank you for your response.
> >
> > Best regards.
> >
> 
> Frediano
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds