This is a series of articles for SQL server bug fixes:
This article is to discuss an common issue when we try to install SQL Server 2019 Developer Edition, custom mode.
Usually, we install SQL Server by default, it works just fine. However, sometimes, we might face some problems in the new version installation. I got an issue when I installed the SQL Server Developer Edition 2019, that is related to a PolyBase issue. This article will introduce the problem, the reason and the fix. (SQL Server 2019 Developer Edition Installation see here)
Problem
When use login to SQL Server, the login faled, and got the error 18456:
![]()
![]()
![]()
Reason
Microsoft SQL Server, Error 18456 is the most common error in SQL server login. There are multiple reasons for this error. At least, we can divide them as two types, the error come from
- SQL Server authentication mode
- Windows Authentication Mode
SQL Server authentication mode
In our demo, the first two cases are "Login failed for user 'sa'. (Microsoft SQL Server, Error 18456)" that is for a specific user login with the SQL Server authentication mode. For this situation, one potential reason is you are trying to use SQL Server Authentication, but the SQL server instance is configured for Windows Authentication mode. This happens when a SQL Server Basic Mode is installed; by default, it is only open for Windows Authentication mode, not for SQL Server Authentication Mode.In this case, we need to switch the Windows Authentication Mode to the Mixed Mode.
The fix for this, I have discussed in my SQL Server installation article SQL Server Installation: 2022 Developer Edition --- Basic, or you may see [ref1], [ref2].
SQL Server authentication mode
For this, the main reason is due to the ID is not in the login list of the Server. We can get the login list by Click Login, under the security folder of the given database:
![]()
This can be obtain by SQL Command:
exec master.dbo.xp_logininfo
![]()
In different SQL Server:
![]()
We can get only one specific group with given parameter:
exec master.dbo.xp_logininfo 'NT SERVICE\Winmgmt'
![]()
Get the group members by the optional second parameter 'members':
![]()
Fix
If we have the person's ID, or the group name, then we can add it into the login list:
![]()
The name must be a valid user ID or AD group name:
![]()
Server role: by default --- public
![]()
OK
![]()
Choose the specific data tables, and give the appreciate rights => OK
![]()
OK
![]()
OK => Done
Summary
This login failed with SQL Server authentication is mainly due to lack of the individual ID or group the ID is belong to. Find out it and add it. The error is fixed.
References