使用pymssql连接Azure SQL Server失败但tsql可正常连接

Let’s break down your issue first: you can successfully connect via tsql only when specifying the -D database_name flag, but pymssql throws a 20002 "Adaptive Server connection failed" error even with matching credentials. Since you only have permissions to access database_name, failing to set it as the initial database causes the connection to fail—this is the key clue here.

Here are targeted fixes to get pymssql working:

1. Force Encrypted Connection (Critical for Azure SQL) #

Azure SQL Database requires encrypted connections by default, but older versions of pymssql might not enable this automatically. Add the encrypt=True parameter to your connect call:

pymssql.connect(
    server='server.database.windows.net',
    port=1433,
    user='username@server',
    password='password',
    database='database_name',
    encrypt=True
)

2. Verify Server Parameter Format #

Some users report issues when splitting server and port into separate parameters. Try combining them into a single server string:

pymssql.connect(
    server='server.database.windows.net:1433',
    user='username@server',
    password='password',
    database='database_name',
    encrypt=True
)

3. Add Charset and Login Timeout #

Encoding mismatches or short timeouts can sometimes trigger this error. Add these parameters to rule out such cases:

pymssql.connect(
    server='server.database.windows.net',
    port=1433,
    user='username@server',
    password='password',
    database='database_name',
    encrypt=True,
    charset='utf8',
    login_timeout=30
)

4. Upgrade pymssql to the Latest Version #

Older pymssql releases have known compatibility quirks with Azure SQL. Update to the stable latest version using pip:

pip install --upgrade pymssql

Why This Works #

Your tsql command succeeds because -D explicitly sets the initial database you’re authorized to access. When pymssql fails, it’s typically due to one of these issues:

• Missing encrypted connection (Azure blocks unencrypted attempts entirely)
• Failure to properly pass the database parameter as the initial connection context
• Compatibility bugs in older library versions

Since you don’t have access to Azure’s default master database, the connection can’t establish unless it immediately targets database_name with the correct security settings.

内容的提问来源于stack exchange，提问作者Joe