April 17, 2009 at 5:55 am
Hi all its my first post so please be gentle...:-)
Ok, I have a SQL 2000 server that is SOX audited. I have recently installed a audit trace that produces a daily audit report for all login failures & successes, logouts, updates, inserts etc etc, you get the idea.
Now over the last week I have had two login failures against domain accounts. Having spoken to the user involved (the other failure was via a application odbc connection) he connected to SQL via Query Analyzer and ran a query - he has access to do this and does this daily. My SQL error reads as follows..
2009-04-15 20:01:51.61 logon Login succeeded for user 'Domain\UserA'. Connection: Trusted.
2009-04-15 20:01:51.61 logon Login failed for user 'Domain\UserA'
2009-04-15 20:01:52.15 logon Login succeeded for user 'Domain\UserA'. Connection: Trusted.
As you can see, the login succeeds then instantly fails (at exactly the same time) then reconnects successfully.
The user swears he only connected to QA once and never received any sort of login failure (which he should not get as he connects using his AD account).
As the error has been flagged as a failed login to a SOX audited box I somehow have to explain/prove why this has happened, and to be honest I have drawn a bit of a blank. I have tried to recreate the error without any success.
Has anybody know of a reason why SQL would do this or alternativly has anyone else had similar problems ??
Thanks in Adavance
bg
April 17, 2009 at 6:33 am
Does he have any applications or services starting as his own account like sharepoint. I found that the developers would start sharepoint and keep trying to connect.
You can do a netstat command on his laptop or server and see what is running.
April 17, 2009 at 7:43 am
Hi tracy. I have investigated all such routes, netstats etc etc. He connects via QA regually but on this one occasion he received the login failure - for no apprant reason.
April 17, 2009 at 8:33 am
2009-04-15 20:01:51.61 logon Login succeeded for user 'Domain\UserA'. Connection: Trusted.
2009-04-15 20:01:51.61 logon Login failed for user 'Domain\UserA'
2009-04-15 20:01:52.15 logon Login succeeded for user 'Domain\UserA'. Connection: Trusted.
If the user is connecting directly to SQL Server you need to change to both Windows authentication and SQL authentication. In 2000 all AD users login needs to be manually added to SQL Server and the database.
As the error has been flagged as a failed login to a SOX audited box I somehow have to explain/prove why this has happened, and to be honest I have drawn a bit of a blank. I have tried to recreate the error without any success.
This is a very common error with documented solutions which works in some cases so if you don't know this error why are you in charge of SOX audit in your company?
Hi tracy. I have investigated all such routes, netstats etc etc. He connects via QA regually but on this one occasion he received the login failure - for no apprant reason.
There is a valid reason that is not the persons fault why your SOX implementation did not create a rider and mitigation for this error is the actual problem here. You need to manually add the users account in SQL Server and make sure you are using both Windows and SQL authentication, if your SOX does not allow that then you will be documenting useless errors that may not relate to what your employees are doing.
Kind regards,
Gift Peddie
April 17, 2009 at 9:08 am
Gift Peddie (4/17/2009)
2009-04-15 20:01:51.61 logon Login succeeded for user 'Domain\UserA'. Connection: Trusted.
2009-04-15 20:01:51.61 logon Login failed for user 'Domain\UserA'
2009-04-15 20:01:52.15 logon Login succeeded for user 'Domain\UserA'. Connection: Trusted.
If the user is connecting directly to SQL Server you need to change to both Windows authentication and SQL authentication. In 2000 all AD users login needs to be manually added to SQL Server and the database.
Yes I know that, both Windows authentication and SQL authentication is already enabled. Sorry maybe should of mentioned that eariler.
The end user connects to the SQL server freqently (along with many others) without problems. The SQL server receives hundreds of connections each day from both of SQL and Windows authentication.
As the error has been flagged as a failed login to a SOX audited box I somehow have to explain/prove why this has happened, and to be honest I have drawn a bit of a blank. I have tried to recreate the error without any success.
This is a very common error with documented solutions which works in some cases so if you don't know this error why are you in charge of SOX audit in your company?
Im not in charge of the SOX audit, Im a DBA who has to fill in a audit report daily. If you could point me in the direction of these documented solutions of why SQL accepts a login, then fails it and moments later reconnects without any record of the login failure in the secuirty event log (the only reference for the failure is in the SQL error log), or without the end user even realising there has been a login failure it would be very much appreciated.
Hi tracy. I have investigated all such routes, netstats etc etc. He connects via QA regually but on this one occasion he received the login failure - for no apprant reason.
There is a valid reason that is not the persons fault why your SOX implementation did not create a rider and mitigation for this error is the actual problem here. You need to manually add the users account in SQL Server and make sure you are using both Windows and SQL authentication, if your SOX does not allow that then you will be documenting useless errors that may not relate to what your employees are doing.
Like I've already said the server accepts windows and sql authentication. This server has only recently (in the last 2 months) come under the SOX radar. In the 1000's of logins for this server over that period I have had two incidents of these "errornous/strange" login failures.
April 17, 2009 at 9:35 am
The first link explains the issue in detail and some known solutions to the problem.
http://msdn.microsoft.com/en-us/magazine/cc163740.aspx
The second link explains the error in Asp.net context and the issues related to this error. When a user gets it in Asp.net it must be resolved in IIS not AD.
http://blogs.msdn.com/nunos/archive/2004/03/12/88468.aspx
This is a developer solution which is very simple but some security people don't accept but their acceptance is not relevant because the Asp.net team now own IIS because of this and other AD related issues. And no login to the network is not login to Asp.net or SQL Server.
http://geekswithblogs.net/ranganh/archive/2005/05/25/40503.aspx
Kind regards,
Gift Peddie
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply