將伺服器連結到 Azure 錯誤：“訪問遠端伺服器被拒絕，因為不存在登錄映射。”

我正在建立一個從本地 SQL Server 到 MS Azure 上的數據庫的連結伺服器（見下文）。當非管理員使用者通過連結伺服器查詢 Azure 數據庫時，他們得到：

Msg 7416, Level 16, State 2, Line 1 對遠端伺服器的訪問被拒絕，因為不存在登錄映射。

關於如何使它工作的任何想法？

EXEC sp_addlinkedserver
@server='LincServer',                              
@srvproduct='',     
@provider='sqlncli',
@datasrc='myAzureDB.database.windows.net',
@location='',
@provstr='Encrypt=Yes;TrustServerCertificate=No;User ID=myAzureID',
@catalog='MyDB'

EXEC sp_addlinkedsrvlogin 
@rmtsrvname = 'LincServer',
@useself = 'false',
@rmtuser = 'myAzureID',
@rmtpassword = '########'
GO

另一個答案是錯誤的，我想用重現程式碼和圖片來反駁它。

這裡的問題是參數@provstr，這是一個已知問題，如何解決它在這裡描述：訪問遠端伺服器被拒絕，因為沒有登錄映射存在：

問題：無法通過連結伺服器 SQL Server 2005 執行查詢。此問題僅發生在非系統管理員帳戶中。

您收到以下消息：

消息 7416，級別 16，狀態 2，第 1 行訪問遠端伺服器被拒絕，因為不存在登錄映射。

原因：使用參數@provstr 創建連結伺服器並且使用本地SQL Server 非管理員或非Windows 帳戶時，必須將參數“使用者名”添加到@provstr

解決方案：將“使用者 ID=使用者名”添加到連結伺服器上的提供程序字元串中

執行 master.dbo。sp_addlinkedserver @server = N’LinkServerName’, @provider=N’SQLNCLI’,@srvproduct = ‘MS SQL Server’, @provstr =N’SERVER=serverName\InstanceName; 使用者 ID=myUser’

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N’LinkServerName’，@locallogin = NULL，@useself = N’False’，@rmtuser = N’myUser’，@rmtpassword = N’*****’

OP已經做到了，他在連結伺服器定義中將遠端登錄包含在提供程序字元串中，所以我真的不知道還有什麼可能導致錯誤。

但是現在我想證明，按照引用的指南，您可以擺脫錯誤，而按照其他答案，我們什麼也解決不了。

這是我的複制程式碼。

我有 2 台伺服器，預設和命名實例 (hp2\sql_2012_dev)，我將使用 @provstr 將 hp2\sql_2012_dev 伺服器連結到預設伺服器，而不傳入非管理員的 @provstr 遠端登錄 rmt，這將導致 OP所有非管理員使用者的錯誤。

我將使用 OP 的程式碼將所有本地登錄映射到 1 個遠端登錄。然後我將嘗試應用該建議來單獨映射有問題的登錄，但這不會解決問題。

然後我將重新創建我的連結伺服器，通過提供者字元串傳遞遠端登錄，這將解決問題而無需任何額外的個人映射。

EXEC master.dbo.sp_addlinkedserver 
@server = N'LinkServer2012_rmt', 
@provider=N'SQLNCLI',
@srvproduct = '', 
@provstr=N'SERVER=hp2\sql_2012_dev';--don't pass User ID=rmt' now in order to cause the error

EXEC master.dbo.sp_addlinkedsrvlogin 
@rmtsrvname = N'LinkServer2012_rmt', 
@locallogin = NULL , 
@useself = N'False', 
@rmtuser = N'rmt', 
@rmtpassword = N'*****';

此時非管理員登錄 l 嘗試選擇：

select * 
from linkserver2012_rmt.master.dbo.spt_values; 

並得到了錯誤

Msg 7416, Level 16, State 2, Line 1 對遠端伺服器的訪問被拒絕，因為不存在登錄映射。

好的，我們現在正在嘗試單獨映射它：

EXEC master.dbo.sp_addlinkedsrvlogin 
@rmtsrvname = N'LinkServer2012_rmt', 
@locallogin = N'l', 
@useself = N'False', 
@rmtuser = N'rmt', 
@rmtpassword = N'*****';

並且登錄 l 重新嘗試從連結伺服器中選擇，但他遇到了同樣的錯誤

此時我刪除並重新創建連結伺服器，這次在連結伺服器定義中傳入 rmt 登錄，並將所有本地登錄映射到 rmt，而不做任何單獨的映射：

exec master.dbo.sp_dropserver @server=N'LinkServer2012_rmt', @droplogins='droplogins';

EXEC master.dbo.sp_addlinkedserver 
@server = N'LinkServer2012_rmt', 
@provider=N'SQLNCLI',
@srvproduct = '', 
@provstr=N'SERVER=hp2\sql_2012_dev;User ID=rmt'; --now pass in rmt login 

EXEC master.dbo.sp_addlinkedsrvlogin 
@rmtsrvname = N'LinkServer2012_rmt', 
@locallogin = NULL , 
@useself = N'False', 
@rmtuser = N'rmt', 
@rmtpassword = N'*****';

這次非管理員登錄l完全沒有問題：

我不能認為我的文章是對OP 問題的真正答案，也許原因是Azure，我無法測試它，因為我無法訪問 Azure 伺服器。但至少它在連結到本地實例時解決了同樣的問題。

更新

在創建這樣的連結伺服器時，盡量不要使用@provstr：

EXEC sp_addlinkedserver   
  @server=N'LincServer', 
  @srvproduct=N'',
  @provider=N'SQLNCLI', 
  @datasrc=N'myAzureDB.database.windows.net',
  @catalog='MyDB';

在 MS Premier Support 的幫助下，我們能夠通過從我們用於訪問連結伺服器的視圖中刪除所有授予的使用者權限來解決問題。也就是說，我們無法顯式授予基於連結伺服器的視圖的 SELECT 權限，但如果帳戶以 db_datareader 角色的成員身份訪問視圖，它似乎工作得很好。如此多的細粒度安全性！MS 正在研究，希望很快有答案，我很樂意分享。

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