none
Login failed for user ''. Reason: an attempt to login using SQL authentication failed. Server is configured for Windows authentication only

    Question

  • I am running SQL Server 2008 SP1. My windows event log is filling up with the error "Login failed for user ''. Reason: an attempt to login using SQL authentication failed. Server is configured for Windows authentication only."  This server is definitely configured for mixed mode authentication.  The service has been restarted and yet this error keeps popping up every 40 seconds or so. What am I missing?

    • Moved by Tom Phillips Tuesday, July 03, 2012 1:29 PM Security question (From:SQL Server Database Engine)
    Monday, July 02, 2012 10:20 PM

Answers

  • Thanks to everyone who responded. Turns out there was an application running on the server that was failing to log in.  This application wasn't actively being used by anyone and after I turned off the services associated with the application, the error stopped.

    Tuesday, July 03, 2012 5:57 PM

All replies

  • Can we test a single connection using SQL authentication and If still persist, you have to double check that credential if it is still trying to connect SQ Server and identify if it is hitting the same DB on the same server or other DBs  since I do think this errors is related to other DBs

    Kindly work out  it and please let know me your feedback                                               


    Shehap (DB Consultant/DB Architect) Think More deeply of DB Stress Stabilities



    • Edited by ShehapMVP Monday, November 12, 2012 12:58 AM
    Tuesday, July 03, 2012 12:26 AM
  • Hi Robert,

    Please identigy the login that you are getting the issue... Most prob its becuase of SPN (or) Kerbros authentication issue from AD level. Please check with your AD engineers.

    Thanks,

    Satish Kumar.


    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

    Tuesday, July 03, 2012 3:26 AM
  • Can you please go back and check if the server has definitely been configured for sql and windows autentication mode(mixed mode) in the server properties-->security tab. If not make it sql and windows authentication and restart sql.


    Cheers!! Kalps

    Tuesday, July 03, 2012 6:28 AM
  • My windows event log is filling up with the error "Login failed for user ''. Reason: an attempt to login using SQL authentication failed.

    1,First can you please check what is the Authentication mode set for the SQL server Database engine?

    2.Any database has been dropped and associated login is trying to connect from the application?

    3.Can you paste the errorlog completely because the each login failed will have severity and state.. hence can you please paste the error log here.

     


    Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.

    Tuesday, July 03, 2012 10:18 AM
  • Here is the complete error log:

    Event Type: Failure Audit
    Event Source: MSSQL$EPM
    Event Category: Logon
    Event ID: 18456
    Date:  7/3/2012
    Time:  5:39:52 AM
    User:  N/A
    Computer: TETTNANG
    Description:
    Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: 10.28.0.117]

    For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
    Data:
    0000: 18 48 00 00 0e 00 00 00   .H......
    0008: 0d 00 00 00 54 00 45 00   ....T.E.
    0010: 54 00 54 00 4e 00 41 00   T.T.N.A.
    0018: 4e 00 47 00 5c 00 45 00   N.G.\.E.
    0020: 50 00 4d 00 00 00 07 00   P.M.....
    0028: 00 00 6d 00 61 00 73 00   ..m.a.s.
    0030: 74 00 65 00 72 00 00 00   t.e.r...

    As far as I know, no databases have been dropped and the database engine is definitely set to mixed mode authentication.

    Tuesday, July 03, 2012 12:42 PM
  • I am able to make a connection to the database using SQL authentication.
    Tuesday, July 03, 2012 2:50 PM
  • Search the start of the error log for the statement "Authentication mode is MIXED."  That is the 6th item in my error log when the Database Engine starts.

    Also execute SELECT SERVERPROPERTY('IsIntegratedSecurityOnly'); Return of 1 means Windows authentication only; 0 means mixed mode; NULL means a typo. I bet the server isn't really in mixed mode.

    I know you said twice that the server was in mixed mode already. So you think the server is in mixed mode, but it isn't? I believe you when you say you have restarted, so my next guess is that there are two instances of SQL Server installed and you are connecting to different instances.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Tuesday, July 03, 2012 2:57 PM
  • Rick, Thanks for the response. I ran the query and the result returns 0. 

    It appears there are two instances of SQL server installed, one with a named instance and one the default. However, the default appears not to be running as it's not listed in services.msc. Also, the error in the log indicates it's connecting to my named instance.

    Tuesday, July 03, 2012 3:15 PM
  • Strange. Well, when you get error 18456, the SQL Server error log contains an error state code. The error state code gives further information about why the login failed. This info is not passed to the client, because they didn't log in and SQL Server isn't sure who they are. Aaron Bertrand has an extensive list of error codes in his blog here http://www2.sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx

    Look up the error in the SQL Server log and then check the state code against the list.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Tuesday, July 03, 2012 5:54 PM
  • Thanks to everyone who responded. Turns out there was an application running on the server that was failing to log in.  This application wasn't actively being used by anyone and after I turned off the services associated with the application, the error stopped.

    Tuesday, July 03, 2012 5:57 PM
  • Seems the issue within the program permissions with web pages in ASP.net, because the user here it null as per your log.

    we know Because a different version of web server is ran on from your side, they made it allow to use windows permissions for windows to get trust in it. In an IIS server with anonymous(unknown) permissions, windows do not trust the connection, hence you to use the machine account aspnet. so correcting or debugging in the

    application code and required to modify..

    how ever it os good that the issue resolved


    Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.

    Tuesday, July 03, 2012 10:06 PM
  • It sounds like this issue is resolved by Robbert. However, I just ran into an issue related to ODBC connections that were using TCP and not explicitly specifying that the connection should use a trusted connection. Something about an older system that worked with named pipes but TCP wasn't able to impersonate the user unless it was explicitly told to. I don't fully understand the issue and don't have an example. If others in the future hit this problem and find this thread, e-mail me at rick.byham@microsoft.com and I will see if I can tease out the details. Mention ID number 984320.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Tuesday, July 10, 2012 9:35 PM