DB Connection String Works Local but Not on Server - Fixes

1. Authentication Type Mismatch — Windows vs SQL Server Auth

This is the #1 reason I see this in the wild. Your local dev machine is probably using Integrated Security=true (Windows Authentication) because you're logged in as a domain user that has SQL access. But on the server, the application runs under a different account — maybe Network Service, IIS AppPool, or a local service account.

Had a client last month whose entire print queue died because their ASP.NET app used Integrated Security on a server that ran under IIS APPPOOL\DefaultAppPool. That account had zero access to SQL Server.

The Fix

1. Check the connection string in your web.config or appsettings.json. If it says Integrated Security=true or Trusted_Connection=true, that's Windows Auth.
2. Switch to SQL Server Authentication: User Id=myUser;Password=myPass;. Create a SQL login on the server.
3. If you must keep Windows Auth, grant permissions to the specific account the app runs under. Use whoami on the server to find it.

Local (works):
Server=.;Database=MyDB;Integrated Security=true;

Server (broken):
Server=.;Database=MyDB;Integrated Security=true;

Fixed server:
Server=.;Database=MyDB;User Id=sqlUser;Password=Pass123;

2. Server Name or Instance Resolution

Locally, Server=. or Server=localhost works because it's the same box. On the server, you often need the actual server name or IP. But here's the trap: if the SQL Server is a named instance, like SQL2019, the connection string needs Server=MyServer\SQL2019. If it's a default instance, use just the server name or IP.

Also, the SQL Server Browser service must be running for named instances. Saw a small business lose half a day because they installed SQL Server with a named instance and never started the Browser service.

Checklist

1. On the server, open SQL Server Configuration Manager.
2. Go to SQL Server Network Configuration > Protocols for [Instance]. Ensure TCP/IP is Enabled.
3. Restart the SQL Server service after enabling TCP/IP.
4. Verify the port: Default instance uses 1433. Named instances use a dynamic port unless you set a static one. Check in SQL Server Configuration Manager > TCP/IP > IP Addresses > IPAll > TCP Dynamic Ports.
5. If using a named instance, include it: Server=PRODSRV\SQLEXPRESS

// Correct for named instance:
Server=PRODSRV\SQLEXPRESS;Database=MyDB;User Id=user;Password=pass;

// Correct for default instance with static port:
Server=PRODSRV,1433;Database=MyDB;User Id=user;Password=pass;

3. Firewall Blocking the SQL Port

This one's obvious but often missed. Your local machine talks to SQL Server on the same machine — no firewall between them. From another server, the Windows Firewall on the SQL box might be blocking port 1433 (or whatever port your instance uses).

Had a client who moved their app to a new server and couldn't connect. Turned out the old server had firewall rules from years ago, the new one didn't. Quick telnet test showed the port was closed.

Test and Fix

1. From the app server, open a command prompt and run: telnet SQLServerIP 1433
2. If it hangs or says 'Could not open connection', the firewall is blocking it.
3. On the SQL Server, open Windows Firewall with Advanced Security. Create an inbound rule for TCP port 1433 (or your instance's port).
4. If using a named instance with dynamic ports, either set a static port or enable SQL Server Browser and allow UDP port 1434 in the firewall.

# Test connectivity from app server
telnet 192.168.1.100 1433

# If telnet not installed, use Test-NetConnection in PowerShell:
Test-NetConnection -ComputerName 192.168.1.100 -Port 1433

Quick tip: If you're still stuck, check the SQL Server error log. On the SQL box, run EXEC xp_readerrorlog in SSMS. Look for 'Login failed' messages — they tell you exactly which account tried to connect and why it failed.

Quick-Reference Summary Table

These three causes cover about 90% of cases where a connection string works locally but bombs on the server. Start with authentication — it's the one that trips up everyone, including me a few times. Then check the server name and firewall. If those don't fix it, you might be dealing with a driver version mismatch or a DNS issue, but that's less common.