SQL Server Connection Errors: SQL in Sixty Seconds #030

SQL Server connection errors can stop you before your first query runs. This episode covers checks for a new installation. Start with the exact error message and the instance you intended to reach.

I created this video to accompany my connection error troubleshooting article. It walks through common connection problems. The recording uses older tools, but checking the failing connection step still matters.

Start with the Failed Connection

1. Confirm the server and instance name, then check that the intended SQL Server service is running.
2. Check the protocol and listening port. Compare a local connection with the failing remote connection.
3. For a network failure, inspect the required firewall rule and address resolution.
4. For a named instance, check whether the client uses SQL Server Browser or an explicit port.
5. If the client uses an alias, verify its destination before changing it.
6. For a login or certificate error, investigate that error separately from network reachability.

These are checks, not instructions to change every setting. Keep firewall access limited to the required port and trusted clients. Keep encryption enabled while correcting certificate problems.

What Each Failure Looks Like

I triggered seven failures against a local SQL Server 2025 instance with sqlcmd and a 5 second login timeout. Each cause produced a different message. The table lists what the client printed.

The first two failures look alike. Both took about six seconds, because of the login timeout. A closed port did not return a quick refusal on my PC. It timed out. So a long wait that ends in a timeout points at the name or the port, not at the login.

The Login Messages Say Little on Purpose

The client message for a bad password is only Login failed for user. The server knows the real reason and keeps it in the ERRORLOG. After my tests it listed three different reasons, one for each login failure. Read it from a working session.

EXEC master.dbo.xp_readerrorlog 0, 1, N'Login failed';

The entries said Password did not match that for the login provided. They also said Failed to open the database specified in the login properties and The account is disabled. Each ended with the client address. That one query turns three identical-looking failures into three fixes.

Find the Port and the Name

When you can connect locally, ask the instance where it listens. My instance listens on port 1455 on every address. A second port on the loopback address belongs to the dedicated administrator connection, and a third serves Service Broker. The server name comes back in the form machine and instance.

SELECT @@SERVERNAME AS ServerName, SERVERPROPERTY('InstanceName') AS InstanceName;
SELECT ip_address, port, type_desc, state_desc FROM sys.dm_tcp_listener_states ORDER BY port;

Then test both spellings from the command line. The instance name and the explicit port both returned the same server. If the name fails and the port works, check three things. Is the SQL Browser service running? Does a firewall rule block its port? Is the instance name spelled correctly?

sqlcmd -S .\SQLDEV -E -C -Q "SELECT @@SERVERNAME"
sqlcmd -S tcp:localhost,1455 -E -C -Q "SELECT @@SERVERNAME"

The -C switch trusts the server certificate, which my lab instance needs. Without it, the connection failed with the untrusted certificate message. Use the switch on a lab machine, and install a trusted certificate on a real server.

Repair a Default Database Error

Error 4064 means the login’s default database is gone or off limits. The login itself is fine. Connecting with -d master worked, and so did a normal connection after I changed the default.

ALTER LOGIN AppLogin WITH DEFAULT_DATABASE = master;

Find the Matching Error

The first two articles below cover network connection failures. The SQLCMD and default-database articles address different parts of a connection. The dedicated administrator connection article covers a narrower scenario.

Related Reading

• Named Pipes error 40: could not open a connection
• TCP error 10061: connection refused
• Connect with Windows Authentication using SQLCMD
• Dedicated administrator connection for SQL Server Express
• Error 4064: cannot open the default database

A connection error is not a verdict on the server, it is a message that names the step that failed.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.