[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 95
  • Last Modified:

Tacking Application details with the help of Client Process ID

Hi,

   Can somebody help me out is there any way to find the application details which are connecting to SQL server with the help of Client Process ID. The real problem is a failure of particular login which get failed from one of the application and I am unable to trace the application details except details of client Process ID in SQL profiler Trace.

Regards
Deepak
0
Deepak Kumar
Asked:
Deepak Kumar
  • 6
  • 5
  • 3
  • +1
2 Solutions
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
The client process id is the ID that you'll in the client computer when you open the Task Manager and check for the processes. It should match one of the process and that's the application you're looking for.
1
 
ste5anSenior DeveloperCommented:
What does "The real problem is a failure of particular Login" mean?
0
 
Deepak KumarEngineerAuthor Commented:
Hi Vitor,

   Thank you very much for your reply. Firstly I would like to tell the complete scenario. We have a SQL server instance(default) running on dedicated Windows Server 2008 machine. It was configured in the past few DTS packages or some jobs to use 'SA' login to process  . Everyday there is a SA login attempted by one of those jobs/packages from the same server machine and its get failed. I have also checked the DTS packages and Jobs manually to find the logins details but with no luck we could trace the SA login by them. There are no job failures or anything as such.

Then I started running SQL Profiler to trace the details but the details which it provided doesn't allow me to narrow down to the problem except the Client Process ID .

I have checked the Windows Security logs to compare the Process ID which is provided by Profiler trace but no luck.

Kinldy let me know, is there any way we can track the Login Attempts of SA?

Thanks
Deepak
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
ste5anSenior DeveloperCommented:
I still don't get your point.. Do you look for sessions using SA? Do you want to disable SA?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Deepak in none of your described attempts you said that you used my suggestion.
I would like at least that you try to use it and tell me if it worked or not.

NOTE: If the process runs fast in the client side you may not able to have enough time to see it after capturing the client process ID.
0
 
Deepak KumarEngineerAuthor Commented:
Hi Virto,
 
      Here the client computer is also the same machine where the SQL server instance is hosted.  
   The processes ID from the task manager processes with the Client process ID is unable to trace as it gets disappeared within the moment i check it when the SA login attempts gets failed .

Hence the point which you have mentioned in NOTE section is the scenario which i am facing currently.

Is there any other way we can trace the SA login failure?

Regards
Deepak
0
 
Deepak KumarEngineerAuthor Commented:
Hi ste5an,

    I need to find the process name from where the attempt to login with SA credentials and gets failed.

Regards
Deepak
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, I guess what's really happening since you can't find. The name of the application is not a requisite for the connection string and so many applications don't provide it.
It will be very hard to find the application unless you can have the task manager on the top while you're running the trace and with some luck you can identify immediately the client process ID when it appears in the SQL Profiler.
0
 
ste5anSenior DeveloperCommented:
Just for curiosity: Why?

It seems that no one cares about that failure. Otherwise you would have already be informed.

And as Vitor already wrote: You can only reliable identify the originating client IP. So peek into the Windows logs of that client and correlate them with the sa login failure.
0
 
Deepak KumarEngineerAuthor Commented:
Hi Virto,
  Thank you for your suggestions. I have setup a task in task scheduler with the DOS commands(>tasklist) which will capture the Process details with its PID whenever the even ID 18456(related to login failure) occurs in Application log . This will be useful to co-relate the PID with the profiler client Process ID in order to find which process fires the SA login attempt.

Hope this will help me out on this regards.

Thanks
Deepak
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok. Let us know the results.
Cheers
0
 
Deepak KumarEngineerAuthor Commented:
Hi All,
 
   I have successfully able to find which processes the SA login is being fired. The client process name is DTSRun.exe which narrows down the search to DTS packages. I am manually checking all the packages whether SA login is used or not and found few packages have configured to use SA logins.

Apart from that is there any script which gives all the DTS packages connection information completely.

I heard that there could also be config file where the connection info will be stored and read by SQL server whenever it is getting executed.

Can we able to get all those information using SQL script?

thanks
Deepak
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can we able to get all those information using SQL script?
Unfortunally not. DTSx packages are stored in SSIS so you'll need to open one by one to see them.
0
 
Deepak KumarEngineerAuthor Commented:
Thank you all for your response.
0
 
Brad FeatherstoneCommented:
Let's try this from the other direction.
Set up SSIS Logging in each of the packages.  I prefer to use a central dedicated database as the log destination.
Let the packages run.
Read the database logs.  The information you need will be there.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 6
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now