Question on SQL Server Administration side:Unintended Sql server failover

SQL Server Administration
1

Hi Guys,

Question on SQL Server Administration side:
I am new to this situation. Failover happends(Primary to secondary) without any manual interventions during a specific time.
A login name was mentioned in the error log but that doesn't exists in Databse Login/Active directory login.

Error: 18456, Severity: 14, State: 5. Reason: Could not find a login matching the name provided.

Anybody can give a hint on this issue. Also appreciate if anybody shares a query that can capture the sql activites during a specific time.

Thanks
Santhosh

2

If this is a availability group and you are seeing the login failure message only after failover, then the login must have not been created on the other node which became primary after failover. Also check if the login SID's are same on both the nodes.

3

Hi Ahmed. Thanks for looking into my issue. Surprisingly there is no such login on both the nodes. So can't check the SIDs. Any chance to help me with fetching the sql activities during a specific time because some of the backup jobs are running in the same time, doubt if it affects.

Regards,
Santhosh

4

You have 2 different issues here.

  1. You need to create a login with required permissions.
  2. Regarding the failover, you can find the events in the SQL error log and cluster log.
    No, backups will not cause a failover.
5

Hi Javed.

Thanks. But That login is not required by the organization. So I can't create.

Thanks
Santhosh

6

Then you don't need to worry about it. Just take the hostname from which the login is coming and inform the respective server owner about this login failure.

7

Hi Javed. Not sure the login is coming from which host ? Can't rectify. Any help apprciated.

Thanks
Santhosh

8

You will find the client IP in SQL error log from which the connection is coming.

9

Hi Javed,

I can't find the ip in error log. I get the below in error log.

Microsoft SQL Server 2017 (RTM-CU31-GDR) (KB5029376) - 14.0.3465.1 (X64)
Jul 30 2023 15:31:58
Copyright (C) 2017 Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)

Thanks
Santhosh