0

I have a user-defined endpoint on SQL Server. I would like to monitor all the connections to that endpoint. What event/action/predicate do I need to add to extended event session?

I am open to any other suggestions out extended events to perform this monitoring. Please help.

hexbit1
  • 61
  • 2
  • 8
  • What are you trying to accomplish with the monitoring? If you're looking to see if the endpoint is being used, you should be able to get that info from sys.dm_exec_connections. Of course, that's not event based, so you won't catch ephemeral connections... – Ben Thul Sep 27 '17 at 14:41
  • Basically, we have a dedicated login that is used for specific purpose. We would want this login to use only this user-defined endpoint. This user has deny on all other TCP endpoints. – hexbit1 Sep 27 '17 at 20:01
  • But there are other SYSADMIN accounts, that process-wise should not use this endpoint. For this reason, would like to have a log of all the login operations that use this endpoint. Am planning to use Extended Events for logging purposes. Unfortunately, I am unable to boil down to an event object that would help me get this detail (may be due to lack of documentation). I found an event object called **Login** but its versioned schema does not contain the TCP endpoint column. I don't see any Action, Predicate objects that contain endpoint details. – hexbit1 Sep 27 '17 at 20:01
  • Any suggestions? – hexbit1 Sep 28 '17 at 21:52
  • I looked into this a bit yesterday and I'm not seeing an XE out of the box that does what you want it to do. You might be able to get clever with something like Resource Governor, classifying by endpoint (the endpoint that the special purpose account to one group; everyone else to the default group) At that point, you could set up your XE to look for the exceptions (i.e. admins on non-default workgroup). – Ben Thul Sep 28 '17 at 22:37
  • Yeah, it seems like it. Thanks for the suggestion (Resource Governer). I will give it a try – hexbit1 Sep 28 '17 at 23:11

0 Answers0