NateB's picture

I've been doing a TON of troubleshooting on this issue, and I'm stuck. The Long-Story-Short version is this:

I've been able to install the official Microsoft ODBC drivers, and they show enabled. I was also able to get the sqlsrv and pdo_sqlsrv plugins installed and show active in phpinfo.php. however, using them to connect presents an error "could not establish connection with server"

the exact same code works on a test WAMP server (so it works under windows, and it makes me think somehow the drivers are just, incorrectly installed in linux)

I have been able to connect to the server using FreeTDS "tsql" command, and able to get the info from the tables I need, so Maybe i'll need to just change directions and use odbc in php to connect.

not sure, I just know it's not working. I also spun up an ubuntu VM, and installed everything from scratch, and run into the same issue. So that leaves me thinking there is something specifically in the microsoft documentation for linux that is lacking or that i'm missing somehow.

 

my odbcinst:

root@lamp /etc# odbcinst -j
unixODBC 2.3.11
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

contents of odbcinst.ini

root@lamp /etc# cat odbcinst.ini
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.6.1
UsageCount=1

contents of odbc.ini (REDACTED IP)

root@lamp /etc# cat odbc.ini
[MSSQLTest]
Driver=ODBC Driver 17 for SQL Server
Description=SQL via Microsoft Drivers
Server=tcp:192.168.##.## 

attempts to try connecting with 'isql' using the above configs:

root@lamp /etc# isql -v MSSQLTest
[08001][unixODBC][Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x2746
[08001][unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Client unable to establish connection
[ISQL]ERROR: Could not SQLConnect

root@lamp /etc# isql -v MSSQLTest 'UID' 'PWD'
[08001][unixODBC][Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x2746
[08001][unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Client unable to establish connection
[ISQL]ERROR: Could not SQLConnect

FreeTDS config:

root@lamp /etc/freetds# cat freetds.conf
#
# 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 = auto

        # 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

        # To reduce data sent from server for BLOBs (like TEXT or
        # IMAGE) try setting 'text size' to a reasonable limit
;       text size = 64512

        # If you experience TLS handshake errors and are using openssl,
        # try adjusting the cipher list (don't surround in double or single quotes)
        # openssl ciphers = HIGH:!SSLv2:!aNULL:-DH

# A typical Sybase server
[egServer50]
        host = symachine.domain.com
        port = 5000
        tds version = 5.0

# A typical Microsoft server
#[egServer73]
#       host = ntmachine.domain.com
#       port = 1433
#       tds version = 7.3

#Our MSSQL Server
[appserver]
        host = 192.168.##.##
        port = 1433
        tds version = 7.4
        client charset = UTF-8

Connecting with freetds/tsql: (Redacting for length, just showing connection)

root@lamp /etc/freetds# tsql -S appserver -U UID -P PWD
locale is "C"
locale charset is "ANSI_X3.4-1968"
using default charset "UTF-8"
1> USE testing
2> Go
1> GO
1> SELECT * FROM equipment
2> GO
Id      Dept    EquipId Description     CycleUnits      CycleUOM        InActive
1       4       107     ATLAS COPCO GS75-VSD #107       1       M       0
2       3       112     NORDSON 8000 POWDER COLLECTOR 112       1       M       0
3       3       113     NORDSON 8000 POWDER COLLECTOR 113       1       M       0
4       3       115     R.E. BATCH OVEN #115    1       M       0

Our end goal, is to move the internal website we're using (currently hosted on our NAS with an apache/mysql setup) to it's own standalone web-server but to have the data housed in our MSSQL server with much of our other data, so that we can run other reports also. this website will be low traffic, and shouldn't need much access other than to these specific tables/views, as we use it mostly to display production information on a monitor out on the shop floor for our production staff to reference.

currently they have an excel spreadsheet, that is VERY difficult to read on the TV, and if we can get the same query working on a webpage, it would format nicer, and generally be a quality of life improvement out on the floor. 

Im hoping someone has run into these issues and found a solution for being able to use the sqlsrv plugins for php, as much of the code we've already run, is set up using that... i should be able to re-work code to connect differently, but if we can just find a fix for the drivers, it would make things easier in the long run i think.

 

Thanks, And i know this is a long post, i'm just trying to give as much info as possible. as my eyes are blurring after going through the MS troubleshooting, and much of the other sites. I'm still scratching my head as to how to find the exact problem, just that it wont connect on a linux server, but DOES when I installed WAMP on my local machine... both servers are using MSODBC 17 drivers, so I could try to make sure to keep as many variables the same as possible.

-Nate

Forum: 
Tags: 
Jeremy Davis's picture

Belated apologies that I never responded here. I know that since then we discussed this offline (or at least one on one) and didn't really reach any resolution. Any/all suggestions and thoughts I had, you'd already tried with no joy.

But I just wanted to touch base and see if you had any luck in the end?

If you did, when you get a chance it'd be great if you could post back as I'm sure it'd be useful knowledge for anyone else who finds themselves in the same situation.

NateB's picture

unfortunately, I did not find a solution yet. What we ended up doing, was spinning up a WAMP server on a spare windows host to just get something working. When workload on the production floor slows down, and we have other hardware for a proper Proxmox host, I'll revisit this.
As we discussed one-on-one before, at home I don't run into the problem as I don't use Windows or MS SQL.
When I revisit this, I'll update the thread with any solution I find, or other troubleshooting I've tried.
Thanks!!!
-Nate

Jeremy Davis's picture

Thanks for the update. When you do revisit, please don't hesitate to ask any more questions and/or share any progress.

Add new comment