CODEWITHSUNDEEP
pythonsql-serverodbcpyodbc
notjoshno
notjoshno

Reputation: 357

PYODBC can't connect to SQL Express instance

I'm running an instance of SQL Server Express 2017 on my computer. I am attempting to connect to it with the following connection string:

"Driver=ODBC Driver 13 for SQL Server;Server=COMPUTERNAME\\SQLEXPRESS,1433;Database=databasename;Uid=testuser;Pwd=testpassword;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;"

I have successfully connected to the server manually using those connections and SQL Server Authentication using SQL Server Management Studio.

Firewall shouldn't be an issue as it's a locally hosted server, but I've opened port 1433 for inbound and outbound connections anyway.

This connection string format has worked in the past with a database hosted on Azure (which I can no longer afford to do).

The error message:

pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 13 for SQL Server]TCP Provider: No connection could be made because the target machine actively refused it.\r\n (10061) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 13 for SQL Server]Login timeout expired (0); [08001] [Microsoft][ODBC Driver 13 for SQL Server]Invalid connection string attribute (0); [08001] [Microsoft][ODBC Driver 13 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (10061)')

Any more suggestions?

Upvotes: 0

Views: 1581

Answers (1)

jonathano88
jonathano88

Reputation: 21

I'm going to answer this question in case someone else finds it, even though it's old. What worked pretty well for me was:

  1. Finding out the port used by the SQL Express instance (which in my case turned out to be good 'ol 1433)
  2. Using Network Library=DBMSSOCN to avoid issues regarding named instances (couldn't make it work that way)

How?

Doing it this way: pyodbc.connect('driver={SQL Server Native Client 11.0};server='+DBIPAddress+';port='+DBPort+';Network Library=DBMSSOCN;database='+DBName+';uid='+DBUser+';pwd='+DBPass)

Where

  • DBIPAddress is the IP address of the SQL Server server
  • DBPort is the port
  • DBName is the name of the database you're trying to connect to
  • DBUser is the username you're using
  • DBPass is the password

I guess you could use {SQL Server} driver also, but I haven't tried. What we're doing is forcing a connection using the IP and port regardless of the existence of a named instance.

Upvotes: 2

Related Questions