Unable to connect to MS-SQL with ISQL
Ok, so I solved it (pretty much). The password and username in my odbc files were being ignored. Because I was calling the DB queries from Asterisk, I was using a file called res_odbc.ini too. This contained my username and password also, and when I run the query from Asterisk, it conencts and returns the correct result.
In case it helps, here is my final working configuration.
odbc.ini
[asterisk-connector]
Description = MS SQL connection to asterisk database
driver = /usr/lib64/libtdsodbc.so
servername = SQL2
Port = 1433
User = MyUsername
Password = MyPassword
odbcinst.ini
[FreeTDS]
Description = TDS connection
Driver = /usr/lib64/libtdsodbc.so
UsageCount = 1
[ODBC]
trace = Yes
TraceFile = /tmp/sql.log
ForceTrace = Yes
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
[SQL2]
host = 192.168.1.59
port = 1433
tds version = 8.0
res_odbc.conf
[asterisk-connector]
enabled = yes
dsn = asterisk-connector
username = MyUsername
password = MyPassword
pooling = no
limit = 1
pre-connect = yes
Remember if you are using Centos 64 bit to modify the driver path to lib64. Most of the guides online have the wrong (for 64 bit) paths.
Good luck - it's a headache :)
Related videos on Youtube
massiveattack
Updated on September 15, 2022Comments
-
massiveattack over 1 year
First post on StackExchange - please go easy :)
I have setup ODBC in Centos 6 in order to perform ms-sql queries from my Asterisk installation.
My Config files are:
/etc/odbc.ini
[asterisk-connector] Description = MS SQL connection to 'asterisk' database Driver = /usr/lib64/libtdsodbc.so Setup = /usr/lib64/libtdsS.so Servername = SQL2 Port = 1433 Username = MyUsername Password = MyPassword TDS_Version = 7.0
/etc/odbcinst.ini
[odbc-test] Description = TDS connection Driver = /usr/lib64/libtdsodbc.so Setup = /usr/lib64/libtdsS.so UsageCount = 1 FileUsage = 1
/etc/asterisk/res_odbc.conf
[asterisk-connector] enabled => yes dsn => asterisk-connector username => MyUsername password => MyPassword pooling => no limit => pre-connect => yes
I am able to connect via ISQL when I pass in the password and username:
[root@TestVM etc]# isql -v asterisk-connector MyUsername MyPassword +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL>
..but I should be able to connect without the username / password. All that returns is:
[root@TestVM etc]# isql -v asterisk-connector [S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source [01000][unixODBC][FreeTDS][SQL Server]Adaptive Server connection failed [ISQL]ERROR: Could not SQLConnect
It is as if ISQL cannot read the username and password from the config files.
I need to be able to perform MS-SQL lookups from within the Asterisk dialplan, but for that to happen I must be able to call ISQL with just the data source name and can't pass in the authentication parameters.
All the guides I've read online state that I should be able to connect with just the
isql -v asterisk-connector
command, but that's not happening for me.
I've been pulling my hair out for a few days on this, so any help or pointers in the right direction would be much appreciated.
Thanks in advance.
Edit:
I have turned on logging, and may have a clue. The username and password definitely aren't being passed in. Look:
[ODBC][27557][1455205133.129690][SQLConnect.c][3614] Entry: Connection = 0xac3080 Server Name = [asterisk-connector][length = 18 (SQL_NTS)] User Name = [NULL] Authentication = [NULL] UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE' DIAG [01000] [FreeTDS][SQL Server]Adaptive Server connection failed DIAG [S1000] [FreeTDS][SQL Server]Unable to connect to data source
So User Name and Authentication here are [NULL]. It's obviously not picking up the username / password in odbc.ini or res_odbc.conf, but the question is why. I'll keep investigating :)
Edit2:
The OSQL utility returns:
[root@TestVM etc]# osql -S SQL2 -U MyUsername -P MyPassword 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 reading /root/.odbc.ini [SQL2] not found in /root/.odbc.ini reading /etc/odbc.ini [SQL2] found in /etc/odbc.ini found this section: looking for driver for DSN [SQL2] in /etc/odbc.ini no driver mentioned for [SQL2] in odbc.ini looking for driver for DSN [default] in /etc/odbc.ini osql: error: no driver found for [SQL2] in odbc.ini
-
massiveattack about 8 yearsThanks for that, mauro. I changed odbc.ini to reflect - using UID and PWD, but get the same error in my logs - User Name and Authentication show as [NULL].