Solved

Sql Login attemps

Posted on 2016-09-01
7
50 Views
Last Modified: 2016-09-02
Years ago we someone attempted to compromise our server. We changed ports of sql, change accounts, disable sa define swl accounts specific for each application by separate and so on. After that all was fine but wince that dates until today in sporadic random cases we receive this kind kind of events id 18456 trying to access the masterdb:

Login failed for user 'school'. Reason: Could not find a login matching the name provided. [CLIENT: 169.254.31.187]

this is the sql profile trace where we can see not so much just say IIS and :

      <Column id="64" name="SessionLoginName">school</Column>
      <Column id="1" name="TextData">Login failed for user 'race'. Reason: Could not find a login matching the name provided. [CLIENT: 169.254.31.187]</Column>
      <Column id="9" name="ClientProcessID">10844</Column>
      <Column id="49" name="RequestID">0</Column>
      <Column id="2" name="BinaryData">184800000E0000000C000000310034003900330033002D00370031003000370038000000070000006D00610073007400650072000000</Column>
Of course that use 'school' does not exist since years but existed years ago. We never found where is executed that attempt and sql profle like you say not say so much or what application inside the server is trying to access. How can we know form what app or how is trying to made that attempt?

Like a mentioned before that user "school" does not exist anymore since years but existed and whatever is trying to connect there thinks already exist. And this is a very random issue could happen in 2 days or 2 weeks or in a month. But like that user did not exist who knows what is this.
0
Comment
Question by:Alex E.
  • 3
  • 3
7 Comments
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 250 total points
ID: 41780559
If you run a SQL profiler make sure the Application Name and "Host Name" columns are selected as that will show you from where that is made. You can also add a Audit trigger at server level and log into a table all these attempts  - let me find some code sample for you.

" And this is a very random issue could happen in 2 days or 2 weeks or in a month." - my guess this is some service or scheduled tasks that wakes up and runs something every now and then

So these are few quick examples about how you can add a DDL login audit trigger and log the info you need - you can/should filter its action for "school" login only

https://ermahblerg.com/2012/11/07/logon-auditing-in-sql-server/
somone posted identical here: http://www.sqlfingers.com/2016/07/using-sql-server-logon-trigger.html and if you look at comments you can see how to add a filter so only specific login is audited and logged
0
 

Author Comment

by:Alex E.
ID: 41780606
Yes is a random but when happens there are hundred of attempts in that form.

Yes in SQL profiler I have enabled Application name it says there "Internet Information Services" and in HostName it says "learncomputer" the last one is the name of our server. Then the problem maybe is originated inside the same server for the HostName showing our name of our server?
0
 
LVL 39

Expert Comment

by:lcohan
ID: 41780634
Indeed if IIS is running on the same box as SQL that's what you'll see and is likely to be some SQL injection from some page done by some (lo)user that had the login credentials at some point and now they may try to get back in.
Or..just to not be paranoic - but you can't skip the security aspect - maybe just some old page/iframe import/export, report etc module where this login was hard-coded.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:Alex E.
ID: 41780684
is there a tool or a way to scan the complete machine everywhere and look for that old sql user name? We remember the old credentials however I ask because I think you are right and maybe is something inside the server that executes in that random ways. We looked the whole register for that user and nothing we looked the task scheduler and nothing but we don't know how to search more deep or where. I think detecting the app or service or whatever and deleting this will be done. And we changed the port of sql also during the time a lot of times and agter that the issue persists then I suppose is something that has the connection string or windows login because after change the port continues the issue then if there is someone trying to scan new port find so fast I doubt and i make note this user credentials was a very old user deleted totally and if someone left something to attack they are attacking a honey pot.
0
 
LVL 46

Accepted Solution

by:
Vitor Montalvão earned 250 total points
ID: 41781090
You have the computer name and the application name and you even have the ClientProcessID.
When that happens check in the Task Manager with process has that ID.
My guess is that's something that you forgot to change years ago. Maybe an old website that still active?
0
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 250 total points
ID: 41781581
Vitor is right on - Task manager should be able to help figure out the process but if thats just a w3sp shell for instance you're back to square one so I would try use Process Monitor from Microsoft to get more detail about it:

https://blogs.technet.microsoft.com/appv/2008/01/24/process-monitor-hands-on-labs-and-examples/

This is good tool to help debug this kind and other type of issues.
0
 

Author Closing Comment

by:Alex E.
ID: 41782212
Thank you for all information. Going very deep we found an old installation of openmeetings that  left a configuration of the old user name. We found with the pid.
0

Featured Post

Make managing Office 365 email signatures a breeze

Are you using Office 365? Having trouble trying to set up email signatures for your users? Getting stressed out managing multiple signatures? Need an easier way to manage? We have a solution for you, try the most-user friendly and powerful signature management tool on the market.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now