SQL Server Network Interfaces: Connection string is not valid [87]

Question

When I am running this from cmd on my SQL Server 2008 instance:

sqlcmd -U sa -S mymachinen_name\MSSQLSERVER

(where MSSQLSERVER is my instance name)

I get prompt for password and after that I get this:

Password:

HResult 0x57, Level 16, State 1 SQL Server Network Interfaces: Connection string is not valid [87].

Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : 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..

Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

I have my SQL Server browser service started and also I can login through Management Studio with Windows authentication.

Also found this KB article, but this is for SQL Server 2005/2000.

Whisler

possible duplicate of Microsoft SQL Server Native Client 10.0 Login timeout expired – Mell

Thanks .. one more thing .. 'Sa" should be Window authentication user or Sql server user?? – Whisler

sa is the SQL Server default admin account (which is usually disabled now. – Mell

whats the password of this account .. – Whisler

It is set on installation, there is no default. Try to log on without a username. – Mell

thanks ..I can have a different user .. point is should it has to be sql user or windows user .. as i told I login to studio using windows authentication .. shall it be that user?? – Whisler

It is preferred to use the Windows account indeed. – Mell

hi .. actually confused.. but then you asking me not to give any user .. The issue is I am running this before: sqlcmd -U sa -S mymachine_name , and was not able to login .. now I am running this as you said ..sqlcmd -S mymachine_name I am able to login .. so where is the user in this case so where is the question of window authentication.. the window authentication option I see when I login through studio – Whisler

It takes your current account. There is no need for username / password, just like in SSMS. – Mell

also can you please tell me how this will identify on which instance I want to run the sql scripts and on which database @Echo Off FOR /f %%i IN ('DIR *.Sql /B') do call :sqlscripts %%i GOTO :END :sqlscript Echo Executing %1 SQLCMD -S localhost -i %1 Echo Completed %1 :END – Whisler

Answer 1

Typically, the MSSQLSERVER service is the service of a un-named default instance.

Therefore, try this:

sqlcmd -U sa -S mymachine_name

(without specifying any instance name - just the machine name)

Trattoria

Why oh why do they not allow you to use this name?? I would think both using this name and not using it should work. Oh well, thanks for being a life saver! – Cozen

Answer 2

I encountered this error trying to connect to the server through Jenkins and resolved it this way:

sqlcmd  -e -S "SERVER_NAME,PORT_NUMBER" -U %USER% -P %PASSWORD% -i "D:\\test.sql"

Imbrue

Answer 3

Please note that when connecting via sqlcmd to another server, if the target server is part of a HA cluster group, you will want to specify -M. Also, you will want to not specify the DB that is using the AG. (this is useful for installing jobs via sqlcmd to remote locations, among other things)

sqlcmd -S SERVERNAME,1433 -d msdb -Q "SELECT job_id FROM msdb.dbo.sysjobs WHERE (name = 
N'RANDOM_JOB_NAME')" -M

Gilmour

The reason I mention this is because I encountered the same error that the OP encountered - that the connection string was invalid(87). For myself the reason this error was being thrown was relative to trying to connect to a node of an AG to update a JOB. – Gilmour

Answer 4

It is set on installation, there is no default. Try to log on without a username

Whisler