Reputation: 1002
I try to connect to MS-SQL database on the Azure cloud from Python code like bellow.
import pyodbc
connect_str = "Driver={ODBC Driver 17 for SQL Server};" + \
"Server={server},1433;".format(server='tcp:ipaddress.database.windows.net') + \
"Database={database};".format(database='mydb') + \
"uid={uid};".format(uid='myuserid') + \
"pwd={pwd};".format(pwd='secretpswd') + \
"Encrypt=yes;TrustServerCertificate=no;"
cnxn = pyodbc.connect(connect_str)
I get error:
pyodbc.InterfaceError: ('28000', "[28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'myuserid'. (18456) (SQLDriverConnect)")
I tried removing port number from Server definition in connection string. Also I tried without option Encrypt and TrustServerCertificate. All the time it shows the same error.
I tried to connect with the same credentials using Management Studio and it worked.
Could you specify what I'm doing wrong?
Upvotes: 4
Views: 6830
Reputation: 1002
Ok so I managed to find out what was wrong.
My password contained some values that weren't escaped properly.
Firstly if value for connection string option contains ';' it should be escaped with curly braces.
So I needed to replace:
"pwd={pwd};".format(pwd='secretpswd')
-string pwd=secretpswd
with
"pwd={{{pwd}}};".format(pwd='secretpswd')
- string pwd={secretpswd}
Also if password contains any of curly braces it should double it. It can be done like
pwd = 'password_with_curly_braces_{}'
pwd = pwd.replace('}', '}}').replace('{', '{{')
Upvotes: 3