January 16, 2012 at 2:31 pm
but the logon trigger only works if the person doesn't know how to change their connection string to explicitly say an application name:
so the logon trigger is not 100% realiable, it will on discourage non determined users:
Lowell
January 16, 2012 at 2:38 pm
Lowell (1/16/2012)
but the logon trigger only works if the person doesn't know how to change their connection string to explicitly say an application name:so the logon trigger is not 100% realiable, it will on discourage non determined users:
Agreed.
In fact, I think that an application role with a strong password would be a better solution.
However, I had to do this because there was no way to change the application, users knew app credentials and were not smart enough to change the connection string.
It's a sort of "security by obscurity" or "false security", but seemed to work at the time.
Another possible solution could be allowing connections from the application/web server only for this login. Again, would not be a proper security measure.
-- Gianluca Sartori
January 16, 2012 at 2:43 pm
You also need to watch out for maintenance plans which, iirc, get an application name of SQL Server Management Studio.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 16, 2012 at 2:53 pm
I think the better method is to use a firewall. Through the firewall, put explicit permissions for IP addresses to access the Database Zone. This doesn't prevent somebody from trying to use SSMS on the app server, but does prevent it from their desktops unless they are supposed to have access.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 16, 2012 at 3:14 pm
Another option, especially if there's a middle-tier for the application, is for the app to impersonate a different windows login for connections to the DB. That way, the users don't have any permission on the DB at all. Not the easiest thing to implement though.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 16, 2012 at 3:22 pm
GilaMonster (1/16/2012)
Another option, especially if there's a middle-tier for the application, is for the app to impersonate a different windows login for connections to the DB. That way, the users don't have any permission on the DB at all. Not the easiest thing to implement though.
Good point. It think it would be the same with SQL authentication, as it basically falls into the same "don't give out credentials" category.
-- Gianluca Sartori
July 14, 2015 at 2:06 pm
This is a good solution. Thanks but when I tried it in SQL 2014,
I did not see error message that we have supplied in trigger, instead I m getting this,
"Logon failed for login 'test' due to trigger execution.
Changed database context to 'ReportServer'.
Changed language setting to us_english. (.Net SqlClient Data Provider)"
I need to display correct error message. any suggestions?
Thanks again.
July 16, 2015 at 2:02 am
Sorry, it's not possible. You have no control over the error message displayed at the client.
-- Gianluca Sartori
December 3, 2015 at 10:45 am
Hi Instead of throwing error message from script can I send email only to dba saying this user tried to logon from ssms? Dont throw error and allow them to login but send me an email.
--SELECT * FROM sys.dm_exec_sessions order by login_time desc
USE [master]
GO
/****** Object: DdlTrigger [TR_LOGON_APP] Script Date: 12/3/2015 11:56:37 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [TR_LOGON_APPUSER_SSMS]
ON ALL SERVER
FOR LOGON
AS
BEGIN
DECLARE @program_name nvarchar(128)
DECLARE @host_name nvarchar(128)
DECLARE @login_name nvarchar(128)
DECLARE @login_time DATETIME
DECLARE @Subject_line nvarchar(128)
DECLARE @body_line nvarchar(MAX)
SELECT @program_name = program_name,
@host_name = host_name,
@login_name = login_name,
@login_time = login_time
FROM sys.dm_exec_sessions AS c
WHERE c.session_id = @@spid
IF ORIGINAL_LOGIN() LIKE '%user' OR ORIGINAL_LOGIN() LIKE 'ssis%' OR ORIGINAL_LOGIN() LIKE 'bo%'
AND @program_name LIKE '%Management%Studio%'
BEGIN
--RAISERROR('This login is for application use only.',16,1)
SET @Subject_line = 'Application user tried to login to ssms on '+@host_name+' Detected'
SET @body_line = 'Application user ' + @login_name+ ' tried to login to SSMS from ' + @host_name+ ' from '+ @program_name+ ' at ' + @login_time +'.'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'dev',
@recipients = 'abc@abc.com',
@subject = @Subject_line,
@body = @body_line
--ROLLBACK;
END
END;
GO
I tried something like this but still its throwing error message while loging.
Any suggestions?
December 3, 2015 at 11:08 am
i think you'll need EXECUTE AS OWNER in the trigger; otherwise it's the end users context, i think, who might not have access to msdb, and thus the ability to send mail.
Lowell
December 3, 2015 at 11:47 am
I tried it but its giving me error.
ALTER TRIGGER [TR_LOGON_APPUSER_SSMS]
EXECUTE AS OWNER
December 3, 2015 at 12:08 pm
dallas13 (12/3/2015)
I tried it but its giving me error.ALTER TRIGGER [TR_LOGON_APPUSER_SSMS]
EXECUTE AS OWNER
i have a couple of model DDL triggers that execute as specific users; i think that's the way to go
CREATE TRIGGER [Logon_Trigger_Monitor_Excel]
ON ALL SERVER WITH EXECUTE AS 'HERMES\SERVICEACT'
Lowell
December 3, 2015 at 12:31 pm
Thanks but still its throwing the error message.
December 3, 2015 at 12:39 pm
dallas13 (12/3/2015)
Thanks but still its throwing the error message.
what specific error do you get? also look in the SQL error log for a detailed error instead of an "ended in a trigger " kind of error.
Lowell
December 3, 2015 at 12:40 pm
Lowell (12/3/2015)
dallas13 (12/3/2015)
I tried it but its giving me error.ALTER TRIGGER [TR_LOGON_APPUSER_SSMS]
EXECUTE AS OWNER
i have a couple of model DDL triggers that execute as specific users; i think that's the way to go
CREATE TRIGGER [Logon_Trigger_Monitor_Excel]
ON ALL SERVER WITH EXECUTE AS 'HERMES\SERVICEACT'
Got bitten by the quote bug. 🙂 Lowell beat me to it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply