September 2, 2010 at 3:49 am
Hi
Application like MS NAVision allow Windows Authentication to connect to the Database server. This means that all users needing access to it should be configured as such.
However, this opens up the possibility of the users connecting directly to the database through SQL Express/ SSMS using their Windows Login. Is there a way to stop this from happening and still allow them to use Windows Authentication to connect from NAV?
I am not a Database Administrator, but I am managing the projects for the client.
Any guidance/pointers are appreciated.
Shreekar
September 2, 2010 at 4:56 am
it's possible, but let me explain the pitfalls.
once i have a username and password with access to a database, i can use any application to get there...SSMS, a program i write, LinqPad,excel,access,HaxxorPad, anything.
you can use a logon trigger to prevent any connection string with a specific app name to grant/prevent access, but NOT on a per-database level...it's at the login handshake, before they get to any database.
so you can prevent anyone from connecting if they are using SSMS, but unless you write the trigger to allow certain logins, you could prevent admins,developers, and the folks that are supposed to use SSMS on a daily basis from connecting.
i might suggest a logon trigger to track logins and their applications, and then review the audit info and repremand anyone who connects with anything except the app instead.
anyway, if you look in BOL there are a couple of examples, and here is one as well:
--Prevent access from SSMS
--drop TRIGGER logon_trigger_not_from_SSMS on all server
CREATE TRIGGER logon_trigger_not_from_SSMS
ON ALL SERVER FOR LOGON
AS
BEGIN
IF APP_NAME() LIKE '%Microsoft SQL Server%'
BEGIN
IF suser_name() IN ('Stormdev\Lowell','sa') --the only persons allowed to use SSMS, no sa allowed for testing
BEGIN
--only allowed from my host machine
IF host_name() !='STORMDEV'
BEGIN
RAISERROR('SSMS connections are restricted on to specific dba machines.', 16, 1)
ROLLBACK
END --host name check
END --suser_name check
ELSE
BEGIN
RAISERROR('SSMS connections are not permitted with this logon.', 16, 1)
ROLLBACK
END
END --app name check
ELSE
BEGIN
RAISERROR('SSMS connections are restricted on this server.', 16, 1)
ROLLBACK
END
END --trigger
Lowell
September 2, 2010 at 5:01 am
Thanks a lot for confirming my suspicion. I guess I will have to get the DBA to prepare a report of accesses and review it weekly.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply