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
68 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
[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
  • 7
  • 6
13 Comments
 
LVL 49

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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 49

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 49

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
 

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 49

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 49

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 49

Expert Comment

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

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

735 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