Connecting to SQL Server throws pyodbc.InterfaceError

Question

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?

Konrad Konrad · Accepted Answer · 2019-04-12T12:20:08

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('{', '{{')

Connecting to SQL Server throws pyodbc.InterfaceError

1 Answers