locked
How to Handle Bad logins - alerts RRS feed

  • Question

  • Hi ALL ...

    i got so many alerts related to bad logins from all servers. How to handle these alerts. Below one is the example ...

    EX 1) : Server Name : ErrorLog Report - Bad login(s):  'NT AUTHORITY\SYSTEM'

    Regards

    Pradeep


    Thursday, April 10, 2014 6:24 PM

Answers

  • Hi All ....

    Thanks for your replies ..........

    Actually the login 'NT AUTHORITY\SYSTEM' having sys admin privilages . The default database for this login is 'master ' only. But in properties of this login in user mapping i have observed that it is not mapped to none of the database in server . How can we find the previous user mapped database of this login . Still i am getting bad logins alerts. Please suggest me how to avoid these type of  alerts ....

    Pradeep



    If it is sysadmin it does not needs to be mapped to any database as such it can access database.BTW there is no need to to give anybody access to System account ,This account should be disabled.Its difficult to actually say why it is failing use profiler to track more information and post here.

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Marked as answer by reddy pradeep Thursday, April 17, 2014 11:25 PM
    Thursday, April 17, 2014 7:36 PM

All replies

  • share more error details from the SQL logs :

    Or can go through the below articles:

    http://blog.sqlauthority.com/2009/08/20/sql-server-fix-error-cannot-open-database-requested-by-the-login-the-login-failed-login-failed-for-user-nt-authoritynetwork-service/

    http://stackoverflow.com/questions/2251839/login-failed-for-user-nt-authority-network-service


    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Saturday, April 12, 2014 1:49 PM
  • Hi Neha ,

    Below are the more details from logs ..... The above two articles are not related to my issue. Iwant to handle this type of alerts. Actuallly daily we got this type of alerts many times  from same server

    LogDate                 ProcessInfo  LogText                                                                                                                                                                                                               
    ----------------------- ------------ -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    xxxxx      Logon        Error: 18456, Severity: 14, State: 16                                                                                                                                                                                   
    xxxxx      Logon        Login failed for user 'NT AUTHORITY\SYSTEM'. [CLIENT: <local machine>]                                                                                                                                                 
    xxxxx       Logon        Error: 18456, Severity: 14, State: 16.                                                                                                                                                                                  
    xxxxxx     Logon        Login failed for user 'NT AUTHORITY\SYSTEM'. [CLIENT: <local machine>]

    (4 rows affected)

    So please suggest me ........

    Thanks in advance ......

    Pradeep


    Monday, April 14, 2014 11:04 PM
  • 1.Please check the sys admin right are there or not if yes please follow the below link.

    2.Might be looking for the missing database:-

    http://www.sharepointassist.com/2009/04/19/login-failed-for-user-nt-authoritysystem-client/

    3. if not have look the details

    Could you please refer the following links

    http://sql-articles.com/articles/troubleshooting/troubleshooting-login-failed-error-18456/


    EA



    Tuesday, April 15, 2014 12:46 AM
  • generally below tasks which are run by the NT Authority\System user in the server.

    1.EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters - SQL Agent Alert Engine

    2.SELECT ISNULL(SUSER_SNAME(), SUSER_NAME()) - SQL Agent EMail Logger

    3.UPDATE msdb.dbo.sysjobactivity - SQL Agent Job Invocation Engine

    4.msdb.dbo.sysmail_help_admin_account_sp;1 - SQL Agent90 - id <2764>

    so grant sysadmin rights on SQL to NT Auth\SYSTEM and see if the error still occurs. 


    sp_addsrvrolemember [NT Authority\System],'sysadmin
    OR

    USE <dbname>;GRANT CONTROL SERVER TO <NT Authority\System>];GO

    Tuesday, April 15, 2014 6:14 AM
  • Is it possible that is a reporting service trying to start up?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, April 15, 2014 1:11 PM
  •  ------------ -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    xxxxx      Logon        Error: 18456, Severity: 14, State: 16                                                                                                                                                                                   
    xxxxx      Logon        Login failed for user 'NT AUTHORITY\SYSTEM'. [CLIENT: <local machine>]                                                                                                                                                 
    xxxxx       Logon        Error: 18456, Severity: 14, State: 16.                                                                                                                                                                                  
    xxxxxx     Logon        Login failed for user 'NT AUTHORITY\SYSTEM'. [CLIENT: <local machine>]

    (4 rows affected)

    So please suggest me ........

    Thanks in advance ......


    Pradeep read commnets mentioned in this article

    http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx

    State 14 in error can mean following

    1. Database is offline

    2. Database is in restoring state

    3. Autoclose is ON for database.

    4 Database is still coming online and will only accept connection when online completely.

    5. Make sure database to which user is connecting is present check sys.databases for entry.If that is default database in SSMS while connecting change default database to MASTER database


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Tuesday, April 15, 2014 1:46 PM
  • Hi All ....

    Thanks for your replies ..........

    Actually the login 'NT AUTHORITY\SYSTEM' having sys admin privilages . The default database for this login is 'master ' only. But in properties of this login in user mapping i have observed that it is not mapped to none of the database in server . How can we find the previous user mapped database of this login . Still i am getting bad logins alerts. Please suggest me how to avoid these type of  alerts ....

    Pradeep


    Thursday, April 17, 2014 7:07 PM
  • Hi All ....

    Thanks for your replies ..........

    Actually the login 'NT AUTHORITY\SYSTEM' having sys admin privilages . The default database for this login is 'master ' only. But in properties of this login in user mapping i have observed that it is not mapped to none of the database in server . How can we find the previous user mapped database of this login . Still i am getting bad logins alerts. Please suggest me how to avoid these type of  alerts ....

    Pradeep



    If it is sysadmin it does not needs to be mapped to any database as such it can access database.BTW there is no need to to give anybody access to System account ,This account should be disabled.Its difficult to actually say why it is failing use profiler to track more information and post here.

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Marked as answer by reddy pradeep Thursday, April 17, 2014 11:25 PM
    Thursday, April 17, 2014 7:36 PM
  • Hi All ,

    Still i am getting the same error . Even i am unable to disable this login also . Actually  for every one hour , i got this alert . Please help me ..........

    Tuesday, April 22, 2014 2:52 AM
  • Hi All ,

    I have checked everything on the server . All DB's are  online only. This login has sysadmin priviliges . The login has been altered and mapped to default database. I have checked that the DB property auto-close is also set to false only. Still i am getting the same error .

    Pls give your inputs ..... Treat this one as a challenge . 


    Wednesday, April 23, 2014 9:42 AM
  • Hi All ,

    I have checked everything on the server . All DB's are  online only. This login has sysadmin priviliges . The login has been altered and mapped to default database. I have checked that the DB property auto-close is also set to false only. Still i am getting the same error .

    Pls give your inputs ..... Treat this one as a challenge . 


    Pradeep,

    Instead of giving us challange why dont you take this as challange for yourself and create a profiler trace and actually trace out what is causing issue.I already asked you did you do that .I am sure you did not.Its really difficult to answer why you are getting this message without having physical access to your system.Check oout below links

    Create profiler to track failed login

    Use extended event to track failed logins

    Extended event was introduced from 2008 onwards


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Wednesday, April 23, 2014 9:51 AM
  • What is your problem really?

    That you have an application that's login, or something else that is not working?

    Or is the problem that you don't want to see these alerts?

    The intial question suggests the latter. But in that case, why would you try to make it possible for that service to login? Maybe that service will perform damage in your SQL Server instance?

    Since it says "Local machine" in the error message, it means a service running on the same machine. Which could be IIS, or another instance. But if you don't want to see those messages, you should try to find where it's coming from. Letting the stranger seems to be a dangerous proposition.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, April 23, 2014 9:36 PM
  • Hi All ,

    Thanks for your answers ....

    But , i have checked the logs and found as like below

    Message
    [298] SQLServer Error: 4060, Cannot open database "XXXXX" requested by the login. The login failed. [SQLSTATE 42000]

    Actually the databases related to this login has been deleted long back only . Recently i mapped this login to master database as a default database . Auto close is also set to false only . Today  i have started the profiler also . will see what the trace file shows . I will post the trace file information ....

    Before that any comments ..........

    Thursday, April 24, 2014 9:42 AM
  • Hi All ,

    Recently i have facing one mor issue related to logins . Actually i got same alert as like above with different login . But that login mapped to deleted database . So i have deleted that login also . Eventhough i got the same alerts .

    Any one please help me how to avoid the getting of this type of alerts ......

    Friday, April 25, 2014 3:02 AM
  • Dropping the login will of course not remove the alert, since the process that is knocking on the door will keep on knocking. You will need to find the process that is knocking.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, April 25, 2014 9:48 PM
  • Hi Shanky .....

    I have created a profiler trace for login . I have found the below user error message .

    'Could not find stored procedure 'GetDBVersion''. Under Application name column shows as is 'reporting server' . Below one is the stored procedure

    declare @p1 nvarchar(64)
    set @p1=NULL
    exec GetDBVersion @DBVersion=@p1 output
    select @p1

    Any ideas please ...........

    Friday, May 2, 2014 5:41 AM
  • I have created a profiler trace for login . I have found the below user error message .

    'Could not find stored procedure 'GetDBVersion''. Under Application name column shows as is 'reporting server' . Below one is the stored procedure

    declare @p1 nvarchar(64)
    set @p1=NULL
    exec GetDBVersion @DBVersion=@p1 output
    select @p1

    Any ideas please ...........

    Ideas? Look for the application that is causing the error!

    Your questions remind of the story of one man (presumably sober) who finds another man (who is intoxicated) who is creeping on the ground below a street light. The sober man asks the other guy what is going.

    - I've dropped my keys and I'm looking for them.
    - Did you drop them here?
    - No.
    - So why you are you looking here?
    - Because there is light here so that I can see.

    You can trace things in SQL Server all day long and you can ask for help in SQL Server forums. But the problem is that you have an orphaned(?) application somewhere.

    That trace happens to include some information that may be useful: hostname and client process id. (Then again, the application can lie about the host, so it is better to look in sys.dm_exec_connection where the IP address should be reliable.)

    But you have to go out in the dark world outside SQL Server to find this application. You need to talk to developers and other people. Maybe there is someone who will say "GetDBVersion? Ah, then it must be application X!".

    But it is highly unlikely that anyone where will know what application it might be.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 2, 2014 8:42 AM