October 17, 2011 at 8:25 am
We have an older ERP system that we are moving away from. This system does not integrate w/ Active Directory and uses sql authentication for all users.
All users have read/write permissions to the db based on security definitions within ERP system.
My network team just pushed out office 2010 to all users, which includes MS Access. Our users didn't have Access bundled with Office 2007 prior to this update.
So now users can create an ODBC connection from Access, using the ERP system logins and access the db's for that system with read/write/delete access.
I know of one way to block specific application names that can connect to sql for ALL users. I am hoping that others have run into this before and can suggest a better solution.
October 17, 2011 at 12:57 pm
I've been testing a logon trigger that will not allow connection if program name is Ms office. However, I need to allow a couple logins ('sa' and 'Finance') access to sql from MS office. The trigger below works, but only for all users. Any suggestions? Thanks
CREATE TRIGGER application_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
IF EXISTS (SELECT *
FROM sys.dm_exec_sessions
WHERE
program_name IN (N'2007 Microsoft Office System', N'Microsoft Office 2010')
and LOGIN_NAME NOT IN ('sa', 'finance'))
ROLLBACK;
END;
October 18, 2011 at 10:35 am
It looks like it should work as written. Have you tested it with just one of the criteria (application name or login name)?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 18, 2011 at 10:45 am
This is a duplicate thread.
Resolution available here : http://www.sqlservercentral.com/Forums/Topic1191993-359-1.aspx#bm1192187
October 18, 2011 at 10:50 am
Well, figures I'd get to the dead thread.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply