Unable to connect to MSSQL Server database using Python

Question

I've been connecting to a MSSQL SERVER 2008 database using Microsoft SQL Server Management Studio graphic interface, as depicted in this screenshot

I would like to connect to this database using a python script. I installed pymssql and I've failed to connect to the database. I tried the following command:

import _mssql
conn = _mssql.connect(server="POLIVEIRA-PC\\MSSQLSERVER2008", user="POliveira-PC\\POliveira", password="my_password", database="database_name")

with and without the user and password flags. I always end up with this error:

Traceback (most recent call last): File "", line 1, in File "_mssql.pyx", line 1887, in _mssql.connect (_mssql.c:20477) File "_mssql.pyx", line 632, in _mssql.MSSQLConnection.init (_mssql.c:6169) _mssql.MSSQLDriverException: Connection to the database failed for an unknown reason.

Can you help me connect to this database using Python (either using pymssql module or not). I'm not experienced with Python, nor do I with SQL, therefore I would like to do it in the simplest manner possible.

I'm running Windows 7 64 bit. Pyhton v2.7.9

In your screenshot you're using windows authentication, while your code is using SQLserver authentication... Is you server set up for this? — thebjorn
– thebjorn, Commented Jan 25, 2015 at 1:26
@thebjorn I don't know if the server is set up for that, but I would like my code to connect using windows authentication then. Let me just give you the context: I'm writing my MSc thesis and I was given a bunch of database files to extract data from. I'm the only one accessing the database, which is stored in my PC. — POliveira
– POliveira, Commented Jan 25, 2015 at 10:44
I don't think pymssql allows you to connect using windows authentication anymore (looks like it was removed in 2010 some time). If you really want to use windows authentication then the adodb library can do that. — thebjorn
– thebjorn, Commented Jan 25, 2015 at 15:50

Camilo Velasquez · Accepted Answer · 2017-06-09 20:20:20Z

1

I recommend you to use pyodbc, if you're using anaconda, use the 3.0.10 version of pyodbc, example:

import pyodbc 
from urllib.parse import quote_plus    
params = quote_plus("DRIVER={SQL Server};SERVER=POLIVEIRA-PC\\MSSQLSERVER2008;DATABASE=dbname;UID=userid;PWD=password")
    try: cnxn = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)
    except Exception as e:
        raise SystemExit('Error: Conexion Base de Datos SQL %s' % e)

And if the problem is a remote connection in this links they talk about it

https://blogs.msdn.microsoft.com/walzenbach/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008/

http://akawn.com/blog/2012/01/configuring-sql-server-2008-r2-express-edition-for-remote-access/

Hope this works

answered Jun 9, 2017 at 20:20

Camilo Velasquez

1631 gold badge2 silver badges11 bronze badges

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

Comments

thebjorn · Accepted Answer · 2015-01-25 01:38:54Z

0

You need to create a login with SQL Server authentication and then connect with this user:

enter image description here

(you'll need to connect this login to a user etc., but that's unrelated to logging in), then use

import pymssql
pymssql.connect(host=r"POLIVEIRA-PC\MSSQLSERVER2008", user='logintest', password='secret', database='database_name')

don't use the _mssql module directly.

answered Jan 25, 2015 at 1:38

thebjorn

27.6k12 gold badges107 silver badges152 bronze badges

4 Comments

POliveira Over a year ago

I've created a new login connection and then, when I tested that login via SQL Server Management Studio interface, I got this error

POliveira Over a year ago

When trying to connect using pymssql, I got a similar error as before:

Traceback (most recent call last):   File "<stdin>", line 1, in <module>   File "pymssql.pyx", line 639, in pymssql.connect (pymssql.c:10246) pymssql.InterfaceError: Connection to the database failed for an unknown reason.

thebjorn Over a year ago

That is a SQL Server setup issue (you need to configure your sql server to accept connections over tcp/ip).

POliveira Over a year ago

let me thank you for trying to help me first of all. However, I did configure MS SQL Server to accept connections over tcp/ip, as described here and I'm still unable to perform a successful connection to the database using the newly created user.

Collectives™ on Stack Overflow

Unable to connect to MSSQL Server database using Python

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related