Error while trying to connect to sql server with python using pyodbc

Question

I'm trying to connect to sql server from a different computer. I'm using the following code. The odbc driver is installed and also pyodbc is installed.

import pyodbc 

server = 'SERVER'
database = 'DBNAME' 
username = 'SA' 
password = 'SQLPWD' 
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

However I'm receiving the following error.

Traceback (most recent call last):
File "test.py", line 7, in <module>
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL 
Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
pyodbc.InterfaceError: ('28000', "[28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL 
Server]Login failed for user 'SA'. (18456) (SQLDriverConnect); [28000] [Microsoft][ODBC 
Driver 17 for SQL Server][SQL Server]Login failed for user 'SA'. (18456)")

I'm not sure where I'm going wrong and the older questions have not been much helpful. I'm new to Python.

Personally I recommend not using the sa account for your application. Also find out what the real error is from the SQL Server logs and then post that in an edit. — Thom A
– Thom A ♦, Commented Oct 5, 2020 at 13:13
Check if your password, server address and db name are correct. Take a look on the sql server and check if the sa user is enabled and the connection mode is set to mixed mode (allow windows and other connection). Check qith sql configuration manager that you sql is opened on the port 1433 on the right ip address, also configure the firwall rules. — Carlo Zanocco
– Carlo Zanocco, Commented Oct 5, 2020 at 13:15
Does the password contain a semicolon (;) or curly brackets ({ or })? — Gord Thompson
– Gord Thompson, Commented Oct 5, 2020 at 13:15

Carlo Zanocco · Accepted Answer · 2020-10-06 10:03:06Z

Probably the problem is related to:

SA access disabled (REFERENCE)
Wrong Server Authentication Selection (REFERENCE)
SQL Server is not open for remote access (REFERENCE)

You can use SSMS to manage your server configuration.

IMPORTANT: As suggested by @Larnu is not a good idea use the sa user for remote access, create new user with limited access to the database & table is a better way to manage the server and secure it. Take a look here to better understand.

SA Access disabled

To enable it you have to do:

Connect to your server with SSMS (Using the Windows Authentication)
Open the Security --> Logins Section and double click on the user sa
Open the page General type a Password and Confirm Password, go in the page Status and set the Login on Enabled.

Wrong Server Authentication Selection

In the Object Explorer right-click on the server instance and open the Properties
Open the page Security and check the option SQL Server and Windows Authentication mode

SQL Server is not open for remote access

In the Object Explorer right-click on the server instance and open the Properties
Open the page Connections and check Allow remote connections to this server
Open your Start Menu and search for SQL Server Configuration Manager
Under the SQL Server Network Configuration select Protocols for
Double-Click on TCP/IP and croll to the buttom, set the TCP Port to 1433
In the left pane of SQL Server Configuration Manager click SQL Server Services, right-click SQL Server<instance_name>, and click Restart.

Now the issues should be solved, if not take a look on how to add a rule to windows firewall in the section Configure a Windows Firewall for Database Engine Access of the reference

Collectives™ on Stack Overflow

Error while trying to connect to sql server with python using pyodbc

1 Answer 1

SA Access disabled

Wrong Server Authentication Selection

SQL Server is not open for remote access

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

SA Access disabled

Wrong Server Authentication Selection

SQL Server is not open for remote access

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related