Sqlalchemy unable to work with azure SQL server ( The rest aws local all works)

[suman deb]

Hi I have followed the sqlalchemy connection strings docs for mssql

This is my connection string that works for mssql in other platforms ( aws-rds, vm)

i am trying to use pymssql .

 db_url = '{}+pymssql://{}:{}@{}:{}/{}'.format("mssql", user, password, host, port, dbname)

engine = create_engine('mssql+pymssql://scott:tiger@hostname:port/dbname')

Error:-

File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 393, in connect

    return self.dbapi.connect(*cargs, **cparams)

  File "pymssql.pyx", line 641, in pymssql.connect (pymssql.c:10824)

sqlalchemy.exc.OperationalError: (pymssql.OperationalError) (20002, 'DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (testservercri.database.windows.net:1433)\n')

i have tested with FreeTDS 1.0 ,,tds version = 7.0 in /etc/FreeTDS/freetds,.conf

It connects and works.

root@DNDSaltMaster:~# tsql -H  <servername> -U criuser -p 1433

Password:

locale is "en_US.UTF-8"

locale charset is "UTF-8"

using default charset "UTF-8"

1>

Please let me know if this a know bug or am i missing some steps. 

[Mike Bayer]

On Mon, Sep 18, 2017 at 5:54 AM, suman deb <suman....@gmail.com> wrote:
> Hi I have followed the sqlalchemy connection strings docs for mssql
>
> This is my connection string that works for mssql in other platforms (
> aws-rds, vm)
>
> i am trying to use pymssql .
>
>
> db_url = '{}+pymssql://{}:{}@{}:{}/{}'.format("mssql", user, password,
> host, port, dbname)
>
>
> engine = create_engine('mssql+pymssql://scott:tiger@hostname:port/dbname')
>
> Error:-
>
> File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py",
> line 393, in connect
> return self.dbapi.connect(*cargs, **cparams)
> File "pymssql.pyx", line 641, in pymssql.connect (pymssql.c:10824)
> sqlalchemy.exc.OperationalError: (pymssql.OperationalError) (20002, 'DB-Lib
> error message 20002, severity 9:\nAdaptive Server connection failed
> (testservercri.database.windows.net:1433)\n')

you're not getting through FreeTDS yet so you need to ensure you can
fully connect with tsql. See the instructions at
http://www.freetds.org/userguide/serverthere.htm

when that works, then we know what the pymssql connect string should
be and you can get connected.

>
>
> i have tested with FreeTDS 1.0 ,,tds version = 7.0 in
> /etc/FreeTDS/freetds,.conf
> It connects and works.
> root@DNDSaltMaster:~# tsql -H <servername> -U criuser -p 1433
> Password:
> locale is "en_US.UTF-8"
> locale charset is "UTF-8"
> using default charset "UTF-8"
> 1>
>
> Please let me know if this a know bug or am i missing some steps.
>
>

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[suman deb]

It does connect

root@DNDSaltMaster:~# tsql -S testservercri.database.windows.net -U criuser

Password:

locale is "en_US.UTF-8"

locale charset is "UTF-8"

using default charset "UTF-8"

1> r

[testservercri]

        host = testservercri.database.windows.net

        port = 1433

        tds version = 7.1

[Mike Bayer]

On Mon, Sep 18, 2017 at 2:44 PM, suman deb <suman....@gmail.com> wrote:
> It does connect
>
> root@DNDSaltMaster:~# tsql -S testservercri.database.windows.net -U criuser
> Password:
> locale is "en_US.UTF-8"
> locale charset is "UTF-8"
> using default charset "UTF-8"
> 1> r
>
> [testservercri]
> host = testservercri.database.windows.net
> port = 1433
> tds version = 7.1
>

oh and you said that also, sorry. there are other problems with
Azure that have been reported on pyodbc once you connect, but you
aren't getting that far here.

Here's direct pymssql connect:

import pymssql

conn = pymssql.connect("testservercri.database.windows.net", criuser,
password, "dbname")

if that doesn't work then your problem is with pymssql. if it does,
then it has to work from SQLAlchemy also.

http://pymssql.org/en/stable/ref/pymssql.html#pymssql.connect

if the above is not working you might want to try connecting with
pyodbc. there's some workarounds needed for that but I've had two
people already use them.