— New online course!  Getting Started with Azure DevOps — Special launch price 50% off —

SqlCmd Error: Login timeout expired. Error code 0x2AF9

by

I ran into a really weird problem on one of my machines while trying to connect to SQL Server using SqlCmd. SqlCmd commands have been running just fine on all my machine for month if not years and suddenly on just one machine, SqlCmd commands started failing with the following error:

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x2AF9.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.

Just in case you’re wondering, this is on a couple different Macs running Big Sur talking to a SQL Server container. Since the errors are related to login, it’s not a problem with the T-SQL itself. Using a sample command that attempts to drop a database, here are a couple of variations that DON’T work (on just this one machine):

  • sqlcmd -Q “drop database if exists [my-database]” -d master -U sa -P Pa\$\$word
  • sqlcmd -S localhost -Q “drop database if exists [my-database]” -d master -U sa -P Pa\$\$word

The first failing command assumes that the database is local because it doesn’t specify the ‘-S’ argument. The second one explicitly set the server name using the ‘-S” argument to localhost. Both of these give the exact same error message.

Here’s a version that DOES work:

  • sqlcmd -S 127.0.0.1 -Q “drop database if exists [code-generator]” -d master -U sa -P Pa\$\$word

It works and that seems absolutely bonkers because the only thing that I changed was ‘-S localhost’ to ‘-S 127.0.0.1’.

No clue why this fixes the problem but it does. I burned a whole lot of hours trying to figure this out.

I hope this helps.

-Ben

One Response to "SqlCmd Error: Login timeout expired. Error code 0x2AF9"

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: