Prevent users from using power pivot excel connections to a database A couple of weeks ago my boss connected to our production database via excel and was able to query the database.  Because of how we're doing security to the database (AD groups) he should have access to the database.  I'd rather not have users just willy nilly creating pivot tables against our OLTP databases.  Is there a way to prevent these sort of connections?

The way our security works, when a user accesses the application, it checks the AD group, and if the user is in the group the application lets them in.  We use the same AD group to allow access to the database, the application passes the user's information to SQL Server.
You can do that using LOGON TRIGGER.

something like below :

--Prevent acces from Excel
CREATE TRIGGER tr_stop_excel_users
ON ALL SERVER FOR LOGON
AS
BEGIN
IF APP_NAME() LIKE '%excel%'
 ROLLBACK
END


Refer to my answer here as it has the script to achieve it as well. You have the flexibility of customizing the script to check for Application, Users, etc and depending on what you want to allow or not, the table that governs the script can be modified.