SQLAlchemy PyODBC MS SQL Server DSN-less connection

Question

Using python 2.7 and the MS odbc driver via pyodbc. The connection string looks like this:

MSSQL + pyodbc: // MyUser: mypass @ ServerIP / instance_name driver = ODBC + driver + 11 + for + SQL + server

I get login unsuccessfully. However, if I use the same credentials and "serverip \ instancename" in the Microsoft SQL Server Management Studio, I can connect.

The thing that drives me crazy a couple of days ago, this same connection string worked for me, but for a different sql server instance on the same machine. So I'm trying to figure out how to solve the problem.

Thanks for any pointers.

Answer 1

I just went through this, the reason for its failure is that the port, each instance is listening on its port, so you need to specify which port is for this instance.

my code is:

DBserver='host.abc.nt' DBengine='devenv' DBport='####' DBname='mydb' DBDriver='ODBC Driver 13 for SQL Server' DBuser='user' DBpwd='pass' DBuserpass = DBuser if len(DBuser) > 0 else '' DBuserpass = DBuserpass + ':' + DBpwd if len(DBuser) > 0 else '' if len(DBengine) > 0: DBserver = DBserver + '\\' + DBengine engine = sa.create_engine(f'''mssql+pyodbc://{DBuserpass}@{DBserver}:{DBport}/{DBname}?driver={DBDriver}''') query=engine.execute('SELECT DB_NAME(), @@SERVERNAME') query.fetchall() [('mydb', 'host\\devenv')] 

After you create the engine, you will not need other libraries except sqlalchemy

but if you want to know the instance port you can use: Github repository from gordthompson / sqlserverport

or on Windows, you connect to SQL Studio (or something else), select the database, and in the meantime, run netstat in CMD to get the port:

 C:\Users\pla>netstat -qfa | findstr host TCP 10.0.0.1:##87 host.abc.nt:4096 ESTABLISHED 

Answer 2

When learning sqlalchemy, I had the same problem. This method worked for me:

 import urllib params = urllib.quote_plus("DRIVER={SQL Server Native Client 10.0};SERVER=dagger;DATABASE=test;UID=user;PWD=password") engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params) 

What from sqlalchemy documentation. This doesn't look like login credentials, but the error message was the same for me, and it worked.

ref http://docs.sqlalchemy.org/en/latest/dialects/mssql.html#pass-through-exact-pyodbc-string