<p>I'm trying to connect a python 2.7 script to Azure SQL Data Warehouse.</p>

<p>The coding part is done and the test cases work in our development environment. We're are coding in Python 2.7 in MacOS X and connecting to ADW via ctds. 
The problem appears when we deploy on our Azure Kubernetes pod (running Debian 9). 
When we try to instantiate a connection this way:</p>

<pre><code># init a connection
self._connection = ctds.connect(
    server='myserver.database.windows.net',
    port=1433,
    user="my_user@myserver.database.windows.net",
    timeout=1200,
    password="XXXXXXXX",
    database="my_db",
    autocommit=True
)
</code></pre>

<p>we get an exception that only prints the user name</p>

<pre><code>my_user@myserver.database.windows.net
</code></pre>

<p>the type of the exception is </p>

<pre><code>_tds.InterfaceError
</code></pre>

<p>The code deployed is the exact same and also the requirements are.</p>

<p>The documentation we found for this exception is almost non-existent.</p>

<p>Do you guys recognize it? Do you know how can we go around it?</p>

<p>We also tried in our old AWS instances of EC2 and AWS Kubernetes (which rans the same OS as the Azure ones) and it also doesn't work.</p>

<p>We managed to connect to ADW via sqlcmd, so that proves the pod <em>can</em> in fact connect (I guess).</p>

<p>EDIT: SOLVED. JUST CHANGED TO PYODBC</p>

<pre><code>def connection(self):
    """:rtype: pyodbc.Connection"""
    if self._connection is None:

        env = '' # whichever way you have to identify it
        # init a connection

        driver = '/usr/local/lib/libmsodbcsql.17.dylib' if env == 'dev' else '{ODBC Driver 17 for SQL Server}' # my dev env is MacOS and my prod is Debian 9
        connection_string = 'Driver={driver};Server=tcp:{server},{port};Database={db};Uid={user};Pwd={password};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;'.format(
            driver=driver,
            server='myserver.database.windows.net',
            port=1433,
            db='mydb',
            user='myuser@myserver',
            password='XXXXXXXXXXXX'
        )
        self._connection = pyodbc.connect(connection_string, autocommit=True)

    return self._connection
</code></pre>

<p>As Ron says, pyodbc is recommended because it enables you to use a Microsoft-supported <a href="https://www.microsoft.com/en-us/download/details.aspx?id=56567" rel="nofollow noreferrer">ODBC Driver</a>.</p>

<p>I'm going to go ahead and guess that ctds is failing on redirect, and you need to force your server into "proxy" mode.  See: <a href="https://learn.microsoft.com/en-us/azure/sql-database/sql-database-connectivity-architecture" rel="nofollow noreferrer">Azure SQL Connectivity Architecture</a></p>

<p>EG</p>

<pre><code># Get SQL Server ID
sqlserverid=$(az sql server show -n sql-server-name -g sql-server-group --query 'id' -o tsv)

# Set URI
id="$sqlserverid/connectionPolicies/Default"

# Get current connection policy
az resource show --ids $id

# Update connection policy
az resource update --ids $id --set properties.connectionType=Proxy
</code></pre>
