SQL Server: Login failed for user 'testing'-CodePudding

This occurs on a new installation of SQL Server 2017 when attempting to log in and issue queries from cmd via sqlcmd.

The problem occurs with ANY local or domain accounts. However I am now focusing on a single local account named 'testing', with a relatively easy to type password of 'test'.

C:\sqlcmd -S WIN-CERULEAN-22 -U testing
Password:

Entering the correct (or incorrect) password yields:

Password: Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server: Login failed for user 'testing'..

If I change the Server string to something that doesn't exist, I get a login timeout failure (as expected).

The account 'testing' has sysadmin role assigned.

The 'testing' account is granted permission to connect to database engine, and Login is enabled.

The SQL Server instance has been restarted multiple times throughout troubleshooting. The host OS (Server 2022) was restarted at least once to rule that out.

I've compared every user setting with this new installation against a known good/working installation and am unable to see any differences. I am able to login on the known good/working SQL Server instance with a local account there without issue.

What am I missing here to cause SQL Server to reject a known good password?

edit - @RossBush with the save. Needed to change this setting to SQL Server and Windows Authentication. Made that change, restarted SQL Server, and am able to log in to the 'testing' account.

CodePudding user response：

When users who have been properly provisioned against a SQL Server database can't connect, one thing to always check is if mixed mode authentication is enabled. To check this value, select the instance and bring up the properties dialog and select properties. The available values under "Server authentication" are:

Windows Authentication mode
SQL Server and Windows Authentication mode

If you want users to be able to connect using SQL Server logins, make sure that the second option is checked.

If this is the case then after the .. in this error you posted -> "SQL Server: Login failed for user 'testing'..", you should see verbiage to along the lines of "if using a sql server login then make sure mixed mode authentication is enabled".

CodePudding user response：

You appear to be typing a hostname instead of a full SQL instance name. I cannot see what the full name of your SQL instance in your screenshots, but that's your problem.

Syntax should be something like:

sqlcmd -S WIN-CERULEAN-22\INSTANCE_NAME -U testing