Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Service broker procedure. Access to the remote server is denied  [new]
IlyamI
Member

Откуда:
Сообщений: 55
Всем доброго дня.

Пытаюсь заставить работать процедуру с линкованным сервером.

Имеется: полностью настроенный service broker под конкретную задачу (тип сообщения, очередь, сервис, контракт и т.д.) Создана и вызывается при получении сообщения процедура.

Одна беда - вызываемая процедура (пр.1) в зависимости от сообщения вызывает другую процедуру (пр.2). В пр.2 имеется запрос на другой сервер через линк. Пр.2 даже не начинает выполняться. В момент ее вызова падает с ошибкой "Access to the remote server is denied because the current security context is not trusted".

Небольшое расследование показало, что пр.1 вызывается в следующем контексте безопасности, соответственно, контекст выполнения пр.2 тот же:
select CURRENT_USER, SESSION_USER, SUSER_NAME(), SUSER_SNAME(), SYSTEM_USER, USER, USER_ID(), USER_NAME()

dbo dbo sa sa sa dbo 1 dbo

Линк создан с параметром "Be made using this security context":
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'сервер name',@useself=N'False',@locallogin=NULL,@rmtuser=N'remote user',@rmtpassword='user password'


Подскажите, почему не работает линк?
16 июл 19, 17:16    [21927806]     Ответить | Цитировать Сообщить модератору
 Re: Service broker procedure. Access to the remote server is denied  [new]
invm
Member

Откуда: Москва
Сообщений: 9123
Придется включить у БД trustworthy.
16 июл 19, 17:34    [21927821]     Ответить | Цитировать Сообщить модератору
 Re: Service broker procedure. Access to the remote server is denied  [new]
IlyamI
Member

Откуда:
Сообщений: 55
invm,

проставил у обеих БД это свойство в ТРУ, не помогло
16 июл 19, 17:47    [21927838]     Ответить | Цитировать Сообщить модератору
 Re: Service broker procedure. Access to the remote server is denied  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1172
IlyamI,

у вас процедура 1 это процедура активации очереди?
16 июл 19, 17:58    [21927860]     Ответить | Цитировать Сообщить модератору
 Re: Service broker procedure. Access to the remote server is denied  [new]
IlyamI
Member

Откуда:
Сообщений: 55
felix_ff,

Да, это процедура, которая вызывается при появлении сообщения в очереди.
16 июл 19, 18:06    [21927871]     Ответить | Цитировать Сообщить модератору
 Re: Service broker procedure. Access to the remote server is denied  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7399
IlyamI,

судя по сообщения, связанный сервер должен быть подключен через доверительное соединение, т.е. Win авторизация.
16 июл 19, 18:20    [21927888]     Ответить | Цитировать Сообщить модератору
 Re: Service broker procedure. Access to the remote server is denied  [new]
invm
Member

Откуда: Москва
Сообщений: 9123
IlyamI
проставил у обеих БД это свойство в ТРУ, не помогло
Не надо у обоих. Надо у вызывающей.
Владелец БД кто?
16 июл 19, 18:27    [21927897]     Ответить | Цитировать Сообщить модератору
 Re: Service broker procedure. Access to the remote server is denied  [new]
IlyamI
Member

Откуда:
Сообщений: 55
invm,

Владелец базы - SA

  alter proc test_link
  with execute as 'dbo'
  as

  begin
  select * from [remote server].[remote db].[dbo].[LOGS]
	
  end

  exec test_link

вызов процедуры заканчивается ошибкой.

Если закомментировать "with execute as 'dbo'", то из-под моей учетки работает.
16 июл 19, 18:36    [21927905]     Ответить | Цитировать Сообщить модератору
 Re: Service broker procedure. Access to the remote server is denied  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1172
IlyamI,

Вам предстоит изрядный гемморой.
Процедуры активации работают в контексте execute as user выражения, в связи с этим моментом вы заперты в песочнице, а поскольку к линкованным серверам доступ имеют логины а не пользователи, вам необходимо олицетворение контекста логина который будет сопоставляться с удаленным логином (аля прокси)

в ближайшем рассмотрении это должно выглядеть как то так:
create login proxy_for_remotesrv with password = 'strong_password';
deny connect to proxy_for_remotesrv;
go
sp_addlinkedsrvlogin N'remote server', N'false', N'proxy_for_remotesrv', N'remote_login', N'remote_login_password';


после чего в процедуре активации вам необходимо будет применить олицетворение логина
execute as login = 'proxy_for_remotesrv';
select * from [remote server].[db].[dbo].[table];
revert;


trustworthy можно не включать если озаботитесь безопасностью и подпишите процедуру сертификатом с нужными правами
16 июл 19, 19:26    [21927937]     Ответить | Цитировать Сообщить модератору
 Re: Service broker procedure. Access to the remote server is denied  [new]
invm
Member

Откуда: Москва
Сообщений: 9123
IlyamI,

Вы делаете что-то не то или не там.
+ Изучайте
use master;
create login TestLogin with password = '', check_policy = off;
exec sys.sp_addlinkedserver @server = N'loopback', @srvproduct = N'', @provider = N'SQLNCLI', @datasrc = @@servername;
exec sys.sp_serveroption @server = N'loopback', @optname = N'data access', @optvalue = N'true';
exec sys.sp_addlinkedsrvlogin @rmtsrvname = N'loopback', @useself = N'false', @locallogin = null, @rmtuser = N'TestLogin', @rmtpassword = N'';
go

create database Test001;
alter authorization on database::Test001 to sa;
go

use Test001;
go

create procedure dbo.spTest
as
begin
 select top (1) system_user, * from loopback.master.dbo.spt_values;
end;
go

exec dbo.spTest;
go

alter procedure dbo.spTest
with execute as 'dbo'
as
begin
 select top (1) system_user, * from loopback.master.dbo.spt_values;
end;
go

exec dbo.spTest;
go

alter database Test001 set trustworthy on;
go

exec dbo.spTest;
go

use master;
exec sys.sp_dropserver @server = N'loopback', @droplogins = N'droplogins';
drop login TestLogin;
go

drop database Test001;
go
16 июл 19, 19:35    [21927942]     Ответить | Цитировать Сообщить модератору
 Re: Service broker procedure. Access to the remote server is denied  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1172
ой не про trustworthy я глупость сморозил мы ж из базы вылазим за логинами.
16 июл 19, 19:36    [21927944]     Ответить | Цитировать Сообщить модератору
 Re: Service broker procedure. Access to the remote server is denied  [new]
IlyamI
Member

Откуда:
Сообщений: 55
Всем спасибо за помощь.

Сегодня все заработало, хотя я ничего не настраивал. Админы божаться, что тоже ничего не делали.

Вопрос закрыт. Все работает.
17 июл 19, 14:02    [21928561]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить