SQL Server 2022 Developer Edition Installation --- Basic

Introduction

As developers, we always have a chance to install SQL Server Developer Edition for testing. We might meet the same issues in each installation; I will screenshot the installation process here, with a brief discussion. There are two modes for SQL Server installation.

• Basic
• Custom

We will discuss them respectively. 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. This article series could be the basis for other SQL Server installation debug articles.

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

A. Introduction

This article will discuss the SQL Server Basic mode installation. 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)