阻止對主要 AG 副本的讀取訪問，但在可讀輔助副本上允許它

我們有一個獨特的情況，我們希望允許使用者使用 SSMS 查詢數據庫的可讀輔助副本以進行臨時報告，但不允許他們從主副本中讀取。我們已經設置了只讀路由來實現這一點。這也是 SQL 2016 上的全部內容。

我最初的想法是在主副本和輔助副本上創建登錄名，並授予對相關數據庫的讀取訪問權限。然後我們會拒絕連接或禁用目前主副本上的登錄。在 SSMS 中，使用者可以使用 ApplicationIntent=ReadOnly 連接到偵聽器並被路由到輔助副本，而無需接觸主副本。

我們將使用基本邏輯在主副本伺服器和輔助副本伺服器上設置一個簡單的作業：如果目前伺服器 = 主然後禁用登錄；如果目前伺服器 = 輔助伺服器，則啟用登錄。

問題是當登錄在主伺服器上被禁用時，我在連接到具有隻讀意圖的偵聽器時登錄失敗。當我在主副本上重新啟用登錄時，它工作得很好，並且連接被正確路由到可讀的輔助副本。

我在主伺服器上設置了一個跟踪，果然，我可以看到登錄連接並在主副本上的 master 和 msdb 中執行一些系統類型查詢——即使我在 SSMS 中使用 ApplicationIntent=ReadOnly 連接。我不確定這是否是 SSMS 在幕後所做的事情，或者它是否是通過只讀路由過程的登錄的預設行為。

以下是我在主數據庫上使用快速分析器跟踪擷取的查詢：

--msdb query

   select 
       case 
           when object_id('dbo.sysdac_instances') is not null then 1             
       else
           0 
       end

--master query

SELECT
dtb.name AS [Name],
dtb.database_id AS [ID],
CAST(has_dbaccess(dtb.name) AS bit) AS [IsAccessible]
FROM
master.sys.databases AS dtb
ORDER BY
[Name] ASC

以前有人處理過這種情況嗎？看來我們基本上需要允許主副本上的登錄連接權限，同時拒絕它對主副本上 AG 中的數據庫的讀取訪問權限，但授予該登錄權限以讀取可讀輔助副本上的數據庫。

另一種選擇是創建一個直接指向輔助副本的 DNS 條目，但我們不能保證副本始終是輔助副本，因為可能會發生故障轉移。

看來我們基本上需要在主副本上允許登錄連接權限

$$ … $$

這是完全正確的。用於連接的登錄名必須能夠連接到主伺服器。

當您ApplicationIntent=ReadOnly在指向 AG 偵聽器的連接字元串中使用時，驅動程序最初會連接到主實例。這樣 SQL Server 可以查看副本列表並查看是否有任何可用的線上可讀副本發送給您。這記錄在這裡：

只讀應用程序意圖和只讀路由

可用性組的主數據庫處理傳入的只讀路由請求，並嘗試查找連接到主副本並配置為只讀路由的聯機只讀副本。客戶端從主副本伺服器接收回連接資訊並連接到辨識的只讀副本。

微軟頂級人 Sean Gallardy 在 PFE 部落格上對流程進行了很好的總結：

查找已只讀路由的連接

1. 我們通過驅動創建連接
2. 驅動通過監聽器連接SQL Server，並傳遞相關資訊
3. SQL Server 注意到我們只讀了路由設置並檢查了配置
4. SQL 伺服器向驅動程序報告我們有一個可以使用的只讀輔助
5. SQL Server 將新的輔助資訊發送給驅動程序
6. 驅動程序使用給定資訊創建與輔助節點的新連接

關於您在問題中提到的其他項目：

$$ … $$雖然拒絕它讀取主數據庫上的 AG 中的數據庫，但授予該登錄權限以讀取可讀輔助副本上的數據庫。

數據庫級別的權限將在 AG 中複製 - 因此，如果您刪除使用者對主伺服器的讀取訪問權限，他們也將失去對輔助伺服器的讀取訪問權限。

我對這種努力的目標有點困惑，因為理論上數據應該是相同的（或接近相同的）。出於性能原因，也許您根本不希望某些登錄在主伺服器上執行查詢。表達只讀意圖並連接到偵聽器確實是解決此問題的最直接方法。

其他選項將涉及單獨的硬體，或在您的應用程序中處理直接連接到正確副本的自定義編碼（並在發生故障轉移時重試邏輯，並在每個副本上安排作業以在故障轉移的情況下禁用/啟用登錄， ETC）。

引用自：https://dba.stackexchange.com/questions/238844