Windows环境下Python连接SQL及Azure DB失败问题求助

Hey there, let’s work through your two SQL connection headaches one by one—these are super common issues, so I’ve got actionable fixes for both!

This error usually stems from network configuration mismatches or driver compatibility issues between your Python setup and the SQL server. Here’s what to check step by step:

• Enable TCP/IP for your SQL Server
  Open the SQL Server Configuration Manager, head to SQL Server Network Configuration > Protocols for [Your Server Name]. Make sure TCP/IP is turned on, then restart the SQL Server service to apply changes. Without TCP/IP enabled, remote connections (including from Python) will get blocked.

• Tweak FreeTDS settings (if using pymssql)
  pymssql relies on the FreeTDS library to communicate with SQL Server. Outdated or misconfigured FreeTDS often causes this error. Try adding the tds_version parameter directly to your connection call (use 7.4 for modern SQL Server versions):

  conn = pymssql.connect(host='your-server-address', user='your-username', password='your-pass', database='your-db', tds_version='7.4')
  

  If that doesn’t work, locate your freetds.conf file (typically in C:\Program Files\FreeTDS\etc on Windows) and set the global tds version to 7.4.

• Verify firewall and port access
  Ensure the Windows Firewall on your SQL Server machine allows incoming traffic on port 1433 (the default SQL Server port). If connecting to a remote server, confirm the network firewall isn’t blocking this port either.

• Double-check authentication
  For Windows Authentication, make sure your Python process has permission to access the server (try running your script as an admin if needed). For SQL Server Authentication, verify your username/password are correct and the account has remote connection privileges.

Looking at your code, there are syntax errors plus Azure-specific quirks causing the failure. Let’s fix this:

First, clean up the code syntax issues #

Your script defines connPDW but then tries to use an undefined conn variable—this will throw a NameError immediately. Here’s the corrected base code:

import pandas as pd
import pymssql

# Fill in your actual password and database name!
connPDW = pymssql.connect(
    host=r'dwprd01.database.windows.net,1433',  # Add port 1433 for Azure
    user=r'your-sql-admin@dwprd01',  # Azure SQL doesn't support domain accounts for SQL auth
    password='your-actual-password',
    database='your-target-database',
    tds_version='7.4'
)

connPDW.autocommit(True)
cursor = connPDW.cursor()

sql = """SELECT TOP (10) * FROM TableName"""
cursor.execute(sql)

Now, address Azure-specific requirements #

1. Use the correct authentication format
   Your original code uses internal\admaaron—a Windows domain account won’t work with Azure SQL’s standard SQL authentication. Instead, use the SQL admin account you created when setting up the Azure SQL Server (format: admin-username@your-server-name).

2. Add the port number to your host
   Azure SQL requires explicit port 1433 in the host string. Append ,1433 to your server address like in the example above.

3. Whitelist your IP in Azure’s firewall
   Azure blocks all incoming IPs by default, even if you can connect to a local SQL Server. Go to your Azure SQL Server in the Azure Portal, navigate to Firewall and virtual networks, and add your current public IP to the allowed list.

4. For Azure AD authentication (if needed)
   pymssql has limited support for Azure AD. If you must use an Azure AD account, switch to pyodbc instead. Here’s a quick example:

   import pyodbc
   
   conn = pyodbc.connect(
       'DRIVER={ODBC Driver 17 for SQL Server};'
       'SERVER=dwprd01.database.windows.net,1433;'
       'DATABASE=your-db;'
       'UID=admaaron@yourdomain.com;'
       'PWD=your-password;'
       'Authentication=ActiveDirectoryPassword'
   )
   

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