MSSQL connection with official driver

[zgi...@gmail.com]

Hi,

Probably a very simple thing but i can't find the answer. I have to work with MSSQL database (on azure) and i've just installed the Microsoft official linux drivers.

How can i make pyodbc use it? What should the connection string look like? {SQL Server} doesn't work. It seems to look after a file/entry somewhere as it returns:

Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found (0) (SQLDriverConnect)

The part for the driver in the string:

'DRIVER={SQL Server}',

I've tried to configure FreeTDS too but seems like I'm out of luck entirely today. If there is someone out there who has ever configured an MSSQL connection from Ubuntu with Python, please help :).

Many thanks

[icsny...@gmail.com]

http://www.connectionstrings.com/sql-azure/

Without looking at your connection string this is the best I can offer. It looks like your driver is titled, "SQL Server", change that to whatever makes sense from the link above.

[zgi...@gmail.com]

Thanks for the suggestion. It was my first clue too, but apparently it doesn't really consider what i'm writing in there. Currently my connection string is constructed this way (it was working on windows with the windows driver but i'd like to test it on a linux dev server):

def connect_db():

    """Connects to the database."""

    # DB Configuration [driver, server, database, userid, password]

    config = [

        'DRIVER={SQL Server Native Client 10.0}',

        'SERVER=xxxxxxxxx.database.windows.net',

        'DATABASE=xxxxxxxxxxx',

        'UID=xxxxxxxxxxxxx',

        'PWD=xxxxxxxxxxx'

    ]

    cnxn = pyodbc.connect(';'.join(config))

The problem persists with the same error message. I've got the feeling that it is a very rare case (to use mssql with python on a linux server).. A working example would be delightful.

[ashaner]

I use pyodbc and freetds on ubuntu. Although you didn't specify exactly what didn't work when you tried FreeTDS, the DRIVER would be {FreeTDS} in that case.

[zgi...@gmail.com]

Thanks, I've managed to make it work with FreeTDS on another server, which was running Ubuntu 13.04 No luck with 12.04 as of now. Can't get past this:

 tsql -H xxxxxxxxxxxxxxx.database.windows.net -p 1433 -U xxxxxxxxx -P xxxxxxxxxxx

locale is "en_US.UTF-8"

locale charset is "UTF-8"

using default charset "UTF-8"

Error 20004 (severity 9):

        Read from the server failed

        OS error 104, "Connection reset by peer"

Error 20002 (severity 9):

        Adaptive Server connection failed

There was a problem connecting to the server

As it is for a demo, we'll stick with 13.04 for now. I'll try with a clean install of 12.04 next time.

Thanks for all the suggestions!

[zgi...@gmail.com]

This is the configuration which worked with windows azure SQL on Ubuntu 13.04:

http://www.gazoakley.com/content/connecting-sql-azure-python-ubuntu-using-freetds-and-unixodbc