iT邦幫忙

0

Logon Trigger有問題導致無法登入

問題:Trigger有問題導致無法登入SQL SERVER

情況:

想要針對每個登入資料庫的帳號做紀錄,首先先建一個紀錄用Table取名 ServerLogonHistory
在網站上找到一個Logon trigger

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [Connections]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON 
AS
BEGIN
INSERT INTO ServerLogonHistory
SELECT ORIGINAL_LOGIN(), HOST_NAME(),USER, @@SPID, GETDATE(), APP_NAME(), DB_NAME()
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [Connections] ON ALL SERVER
GO

結果一用.... 全部帳號都無法登入 一個傻眼 /images/emoticon/emoticon16.gif

登入名稱'sa'的登入因觸發程序執行而失敗
已將資料庫內容變更為'master'
已將語言設定變更為繁體中文 (Microsoft SQL Server,錯誤: 17892)

https://ithelp.ithome.com.tw/upload/images/20190430/20107186R5RNz2k85U.jpg

啊~ 沒想到這麼快就把資料庫弄爆

GOOGLE一下,可以用cmd下sqlcmd方式去drop trigger

C:\Users\XXX>sqlcmd -S LocalHost -d master -A
1> DROP TRIGGER Connections ON ALL SERVER
2> GO

https://ithelp.ithome.com.tw/upload/images/20190430/20107186StTWgjKKdj.jpg
還是不行啦 死都不讓我進去就是了/images/emoticon/emoticon46.gif

繼續請教GOOGLE大神,發現了DAC這個神奇的東西
SQL Dedicated Administrative Connection (DAC)

MSDN定義DAC:
SQL Server 為系統管理員提供了特殊的診斷連接,可在伺服器的標準連接失效時使用。這個診斷連接可讓系統管理員存取 SQL Server 以執行診斷查詢和排解疑難問題,即使 SQL Server 未回應標準連接要求。

使用方法: 進SSMS,伺服器名稱敲 admin:Your_Server\Instance,再輸入帳密
https://ithelp.ithome.com.tw/upload/images/20190430/20107186XIq52G6WLK.jpg

阿咧~ 不行,仔細看了一下網路大神的說法

你應該用【新增查詢】右邊的【資料庫引擎查詢】(Database Engine),而非Object Explorer 上的 Connection。

https://ithelp.ithome.com.tw/upload/images/20190430/20107186pWZENPKdAI.jpg

再試一次
https://ithelp.ithome.com.tw/upload/images/20190430/20107186jNd9exqS6Z.jpg

進來了!趕快把有問題的trigger drop掉
https://ithelp.ithome.com.tw/upload/images/20190430/20107186SZxnXwixiQ.jpg

這時候就可以用Object Explorer 上的 Connection登入了
進來囉!!!/images/emoticon/emoticon08.gif
https://ithelp.ithome.com.tw/upload/images/20190430/20107186nTtX2xpAOl.jpg

查看一下原因...
原來是沒有指定Table的DB害Trigger找不到
Trigger一失敗,就會整個RollBack所以所有帳號都被卡死了
https://ithelp.ithome.com.tw/upload/images/20190430/20107186xY1KiQBJl3.jpg

結論: 用Trigger真的要小心謹慎再小心 還有心臟要夠大顆(不對


參考資料:
Identify SQL Server databases that are no longer in use
https://www.mssqltips.com/sqlservertip/3171/identify-sql-server-databases-that-are-no-longer-in-use/
SQL Dedicated Administrative Connection (DAC) 專用管理員連接
https://blog.xuite.net/tolarku/blog/30615763-SQL+Dedicated+Administrative+Connection+%28DAC%29+%E5%B0%88%E7%94%A8%E7%AE%A1%E7%90%86%E5%93%A1%E9%80%A3%E6%8E%A5
SQL SERVER – Fix : Error : 17892 Logon failed for login due to trigger execution. Changed database context to ‘master’.
https://blog.sqlauthority.com/2009/06/27/sql-server-fix-error-17892-logon-failed-for-login-due-to-trigger-execution-changed-database-context-to-master/comment-page-3/#comments

----- 10/16 補充

昨天將SQL SERVER服務重開後又遇到一樣的問題(這個既視感....)
這次我沒有新增觸發程序,結果又出現因為觸發程序LOGON FAIL
我的老天鵝啊啊啊啊

處理程序

  1. 利用DAC進入查詢頁面
  2. 找出所有觸發程序
--找出trigger
select * from sys.triggers
select * from sys.server_triggers

一開始在sys.triggers裡面甚麼都沒有
還以為系統中邪了(誤
後來才看到還有sys.server_trigger
裡面塞了一個我半年前在測試logon用到的trigger
因為昨天對這個trigger裡面使用的table做異動
想當然爾就又死給我看囉 /images/emoticon/emoticon46.gif

trigger 使用真的要小心小心再小心啊/images/emoticon/emoticon73.gif

參考資料
Logon failed for login due to trigger execution
https://dba.stackexchange.com/questions/218811/logon-failed-for-login-due-to-trigger-execution


尚未有邦友留言

立即登入留言