Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
Medium Priority
?
122 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 52

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 52

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 52

Assisted Solution

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

Accepted Solution

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

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 52

Expert Comment

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

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

618 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