There's an address in our connection strings that looks like a server name but isn't one: ag1-listener.sd.local. We'd seen it for years. It never mattered. When we switched to SQL Auth, it mattered a lot.
For context: the app had always used Windows Authentication to connect to SQL Server. One flag, no credentials, nothing to manage. When services moved to Linux containers on EKS, that stopped working — Linux has no Kerberos domain context. We needed SQL Auth: explicit username and password. Store credentials in AWS Secrets Manager, fetch at runtime, substitute into the connection string.
Seemed mechanical. It worked locally. It worked across every integration environment. In production-like testing, every connection failed with the same error:
Login failed for user 'svc_app'.
First thing to check — whether the login actually exists on the node you're connected to:
SELECT name FROM sys.server_principals
WHERE name = 'svc_app';
Empty result set. The login wasn't there. But it existed in every other environment. That's when it got interesting.
The connection string that caused it
The app is multi-tenant. Each client has its own database. Connection strings are stored centrally. When a request arrives, the app reads the stored connection string, injects credentials fetched at runtime, and connects.
A stored connection string looks like this:
data source=ag1-listener.sd.local;
initial catalog=ClientDatabase;
user id={{UserId}};
password={{Password}}
The credentials come from Secrets Manager at runtime. The question is: which secret do you fetch?
In lower environments, the stored addresses were direct server names — sql-dev-01, sql-dev-02. One secret per server:
/dev/sql-dev-01_credentials → { "userId": "svc_app", "password": "..." }
/dev/sql-dev-02_credentials → { "userId": "svc_app", "password": "..." }
When the connection string said sql-dev-01, the provider fetched the matching secret. Clean.
In the production-like environment, the stored connection strings said ag1-listener.sd.local.
And ag1-listener.sd.local is not a server name.
What a listener actually is
An Availability Group listener is a virtual DNS name that always points to whoever is currently the primary SQL Server node in a cluster. Today it routes to sql-server-01. After a failover, it routes to sql-server-02. The app never has to change anything — the listener handles it transparently.
That's the design. The listener exists specifically so you don't know or care which physical server you're on.
SQL Auth breaks this.
With Windows Authentication, your identity was a domain account with rights on every SQL Server node. "Which server?" was irrelevant — the same Kerberos token worked everywhere.
With SQL Auth, credentials are server-level objects. A SQL Login is created on a specific SQL Server instance. If the login exists on sql-server-01 but not sql-server-02, it authenticates on one and fails on the other.
So when we tried to extend the per-server credential approach:
/prod/sql-server-01_credentials → works if listener routes here
/prod/sql-server-02_credentials → works if listener routes here
We needed to know which server the listener was pointing to before choosing which secret to fetch. But to find that out, you have to connect. To connect, you need credentials. To get credentials, you need to know which server the listener points to.
That's a circle. You can't break into it.
Why lower environments never showed this
Local: If you're running locally, your connection string provider likely short-circuits to Integrated Security=True against localhost. No SQL Auth, no listener, no Secrets Manager. Zero signal.
Integration environments: No Availability Groups. Single standalone SQL Server instances. Direct server names in the connection strings. Per-server credential lookup works exactly as designed. Still zero signal.
Production-like environment: Real Availability Groups. Real listeners in the connection strings. SQL Auth enabled. All three conditions together for the first time. The bug had always been there — it just had nowhere to show up.
The conversation with the DBA
When it broke, we spent time in the application code first. The provider looked correct. The secret naming looked right. It took the DBA explaining AG architecture for the root cause to land.
"The listener is intentionally abstract. After any failover it points to a different physical server. Any credential mapping that depends on knowing the server name will break the moment a failover happens."
So per-server credentials were the wrong model entirely, not just broken in this one case.
"The SQL login needs to exist on all nodes with the same username and password. Then it doesn't matter which node the listener routes to — the credential is always valid."
That reframed the whole problem. The listener answers where do I connect? The credential needs to answer how do I authenticate? — independently of where. Those two concerns have to be separated.
The fix
A SQL Login is a server-level object. It has to be created on each SQL Server instance individually — it doesn't replicate automatically across AG nodes.
Create the login on every node in the cluster:
-- Run on each node in the Availability Group
CREATE LOGIN [svc_app] WITH PASSWORD = 'your-password';
Then inside each database on that node, create the corresponding user and grant access:
USE [ClientDatabase];
CREATE USER [svc_app] FOR LOGIN [svc_app];
EXEC sp_addrolemember 'db_datareader', 'svc_app';
EXEC sp_addrolemember 'db_datawriter', 'svc_app';
Store the credentials once — one secret, fetched at runtime, applied to every connection:
{
"userId": "svc_app",
"password": "..."
}
The connection string template stays the same. At runtime:
data source=ag1-listener.sd.local;
initial catalog=ClientDatabase;
user id=svc_app;
password=...;
MultiSubnetFailover=Yes
The listener routes to whichever node is currently primary. That node has the login. Authentication succeeds. If a failover happens and the listener routes to a different node, same result — the login is there too.
The scenario that still breaks it
One gap remains after this is working.
A new SQL Server cluster gets added to the environment. DB-ops provisions it, adds databases, starts assigning clients to it. But nobody ran CREATE LOGIN on the new nodes.
The login exists on every older node. Not on the new cluster. Clients on the new cluster fail with the exact same error. Everyone else is fine.
From the outside it looks like a data problem. Some clients broken, some not. Seemingly random. The diagnosis is one query on the suspected node:
SELECT name FROM sys.server_principals
WHERE name = 'svc_app';
-- Empty = login not created on this node
Fix: run CREATE LOGIN and CREATE USER on each new node before any clients land there. Simple operation. The risk is forgetting it exists.
What would have caught this earlier
One question during design: "What format are the connection strings stored in production?"
Answer: listener addresses.
That single fact collapses the per-server credential model immediately. You can't map credentials to a virtual DNS name — you need credentials that work on whatever physical server the name resolves to. That conversation should have happened in the design meeting. It happened in production testing instead.
The lower environments used standalone servers with direct names, so everything appeared to work. If production-like connection strings had been used in integration testing — or if the DBA had been in the design review — this would have been an hours-long problem. It wasn't.
Infrastructure assumptions tend to be invisible until the environment changes. Windows Auth worked everywhere because the domain handled it silently. SQL Auth makes every assumption explicit. The assumption that you can map credentials to a server name was always there — it just never mattered until the listener made "server name" mean something ambiguous.
Until next time,
Deepanshu
Backend engineer writing about production bugs, distributed systems, and engineering patterns learned the hard way.
Top comments (0)