Solved

Tacking Application details with the help of Client Process ID

Posted on 2016-08-24
15
40 Views
Last Modified: 2016-09-01
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
Comment
Question by:Deepak Kumar
  • 6
  • 5
  • 3
  • +1
15 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41769852
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
 
LVL 32

Expert Comment

by:ste5an
ID: 41769864
What does "The real problem is a failure of particular Login" mean?
0
 

Author Comment

by:Deepak Kumar
ID: 41770260
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
 
LVL 32

Expert Comment

by:ste5an
ID: 41770263
I still don't get your point.. Do you look for sessions using SA? Do you want to disable SA?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41770312
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
 

Author Comment

by:Deepak Kumar
ID: 41770339
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
 

Author Comment

by:Deepak Kumar
ID: 41770344
Hi ste5an,

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

Regards
Deepak
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41770359
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
 
LVL 32

Assisted Solution

by:ste5an
ste5an earned 250 total points
ID: 41770402
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
 

Author Comment

by:Deepak Kumar
ID: 41775786
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41775818
Ok. Let us know the results.
Cheers
0
 

Author Comment

by:Deepak Kumar
ID: 41779280
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
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 250 total points
ID: 41779340
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
 

Author Closing Comment

by:Deepak Kumar
ID: 41779819
Thank you all for your response.
0
 
LVL 1

Expert Comment

by:Brad Featherstone
ID: 41780558
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

743 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

11 Experts available now in Live!

Get 1:1 Help Now