Connecting to local SQL Server instance

Question

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 1

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.

Answer 2

Yes, 1433 s the default.

This works fine for me:

library(RODBC)
dbconnection <- odbcDriverConnect("Driver=ODBC Driver 11 for SQL Server;Server=server_name; Database=db_name;Uid=; Pwd=; trusted_connection=yes")
initdata <- sqlQuery(dbconnection,paste("select * from MyTable;"))
odbcClose(channel)

Answer 3

Try connecting on the default SQL server port, which is 1433, not 5000. And check that you can connect to the correct instance using SQL mgmt studio.

Answer 4

I had a similar issue, with the following error:

_mssql.MSSQLDatabaseException: (18456, b"Login failed for user
'script_svc'.DB-Lib error message 20018, severity 14:\nGeneral SQL Server
error: Check messages from the SQL Server\nDB-Lib error message 20002,
severity 9:\nAdaptive Server connection failed\n")

The user I had established was a local user on the machine.

The solution for me was putting ".\" in front of the username and it then recognized it as a local user and allowed the query to work.

Your results may vary.. but I thought I would mention it.

Answer 5

Have you tried using pyodbc instead?

import pyodbc
cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER=SERVERNAME;DATABASE=testdb;UID=me;PWD=pass')
cursor = cnxn.cursor()
cursor.execute("select user_id, user_name from users")
rows = cursor.fetchall()
for row in rows:
    print row.user_id, row.user_name

Don't forget to add the ODBC driver to your Windows. Go to: Control Panel > Systems and Security > Administrative Tools > ODBC Data Sources

Either the 32-bit or 64-bit version depending on your computer.

Then you click on the System DNS file. If you do not see any MySQL driver you have to click ADD. It brings up a list, from that list select the MySQL driver.

For me, it was ODBC Driver 13 for SQL Server. Click finish. Once you do that then you have to change your connection line in your code to the corresponding Driver that you just filled out.

Source: pyodbc + MySQL + Windows: Data source name not found and no default driver specified