I have created the following test environment: USE [OmegaCoreAudit] GO /****** Object: Table [dbo].[TEST_TRAIL] Script Date: 6/9/2025 8:02:50 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[TEST_TRAIL]( [TIMESTAMP_STS] [datetime2](7) NOT NULL, [APP_01] [nvarchar](200) NULL, [APP_02] [nvarchar](200) NULL, [SQL_TEXT] [nvarchar](2000) NULL, [LOGIN_NAME] [nvarchar](200) NULL, [MODE] [nvarchar](50) NULL ) ON [PRIMARY] GO USE [master] GO /****** Object: DdlTrigger [ALL_SRV_LOG] Script Date: 6/9/2025 7:39:42 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER TRIGGER [ALL_SRV_LOG] ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON AS BEGIN DECLARE @EventData XML; declare @v_app_name_01 nvarchar(128); declare @v_sql_text nvarchar(2000); declare @v_app_name_02 nvarchar(128); SET @EventData = EVENTDATA(); set @v_app_name_01 = @EventData.value('(/EVENT_INSTANCE/ApplicationName)', 'nvarchar(128)'); set @v_sql_text = @EventData.value('(/EVENT_INSTANCE/TSQLCommand)', 'nvarchar(2000)'); set @v_app_name_02 = APP_NAME(); insert into [OmegaCoreAudit].dbo.[TEST_TRAIL] (TIMESTAMP_STS, app_01, app_02, sql_text, login_name, mode) values (CURRENT_TIMESTAMP, @v_app_name_01, @v_app_name_02, @v_sql_text, ORIGINAL_LOGIN(), 'LOG') ; END USE [master] GO /****** Object: DdlTrigger [ALL_SRV_DDL] Script Date: 6/9/2025 7:21:26 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER TRIGGER [ALL_SRV_DDL] ON ALL SERVER WITH EXECUTE AS 'sa' FOR DDL_EVENTS AS BEGIN DECLARE @EventData XML; declare @v_app_name_01 nvarchar(128); declare @v_sql_text nvarchar(2000); declare @v_app_name_02 nvarchar(128); SET @EventData = EVENTDATA(); set @v_app_name_01 = @EventData.value('(/EVENT_INSTANCE/ApplicationName)', 'nvarchar(128)'); set @v_sql_text = @EventData.value('(/EVENT_INSTANCE/TSQLCommand)', 'nvarchar(2000)'); set @v_app_name_02 = APP_NAME(); insert into [OmegaCoreAudit].dbo.[TEST_TRAIL] (TIMESTAMP_STS, app_01, app_02, sql_text, login_name, mode) values (CURRENT_TIMESTAMP, @v_app_name_01, @v_app_name_02, @v_sql_text, ORIGINAL_LOGIN(), 'DDL') ; END Left it for a while, and when queried the table, noticed that fields: APP_01 (populated by EventData.ApplicationName) - is always null. APP_02 (populated by APP_NAME()) - is populated. I can understand that for LOGON events the EventData.ApplicationName gives no value, as in the following URL: https://schemas.microsoft.com/sqlserver/2006/11/eventdata/events.xsd ... this is somehow indicated. Question: Why on DDL_EVENT the APP_01 is always empty (while APP_NAME() does give a value)? best regards Altin

I keep having trouble with logging into (localdb)\MSSQLLocalDB. It gives the error: > Logon failed for login 'Domain\user' due to trigger execution. > Changed database context to 'master'. > Changed language setting to us_english. When in this state I can't do much diagnostics because I can't log into it. I've been getting out of this with the following shell command.

SqlLocalDb stop MSSQLLocalDB -k
SqlLocalDb delete MSSQLLocalDB

I can then create a new database by logging into the database again, but obviously all data is lost. Trouble is that a few days later the same thing happens again. I'm fairly certain I'm not adding any login triggers. I work on a corporate network and wouldn't be at all surprised if some rogue security tool is doing it. I'm wondering if there is any way of seeing the trigger or capturing diagnostics without being able to login?

Here is the setup: on MSSQL-Server-2022, we have a contained availability group with 4 replicas: 3 synchronous, 1 asynchronous, and a listener. We created a logon trigger to limit access to a specific login from only one IP address. The trigger works well on the listener and primary replica (got trigger error). But when I try to connect to secondary replicas (synchronous and asynchronous), it does not work at all. (I can connect to asynchronous replica with that login from any IP) Do you have any ideas why this is happening? (The login will use the asynchronous replica to read) Here is the code:

CREATE TRIGGER [trg_LimitLogin]
ON ALL SERVER
WITH EXECUTE AS 'login with enough permission on server'
FOR LOGON
AS
BEGIN
    DECLARE @LoginName NVARCHAR(255)
    DECLARE @ClientIP NVARCHAR(255)
    -- Get the login name of the user attempting to connect
    SET @LoginName = ORIGINAL_LOGIN()
    -- Get the IP address of the client
    SET @ClientIP = (SELECT top 1 client_net_address
                     FROM sys.dm_exec_connections
                     WHERE session_id = @@SPID)
    -- Restrict the login for the user 'usr_tutunchian'
    IF  @LoginName NOT LIKE '%$'
	   AND (@LoginName = 'That login'
AND @ClientIP != '1.1.1.1 (for posting code)')
BEGIN
        ROLLBACK
    END
	ELSE
	BEGIN
	 RETURN
	 END
END

In order to audit logins I've used before a server level trigger catching the LOGON and saving it to a table, there are endless examples to do this. But trying to do the same on and AWS RDS sqlserver instance fails with error *Cannot create the trigger 'LogLogons', because you do not have permission.*. I've full sysadmin permission. I've read and checked other questions and sites, there are references to mysql and log_bin_trust_function_creators parameter that needs to be enabled and some other things (Can I CREATE TRIGGER in an rds DB? ) but for MS SQL Server can't find a confirmation wether server level triggers can be created or not. Found this but is not clear if logon triggers can be created or not. CREATE TRIGGER LogLogons ON ALL SERVER WITH EXECUTE AS 'authorized_login' FOR LOGON AS INSERT DBA.dbo.loginaudit DEFAULT VALUES; --DROP TRIGGER LogLogons ON ALL SERVER;

Logon trigger as below: create or replace TRIGGER USER3.MY_TRIGGER AFTER LOGON ON DATABASE BEGIN IF SYS_CONTEXT ('USERENV', 'SESSION_USER') IN ('USER30') THEN IF SYS_CONTEXT ('USERENV', 'HOST') NOT IN ('HOST1', 'HOST2', 'HOST3', 'HOST4') THEN INSERT INTO USER3.MY_TABLE (USERNAME, SID, OS_USER, HOST, IP, TERMINAL, DB_NAME, INSTANCE, INSTANCE_NAME, MODULE, SERVER_HOST, SERVICE_NAME, TIMESTAMP) VALUES (sys_context('USERENV', 'SESSION_USER'), sys_context('USERENV', 'SID'), sys_context('USERENV', 'OS_USER'), sys_context('USERENV', 'HOST'), sys_context('USERENV', 'IP_ADDRESS'), sys_context('USERENV', 'TERMINAL'), sys_context('USERENV', 'DB_NAME'), sys_context('USERENV', 'INSTANCE'), sys_context('USERENV','INSTANCE_NAME'), sys_context('USERENV','MODULE'), sys_context('USERENV','SERVER_HOST'), sys_context('USERENV','SERVICE_NAME'), SYSTIMESTAMP); COMMIT; RAISE_APPLICATION_ERROR(-20000, 'Denied! You are not allowed to logon.'); END IF; END IF; END; Without disturbing the existing structure in any way, I want to define read only (select) permission only on its own objects (USER30) if it comes from HOST2. Can you help with this issue? Best Regards,

I have 100+ databases in my instace of SQL SERVER and mostly is a legacy DB but i dont have a control of what is new and what is old. My idea is create a Trigger in all server for every transact and INSERT in table for audit. I create this table on master: CREATE TABLE Audit_Logins (Login_Name NVARCHAR(256), Login_Time DATETIME, Db_name NVARCHAR(100), Host_Name NVARCHAR(200) ); And try create a trigger on LOGON for store this information CREATE TRIGGER insert_log_on_logon ON ALL SERVER FOR LOGON AS DECLARE @login NVARCHAR(200); DECLARE @db NVARCHAR(100); SET @login = ORIGINAL_LOGIN(); SET @db = DB_NAME(); IF(@login LIKE '%domain%' AND @db NULL) BEGIN INSERT INTO Audit_Logins SELECT ORIGINAL_LOGIN(), GETDATE(), DB_NAME(), EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)', 'NVARCHAR(128)'); END But this doesnt work, if i run a select or any other command not fire the trigger.

**I want to create a logon trigger. Trigger will do the following:** > It will block access to the database based on certain users and > certain machine information. Then it will give an information message > to the user and insert this session information into a table. E.g; **Users:** mike, john, eric, daisy, albert **Machine:** NEWMACH\% **Message to the user:** You are not allowed to logon from machine '||MACHINE|| ' using '|| USERNAME **The information it will insert into the table:** username, osuser, machine, port, terminal, program, module, instance_id, sid, serial# **Table name:** DBADMINISTRATOR.LOGON_TBL **I have a trigger idea as follows;** CREATE OR REPLACE TRIGGER DBADMINISTRATOR.LOGON_FILTER AFTER LOGON ON DATABASE DECLARE USERNAME varchar2 (200); MACHINE varchar2 (200); begin ... ... if ... then raise_application_error(-20001,'You are not allowed to logon from machine '||MACHINE|| ' using '|| USERNAME); end if; end; / Can you please help scripting and will it cause any performance problems? Best Regards,

I have a logon trigger running that is executing as another user to log connection details. It works totally fine without issue except right after a server reboot. At that point, I have to connect to the server via the DAC and disable it. Then I can just enable it again and everything is fine. The error I see in the server logs says: > The connection has been dropped because the principal that opened it > subsequently assumed a new security context, and then tried to reset > the connection under its impersonated security context. This scenario > is not supported. I'm not sure why it would only run into this issue right after a reboot. Any idea what the issue might be? create trigger [LogonAudit] on all server with execute as 'svc_LogDBUser' for logon as begin begin try declare @LogonTriggerData xml ,@SPID varchar(50) ,@EventType varchar(50) ,@LoginTime datetime ,@UserName varchar(50) ,@LoginName varchar(50) ,@LoginType varchar(50) ,@ServerName varchar(50) ,@HostName varchar(50) ,@ClientHost varchar(50) ,@AppName varchar(500) set @LogonTriggerData = eventdata() set @SPID = @@spid set @EventType = @LogonTriggerData.value('(/EVENT_INSTANCE/EventType)', 'sysname') set @LoginTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)', 'datetime') set @UserName = original_login() set @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)', 'varchar(50)') set @LoginType = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginType)', 'sysname') set @ServerName = @LogonTriggerData.value('(/EVENT_INSTANCE/ServerName)', 'nvarchar(257)') set @ClientHost = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)', 'varchar(50)') set @HostName = host_name() set @AppName = app_name() insert into LogDB.dbo.LogonAudit (SPID ,EventType ,LoginTime ,UserName ,LoginName ,LoginType ,ServerName ,HostName ,ClientHost ,AppName) values (@SPID, @EventType, @LoginTime, @UserName, @LoginName, @LoginType, @ServerName, @HostName, @ClientHost, @AppName) end try begin catch --don't worry about it end catch end go

I have 14 production servers with SQL 2016 standard version, 128 gb of RAM, 16 CPU and SSD drives. > Microsoft SQL Server 2016 (SP2-CU15-GDR) (KB4583461) - 13.0.5865.1 (X64) Oct 31 2020 02:43:57 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) All servers have the same hardware but two of my servers are installed on Windows Server 2019 as the others are on Windows Server 2012. I have the same logon trigger enabled on all my databases. This logon trigger runs fine most of the time. But, on one of my servers that is on Windows Server 2019, the logon trigger fails while full backups are executing. (Note that the other server that is on Windows Server 2019 is new and still only has small databases on it so I can't really be sure it will run fine when we put bigger databases on it.) When the full backups starts, latch wait time goes high like crazy, as it is normally around 0 (on SSD drives). This is the only server that has this problem with latch wait time during full backups. (all other stats are fine, CPU is fine, ect.) And while this happens, my logs are full of the errer > Logon failed for login 'login' due to trigger execution After investigation, I've found out that if I disable the logon trigger on this server, the full backups are able to execute fine, with a small occasional peak to latch time, but stays fine most of the time. My questions are: - Could it be possible that the OS (Windows Server 2019 vs Windows Server 2012) makes a difference as my only server that has a problem is on Windows Server 2019? - Why would the logon trigger affect the full backups that much? Right now my only solution is to disable the logon trigger on that server, as clients cannot connect on the server while full backups are taken, since logon trigger execution fails. But we use the logon trigger for security, to log people who login on the servers outside of the application. Here is the definition of my logon trigger

CREATE TRIGGER [Logon_Trigger_Track_IP]
ON ALL SERVER FOR LOGON
AS
BEGIN
  INSERT INTO [master].[dbo].[TRACETABLE2]
   --the auditing snippet below works fine in a 
  --login trigger, 
  --database trigger 
  --or any stored procedure.
  SELECT 
    getdate()                                    AS EventDate,
    HOST_NAME()                                  AS HostName,
    SUSER_SNAME()                                AS sUserName
	WHERE convert(nvarchar(max),ConnectionProperty('local_net_address')) not like '192.168.0%'

  END
GO

The TRACETABLE2 table on master permission has been given to PUBLIC.

CREATE TABLE [dbo].[TRACETABLE2](
	[EVENTDATE] [datetime] NOT NULL,
	[HOSTNAME] [nvarchar](128) NULL,
	[USERNAME] [nvarchar](128) NULL
) ON [PRIMARY]
GO


GO
GRANT INSERT ON [master].[dbo].[TRACETABLE2] TO PUBLIC
GRANT SELECT on [master].[sys].[dm_exec_connections] TO PUBLIC

Collation is Latin1_General_CI_AS on all servers. Here are the latches from the server that is having troubles. Here is the Cost threshold for parallelism. I forgot to mention all my backups are encrypted with a server certificate in AES_256 ******************************************************************************* I did some tests based on the comment of rois and simplifying the trigger to this produces the same problem.

CREATE TRIGGER [Logon_Trigger_Track_IP]
ON ALL SERVER FOR LOGON
AS
   BEGIN
     DECLARE @i INT
  END
GO

--------------------------------------------------------------------------- Update 2021-03-04 : Microsoft was able to reproduce the problem! Waiting for news from them. Will keep you updated. --------------------------------------------------------------------------- Update 2021-05-07 : Okay so after more than two months of troubleshooting with Microsoft, they've figured that I run out of worker threads. But as to why it happens with the logon trigger on servers on Windows Server 2019 and why its not happening on Windows Server 2012, no clue. (The same waits happen but to a lesser extent on Windows Server 2012.) The final conclusion was that the logon trigger has a high cost on the server, and that I can achieve what I need to do with SQL Audit, which has a less higher cost on the server. So I just stopped using logon triggers and switched to SQL Audit instead.

Some of developers on my team know passwords from SQL accounts that have extended permissions We would like to track and be alerted whenever any of developers are using any of those SQL accounts to connect We are about to implement logon trigger that would for each log in attempt, evaluate login's properties, and send email report if they match certain criteria. Logic is following: >if original_login() in (...SQL accounts list here...) and: >a) client IP address is 192.168.x.x VPN subnet (no Production apps connecting from this subnet, only developers can) or b) client host name in (... list of Dev's host machine names...) or c) client application name in (SSMS,az-Data etc.) exec sp_send_dbmail (send report over email to DBA) Trigger would have "execute as" clause and run on behalf of a SQL login whose only permission is to send db mail emails What can be unwanted side effects of enabling this kind of logon trigger on Production ? Can it slow login process or cause any other issues ? p.s. I am aware about DAC and how to use it. Tested connecting using DAC and counting on it to help me disable the trigger if any trouble begins

I've to implement the following requirement: Access to SQL Server instance shall be allowed only from a C# application. Users shall not be able to access any database (even those in which they have access) via SQLCMD, SSMS. Access using SSMS shall be allowed only for logins that are sysadmin, serveradmin. Connection from one specific host machine shall be denied. Connections from a specific C# application shall be allowed. Is it possible, secure and safe to implement this via logon trigger? If yes, how can I implement it in a reliable manner. In order to apply the logon trigger, is it necessary to be done via SSMS, only once to be activated. In case something goes wrong how can I disable it?