Home / server / Questions / 322534
Accepted
Datapimp23
Asked: 2011-10-19 04:32:53 +0800 CST

Want to find cause of recurring failed SQL Server 2005 login attempts

  • 772

I've been noticing recurring failed logon attempts onto our SQL server. It happens every minute with the same login. An example from the log file viewer

10/18/2011 13:54:50,Logon,Unknown,Login failed for user 'LOLZOR\lolsqlserver'. [CLIENT: ] 10/18/2011 13:54:50,Logon,Unknown,Error: 18456 Severity: 14 State: 16

State 16 means: Login valid, but not permissioned to use the target database

Note that the credentials are also used to start all the SQL Server services

Audit Login Failed
NTDomainName>LOLZOR
Error 18456
HostName EU_LOLWEB1
SPID">71</Column> 
SessionLoginName"LOLZOR\lolsqlserver
TextData">Login failed for user 'LOLZOR\lolsqlserver'
ClientProcessID"2364
NTUserName"lolsqlserver
ApplicationName">Queue Reader Main (distribution) 
StartTime">2011-10-18T12:18:21.72+02:00
ServerName">EU_LOLWEB1
DatabaseID">1</Column> 
LoginName">LOLZOR\lolsqlserver
DatabaseName">master
EventSequence">528
  • You will notice that the logons originate from the sql server itself
  • Target DB seems to point to 'Master' (see below)
  • If I trace the pid in process Monitor I can see that the login is performed by qrdsvc.exe

Description

Description:    SQL Server Replication QueueReader Agent
Company:    Microsoft Corporation
Name:   qrdrsvc.exe
Version:    2005.90.4035.0
Path:   C:\Program Files\Microsoft SQL Server\90\COM\qrdrsvc.exe
Command Line:   "C:\Program Files\Microsoft SQL Server\90\COM\qrdrsvc.exe" -Distributor [EU_LOLWEB1] -DistributionDB [distribution] -DistributorSecurityMode 1  -Continuous -XJOBID 0xA368ED128C0EAA43A137B55FD4DD122F -XJOBNAME [[EU_LOLWEB1]].8] -XSTEPID 2 -XSUBSYSTEM Que
PID:    6096
Parent PID: 300
Session ID: 0
User:   LOLZOR\lolsqlserver
Auth ID:    00000000:a3d8bc8d
Architecture:   32-bit
Virtualized:    n/a
Integrity:  n/a
Started:    18/10/2011 11:43:25
Ended:  18/10/2011 11:43:25

So from what I understand from http://www.eraofdata.com/blog/sql-18456-login-failures/ is that the user account does not have enough permissions on the Master database.

As a test I added the LOLZOR\lolsqlserver to the sysadmin role and restarted the SQL service. This did not change anything. the message still pops up every minute.

How is this possible since the account is running the service. I'd like to resolve this so issue and some help would be appreciated .

sql-server login audit

2 Answers

  1. That service should be attempting to connect to the "distribution" database. If that DB isn't available (offline, deleted, currently restoring, etc.) you might get this error.

    If you aren't using ANY replication anymore, just disable that service, and dig up a good faq on cleaning up SQL after distribution isn't being used. There are stored procs like EXEC sp_helpdistributiondb that can tell you about your current setup. That proc should only be run on the distribution SQL server... but the service you are worried about should ALSO only be run on the distributor. If it thinks things are bad, the service won't ever work right until you fix things.

    If it is all in place, configured right, and it really should be running; verify that the return from SELECT @@SERVERNAME matches the current hostname for the SQL Server. Distribution doesn't play well with aliases, IP addresses, and such in its connection strings.

    • 1
  2. Best Answer

    I would try to tackle this from the following points:

    1. Do you have any jobs that are unfamiliar on your SQL Agent ?
    2. Do you have any scheduled jobs on the server itself that are unfamiliar ?

    I just want to rule-out #1 and #2, now let's continue to #3

    You said earlier that you had replication set-up and you stopped using it, this leads me to think that you have some residuals of the settings that cause the replication attempts.

    Take a look over here and here - that's where all the replication settings are stored, maybe you'll find the problematic configuration.

    Regarding the second link, a simple query like this on each DB on that instance Could help.

    SELECT * FROM sys.tables WHERE is_replicated = 1

    I hope this sheds some more light on this issue, I would really appreciated you responding back with the details you gather.

    Thanks, Idan.

    • 1