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:
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