Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Как настроить доступ к БД SSIDB для application role?  [new]
forel
Member

Откуда:
Сообщений: 29
Доброго дня и с прошедшими праздниками.

Есть две БД: MyDB и SSISDB.
В MyDB есть роль приложения MyAppRole (application roles).

Подскажите, пожалуйста, как настроить доступ для роли приложения MyAppRole чтобы из-под неё можно было достучаться до SSISDB и в дальнейшем запускать разные пакеты?

Спасибо!
9 янв 16, 15:08    [18655626]     Ответить | Цитировать Сообщить модератору
 Re: Как настроить доступ к БД SSIDB для application role?  [new]
forel
Member

Откуда:
Сообщений: 29
Забыл добавить:

Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
May 14 2014 18:34:29
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )
9 янв 16, 15:09    [18655635]     Ответить | Цитировать Сообщить модератору
 Re: Как настроить доступ к БД SSIDB для application role?  [new]
Glory
Member

Откуда:
Сообщений: 104751
forel
как настроить доступ для роли приложения MyAppRole чтобы из-под неё можно было достучаться до SSISDB

А в хелпе что-то неясно написано ?

forel
и в дальнейшем запускать разные пакеты?

А вы каким способом собственно собрались эти пакеты запускать ?
9 янв 16, 15:36    [18655728]     Ответить | Цитировать Сообщить модератору
 Re: Как настроить доступ к БД SSIDB для application role?  [new]
forel
Member

Откуда:
Сообщений: 29
Glory, приветствую.

На счет хелпа: увы, я не разобрался. Для пользователя без application role настроить доступ могу, но если пользователь работает в приложении, которое, в свою очередь подключается к MSSQL Server и в котором для него активируется application role, то здесь возникает проблема при доступе к SSISDB.

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


Ради эксперимента активировал юзера guest в SSISDB и включил его в роль ssis_admin - это ничем не помогло.

Может быть вы можете поделиться ссылкой на нужный материал?

Для запуска пакета использую следующий скрипт:
declare 
		@mycmd nvarchar(max),
		@file_name nvarchar(50)

	select @file_name = 'FOR_TEST_ONLY'

	set @mycmd = N'
			Declare @execution_id bigint
			EXEC [SSISDB].[catalog].[create_execution] @package_name=N''Package.dtsx'', @execution_id=@execution_id OUTPUT, @folder_name=N''Oracle_Integration_Packages'', @project_name=N''Integration Services Project1'', @use32bitruntime=False, @reference_id=Null
			Select @execution_id
			DECLARE @var0 nvarchar(50) = '''+@file_name+'''
			EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=30, @parameter_name=N''File_Number'', @parameter_value=@var0
			DECLARE @var1 smallint = 1
			EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N''LOGGING_LEVEL'', @parameter_value=@var1
			EXEC [SSISDB].[catalog].[start_execution] @execution_id
			'

	exec sp_executesql @mycmd
9 янв 16, 16:06    [18655821]     Ответить | Цитировать Сообщить модератору
 Re: Как настроить доступ к БД SSIDB для application role?  [new]
Glory
Member

Откуда:
Сообщений: 104751
forel
то здесь возникает проблема при доступе к SSISDB.

Откуда вы знаете, что проблема возникает ?
Вам об этом сообщает сервер ?

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

Зачем создавать application role в одной базе, если нужны права нужны в другой ?
9 янв 16, 16:31    [18655896]     Ответить | Цитировать Сообщить модератору
 Re: Как настроить доступ к БД SSIDB для application role?  [new]
forel
Member

Откуда:
Сообщений: 29
Glory

Относительно необходимости использования роли приложения: в используемом приложении таким образом реализовано разграничение доступа. При входе в приложение для пользователя активируется роль приложения в соответствии с необходимыми полномочиями - здесь я ничего изменить не могу.

И далее при запуске указанного скрипта из приложения получаю сообщение от сервера об отсутствии прав доступа к БД SSISDB.
При этом в Management Studio при выполнении того же скрипта от имени того же пользователя каких-либо проблем не возникает.

Соответственно, хотелось бы разобраться с настройками прав для роли приложения (если я правильно определил, то затык именно в это) чтобы иметь возможность запускать пакеты, хранящиеся в SSISDB.
9 янв 16, 17:31    [18656163]     Ответить | Цитировать Сообщить модератору
 Re: Как настроить доступ к БД SSIDB для application role?  [new]
forel
Member

Откуда:
Сообщений: 29
Уточнение: приложение и Management Studio запускаю от имени одного и того же пользователя.

Сообщение от сервера при попытке выполнить скрипт из приложения:
The server principal "MyDomen\user_1" is not able to access the database "SSISDB" under the current security context.
9 янв 16, 17:38    [18656197]     Ответить | Цитировать Сообщить модератору
 Re: Как настроить доступ к БД SSIDB для application role?  [new]
Glory
Member

Откуда:
Сообщений: 104751
forel
Уточнение: приложение и Management Studio запускаю от имени одного и того же пользователя.

И где же в вашем в Management Studioскрипте "активируется роль приложения в соответствии с необходимыми полномочиями " ?

forel
Сообщение от сервера при попытке выполнить скрипт из приложения:
The server principal "MyDomen\user_1" is not able to access the database "SSISDB" under the current security context.

И где тут что-то про guest ?
9 янв 16, 17:56    [18656284]     Ответить | Цитировать Сообщить модератору
 Re: Как настроить доступ к БД SSIDB для application role?  [new]
invm
Member

Откуда: Москва
Сообщений: 9646
forel
Сообщение от сервера при попытке выполнить скрипт из приложения:
The server principal "MyDomen\user_1" is not able to access the database "SSISDB" under the current security context.
Ошибка говорит о том, что гость в SSISDB запрещен.
9 янв 16, 19:11    [18656504]     Ответить | Цитировать Сообщить модератору
 Re: Как настроить доступ к БД SSIDB для application role?  [new]
forel
Member

Откуда:
Сообщений: 29
Приношу извинения за задержку - выбили из колеи.

Позвольте вернуться к проблеме.
Glory, скрипт из SSMS это не демонстрация ошибки, а скорее для меня проверка того, что без использования роли приложения необходимый для запуска пакета скрипт работает.

invm , обратил внимание на ваш комментарий - еще раз проверил - действительно, пользователь guest в SSISDB неактивен. Видимо, после какого-то этапа проверки я его отключил. Сейчас снова предоставил ему право connect.

Таким образом чтобы сымитировать запуск пакета из приложения выполнил следующий скрипт:
--1)
select suser_name(), user_name()
--Result: MyDomen\user_1	MyDomen\user_1

sp_setapprole 'RAS_SSIS', 'Pa$$W0rd', @fCreateCookie = true, @cookie = @cookie output

select @cookie, suser_name(), user_name()
--Result: 0x17393...	MyDomen\user_1		RAS_SSIS

--2)
declare 
		@mycmd nvarchar(max),
		@file_name nvarchar(50)

	select @file_name = 'FOR_TEST_ONLY'

	set @mycmd = N'
			Declare @execution_id bigint
			EXEC [SSISDB].[catalog].[create_execution] @package_name=N''Package.dtsx'', @execution_id=@execution_id OUTPUT, @folder_name=N''Oracle_Integration_Packages'', @project_name=N''Integration Services Project1'', @use32bitruntime=False, @reference_id=Null
			Select @execution_id
			DECLARE @var0 nvarchar(50) = '''+@file_name+'''
			EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=30, @parameter_name=N''File_Number'', @parameter_value=@var0
			DECLARE @var1 smallint = 1
			EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N''LOGGING_LEVEL'', @parameter_value=@var1
			EXEC [SSISDB].[catalog].[start_execution] @execution_id
			'

exec sp_executesql @mycmd


В результате выполнения второй части скрипта получаю от сервера сообщение об ошибке:
Msg 916, Level 14, State 1, Line 3
(1 row(s) affected)
Msg 15357, Level 16, State 1, Procedure start_execution, Line 35
The current security context was set by "SetApprole". It cannot be reverted by statement "Revert".


Подскажите, пожалуйста, куда мне еще необходимо заглянуть?

Заранее премного блогдарен.
18 янв 16, 15:46    [18696434]     Ответить | Цитировать Сообщить модератору
 Re: Как настроить доступ к БД SSIDB для application role?  [new]
Glory
Member

Откуда:
Сообщений: 104751
forel
а скорее для меня проверка того, что без использования роли приложения необходимый для запуска пакета скрипт работает.

Странный вы.
не работает с аппликейшен ролью, а проверочный скрипт пишите без аппликейшен роли

forel
В результате выполнения второй части скрипта получаю от сервера сообщение об ошибке:
Msg 916, Level 14, State 1, Line 3
(1 row(s) affected)
Msg 15357, Level 16, State 1, Procedure start_execution, Line 35
The current security context was set by "SetApprole". It cannot be reverted by statement "Revert".



Подскажите, пожалуйста, куда мне еще необходимо заглянуть?

В Procedure start_execution, Line 35
18 янв 16, 15:57    [18696529]     Ответить | Цитировать Сообщить модератору
 Re: Как настроить доступ к БД SSIDB для application role?  [new]
forel
Member

Откуда:
Сообщений: 29
Glory, спасибо за подсказку.
Посмотрел через Profiler выполненяемые инструкции процедуры и увидел, что ошибка вызывается при попытке выполнить инструкцию revert:
IF(
            EXISTS(SELECT [name]
                    FROM sys.server_principals
                    WHERE [sid] = @suser_sid AND [type] = 'S')  
            OR
            EXISTS(SELECT [name]
                    FROM sys.database_principals
                    WHERE ([sid] = @caller_sid AND [type] = 'S')) 
            )

REVERT


Поискав решение подобной ситуации нашел только предложение использовать Job для запуска данной процедуры.

Возможно, есть более изящное решение без использования Job? Если вам оно известно, можете на него намекнуть?

Еще раз спасибо.
18 янв 16, 17:27    [18697349]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить