none
sqlcmd - connecting to SQL Server Express 2017 instance

    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.



    Friday, November 10, 2017 3:18 PM

All replies

  • 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

    Friday, November 10, 2017 10:31 PM
  • Thanks for the reply. I only have one account so I know I am logged in as the same user I installed mssql as. I will check out the error log though to see if there is any more details information.

    Say that I am not sure what I entered for sa user and windows login isn't working. Is my only option to uninstall/reinstall mssql? Thanks again for the help.

    Saturday, November 11, 2017 2:02 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?

    Do you know what user name the \Template Data\master.mdf uses by default?

    Saturday, November 11, 2017 2:27 AM
  • trying giving the password in the command 

    sqlcmd -S .\SQLEXPRESS -U sa -P <password>

    also if the your windows account is a  sql login try this

    sqlcmd -S .\SQLEXPRESS -E


    Thanks, Nithin

    Saturday, November 11, 2017 7:51 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

    Saturday, November 11, 2017 9:45 AM