Unable to connect to MS-SQL with ISQL
Asked Answered
P

4

7

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
Pliny answered 11/2, 2016 at 15:5 Comment(0)
P
0

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 :)

Pliny answered 12/2, 2016 at 16:20 Comment(0)
Y
0

I would replace "Username" with "UID" and "Password" with "PWD" in your odbc.ini.... from FreeTDS Manual - Chapter 4 - Preparing ODBC:

The original ODBC solution to this conundrum employed the odbc.ini file. odbc.ini stored information about a server, known generically as a Data Source Name (DSN). ODBC applications connected to the server by calling the function SQLConnect(DSN, UID, PWD), where DSN is the Data Source Name entry in odbc.ini, UID is the username, and PWD the password. Any and all information about the DSN was kept in odbc.ini. And all was right with the world.

The ODBC 3.0 specification introduced a new function: SQLDriverConnect. The connection attributes are provided as a single argument, a string of concatenated name-value pairs. SQLDriverConnect subsumed the functionality of SQLConnect, in that the name-value pair string allowed the caller to pass — in addition the the original DSN, UID, and PWD — any other parameters the driver could accept. Moreover, the application can specify which driver to use. In effect, it became possible to specify the entire set of DSN properties as parameters to SQLDriverConnect, obviating the need for odbc.ini. This led to the use of the so-called DSN-less configuration, a setup with no odbc.ini.

Yager answered 12/2, 2016 at 7:49 Comment(1)
Thanks 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].Pliny
P
0

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 :)

Pliny answered 12/2, 2016 at 16:20 Comment(0)
A
0

I contacted the Nick Gorham the developer of unixODBC about this exact issue and he confirmed that isql is not reading the username/password from the config file

Hi Nick,

I think unixODBC is a great project but I was surprised to see that it is insecure (or at least I don’t know how to use it properly).

When I connect to the database using the isql I have to type in the password. On a shared server this is insecure because the

$ ps –aux

Command shows the password in clear.

Is there a fix for that? Can I put the password in a file readable only by my user?

Thank you for your help.

The answer:

Hi,

It depends on the driver. Some can read the user and password from the odbc.ini or ~/.odbc.ini file so you can store the password there.

isql is only designed as a simple test app, there is nothing stopping you from modifying ilsq to pull the user and password from a file of your choice, decrypting it if needed.

Ambrosio answered 23/9, 2016 at 15:7 Comment(0)
I
0

I was having a slightly different issue, but my google search lead me here. When trying to connect through isql, I was getting Login failed for user '' even though I had specified a user in my odbc.ini file

[SQLSERVER_SAMPLE]
Driver=ODBC Driver 17 for SQL Server
Server=SERVER
Database=DATABASE
Trusted_Connection=no
UID=USER
PWD=PASSWORD

I tried both UID and User, but both gave the same error. After reading @Andrei Sura's solution, I figured out that the username and password were being ignored.

My solution was to run isql -v SQLSERVER_SAMPLE USER PASSWORD even though the username and password were specified in the odbc.ini file - and it connected.

Infracostal answered 18/6, 2021 at 14:11 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.