new SharePoint 2019 machine - cant login into SQL Server

 We have created a newly win2k16 server with sharepoint 2019, and it worked great...

Until I tried to enable apps (SPFx), and I needed to create a Subscription Service, and therefor use the SQLSERVER.

Whatever I tried I got these error depending on the test, with PowerShell commands (PS), SQL logs, or Sql Server Management Studio (SSMS).

Error: 18456, Severity: 14, State: 5.

Login failed for user '<DOMAIN>\<USER>'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]

Error: 18461, Severity: 14, State: 1.

Login failed for user '<DOMAIN>\<USER>'. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: <local machine>]

Error: 18456, Severity: 14, State: 58.

Login failed for user '<DOMAIN>\<USER>'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: <local machine>]

In the end I found this article that suggests to put SQLSERVER in single user mode, and connect after clearing other connections, mainly SQL Server Agent service.

Follow the article steps

  • The "Configuration Manager" comes with its version in its name (2016)
  • Add the "-m" startup parameter
  • Stop the rest of the services
  • Login via SSMS
My following actions were:
  • Added my "Administrator" account via Object Explorer -> Security -> right click Logins -> "New Login..."
  • Added myself all server roles, and "db_owner" on all Databeses (User Mapping)
  • Changed "sa" user Status -> Login to Enabled
  • Added farm account all server roles
Going back to Configuration Manager, I removed the "-m", and restarted all services

Everything works now!


Popular posts from this blog

OverTheWire[.com] Natas Walkthrough - JUST HINT, NO SPOILERS

SOLVED The item could not be indexed successfully because the item failed in the indexing subsystem

Asp.Net Ending Response options, Response.End() vs CompleteRequest()