在IIS 8.5 Server 2012R2上配置Flask应用使用Trusted Connection连接数据库

Hey there! Let's work through getting that Trusted Connection (Windows Authentication) working for your first Flask app—no need to stress, we'll break it down step by step.

First, Fix Up Your app.py Code #

Looking at your code, there are a few small issues that might be blocking progress or hiding useful error info:

1. Start the server properly
   You have app.run at the end, but it needs parentheses to execute: app.run(debug=True) (add debug=True for easier testing, but remember to remove it in production).

2. Pass template variables correctly
   Your render_template_string call is missing the keyword argument name. Change it to:

   return render_template_string(template, con=con, username=username)
   

   And update your template to show meaningful info (right now you're trying to display the connection object, which isn't helpful):

   template = """
   <h1>Current User: {{ username }}</h1>
   <p>Database Connection: {{ 'Success!' if con else 'Failed' }}</p>
   """
   

3. Get detailed error messages
   Your conn() function just returns False on failure—add error logging to see exactly what's going wrong:

   def conn():
       try:
           # Updated driver for better compatibility (replace if you need the old one)
           conn = pyodbc.connect(
               "Driver={ODBC Driver 17 for SQL Server};"
               "Server=MSSQL01;"
               "Database=FP;"
               "Trusted_Connection=Yes;"
           )
           return conn
       except Exception as e:
           print(f"Connection error: {str(e)}")  # Check terminal/logs for this
           return False
   

   Note: I removed the redundant Description=FP from the connection string—SQL Server doesn't need that.

Configure IIS & web.config for Trusted Connection #

Since you mentioned modifying web.config, I assume you're deploying to IIS. Here's what you need to get Windows Authentication working:

1. Enable Windows Authentication in IIS #

• Open IIS Manager, navigate to your Flask app's site.
• Go to Authentication in the Features View.
• Disable Anonymous Authentication and enable Windows Authentication.

2. Update web.config #

Make sure your config includes these sections (adjust paths to match your setup):

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
  <system.webServer>
    <!-- Enable Windows Auth -->
    <security>
      <authentication>
        <anonymousAuthentication enabled="false" />
        <windowsAuthentication enabled="true" />
      </authentication>
    </security>
    
    <!-- Configure FastCGI for Flask -->
    <handlers>
      <add name="Python FastCGI" path="*" verb="*" modules="FastCgiModule" scriptProcessor="C:\Path\To\Your\Python.exe|C:\Path\To\wfastcgi.py" resourceType="Unspecified" requireAccess="Script" />
    </handlers>
  </system.webServer>

  <appSettings>
    <!-- Point to your Flask app instance -->
    <add key="WSGI_HANDLER" value="app.app" />
    <!-- Path to your app's folder -->
    <add key="PYTHONPATH" value="C:\Path\To\Your\Flask\App\Folder" />
    <!-- Optional: Log location for debugging -->
    <add key="WSGI_LOG" value="C:\Path\To\Logs\wfastcgi.log" />
  </appSettings>
</configuration>

3. Set Up the Application Pool Identity #

The IIS application pool runs under a Windows account—this account needs access to your SQL Server instance and FP database:

• In IIS Manager, go to Application Pools, select your app's pool.
• Right-click > Advanced Settings.
• Under Process Model > Identity, change it to an account that has:
  • A SQL Server login (in SSMS, go to Security > Logins, add the account).
  • Permissions on the FP database (assign db_datareader/db_datawriter or appropriate roles).

Local Testing Notes #

If you're testing locally with app.run(), Flask will use your current Windows user account to connect to SQL Server. Make sure:

• Your user account has a SQL Server login and FP database permissions.
• The SQL Server instance MSSQL01 is reachable from your machine (try pinging it, or connecting via SSMS with Windows Auth first to confirm).

Final Checks #

• Restart IIS after making config changes.
• Check the WSGI log or Flask terminal for error messages (the updated conn() function will print details if the connection fails).
• Verify that REMOTE_USER is populated (this comes from Windows Auth, so it should show your domain/username if everything works).

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