Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Posts
    7

    Unanswered: Unexplained Login Failures

    Hi all,

    I could really use some assistance. I have been researching this problem for over a month now and I have not made any headway or progress.

    I am running SQL Server 2000 on Windows 2000 Server. Hardware is Dual Xeon 2.4/400 Procs, 2GB ram and 1 Raid10 Array with 4x 36 GB 10K RPM drives.

    The server has about 50 dbs on it. All are primarly used in conjuction with some web application or site. On average the server sees about 270ish connections/sessions.

    About 1 - 2 months ago, we started seeing random login failures. We have no explanation for these failures. Our coldfusion code gives us detailed logging information regarding the exact statement that was being executed when the login failed. We try to reproduce the failed login, we cannot. There are no misspelling, code inconsitencies in this regards because the logins are set in the data source which verify.

    We are using per-processor licensing, so unless there is a hidden limit we are hitting or MS is lying about per-processor licensing having unlimmited connections, that is not the issue. Also, I've ruled out some kind of network issue because if that were the case, the login would have timed out, as opposed to failing. I've been running a trace and viewing the failed logins.

    Has anyone dealt with this issue before?



    Best Regards,


    Errol Neal

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    50 databases?

    What's your security model?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You may have users in the company trying to get to the databases, and maybe even for legitimate purposes. Try setting up a desktop machine to run a SQL Profiler trace and see if you can collect a few hostnames from the login failures. The only event you will probably be interested in is the Security Audit->Audit Login Failed. Many MS Office applications attempt a trusted connection to SQL Server, even though you may want a SQL Authenticated connection. It is a real shame that when you turn on login failure auditing in SQL Server you don't get anything about what machine the failure orginated from.

  4. #4
    Join Date
    Mar 2004
    Posts
    7
    I already have a trace running. These db's are used with CF and ASP sites so the connections are coming from 2 servers at the moment.

    We are using Windows and SQL Server logins. The logins that are failing are SQL Server logins.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Are those failures posted in SQL Errorlog? What does the error say? "Login failed for user <user_name>" or something else?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Mar 2004
    Posts
    7
    strangely no. Before I rebuilt our SQL Server, the failures were showing up in the event logs so I have to assume they were showing up in the SQL Error logs. However now, there is no trace of these issues other than what I see in the trace.

  7. #7
    Join Date
    Feb 2004
    Location
    PAKISTAN
    Posts
    106
    Only SQL Users fail; What's the authentication mode, Windows or Mixed.
    Can these users connect to the SQL using Query-Analyzer or EM etc.

  8. #8
    Join Date
    Mar 2004
    Posts
    7
    yes, the auth mode is mixed. The logins fail in our or our customer's applications. The error is emailed along with the query statement that was attempting to be executed. We login using Enterprise Manager using that same username and password and execute the same statement without any problems. Like i said, this problem is very intermittent.

  9. #9
    Join Date
    Feb 2004
    Location
    PAKISTAN
    Posts
    106
    What about Server Net libraries? Make sure the TCP/IP protocol is enabled at the DB server and ur webserver is using this protocol(also check the default port). Any connection that is not a trusted windows connection uses the TCP/IP net library to connect to SQL.

    Howdy!

  10. #10
    Join Date
    Mar 2004
    Posts
    7
    Thank you for your suggestions. I double checked all those items you mentioned and they are configured just fine.
    The problem as i said before is that the login failures are not consistent. They are intermittent and random. Let me give you an example:;

    "Error","5180","04/14/04","09:17:39",,"12.33.92.110, Mozilla/4.0 (compatible; MSIE 5.01; Windows NT 5.0), OLEDB Error Code = 18456<P>Login failed for user '******'.<P><P> <P>SQL = ""INSERT INTO RCOMclickcount(urlID,referer,clickcount,datetime_s tamp)
    VALUES('162001','http://search.msn.com/spresults.aspx?q=resumes+&FORM=IE4','1','2004-04-14 09:17:39')""<P>Data Source = ""******""<P><p>The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (11:2) to (11:50) in the template file D:\***************\redirect.cfm.</p><P><P>Date/Time: 04/14/04 09:17:39<BR>Browser: Mozilla/4.0 (compatible; MSIE 5.01; Windows NT 5.0)<BR>Remote Address: 12.33.92.110<BR>HTTP Referrer: http://search.msn.com/spresults.aspx...umes+&FORM=IE4<BR>Query String: id=162001<P>"

    That is an error from my coldfusion application log. If i execute the same statement in Enterprise manager it works. Also, If I execute the same statement through the web application, it works. I don't know what the problem could be.
    Last edited by enhtech; 04-14-04 at 11:48.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •