error

4 Topics

Catch Error in a SQL Server Logon Trigger
I have written a Logon trigger in a SQL Server to control logon authorization. ALTER TRIGGER [LOG_TRG_01] ON ALL SERVER WITH EXECUTE AS 'SA' FOR LOGON AS BEGIN if ORIGINAL_LOGIN() = 'sa' begin return; end; if ORIGINAL_LOGIN() = 'OMEGACAEVDEV1' begin -- not allowed to logon rollback; end; -- Insert Trail EXEC [OmegaCoreAudit].[OMEGACA].[P_ACC_UNF_TRAIL] 0, 'trail_details', 'ip', 'server', 'db', 0 ; END GO It does (as expected): OMEGACAEVDEV1 is not allowed to logon OMEGACAEVDEV2 is allowed to logon An audit event is inserted by proc P_ACC_UNF_TRAIL in a table for both users. All three above I want to stay this way ! But I need to have error handling in it, so that in case of whatever error the login is allowed to login - but keeping a record on another table named SYS_ERROR_LOG (with error details). In this trigger I have intentionally provoked an error by "select 10/0" The new trigger is: ALTER TRIGGER [LOG_TRG_02] ON ALL SERVER WITH EXECUTE AS 'SA' FOR LOGON AS BEGIN BEGIN TRY if ORIGINAL_LOGIN() = 'sa' begin return; end; --provoke error select 10/0; if ORIGINAL_LOGIN() = 'OMEGACAEVDEV1' begin -- not allowed to logon rollback; end; -- Insert Trail EXEC [OmegaCoreAudit].[OMEGACA].[P_ACC_UNF_TRAIL] 0, 'trail_details', 'ip', 'server', 'db', 0 ; END TRY BEGIN CATCH Insert into OmegaCoreAudit.OMEGACA.SYS_ERROR_LOG ([LOGIN_USER_NAME], [DB_USER_NAME], [USERHOST], [IP_ADDRESS], [OS_USER], [BG_JOB_ID], [ERROR_DATA]) values ('LOGIN_NAME', 'USER_NAME', 'USER_HOST', 'IP', NULL, NULL, 'ERROR_MESSAGE'); END CATCH END GO In the above code "if ORIGINAL_LOGIN() = 'OMEGACAEVDEV1'" represents a simplified version of a wider authorization process. Problem: CATCH is not working. Both users are not allowed to logon ("Logon failed for login '[user]' due to trigger execution") No record is written on table SYS_ERROR_LOG. I was expecting one for each user. What can I do to fix this problem? best regards Altin
Solved
akaraulli
Dec 17, 2024 Place SQL Server Engine
361Views
0likes
13Comments
ERROR 18456
EVENTID: 18456 ---- Login failed for user 'sa'. Reason: Password did not match that for the login provided. This message is being sent hundreds of times daily to my server tracing back to my computer's IP address. I'm not sure why it's trying to log in under "sa" because I'm using windows auth to gain access to the server under a different user. Everything seems to work fine with the exception of very recently my account seems to be creating deadlock sometimes even when not actively running any queries. Any help would be great.
cszabo
Aug 13, 2024 Place SQL Server
636Views
0likes
1Comment
cannot create trigger on database for DDL_SERVER_LEVEL_EVENTS
Is this bug? why I can't create trigger on database for DDL_SERVER_LEVEL_EVENTS,but I can create trigger on database on DDL_DATABASE_LEVEL_EVENTS，what's the reason ? https://docs.microsoft.com/zh-cn/sql/relational-databases/triggers/ddl-event-groups?view=sql-server-ver15 CREATE TRIGGER log2 ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS DECLARE @data XML SET @data = EVENTDATA() INSERT ddl_log (PostTime, DB_User, Event, TSQL) VALUES (GETDATE(), CONVERT(nvarchar(100), CURRENT_USER), @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ; GO CREATE TRIGGER log3 ON DATABASE FOR DDL_SERVER_LEVEL_EVENTS AS DECLARE @data XML SET @data = EVENTDATA() INSERT ddl_log (PostTime, DB_User, Event, TSQL) VALUES (GETDATE(), CONVERT(nvarchar(100), CURRENT_USER), @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ; GO
xihaha000
Aug 10, 2021 Place SQL Server
1.6KViews
0likes
1Comment
Microsoft SQL Server 2017 Web Edition & Reporting Services 2017 Developer Edition error
Dear Community, I've setup a Microsoft SQL Server 2017 Web Edition and downloaded afterwards the Reporting Services 2017, because its no longer included in SQL Installation directly. During the setup of the Reporting Service 2017 Installation I had to choose the Edition and my choice was also there "Developer Edition". Now when I try to configure the Reporting Service I run into the following error message: "The feature: "Using other editions of SQL Server for report data sources and/or the report server database" is not supported in this edition of Reporting Services." I added a print screen with the details. Does someone knows something about this error under Microsoft SQL Server 2017? Looking forward to every useful hint or input. Thanks and kind regards, Joël
Joël Sidler
Nov 28, 2017 Place SQL Server
8.4KViews
0likes
3Comments