Cannot connect to server after creating a logon and user

Question

I've been having troubles in creating new users on the SQL Server. Those were the steps I took:

1. Connect to the server via Windows authentication
2. Access Security > Logons > Create Logon
3. Create a new Logon to use SQL Server authentication
4. In each database, create new users (same name as logon). Those users were set Owned Schemas and Membership as db_datareader and db_datawriter
5. Close the connection and connect by the user created

After that process, I get the message:

It is not possible to connect to DESKTOP-QMQIHKL:

A connection with the server was successfully establishes, but during the logon process an error occurred. (provider: Shared Memory Provider, error: 0 - There is not a process in the other extreme of the pipe). (Microsoft SQL Server, Error: 233).

I've tried to overcome this error message by:

1. Opening SQL Server Configuration Manager and enable the TCP/IP protocol.
2. Opening Windows Services and executing both, SQL Server Browser and SQL Server (MSSQLSERVER).

None of that have worked. For that reason, I would like your help in solving this problem.

Answer 1

Have you enabled SQL Server authentication mode? By default, SQL Server is configured to use exclusively Windows authentication mode, so it will not accept your SQL Server authentication logins.

For changing the accepted authentication modes, see this article: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/change-server-authentication-mode?view=sql-server-ver15