Invalid value specified for connection string attribute

Question

import pyodbc as po    

connection_string = """
driver=ODBC Driver 17 for SQL Server;
server=SHADOW-LN4F5NUO;
database=FBI_Crime_Data;
trusted_connection=True;
"""

connection = po.connect(connection_string)

Output:

Traceback (most recent call last):
  File "C:\Program Files\JetBrains\PyCharm 2019.3.1\plugins\python\helpers\pydev\pydevd.py", line 1434, in _exec
    pydev_imports.execfile(file, globals, locals)  # execute the script
  File "C:\Program Files\JetBrains\PyCharm 2019.3.1\plugins\python\helpers\pydev\_pydev_imps\_pydev_execfile.py", line 18, in execfile
    exec(compile(contents+"\n", file, 'exec'), glob, loc)
  File "C:\Users\Jack\Desktop\GitHub\FBI_Crime_Data_Analysis\main.py", line 133, in <module>
    import_file(file_location="data/ASR122016.TXT")
  File "C:\Users\Jack\Desktop\GitHub\FBI_Crime_Data_Analysis\main.py", line 122, in import_file
    export_dataframe_to_SQL_Server(df=df, table_name=table_name)
  File "C:\Users\Jack\Desktop\GitHub\FBI_Crime_Data_Analysis\main.py", line 40, in export_dataframe_to_SQL_Server
    connection = po.connect(connection_string)
pyodbc.OperationalError: ('08001', "[08001] [Microsoft][ODBC Driver 17 for SQL Server]Invalid value specified for connection string attribute 'trusted_connection' (0) (SQLDriverConnect)")

Server name is correct.
Database name is correct
ODBC driver exists.
Windows Authentication in SSMS works.

Beni Iorga · Accepted Answer · 2024-05-29 22:18:41Z

10

According to this microsoft documentation, recognized values are yes and no

pyodbc documentation says, you can use your Windows account for authentication instead of username/password by providing Trusted_Connection attribute as: Trusted_Connection=yes

So it looks the connectionstrings sql server main page shows you the connection string for .Net libraries. If you click on any of the pages for a specific version of the driver under "ODBC drivers" (like 17 or 13 or 11), it shows Trusted_Connection=yes

edited May 29, 2024 at 22:18

Beni Iorga

33 bronze badges

answered Feb 10, 2020 at 5:10

Emrah Diril

1,7651 gold badge20 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

NoName Over a year ago

I don't think capitalization matters, but yes worked. true and sspi didn't work. Any idea why that may be the case? Does it depend on the SQL Server version?

NoName Over a year ago

Thanks for the hint, I know the problem now. It's not really a typo, just terrible, confusing web design. See this screenshot: i.imgur.com/VX43VWX.png

NoName Over a year ago

What's worse, the URL just says connectionstrings.com/sql-server. Not even telling you that you're viewing a connection string for .Net

Emrah Diril Over a year ago

Good catch! I updated my answer to include your finding.

Hari kumar Reddy · Accepted Answer · 2021-09-13 09:21:20Z

1

For [ODBC Driver 17 for SQL Server] driver
It should be like 
connection_string = "
driver=ODBC Driver 17 for SQL Server;
server=SHADOW-LN4F5NUO;
database=FBI_Crime_Data;
trusted_connection=yes;
"""

For [SQL SERVER DRIVER]
It should be like :

connection_string = "
driver=SQL SERVER DRIVER;
server=SHADOW-LN4F5NUO;
database=FBI_Crime_Data;
trusted_connection=True;
"

answered Sep 13, 2021 at 9:21

Hari kumar Reddy

112 bronze badges

Collectives™ on Stack Overflow

Invalid value specified for connection string attribute

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related