You fire up your .NET application, it connects to SQL Server without a hitch. Queries run, data flows, everything’s fine. Then you open SQL Server Management Studio to run a quick ad-hoc query — and you’re greeted with:
Cannot open user default database. Login failed.
Same server. Same machine. Same Windows login. The app works; SSMS doesn’t. What gives?
The Two Paths to a Database
When your application opens a SQL Server connection, its connection string tells SQL Server exactly which database to use:
Server=myserver;Initial Catalog=MyDatabase;Integrated Security=True;
That Initial Catalog (or Database) keyword means the connection lands directly in MyDatabase. Your login doesn’t need a working default — the app never asks for it.
SSMS, by default, takes a different route. When you type in just the server name and hit Connect, SSMS connects to SQL Server first, then SQL Server looks up your login’s default database and tries to switch to it. If that database is offline, dropped, or you no longer have access to it — the login fails before the Object Explorer even appears.
Why This Breaks Overnight
This typically isn’t something you changed. Common scenarios:
- A DBA ran a security audit and restricted access to
masteror some shared default database. - A development database was dropped or taken offline, and it happened to be your login’s default.
- A server migration or failover changed login-to-database mappings.
- A new provisioning script set every new login’s default to a database that doesn’t exist yet.
You’ll notice it the next morning. Your app is fine. SSMS is dead. Colleagues start pinging you — “hey, is SQL Server down?”
The One-Line Fix
In the SSMS connection dialog, click Options >>, then enter the database name:
- SSMS 19+: Connection Properties tab → “Connect to database” field
- SSMS 18 and earlier, or if the field isn’t visible: use the Additional Connection Parameters tab and add:
Initial Catalog=YourDatabaseName
That’s it. This forces SSMS to bypass your login’s default database entirely, just like your application does.
Fix It Permanently
If you have the ALTER LOGIN permission (or can ask a DBA), fix the default database at the source:
ALTER LOGIN [DOMAIN\Username] WITH DEFAULT_DATABASE = YourDatabase;
Replace YourDatabase with a database that exists and that your login can access (or set it to master if nothing else is available).
The Deeper Takeaway
This is a classic example of two clients using the same server but taking slightly different connection paths. The app specifies Initial Catalog explicitly. SSMS relies on a server-side default. When the default breaks, SSMS breaks — and the app looks smarter than it actually is.
Lesson: always be explicit about your target database. In connection strings, in SSMS, and in your automation scripts. Defaults are convenient until they aren’t.
Have you run into this or a similar head-scratcher? Drop a comment below or reach out to the TekOnline team.
Leave a Reply