1

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 .

Improve this question
5
  • so you are saying that all your sql services start as 'LOLZOR\lolsqlserver'? Commented Oct 18, 2011 at 19:07
  • Yes correct, it is a service account. Commented Oct 19, 2011 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? Commented Jan 9, 2013 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. Commented Jan 9, 2013 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. Commented Jan 9, 2013 at 15:31

2 Answers 2

Reset to default
1
+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.

Improve this answer
4
  • I do have SQL Agent jobs I am unfamiliar with. [imgur]i.imgur.com/bRrVR.png Commented Jan 15, 2013 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? Commented Jan 15, 2013 at 18:28
  • fixed, I will post detailed feedback later. Thanks for pointing me in the right direction. Commented Jan 15, 2013 at 19:42
  • @Datapimp23 Glad to hear, looking forward for your feedback :) Commented Jan 15, 2013 at 21:06
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.

Improve this answer
2
  • 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 Commented Jan 11, 2013 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. ;-) Commented Jan 11, 2013 at 17:01

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.