PYODBC--Data source name not found. How to get valid connection String

Question

I am using SQL Server with Docker. I am trying to connect with pyodbc to my server but then I've got:

pyodbc.Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

Using DBeaver I got some details:

SELECT
    @@servername AS 'Server Name',  
    @@servicename AS 'Instance Name', 
    DB_NAME() AS 'Database Name',
    HOST_NAME() AS 'Host Name'

and this SQL returns:

193aeb2132e4, MSSQLSERVER, model, HDZKNV2

How my connection string should looks like cause this one does not work (and I guess any variation of this)?

'DRIVER={Microsoft Access Text Driver};
 SERVER=193aeb2132e4\MSSQLSERVER;
 DATABASE=model;
 Trusted_Connection=yes;
 User=admin;
 Password=passowrd'

Hi @marc_s Thanks for editing my topic - now it looks a way better! I've changed value for SERVER=193aeb2132e4 but I still got same exception. Does't work for localhost either. What else could be wrong? — Maciek Sienkiewicz
Thank you for the quick reply! After removing trusted_connection same exception occurs. damn! is there any third possible problem? I am sure I've Microsoft Access Text Driver on my machine (there are 4 more: SQL Server, ODBC Driver 17for SQL Server, Microsoft Excel Driver, Microsoft Access Driver) — Maciek Sienkiewicz
thanks a lot marc_s! I've changed driver to ODBC Driver 17 for SQL Server and changed user to uid, then password to pwd It seems I've got a connection now. Please move this response to a new post so I could accept this answer. — Maciek Sienkiewicz

marc_s marc_s · Accepted Answer · 2020-12-13T21:26:30

Several issues:

MSSQLSERVER is the internal instance name for the default, unnamed instance of SQL Server - to connect to it, just use the server name (or IP address) without adding MSSQLSERVER as the instance name
You should not have Trusted_Connection=yes (use currently logged in user's credentials) along side with explicit username/password - use one or the other, but not both together.
And according to the official Microsoft documentation on PyODBC - you should use DRIVER={ODBC Driver 17 for SQL Server} and UID/PWD (not User)

So try these settings:

DRIVER={ODBC Driver 17 for SQL Server};
SERVER=193aeb2132e4;
DATABASE=model;
UID=admin;
PWD=passowrd