Link to home
Start Free TrialLog in
Avatar of Deepak Kumar
Deepak KumarFlag for San Marino

asked on

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

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
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

If your SQL Server instance has the auditing successful logins enabled then you can check in the instance logs for all logins made.
Avatar of Deepak Kumar

ASKER

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
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
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.
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
SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Deepak, has your issue being solved?
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
That's good news.
Move on as no performance impact expected.
Cheers
Deepak, can you close this question?
Cheers
Closing this question as i have got the answer