I ran into something interesting today.
If I use a string with 30 characters in it, I'll be able to connect to a sql-server database. But the second I go to a 31 character string I'll receive a,
sqlalchemy.exc.OperationalError: (OperationalError) (18456, "Login failed for user
Example code:
database_type = "mssql+pymssql"
user_name = "readonly"
user_pass = "j2AZDPBi3ITWnegddQS2xy9HPDLMzXh"
database_uri = "server.domain"
connection_string = r"{0}://{1}:{2}@{3}/Logging".format(
database_type,
user_name,
user_pass,
database_uri,)
print(connection_string)
engine = sa.create_engine(connection_string, echo=True)
connection = engine.connect()
Now that strange part is that if I connect to the database using SQL Server Management Studio I can connect with the larger string.
Why does the password length matter for one and not the other?
I'm using
SQLAlchemy==0.9.7
pymssql==2.1.0
SQL Server 2014
After hitting the same issue myself today, this is caused by a hard limit in freetds <= 0.91. It has been patched in the current version on github, but most distributions still have 0.91 or older.
tsql is not affected by this limit, as it uses the protocol directly, while most libraries that uses the API has a limit on 20 or 30 bytes for the username and password. Removing all traces of the old library and rebuilding freetds from source before reinstalling pymssql solved the issue here.
It can also be useful to set os.environ['TDSVER'] = '7.0' before issuing statements to get the correct TDS version, as pymssql apparently ignores the setting in freetds.conf