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.

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.

SQL Server Installation

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

SQL Server Installation

C. Change the Authentication Mode to Mixed- Error 18456

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

SQL Server Installation

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.

SQL Server Installation

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

SQL Server Installation

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

SQL Server Installation

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.

SQL Server Installation

The Server Properties window is opened.

SQL Server Installation

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

SQL Server Installation

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

SQL Server Installation

OK, we will need to restart the SQL Server.

SQL Server Installation

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.

SQL Server Installation

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

SQL Server Installation

Restarting.

SQL Server Installation

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

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

SQL Server Installation

We got another error: 18407.

SQL Server Installation

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.

SQL Server Installation

The Login Properties - sa window is opened.

SQL Server Installation

Click Status.

SQL Server Installation

Under settings => login is Disabled.

SQL Server Installation

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

SQL Server Installation

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

SQL Server Installation

E. Add a New User

Click Server => Security => Right Click Loginis.

SQL Server Installation

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

SQL Server Installation

You have the new sa1 user account added.

SQL Server Installation

Login by sa1.

SQL Server Installation

You will be asked to Change your Password.

SQL Server Installation

The new sa1 user account will work.

References


Similar Articles