0

I have a Ubuntu 14.04 machine running a local server.

On this server, I have a PHP script that needs to access data on a remote Microsoft Azure SQL Database.

I was unable to find a way to access the database from Ubuntu uing purely PHP, so I tried out a Python script and imported the pyodbc library after following this tutorial: https://snakeycode.wordpress.com/2013/12/04/installing-pyodbc-on-ubuntu-12-04-64-bit/

I can run this script fromthe terminal, and it successfully connects to the Azure SQL Database and prints the output of my query to the console.

This was great news, so I began to test it out on my local server.

On the server, in a PHP method, I call echo shell_exec('my_script.py');

This gives me an error: 

pyodbc.Error: ('08001', '[08001] [unixODBC][FreeTDS][SQL Server]
Unable to connect to data source (0) (SQLDriverConnect)')

Why does my_script.py not work when executed by the local server?

RyanNHG
  • 1,009
  • 2
  • 8
  • 20
  • Is it possible to use Microsoft Sql Server ODBC Driver for Linux directly in PHP? See http://stackoverflow.com/questions/20163776/connect-php-to-mssql-via-pdo-odbc – Jared Moore Jul 08 '15 at 22:30
  • When you run successfully from the terminal, is that on the same machine you describe as your "local server"? You probably will want to connect directly from PHP, which you can do using the same underlying stack of FreeTDS and unixODBC. Have you tried tsql and isql from the command line of your "local server"? – FlipperPA Jul 09 '15 at 17:24

1 Answers1

0

Use TDS version as 8.0 and also mention the same in your pyodbc.connect connection string.

Sample code: 

#!/usr/bin/python
"Proof connection at pyodbc level."
# Test pyodbc connection.

import pyodbc

conn = pyodbc.connect('DRIVER=FreeTDS;DSN=mssql;UID=userid@servername.database.windows.net;PWD=xxxxxxxx;TDS_Version=8.0;')
cursor = conn.cursor()
cursor.execute("SELECT * FROM sys.tables")
print( cursor.fetchall() )
conn.close()

Content of : /etc/freetds/freetds.conf:

[global]
        # TDS protocol version
        tds version = 8.0

        # 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
[mssql]
        host = servername.database.windows.net
        port = 1433
        tds version = 8.0

Content of /etc/odbc.ini file: 

[mssql]
Driver = FreeTDS
Description = ODBC connection via FreeTDS
Trace = Yes
ServerName = mssql
Database = databasename
TDS_Version = 8.0

Contents of /etc/odbcinst.ini file: 

[FreeTDS]
Description=FreeTDS Driver
Driver=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup=/usr/lib/x86_64-linux-gnu/odbc/libtdsS.so

Please let us know if you still cannot access and provide the exact error you run into.

Also an important step is to add the ip address from which you are running this code to firewall rules on sql azure database.

Srikanth Sureddy
  • 34
  • 1