The login's default database was dropped or detached after the login was created — common after cleanup of dev/test databases while service accounts still reference them.. The default database was renamed (e.g., from 'SalesDB' to 'SalesDB_v2') but the login's metadata still points to the old name.. The default database is set to a database that exists but the login has no CONNECT permission on it — happens when a DBA revokes access without updating the default.. The database is offline, in single-user mode, or in the middle of a restore operation, making it temporarily unreachable for the login.. An availability group failover moved the primary replica and the default database is not part of the AG or is not yet synchronized on the new primary.. An automated deployment script recreated the database (drop + create) and the login's default DB reference became stale during the brief window the database did not exist.

Step 1: Identify the broken default database. Run on master: SELECT name, default_database_name FROM sys.server_principals WHERE default_database_name NOT IN (SELECT name FROM sys.databases); — this returns all logins whose default DB does not exist.. Step 2: If you cannot connect normally because your own login has the problem, use sqlcmd with an explicit database override: sqlcmd -S servername -U loginname -P password -d master — the -d flag bypasses the default database check.. Step 3: Change the login's default database to an existing, accessible database: ALTER LOGIN [loginname] WITH DEFAULT_DATABASE = [master]; — master is always available and is the safest fallback.. Step 4: If the login should access a specific database, grant access there: USE [targetdb]; CREATE USER [loginname] FOR LOGIN [loginname]; ALTER ROLE db_datareader ADD MEMBER [loginname]; — adjust the role to match the required permission level.. Step 5: For ADF or Power BI connections, update the connection string to include Initial Catalog=targetdb explicitly. This overrides the server-side default and prevents 4064 even if the default database changes again in the future.. Step 6: In Azure SQL Database, verify the login exists in the target database by connecting to it directly: SELECT name FROM sys.database_principals WHERE name = 'loginname'; — Azure SQL requires a contained database user or a server-level login mapped to each database.. Step 7: After the fix, trigger a manual refresh or re-run the failed ADF pipeline to confirm the connection succeeds. Check sys.dm_exec_sessions to verify the login is now connecting to the intended database.

Medium severityauthentication

SQL Server Error:
4064

What does this error mean?

SQL Server raises error 4064 when a login attempts to connect and its configured default database is unreachable — either because the database was dropped, renamed, taken offline, or the login lacks permission to access it. The server rejects the connection before any query executes. In data-pipeline contexts this is especially disruptive: an ADF linked service, Power BI gateway connection, or SSIS connection manager that uses this login will fail immediately on open, not on the first query. The symptom is a hard connection failure — no partial results, no timeout, just an instant reject. Every downstream activity in the pipeline that depends on this connection fails with it.

Common causes

1The login's default database was dropped or detached after the login was created — common after cleanup of dev/test databases while service accounts still reference them.
2The default database was renamed (e.g., from 'SalesDB' to 'SalesDB_v2') but the login's metadata still points to the old name.
3The default database is set to a database that exists but the login has no CONNECT permission on it — happens when a DBA revokes access without updating the default.
4The database is offline, in single-user mode, or in the middle of a restore operation, making it temporarily unreachable for the login.
5An availability group failover moved the primary replica and the default database is not part of the AG or is not yet synchronized on the new primary.
6An automated deployment script recreated the database (drop + create) and the login's default DB reference became stale during the brief window the database did not exist.

How to fix it

1Step 1: Identify the broken default database. Run on master: SELECT name, default_database_name FROM sys.server_principals WHERE default_database_name NOT IN (SELECT name FROM sys.databases); — this returns all logins whose default DB does not exist.
2Step 2: If you cannot connect normally because your own login has the problem, use sqlcmd with an explicit database override: sqlcmd -S servername -U loginname -P password -d master — the -d flag bypasses the default database check.
3Step 3: Change the login's default database to an existing, accessible database: ALTER LOGIN [loginname] WITH DEFAULT_DATABASE = [master]; — master is always available and is the safest fallback.
4Step 4: If the login should access a specific database, grant access there: USE [targetdb]; CREATE USER [loginname] FOR LOGIN [loginname]; ALTER ROLE db_datareader ADD MEMBER [loginname]; — adjust the role to match the required permission level.
5Step 5: For ADF or Power BI connections, update the connection string to include Initial Catalog=targetdb explicitly. This overrides the server-side default and prevents 4064 even if the default database changes again in the future.
6Step 6: In Azure SQL Database, verify the login exists in the target database by connecting to it directly: SELECT name FROM sys.database_principals WHERE name = 'loginname'; — Azure SQL requires a contained database user or a server-level login mapped to each database.
7Step 7: After the fix, trigger a manual refresh or re-run the failed ADF pipeline to confirm the connection succeeds. Check sys.dm_exec_sessions to verify the login is now connecting to the intended database.

Example log output

Error: 4064, Severity: 11, State: 1.
Cannot open user default database. Login failed.
Login failed for user 'svc_adf_ingest'. (.Net SqlClient Data Provider)

Frequently asked questions

Why did 4064 suddenly appear without any changes?

The most common cause is that the default database was dropped, detached, or taken offline by someone else or by an automated maintenance job. Run: SELECT default_database_name FROM sys.server_principals WHERE name = 'loginname'; — if the result does not match any database in sys.databases, that confirms the cause.

Can I fix 4064 without access to SSMS?

Yes. Use sqlcmd with the -d flag to override the default database on connect: sqlcmd -S server -U login -P pass -d master. Once connected, run ALTER LOGIN [loginname] WITH DEFAULT_DATABASE = [master]; to fix it permanently. You can also use Azure Data Studio, the mssql extension in VS Code, or any client that lets you specify the database in the connection string.

Does Azure SQL Database have error 4064?

Yes. Azure SQL uses the same error code. The fix is the same — specify the database explicitly via Initial Catalog in the connection string. In Azure SQL, each database is more isolated than on-premises SQL Server, so always setting Initial Catalog explicitly is a good default practice.

Will retrying the connection fix error 4064?

No. Error 4064 is deterministic — if the default database is missing or inaccessible, every retry will fail with the same error. Retry logic in ADF or Power BI will exhaust all attempts without success. The only fix is to either change the login's default database or specify Initial Catalog in the connection string.

Source · learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-4064-database-engine-error