SQL Linked Server возвращает ошибку "отсутствует сопоставление имени пользователя" при использовании учетной записи без прав администратора

У меня есть локальный SQL Server 2008R2. Я настроил Linked Server для удаленной базы данных.

Связанный сервер отлично работает при входе на локальный сервер с помощью учетной записи SQL-входа с ролью sysadmin server. Я могу запросить удаленный сервер, поэтому я знаю, что параметр Linked Server верен. Тем не менее, я бы получил ошибку ниже, если я использую учетную запись, у которой нет роли сервера sysadmin.

Msg 7416, Level 16, State 2, Line 2
Access to the remote server is denied because no login-mapping exists.

Для локального и удаленного серверов используется SQL-вход (проверка подлинности Windows не используется)

Какую безопасность мне нужно настроить для обычной учетной записи SQL-входа для использования Linked Server?

Ответ 1

Согласно этому блогу, я должен указать User ID в строке поставщика, если используются учетные записи, отличные от sysadmin. Вот пример.

EXEC master.dbo.sp_addlinkedserver 
    @server = N'MyLinkServerName',
    @provider = N'SQLNCLI',
    @srvproduct = 'SQLNCLI',
    @provstr = N'SERVER=MyServerName\MyInstanceName;User ID=myUser'

Это в точности соответствует тому, с чем я столкнулся, и решает мою проблему.

ОБНОВЛЕНИЕ: См. Ответ @Anton и @Wouter для альтернативного решения.

Ответ 2

В качестве альтернативного решения вы можете использовать параметр @datasrc вместо @provstr. @dataSrc работает без установки идентификатора пользователя

Пример:

EXEC master.dbo.sp_addlinkedserver @server = N'LinkServerName', @provider=N'SQLNCLI',@srvproduct = 'MS SQL Server', @datasrc=N'serverName\InstanceName' 
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LinkServerName', @locallogin = NULL , @useself = N'False', @rmtuser = N'myUser', @rmtpassword = N'*****'

Я добавил комментарий здесь, но он не отображается (не знаю почему).

Ответ 3

@datasrc с этим, я обнаружил, что вы можете полностью избежать использования свойства User ID, используя @datasrc вместо @provstr. Это очень плохо документировано, но пример ниже работает для меня:

EXEC master.dbo.sp_addlinkedserver
    @server = 'SOME_NAME',
    @srvproduct='', -- needs to be explicitly empty, default is NULL and is not allowed
    @datasrc='some_server.com\SOME_INSTANCE',
    @provider='SQLNCLI';

-- Set up the Linked server to pass along login credentials
EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname='SOME_NAME',
    @useself='true', -- Optional. This is the default
    @locallogin=NULL; -- Optional. This is the default

Используя этот метод, вы можете повторно использовать один и тот же связанный сервер для всех ваших пользователей. В настоящее время принятый ответ имеет огромный недостаток, заключающийся в том, что вам необходимо настроить отдельный связанный сервер для каждого пользователя.