Solved

How can we track the login source(IP address/Host Name) of the SQL logins which connects to SQl server 2008 R2

Posted on 2016-09-20
13
53 Views
Last Modified: 2016-11-04
Hello ,
   Is there any way to track the login source(IP address/ Windows User name) of a particular login which is logged into login SQL server without the use of Profiler trace?

Example:- A user called xxx(Windows account) logged in to his machine with the windows credentials and tries to access Production database server which is hosted in network with SQL login.
 
Now can we able to record/ track from which source(Machine IP address/ Windows User Name) the particular sql login has been made?

Regards
Deepak
0
Comment
Question by:Deepak Kumar
  • 7
  • 6
13 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
If your SQL Server instance has the auditing successful logins enabled then you can check in the instance logs for all logins made.
1
 

Author Comment

by:Deepak Kumar
Comment Utility
Hi Vitor,
   Thank you very much for your response. Yes , Auditing Successful Logins has been enabled in the SQL server and i could find the IP address details from the Error log.

I have a question here, Apart from the IP address of the machine , is there any possibility we can get the NTuserName of the same user without using Profiler?

thanks
Deepak
0
 

Author Comment

by:Deepak Kumar
Comment Utility
Hi vitor,
   Today there was a login attempt made by a folk where the IP address was not logged in. From the info i could understand that the request was made via named Pipe .

How can we make all the request comes through IP so that we can track the IP address and if we do so any issue may arise with respect to connectivity?

or Is there any mechanism to track the source details with the Named Pipe?

Login failed for user 'domain\XXXX'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <named pipe>]

Thanks
Deepak
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
is there any possibility we can get the NTuserName of the same user without using Profiler?
Only if the user is trying to login to SQL Server with an AD account.

How can we make all the request comes through IP so that we can track the IP address and if we do so any issue may arise with respect to connectivity?
Open SQL Server Configuration Manager and go to Network Protocols. Disable all but the TCP/IP. This way you guarantee that only TCP/IP are allowed for the SQL Server instance.
0
 

Author Comment

by:Deepak Kumar
Comment Utility
Hi Vitor,

   I am running the default instance of sql server and believe disabling the named pipe Protocol will not create any issue in terms of connectivity with respect to Applications/ any users.

Please let me know if there are any pre-checks done before disabling the named pipe protocol.

thanks
Deepak
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 500 total points
Comment Utility
Good point.
Run this in your SQL instance to see how the clients are being connected:
SELECT net_transport, count(1) connections
FROM sys.dm_exec_connections
GROUP BY net_transport

Open in new window

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Deepak Kumar
Comment Utility
Hi Vitor,
  I could find 82 connections under named Pipe and 162 connections under TCP/IP.

I have checked with developer for any force connect protocol with respect to application connect is not made.

Still is there something I must check?

thanks
Deepak
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
Comment Utility
I have checked with developer for any force connect protocol with respect to application connect is not made.
Well it must be because you've found 82 Pipe connections. The connection type is set by the connection string so better check with developer team how they set the connection string.
You can also dig a little more and try to find which programs and users are using Named Pipe connections so you'll be able to change them:
SELECT c.session_id, c.connect_time, c.net_transport, c.protocol_type, c.auth_scheme, c.client_net_address, c.client_tcp_port, c.connection_id,
	s.login_time, s.host_name,s.program_name,s.client_interface_name,s.login_name
FROM sys.dm_exec_connections c
	INNER JOIN sys.dm_exec_sessions s ON c.session_id=s.session_id

Open in new window

0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Deepak, has your issue being solved?
0
 

Author Comment

by:Deepak Kumar
Comment Utility
Hi Vitor,
  I have checked with the developers and architect. They confirmed me saying there were no defined protocol used in configuration files to connect to SQL server. Hence I started disabling the Named pipe protocol in my development environment and checked the application connectivity and other jobs. All are working fine.
 
Now i will move on further to do the same on my UAT environment and observe how it behaves?

One question, Is there any performance impact or anything if Named pipes are disabled? As of now i don't see any issues in connectivity and the time it takes to connect SQL server.

regards
Deepak
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
That's good news.
Move on as no performance impact expected.
Cheers
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Deepak, can you close this question?
Cheers
0
 

Author Closing Comment

by:Deepak Kumar
Comment Utility
Closing this question as i have got the answer
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

762 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

10 Experts available now in Live!

Get 1:1 Help Now