We will talk about a common problem that arises when we try to install SQL Server 2019 Developer Edition in custom mode in this article. SQL Server is typically installed by default and functions perfectly. Occasionally, nevertheless, we may run into some issues when installing the latest version. Upon installing the SQL Server Developer Edition 2019, I encountered a problem that appears to be related to a PolyBase issue.
Issue
When attempting to log in to SQL Server, an error 18456 was encountered.
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
"Login failed for user'sa'. (Microsoft SQL Server, Error 18456)" refers to a specific user's login using the SQL Server authentication mode in the first two situations of our demo. One possible explanation for this scenario is that the SQL server instance is set up for Windows Authentication mode, but you are attempting to use SQL Server Authentication. This is the result of installing SQL Server Basic Mode, which is limited to operating in Windows Authentication mode and not in SQL Server Authentication mode by default.We must change the Windows Authentication Mode to the Mixed Mode in this situation.
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 => 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.
HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.
