SQL 2008 control access from EXCEL, ODBC, ACCESS

We want change Application access from SQL Authentication to SSPI in order to trace who is access SQL table
We worry user may connect to SQL Server from Microsoft Excel, Microsft Access, SQL Server Management Studio
After google, we found out that is a features in SQL Server to control acccess of user: Logon Trigger

Below Code from Frederik Vandeputte-SQL Server Logon triggers part 2

CREATE TRIGGER logon_trigger_not_from_excel
ON ALL SERVER FOR LOGON
AS
BEGIN
IF APP_NAME() LIKE '%excel%'
ROLLBACK
END


After create Logon Trigger in SQL Server, I open Microsoft Excel, try connect to SQL Server, but connection successful!!
The result is not expected! SQL Server should reject the connection from Microsoft Excel
I can see the database and table in the SQL Server and successful retrieve the data and display in Excel File.
why the connection can establish even I already setup to rollback if connection from Excel?


Step to made connection from Microsoft Excel to local server:
Open Excel file->data->From Other Sources
From SQL Server-> keying .
"." for local server
log on credentials = User WIndows Authentication
I can view all database and table from local server

Now let troubleshoot why the trigger logon is not effective or no rollback the connection:

Below code from Vipul Shah's SQL Server Logon triggers part 2

CREATE DATABASE dbadb
go
use dbadb
CREATE TABLE myTest
(id int identity(1,1),
EventTime datetime,
LoginName varchar(50),
HostName varchar(50),
LoginType varchar(50),
ApplicationName varchar(100))

CREATE TRIGGER myTest_LogonTrigger ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
declare @LogonTriggerData xml,
@EventTime datetime,
@LoginName varchar(50),
@HostName varchar(50),
@LoginType varchar(50)
set @LogonTriggerData = eventdata()
set @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
set @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')
set @HostName = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')
set @LoginType = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginType)[1]', 'varchar(50)')
insert into dbadb..myTest values (@EventTime, @LoginName, @HostName, @LoginType,app_name())
END

Connect to SQLServer from Microsft Excel :
Open Excel file->data->From Other Sources
From SQL Server-> keying .
"." for local server
log on credentials = User WIndows Authentication and Press "Next"
Connection succesful and "Select Database and Table" page appear

Go backup to SQLServer and type the below query
select * from dbadb.dbo.mytest

It show the below result :

id	EventTime	LoginName	HostName	LoginType	ApplicationName
1	2011-01-01 20:25:04.467	testuser		Windows (NT) Login	2007 Microsoft Office system

It show that Excel connection appear as Application Name "2007 Microsoft Office system"

Let alter logon trigger not from excel as below
ALTER TRIGGER logon_trigger_not_from_excel
ON ALL SERVER FOR LOGON
AS
BEGIN
IF APP_NAME() LIKE '%2007 Microsoft Office system%' ROLLBACK
END

Make connection to SQL Server from Microsoft Excel again.
Connection Fail!
It show logon failed for login 'testuser' due to trigger execution.
We successful stop user from connection to SQLServer through Microsoft Excel.

I test few application and the associate application name detect from trigger logon listed as below:

Application	App_name()
Excel Office Excel 2007	2007 Micorosoft office System
Microsoft Office Access 2007	2007 Micorosoft office System
Microsoft SQLServer Management Studio	Microsoft SQL Server Management Studio - Transact-SQL IntelliSense
OSQL	OSQL-32

Reference:
Logon Triggers

Prevent Access to SQL Server 2005 databases from Microsoft Excel and Word

Frederik Vandeputte : SQL Server Logon triggers part 2