2

I have been trying to connect to my Azure SQL Database on my Ubuntu 14.04 laptop using Python.

I found this article from Microsoft: Connect to SQL Database by using Python on Ubuntu Linux, which showed me how to connect to my SQL Database using Python 2.7.6

I confirmed I met the Python 2.7.6 requirement before getting started:

The output of ryan@laptop:~$ python -V was Python 2.7.6

I followed the instructions and entered the following lines into my terminal:

sudo apt-get --assume-yes update
sudo apt-get --assume-yes install freetds-dev freetds-bin
sudo apt-get --assume-yes install python-dev python-pip
sudo pip install pymssql

Everything installed as expected.

I created a python script and entered my information, here is my code:

#!/usr/bin/python
import pymssql
conn = pymssql.connect(server='ryans_server.database.windows.net', user='ryans_user@ryans_server', password='ryans_password', database='ryans_database')

However, when I execute this code, this is the output:

Traceback (most recent call last):
  File "./tp-database.py", line 3, in <module>
    conn = pymssql.connect(server='ryans_server.database.windows.net', user='ryans_user@ryans_server', password='ryans_password', database='ryans_database')
  File "pymssql.pyx", line 637, in pymssql.connect (pymssql.c:9508)
pymssql.OperationalError: (18456, "Login failed for user 'ryans_user'.DB-Lib error message 18456, severity 14:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20002, severity 9:\nAdaptive Server connection failed\n")

I have tried: export TDSVER=7.0, which causes a different error:

pymssql.OperationalError: (20017, 'DB-Lib error message 20017, severity 9:\nUnexpected EOF from the server\nDB-Lib error message 20002, severity 9:\nAdaptive Server connection failed\n')

What can I do to connect to my Azure SQL Database?

Improve this question
2
  • 1
    I'm using pyodbc, please see askubuntu.com/a/567960 for the details. Commented Jul 8, 2015 at 16:50
  • 1
    I gave up on pymssql. The much simpler alternative, called pyodbc, ended up working for me. For anyone who is struggling with the horrows of pymssql, follow this tutorial: snakeycode.wordpress.com/2013/12/04/… Commented Jul 8, 2015 at 18:13

3 Answers 3

Reset to default
4

I was able to connect to ms sql server using pymssql from my Ubuntu box.

This is what I used: 

#!/usr/bin/python
"Proof connection at pyqllevel."
# Test pyodbc connection. Result is 42.
# Note parameters in connection string, <PARAMETER>.

import pymssql
conn = pymssql.connect(server='serverName.database.windows.net',user='username@serverName',password='xxxxxxxxx',database='ustxazsql1')
cursor = conn.cursor()

In addition, for other users who use Windows VS + Python pymssql (installed via VS) to connect SQL Azure, you may encountered a similar error that pointed by PyanNHG:

enter image description here

To resolve this issue on Windows, please re-install pymssql lib from http://www.lfd.uci.edu/~gohlke/pythonlibs/#pymssql:

  1. Download the pymssql .whl file that align with your Python version

  2. Install pymssql from that .whl via pip,

    python -m pip D:\whlFile.whl

Then you could successfully connect to SQL Azure using pymssql (Please note an important step is to add the ip address from which you are running this code to Azure portal - Allow ip addresses for connecting sql azure database.)

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

4

I am currently using the following code to connect to azure sql

def connect(server, database_name):
try:
    conn = pymssql.connect(server=server['hostname'], user=server['db_user'], password=server['db_password'], database=database_name)
    return conn
except:
    logging.warning("connection Error will continue")
    logging.exception('Got exception on connect to db {}, {}'.format(server, database_name))
    return None

the server structure is basically a dict with the password and user

I have not installed freetds directly and used pip install pymssql in a virtual env and it works fine

You can see the entire script at https://gist.github.com/srgrn/71f0786ca4779e3178b9f5d9c6d1ee80

Improve this answer

Comments

1

I had three separate issues connecting to Azure SQL database, first trying with pymssql then pyodbc.

  1. TDS_Version had to be set in the (pyodbc) connection string. I could connect with TDS_Version=7.1 or TDS_Version=7.2 but 7.3 and 8.0 would fail.

  2. I had to explicitly set PORT=1433 (previous port issue)

  3. I was trying to connect with a user I created, which is incorrect. Connect to azure with the login: I can't login with a user I created in sql azure

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.