0

I created a user call root and the password is test in MSSQL but the error occur after I run the command sqlcmd -S laptop-3l55biaa\test\MSSQLSERVER01 -U root -P test

not working either in SSMS

error info

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. .
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
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.
Mike Slinn
  • 7,705
  • 5
  • 51
  • 85
cathacker
  • 21
  • 1
  • 5
  • 1
    As it says in the error message, it cannot find the Sql Server with the name that you have given. Severnames usually only have one or two parts, not three. – RBarryYoung Apr 02 '21 at 16:23

1 Answers1

2

I'm not sure if the server/instance name is valid. From the docs, the argument for the -S option has the format [protocol:]server[\instance_name][,port], so I'm not sure what the test part is in your URL. From experience (if I remember correctly), the URL should be something like machineName\MSSQLSERVER01, or if on local host just .\MSSQLSERVER01.

Update

Regarding .\instancename - it looks like that is some additional functionality that SSMS has (and probably ADS) for connecting to instances on the same machine as the client. Sqlcmd doesn't seem to support it (my bad), and instead of the . you have to type localhost.

To sum up, in order to connect to a local instance listening on the default 1433 port you can just use -S localhost or (in case the port is not default or you want to use the name) -S localhost\instanceName.

np_6
  • 514
  • 1
  • 6
  • 19
  • tried `sqlcmd -S .\MSSQLSERVER01 -U root -P test` same error – cathacker Apr 02 '21 at 16:27
  • 1
    @cathacker Try replacing the "." with your laptop name. – RBarryYoung Apr 02 '21 at 16:30
  • (not sure if that's the case) if `MSSQLSERVER01` is the default instance name, then `-S.` should work. – np_6 Apr 02 '21 at 16:32
  • I replaced "." with my laptop name which made command till `sqlcmd -S LAPTOP-3L55BIAA\MSSQLSERVER01 -U root -P test`, but another error `Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed for user 'root'..` – cathacker Apr 02 '21 at 16:44
  • 1
    @cathacker, check the SQL Server error log for more details about the login failed error. Perhaps the instance isn't configured to allow SQL authentication. – Dan Guzman Apr 02 '21 at 16:49
  • Ok, that would mean that `sqlcmd` was able to reach the server, but the login/pass aren't valid. You can connect with some other credentials and execute `SELECT * FROM sys.server_principals WHERE name = 'root'` to check if there is a login with that username. If there is, then it's probably an invalid password parameter. Or as @DanGuzman pointed out, it might be that SQL authentication is disabled - in order to check that, you can follow these instructions https://www.mssqltips.com/sqlservertip/2191/how-to-check-sql-server-authentication-mode-using-t-sql-and-ssms/ – np_6 Apr 02 '21 at 16:51
  • I did set the authentication to windows and sql authentication, back to the error log, it show `Login failed for user 'root'. Reason: An attempt to login using SQL authentication failed. Server is configured for Integrated authentication only. [CLIENT: ] ` – cathacker Apr 02 '21 at 16:56
  • 1
    @cathacker, you need to restart the SQL Server service for the config change to become effective. – Dan Guzman Apr 02 '21 at 16:57
  • @cathacker Integrated authentication would mean that only Windows authentication is enabled - https://stackoverflow.com/questions/8441158/an-attempt-to-login-using-sql-authentication-failed – np_6 Apr 02 '21 at 16:58
  • Well, good job guys, my problems is perfectly fixed now after I restart the service(actually I just reboot my computer cause restart service doesn't seem working) – cathacker Apr 02 '21 at 17:02