1

I have installed the sql server 2017 for non production environment in my PC. It has installed successfully. when connect using sqlcmd it was throwing an error login failed for login sa. it didn't have the asked for the creating user or anythings... please find the command used to connect

sqlcmd -S servername -U sa

Update:

I can't even log in using windows authentication.

Update2:

I have tried to access sqlcmd from command prompt i got the following error.

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Named Pipes Provider: Could not open a connection to SQL Server [2]. . Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired. Sqlcmd: Error: Microsoft ODBC Driver 13 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..

enter image description here

  • 2
    Have you added the password?-P password – Stijn Wynants Jul 18 '18 at 8:20
  • No...it didn't ask me any thing – Smart003 Jul 18 '18 at 8:26
  • 2
    Did you enable mixed mode authentication? Does sqlcmd -E - S server work, as it uses integrated Windows authentication? – vonPryz Jul 18 '18 at 8:27
  • it gave me this error when tried Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user – Smart003 Jul 18 '18 at 8:36
  • 1
    You need to read Sql Server's errorlog file for more details. Look in C:\Program Files\MSSQL15.MSSQLSERVER\MSSQL\Log or something akin, depending on where and how the installation was done. – vonPryz Jul 18 '18 at 9:41
0

It sounds like one of the following things is happening:

  • The sa account is disabled
    • either explicitly, or because you're in Windows Authentication mode
  • You don't know the password to the sa account

Since this is installed locally on your PC, you should actually be able to connect with sqlcmd without any parameters to use "Trusted Connection" to authenticate you:

sqlcmd

Once you're in, you should be able to check the authentication mode using an approach like this one:

SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')   
WHEN 1 THEN 'Windows Authentication'   
WHEN 0 THEN 'Windows and SQL Server Authentication'   
END as [Authentication Mode]

If that returns "Windows Authentication," then you'll need to go through the process of switching to mixed mode outlined here: Change Server Authentication Mode

If the query above returned "Windows and SQL Server Authentication" then it's possible that you have just mistyped or forgotten the sa account password, or it's disabled for some other reason. In that case, you can run this code (also included in the docs link above):

ALTER LOGIN sa ENABLE;  
GO  
ALTER LOGIN sa WITH PASSWORD = '<enterStrongPasswordHere>' ;  
GO
0

Don't forget to check if Mixed mode is enabled or not?

Here is how to solve it:

  • Login to the MSSQL Server Management Studio with Windows Authentication.

  • In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.

  • Under the Server Properties, select a page of "Security".

  • Select the Server authentication as "SQL Server and Windows Authentication mode" and click Ok.

  • Restart the SQL Services and then try to login with 'sa' details.

(OR)

By using Windows authentication and run the following query:

ALTER LOGIN [sa] WITH PASSWORD='newpassword', CHECK_POLICY=OFF
GO
ALTER LOGIN [sa] ENABLE
GO

The key here is CHECK_POLICY=OFF. That made the trick. I hope this will make 'sa' immune to future changes in their domain setup.

  • I can't even log into server with my windows log in – Smart003 Jul 19 '18 at 10:08
0

The first thing to do in your case it to check SQL Server error log.

Your error is not "server ... was not found" but login failed. This means the authentication was tried but failed. And every failed authentication is written to SQL Server error log.

You can open it using any editor, even Notepad.

You can find it in its default location (I don't think you've changed it while installing server), depending on version the path is smth like this:

C:\Program Files\Microsoft SQL Server\MSSQL11.SQL_2012\MSSQL\Log

You should open the file ERRORLOG without extension, it's the last one. Scroll to the end, in some of last rows you'll find the cause of your "login failed"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.