[Event Notifications] CONNECT and SQL permissions between boxes.

Just ran into this and didn't have it blogged for some reason.

For Event Notifications, you need two permissions on the monitorING server, for the service account running on the monitorED server.  First, there's SQL.  Second, there's Endpoint.  It must have both.

If you just have CONNECT SQL enabled, not the endpoint, then what I saw in sys.transmission_queue was:

An error occurred while receiving data: '24(The program issued a command but the command length is incorrect.)'.

Here's the command to grant it.  Remember that the authorization will be the name you're logged in as, so you might change that or ALTER the AUTHORIZATION afterwards (see http://thebakingdba.blogspot.com/2015/01/event-notifications-changing-security.html)

use [master]
GO
GRANT CONNECT ON ENDPOINT::[ENAudit_Endpoint] TO [yourdomain\monitorEDserviceacct]
GO

[Event Notifications] SQL 2016 and a new exclusion

We just rolled a bunch of servers to SQL Server 2016, and we're now getting this in our event log, once every 5 minutes, for each server.  On the plus side, I didn't know about telemetry_xevents before, and it looks like a nice new tool to query.  However, I don't need these.


Here's how to prevent them from showing up.  Super-simple exclusion.


INSERT INTO ENAudit_Exclusion_List
        (exclusion_set,
         exclusion_type,
         excluded_value,
         insert_datetime,
         update_datetime,
         active
        )
VALUES  ('aw', -- exclusion_set - char(2)
         'ObjectName', -- exclusion_type - varchar(128)
         'telemetry_xevents', -- excluded_value - varchar(128)
         GETDATE(), -- insert_datetime - datetime
         null, -- update_datetime - datetime
         1  -- active - bit
        )