Morgan Thrapp Morgan Thrapp - 3 months ago 69
Python Question

Connecting to local SQL Server instance

I'm attempting to connect to a local instance of SQL Server running on my machine. I am able to connect to a local instance with this code from our server, but it fails on my local machine.

I've enabled named pipes and all the ips in the SQL Server configuration.

The code I'm using is as follows:

from pymssql import connect
server = r'.\SQLEXPRESS2014' # I've also tried MORGANT-PC\SQLEXPRESS and SQLEXPRESS2014
username = 'MyUserName'
password = 'MyPassword'
master_database_name = 'SuperSecretDatabase'
port = 5000
server_args = {'host': server, 'user': username, 'password': password,
'database': master_database_name, 'port': port} # I've tried having the first key be both host and server, because pymssql's docs are unclear on the difference.
master_database = connect(**server_args)


If I use the instance name, I get this error:

pymssql.InterfaceError: Connection to the database failed for an unknown reason.


I set the port to 5000 so that I could try connecting to it with

server = 127.0.0.1
port = 5000


which fails with the slightly different error message:

pymssql.OperationalError: (20009, b'DB-Lib error message 20009, severity 9:\nUnable to connect: Adaptive Server is unavailable or does not exist\nNet-Lib error during Unknown error (10035)\n')


I've read a bunch of answers here on SO, and most of them seem to indicate it's an issue with FreeTDS, but I'm on Windows 8.1, so I don't have FreeTDS.

I've tried connecting with sqlcmd with the host\instance name and that works fine. It also works in SSMS.

I've tried passing
.\SQLEXPRESS2014
to both the
host
and
server
parameter in
pymssql.connect()
and they both fail with the same aforementioned error.

I briefly tried using
adodbapi
, but I'm getting exactly the same error messages.

Answer

The solution ended up being a combination of things.

  1. I needed to disable all IPs other than 127.0.0.1.
  2. I needed to create C:\freetds.conf with the following text:

    [global] port = 1433 tds version = 7.0

  3. I needed to change the account my SQL instance logs in with to LocalSystem.