?
Solved

Tacking Application details with the help of Client Process ID

Posted on 2016-08-24
15
Medium Priority
?
86 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
  • +1
15 Comments
 
LVL 52

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 35

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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 35

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 52

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
 
LVL 52

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 35

Assisted Solution

by:ste5an
ste5an earned 1000 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 52

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 52

Accepted Solution

by:
Vitor Montalvão earned 1000 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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

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.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

650 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