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
58 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 46

Expert Comment

by:Vitor Montalvão
ID: 41806056
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
ID: 41813363
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
ID: 41813417
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 46

Expert Comment

by:Vitor Montalvão
ID: 41815457
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
ID: 41819552
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 46

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 500 total points
ID: 41819577
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

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

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41821262
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 46

Expert Comment

by:Vitor Montalvão
ID: 41841666
Deepak, has your issue being solved?
0
 

Author Comment

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

Expert Comment

by:Vitor Montalvão
ID: 41843172
That's good news.
Move on as no performance impact expected.
Cheers
0
 
LVL 46

Expert Comment

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

Author Closing Comment

by:Deepak Kumar
ID: 41873607
Closing this question as i have got the answer
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

910 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

21 Experts available now in Live!

Get 1:1 Help Now