在Heroku平台使用Django结合Pyodbc连接SQL Server时遭遇TCP Provider错误求助

Alright, let's break down this frustrating connection error you're facing. That 0x2746 (10054) code translates to a TCP connection reset, which means the SQL Server side is closing the connection before it can fully establish—so we'll work through fixes starting from the network layer up to your app's configuration.

1. Verify SQL Server Network Configuration #

First, make sure your SQL Server is set up to accept remote TCP connections:

• Open SQL Server Configuration Manager on your server (or Azure portal if using Azure SQL):
  • Navigate to SQL Server Network Configuration > Protocols for [Your Instance] and ensure TCP/IP is enabled.
  • Double-click TCP/IP, go to the IP Addresses tab:
    • For all active IPs, set Enabled to Yes.
    • Under IPAll, confirm the TCP Port is set to 1433 (or your custom port—just make sure it matches your connection string later).
• If you're using a named instance (not the default), ensure the SQL Server Browser service is running—this helps route connections to the right instance port.

2. Fix Firewall & Network Access Rules #

Heroku dynos use dynamic outbound IP addresses, so your SQL Server's firewall/security group needs to allow traffic from Heroku's IP ranges.

• Get Heroku's current outbound IP ranges by running this command locally:

  heroku run curl https://api.heroku.com/vendor/v4/ips -H "Accept: application/vnd.heroku+json; version=3"
  

• Add all these IP ranges to your SQL Server's inbound firewall rules (for port 1433 or your custom port).
  • For on-prem SQL Server, update your Windows Firewall or cloud security group to allow inbound traffic from these ranges.
  • For Azure SQL Database, add the full CIDR blocks from Heroku's list to your Azure SQL firewall rules.

3. Validate Your Connection String #

A misconfigured connection string is a common culprit. Double-check these details:

• Use the correct format for Django's SQL Server pyodbc engine:

  # In your settings.py
  DATABASES = {
      'default': {
          'ENGINE': 'sql_server.pyodbc',
          'NAME': 'your_db_name',
          'USER': 'your_db_user',
          'PASSWORD': 'your_db_password',
          'HOST': 'your_sql_server_address',
          'PORT': '1433',
          'OPTIONS': {
              'driver': 'ODBC Driver 17 for SQL Server',
              # Add SSL settings if required by your SQL Server
              'extra_params': 'Encrypt=yes;TrustServerCertificate=no'
          },
      }
  }
  

• Key checks:
  • Ensure the driver name matches exactly what's installed on Heroku (ODBC Driver 17 for SQL Server—not an older version).
  • If your server uses a non-default port, make sure PORT is set correctly.
  • For SSL-enabled servers (like Azure SQL), include Encrypt=yes in the extra params.

4. Ensure Heroku Has the Required Dependencies #

Heroku's default Python buildpack doesn't include ODBC drivers, so you need to set up additional buildpacks:

1. Add the Heroku Apt buildpack to your app:

   heroku buildpacks:add --index 1 heroku-community/apt
   

2. Create an Aptfile in your project root with these lines:

   msodbcsql17
   unixodbc-dev
   libgssapi-krb5-2
   

   These packages install the ODBC Driver 17, development libraries, and Kerberos dependencies (often needed for SQL Server authentication).
3. Make sure pyodbc is listed in your requirements.txt file.

5. Test the Connection Directly on Heroku #

To rule out Django-specific issues, test the pyodbc connection directly in a Heroku dyno:

1. Run a Python shell on Heroku:

   heroku run python
   

2. Paste this code (replace placeholders with your actual DB details):

   import pyodbc
   try:
       conn = pyodbc.connect(
           "DRIVER={ODBC Driver 17 for SQL Server};"
           "SERVER=your_server_address,1433;"
           "DATABASE=your_db_name;"
           "UID=your_db_user;"
           "PWD=your_db_password;"
           "Encrypt=yes"
       )
       cursor = conn.cursor()
       cursor.execute("SELECT @@VERSION")
       print("Connection successful! SQL Server version:", cursor.fetchone())
   except Exception as e:
       print("Connection failed:", str(e))
   

If this fails, the issue is with network/driver config—not Django. If it works, check your Django settings.py for typos.

6. Check SQL Server Login Permissions #

Make sure your database user is allowed to connect remotely:

• In SQL Server Management Studio (or Azure Portal), go to your user's properties:
  • Under the General tab, confirm the user has access to your target database.
  • Under the Status tab, ensure Login is set to Enabled.
  • For SQL Server Authentication, make sure the user isn't restricted to local connections only.

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