Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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 .

share|improve this question
    
so you are saying that all your sql services start as 'LOLZOR\lolsqlserver'? –  tony roth Oct 18 '11 at 19:07
    
Yes correct, it is a service account. –  Datapimp23 Oct 19 '11 at 6:32
    
qrdsvc.exe is related to replication. Have you checked the servers and privileges involved in your replication process, or disabled replication to see if the error goes away? –  mike42 Jan 9 '13 at 13:58
    
I think the error means it can't access the database or the database does not exist and I think the database it is trying to access is distribution not master. –  sgmoore Jan 9 '13 at 14:07
    
I do not have any replication or mirroring setup anymore for any of the databases. I did used to mirror to another failover server. I just deleted the database mirroring endpoint as it was still listed. –  Datapimp23 Jan 9 '13 at 15:31

2 Answers 2

up vote 1 down vote accepted
+50

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.

share|improve this answer
    
I do have SQL Agent jobs I am unfamiliar with. [imgur]i.imgur.com/bRrVR.png –  Datapimp23 Jan 15 '13 at 15:51
    
@Datapimp23 What are the logs of these jobs? What's the command. Does the name of the jobs resemble the DB or Table names you had confihured in the oasf ?Did you have a look at the Replication system tables? –  Idan Jan 15 '13 at 18:28
    
fixed, I will post detailed feedback later. Thanks for pointing me in the right direction. –  Datapimp23 Jan 15 '13 at 19:42
    
@Datapimp23 Glad to hear, looking forward for your feedback :) –  Idan Jan 15 '13 at 21:06

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.

share|improve this answer
    
I have no distribution server nor distribution database anymore. They have been deleted months ago. When I run the sp EXEC sp_helpdistributiondb on the database I used to replicate I get zero information. The SELECT @@SERVERNAME does display the correct hostname –  Datapimp23 Jan 11 '13 at 10:50
1  
If that server doesn't have a distribution database for running replication, there is no reason whatsoever to be running the replication agent. Halt that service and move on with your life. ;-) –  Mark Jan 11 '13 at 17:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.