Sqlcmd ошибка подключения

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.

Patrick Hofman's user avatar

asked Feb 22, 2015 at 15:48

rickepm's user avatar

10

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)

answered Feb 22, 2015 at 15:55

marc_s's user avatar

marc_smarc_s

734k176 gold badges1332 silver badges1460 bronze badges

1

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"

Martijn Pieters's user avatar

answered Mar 23, 2020 at 9:12

kartik chawda's user avatar

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

answered Jan 18, 2021 at 15:03

Turner's user avatar

TurnerTurner

931 silver badge9 bronze badges

1

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

answered Feb 22, 2015 at 17:50

rickepm's user avatar

rickepmrickepm

1131 gold badge1 silver badge6 bronze badges

Источник

When I try to run sqlcmd (from the cmd prompt) I get the following error:

HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or in
stance-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 i
f 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.

Not sure why this happens? I can connect to the sql server from within my asp.net website (which runs locally, like the database) but sqlcmd can’t connect.

  • sql-server
  • sqlcmd

marc_s's user avatar

marc_s

734k176 gold badges1332 silver badges1460 bronze badges

asked Oct 25, 2010 at 7:17

meds's user avatar

medsmeds

21.7k37 gold badges163 silver badges314 bronze badges

6

  • Have you provided your credentials?

    Oct 25, 2010 at 7:19

  • Can you show us the parameters for SQLCMD that you’re using??

    Oct 25, 2010 at 9:07

  • Can you show us the parameters for SQLCMD that you’re using??

    Oct 25, 2010 at 9:08

  • All I’m doing is typing in sqlcmd from cmd.exe..

    Oct 25, 2010 at 9:16

  • SQLCMD.exe -S SERVER -d DATABASE -U USER -P PASSWORD

    Oct 25, 2010 at 9:43

2 Answers

If you are not using the default instance, the try then following command:

sqlcmd -S MACHINENAME\INSTANCE_NAME

Notice the capital S. Also notice that I did not give it a user of password, given that default sqlcmd authentication is the windows auth, so I can log in as the current user.
For more information from Microsoft check this out > http://msdn.microsoft.com/en-us/library/ms165702.aspx

Community's user avatar

answered Mar 7, 2014 at 1:53

dev4life's user avatar

dev4lifedev4life

10.8k6 gold badges60 silver badges73 bronze badges

2

  • thank you for pointing out the case-sensitivity of the argument flag

    Sep 4, 2015 at 21:10

  • Not sure if this will help anyone, but I tried the machine name and it did not work for me — I had to use «localhost». I have TCP/IP enabled (for local development), and had to use «localhost» instead of the actual name (seems that the TCP/IP listener was listening only on some IPv6 addresses [which my machine name resolves to], and the loopback address [127.0.0.1] as well, which was the only thing that worked [IPv6 bug?]).

    Mar 9, 2017 at 1:45

Try sqlcmd -S <InstanceName>

  • Your command will work only if you have installed the server as default instance.
  • Run sp_helpserver to know the instance name.

answered Oct 25, 2010 at 11:56

anivas's user avatar

anivasanivas

6,4376 gold badges37 silver badges45 bronze badges

Источник

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.

Patrick Hofman's user avatar

asked Feb 22, 2015 at 15:48

rickepm's user avatar

10

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)

answered Feb 22, 2015 at 15:55

marc_s's user avatar

marc_smarc_s

734k176 gold badges1332 silver badges1460 bronze badges

1

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"

Martijn Pieters's user avatar

answered Mar 23, 2020 at 9:12

kartik chawda's user avatar

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

answered Jan 18, 2021 at 15:03

Turner's user avatar

TurnerTurner

931 silver badge9 bronze badges

1

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

answered Feb 22, 2015 at 17:50

rickepm's user avatar

rickepmrickepm

1131 gold badge1 silver badge6 bronze badges

Источник
  • Remove From My Forums

  • Question

  • I just installed SQL Server Express 2017. I am trying to use ‘sqlcmd’ to connect to the instance. Here is what I have tried:

    C:\Windows\system32>sqlcmd -S .\SQLEXPRESS
    Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user ‘
    test\test’..

    C:\Windows\system32>sqlcmd -S .\SQLEXPRESS -U sa
    Password: Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed
    for user ‘sa’..

    C:\Windows\system32>sqlcmd -S .\SQLEXPRESS -U sa
    Password: Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed

    Any idea why this isn’t working? Do I have to use a specific user or password. I am logged into the same user in Windows that I installed SQL Server Express on.

    Sorry, I don’t know much about this, just trying to get the installation up and running and confirm that I can access the instance. Thanks.

    • Edited by

      Friday, November 10, 2017 3:58 PM

Answers

  • When you installed SQL Server, you should have been asked to specify a Windows account to be the admin account. There is a button to add current user. But if you are now logged into Windows with a different account from what you specified at install time,
    you can not log into SQL Server with that Windows user, with less than you created login for it in SQL Server.

    If you try to log in as sa, you need to specify the same password as you specified when you installed SQL Server. Provided that you selected SQL authentication at all — by default you only get Windows authentication.

    In the SQL Server errorlog you can find more detailed information about the login failures. The error log is in C:\program files\microsoft sql server\mssql14.sqlexpress\mssql\log\errorlog

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by
      Ed Price — MSFTMicrosoft employee
      Tuesday, February 12, 2019 6:47 AM

  • As a side note the SQL Server instance service would not start. Because of that I read a technote online that said to try to copy the \Template Data\master.mdf to the \DATA folder overwriting the master.mdf in the DATA folder. Could doing this have overwritten
    whatever username I set up during the installation?

    If you replace the master database with another master database, you lost everything you had in the master database. That includes logins, as well as information about all databases.

    You will have to employ the procedures decribed in this topic
    Connect to SQL Server When System Administrators Are Locked Out in Books Online to be able to connect to your instance. You will also have to re-attach all your databases.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Proposed as answer by
      Lin LengMicrosoft contingent staff
      Monday, November 13, 2017 5:55 AM
    • Marked as answer by
      Ed Price — MSFTMicrosoft employee
      Tuesday, February 12, 2019 6:47 AM

Источник

Hi everybody

I have a SQL 2014 EXPRESS server. I want to schedule automatic backups using the SQL Server Maintenance Solution by Ola Hallengren. In order to do this I have created a CMD script which is to be used by Task Scheduler. However, I am having some trouble connecting
to the SQL server using the command sqlcmd in the command prompt. 

For starters I’m just trying to directly connect through the prompt using the command 

sqlcmd -S 12.0.2269

Where 12.0.2269 is the IP of my server. I have also tried connecting using the name of the server, and I have also tried connecting using specific ports, which have been opened in the firewall (both inbound and outbound), e.g.

sqlcmd -S 12.0.2269,1433

In addition, I have ticked off the field that says «Allow remote connections to this server» in the SQL Server Management Studio. The server I am trying to connect to is «located» on the machine I am working on, so I don’t quite get what
is wrong. I hope you can help. When I connect using the command

sqlcmd -S 12.0.2269,1433

I get the following error messages

C:\Users\Thomas>sqlcmd -S 12.0.2269,1433Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : TCP Provider: 
Et forsøg på at oprette forbindelse mislykkedes, fordi den part, der havde oprettet forbindelse, 
ikke svarede korrekt efter en periode, eller en oprettet forbindelse blev afbrudt, fordi værten ikke svarede.





  



                       
  





.
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login timeout expired
.
Sqlcmd: Error: Microsoft ODBC Driver 11 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..
C:\Users\Thomas>

When connecting using the server name, these are the errors I am getting

C:\Users\Thomas>sqlcmd -S ./MUUTO270813
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Named Pipes Provider: Could not open a connection to SQL Server [3]. .
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 11 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..

C:\Users\Thomas>

I hope you can help :)

Источник

Понравилась статья? Поделить с друзьями:
  • Sqlstate 08001 ошибка sql server 17
  • Sql1042c произошла непредвиденная системная ошибка sqlstate 58004
  • Sqlstate 01000 ошибка sql
  • Sql создать ошибку
  • Sql сервер ошибка 4064