SQL Server Installation: 2022 Developer Edition --- Basic

This series of articles are the SQL Server installation related. This article was originally purblished on 04/17/2023.

• SQL Server Installation: 2019 Developer Edition --- Custom 
  • A Fix To PolyBase Issue In SQL Server 2019 Developer Edition
• SQL Server Installation: 2022 Developer Edition --- Basic --- this article

As a developer, we always have a chance to install SQL Server Developer Edition for testing, and we might meet the same issues in each installation, I will screenshot the installation process here, with a brief discussion. Due to there are two mode for SQL Server installation:

• Basic
• Custom

We will discuss them respectively. This article is for Basic mode. The custom mode was discussed in the previous article. on 12/20/2021 for SQL Server 2019.

A. Introduction

This article will discuss the SQL Server Basic mode installation. The Custom mode is powerful and could include all features we might need. However, the installation and configuration will be more complex, while the Basic mode is essential.

The installation is simple, just straightforward. However, due to the Basic mode only including Windows Authentication by default, we have some work to do in configuration to use SQL Server Authentication mode. The content of this article,

• A - Introduction
• B - Installation of SQL Server 2022 Develop Edition - Basic Mode
• C - Change the Authentication Mode to Mixed --- Error 18456
• D - Enable SA (System Administrator) user Account --- Error 18470
• E - Add a New User

B. Installation of SQL Server 2022 Develop Edition- Basic Mode

This installation is based on the current SQL Server 2022. Download the SQL Server from Microsoft and start to run the installation.

Choose BASIC; the installation will be straightforward. After installing SSMS from here, open SSMS and log in by Windows Authentication.

C. Change the Authentication Mode to Mixed- Error 18456

In SSMS, choose Server (with name) => Security => Logins.

We see a default user sa (System Administrator) on the bottom; however, with a red cross, it seems not to be working. Right, Click sa => Properties.

In the Login Properties - sa Window: set the password.

Click OK to close the window. Then, relogin by SQL Server Authentication.

We got  Error: 18456.

"Login failed for user 'sa'. (Microsoft SQL Server, Error 18456)" is the most common error in SQL server login. There are multiple reasons for this error. One potential reason is you are trying to use SQL Server Authentication, but the SQL server instance is configured for Windows Authentication mode.

While in our case, we installed SQL Server Basic Mode; by default, it is only open for Windows Authentication mode, not for SQL Server Authentication Mode. We need to switch the Windows Authentication Mode to the Mixed Mode. Open SSMS by Windows Authentication mode => Right Click the related Server => Properties.

The Server Properties window is opened.

Click Security; we can see the Server authentication is by Windows Authentication mode only.

Choose SQL Server and Windows Authentication mode, the mixed mode.

OK, we will need to restart the SQL Server.

This can be done by simply rebooting the computer, but we go the easy way: by using SQL Server 2022 Configuration Manager to restart SQL Server.

Open the SQL Server Configuration Manager => SQL Server Service => SQL Server => Right Click => Restart.

Restarting.

D. Enable SA (System Administrator) user Account --- Error 18470

After restarting SQL Server, retry to login by SQL Server Authentication mode:

We got another error: 18407.

This may happen if the SA (System Administrator) user account is disabled in SQL Server. We try to fix it: Right Click Server => Security => Logins => sa.

The Login Properties - sa window is opened.

Click Status.

Under settings => login is Disabled.

Click Enabled => OK. Relogin for sa by SQL Server Authentication mode.

It is successfully logged in, and the sa user account is no longer associated with a red cross.

E. Add a New User

Click Server => Security => Right Click Loginis.

In the Login - New window: type in Login name => Click SQL Server authentication mode => type in password => confirm password => OK.

You have the new sa1 user account added.

Login by sa1.

You will be asked to Change your Password.

The new sa1 user account will work.

References

• SQL Server Downloads | Microsoft
• Download SQL Server Management Studio (SSMS) - SQL Server Management Studio (SSMS) | Microsoft Learn
• How To Fix: Login failed for user 'sa'. (Microsoft SQL Server, Error 18456) | Fishtank Consulting (getfishtank.com)
• Microsoft SQL Server error 18470 – BQE Knowledgebase (zendesk.com)