Sql server authentication fails using sqlcmd

Question

I just did an install of MS SQL SERVER 2019 on Windows 10.

I created a test sql authentication login (login name: test)

When I try to connect from the command line (from either cmd or powershell) the connection fails with the following error message: Password: Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed for user 'test'..

sqlcmd -U test -P password

--or--

sqlcmd -U test (and fill in the password at the prompt)

If I don't specify login credentials, it uses my windows credentials and the connection works.

What do I need to do to be able to get sqlcmd to login with one of the sql authentication logins I create?

thanks

Answer 1

You need to set the Server Authentication to SQL Server and windows Authentication mode, to do that:

1. In SSMS Object Explorer right click on Server Node and click properties

2. In the left pane, click on Security

3. In the Server Authentication select the SQL Server and windows Authentication mode
4. Click Ok
5. In SSMS Object Explorer right click on Server Node and click stop
6. In SSMS Object Explorer right click on Server Node and click start
7. Run the command again and it will work.