本地正常的内网站点部署IIS后遇SqlException登录失败求助

Hey there, I know this permission issue can feel super frustrating especially when it works perfectly locally but breaks once deployed to IIS—let’s walk through practical, beginner-friendly steps to fix this:

1. Check Your IIS Application Pool Identity #

The key difference between running locally and on IIS is the account your app uses to execute:

• Open IIS Manager, find the Application Pool tied to your site.
• Right-click it → Advanced Settings, look for the Identity field (defaults are often ApplicationPoolIdentity or Network Service).
• Since you’re using Windows Authentication for SQL, this pool identity needs access to your SQL Server. For quick testing:
  • Click the ... next to Identity → Select Custom account → Enter DOMAIN\username and its password (make sure this account exists on the server’s domain, not just your local machine).

2. Verify SQL Server Login Permissions #

Even if the account works locally, it might not have the right access on the server’s SQL instance:

• Open SQL Server Management Studio (either remotely if allowed, or directly on the server).
• Expand Security → Logins:
  • If DOMAIN\username isn’t listed, right-click Logins → New Login → Choose Windows Authentication, enter the account, then go to User Mapping to grant it permissions (like db_datareader or db_datawriter) for your target database.
  • If it already exists, double-check the User Mapping permissions and confirm the Status tab has "Login: Enabled" checked.

3. Rule Out Remote Connection Mix-Ups #

Your remote access to the server doesn’t directly cause this error, but watch for this common pitfall:

• Make sure DOMAIN\username is an account from the server’s domain (not your local machine’s domain). If your local PC and server are in separate domains, this account won’t be recognized. In that case, switch to SQL Server Authentication (update your connection string to use a SQL-specific username/password) or use a local server account (e.g., SERVER-NAME\LocalUsername).

4. Validate Your Connection String #

Double-check that your deployed app uses the correct connection string:

• Open your web.config (or appsettings.json for .NET Core) and locate the connection string:
  • If using Windows Authentication: Ensure it includes Integrated Security=True (no User ID/Password fields).
  • If switching to SQL Authentication: Update it to something like Server=YOUR-SQL-INSTANCE;Database=YOUR-DB;User ID=SQL-USER;Password=SQL-PASS; and make sure SQL Server allows Mixed Mode Authentication.

5. Test the Connection Directly on the Server #

To isolate the issue, test the account’s SQL access right on the server:

• Open Command Prompt on the server and run:

  sqlcmd -S YOUR-SQL-INSTANCE -E -d YOUR-DATABASE
  

  (Replace placeholders with your actual SQL instance and database name.)
• If that fails, try running SSMS as the problematic account:

  runas /user:DOMAIN\username "ssms.exe"
  

  If this can’t connect, the issue is with the account’s SQL permissions, not your IIS setup.

Don’t stress—permission issues when moving from local development to IIS are extremely common, so you’re not alone in this. Take it one step at a time, and you’ll get it sorted!

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