3

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

thebjorn
  • 26,297
  • 11
  • 96
  • 138
POliveira
  • 196
  • 5
  • 14
  • In your screenshot you're using windows authentication, while your code is using SQLserver authentication... Is you server set up for this? – thebjorn Jan 25 '15 at 01: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 Jan 25 '15 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 Jan 25 '15 at 15:50

2 Answers2

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

Camilo Velasquez
  • 153
  • 2
  • 9
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.

thebjorn
  • 26,297
  • 11
  • 96
  • 138
  • I've created a new login connection and then, when I tested that login via SQL Server Management Studio interface, I got [this error](http://imgur.com/fqrGdT9) – POliveira Jan 25 '15 at 11:11
  • When trying to connect using pymssql, I got a similar error as before: `Traceback (most recent call last): File "", line 1, in File "pymssql.pyx", line 639, in pymssql.connect (pymssql.c:10246) pymssql.InterfaceError: Connection to the database failed for an unknown reason.` – POliveira Jan 25 '15 at 11:19
  • That is a SQL Server setup issue (you need to configure your sql server to accept connections over tcp/ip). – thebjorn Jan 25 '15 at 15:20
  • 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](http://stackoverflow.com/questions/2388042/connect-to-sql-server-2008-with-tcp-ip) and I'm still unable to perform a successful connection to the database using the newly created user. – POliveira Jan 25 '15 at 21:22