Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Impersonate из Application Role в ServiceAccount  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1838
Добрый день.
Понадобилось сделать BULK INSERT из под роли приложения.
GRANT EXECUTE ON Test TO MyRole;

Пример рабочий:
ALTER PROCEDURE [dbo].[Test]
WITH EXECUTE AS 'DOMAIN\msa-bulkinsert$'
AS
BEGIN
  CREATE TABLE #TEMP
  (
    [code]  NVARCHAR(MAX) NULL
  )
  DECLARE
    @SQL NVARCHAR(MAX) = ''
  SET @SQL = N'
                       BULK INSERT #TEMP 
                       FROM ''' + '\\server\BulkInsert\20.05.2016_17.47.37.537.csv' + '''
                       WITH(
                               FIELDTERMINATOR = '';'',
                               ROWTERMINATOR = ''\n''
                              )'
  EXEC (@SQL)
  DROP TABLE #TEMP
END

А вот так не работет:
ALTER PROCEDURE [dbo].[Test]
AS
BEGIN
  CREATE TABLE #TEMP
  (
    [code]  NVARCHAR(MAX) NULL
  )
  DECLARE
    @SQL NVARCHAR(MAX) = ''
  SET @SQL = N'
                       BULK INSERT #TEMP 
                       FROM ''' + '\\server\BulkInsert\20.05.2016_17.47.37.537.csv' + '''
                       WITH(
                               FIELDTERMINATOR = '';'',
                               ROWTERMINATOR = ''\n''
                              )'
  EXEC (@SQL) AS LOGIN = 'DOMAIN\msa-bulkinsert$'
  -- или EXEC (@SQL) AS USER = 'DOMAIN\msa-bulkinsert$'
  DROP TABLE #TEMP
END

Cannot execute as the database principal because the principal "DOMAIN\msa-bulkinsert$" does not exist, this type of principal cannot be impersonated, or you do not have permission.

DECLARE @cookie varbinary(8000);
EXEC sp_setapprole 'MyRole', 'Pass', @fCreateCookie = true, @cookie = @cookie OUTPUT;
EXEC Test
EXEC sp_unsetapprole @cookie;


Owner БД совпадает с master и разрешение TRUSTWORTHY для БД выдано.
для DOMAIN\msa-bulkinsert$ выдано IMPERSONATE ANY LOGIN и ADMINISTER BULK OPERATIONS

Хотелось бы выполнить только EXEC внутри ХП а не всю ХП из под другой учетки.
27 май 16, 12:25    [19227440]     Ответить | Цитировать Сообщить модератору
 Re: Impersonate из Application Role в ServiceAccount  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8826
X-Cite,

database principal "DOMAIN\msa-bulkinsert$ exist?
27 май 16, 13:02    [19227625]     Ответить | Цитировать Сообщить модератору
 Re: Impersonate из Application Role в ServiceAccount  [new]
o-o
Guest
X-Cite
EXEC (@SQL) AS LOGIN = 'DOMAIN\msa-bulkinsert$'

Cannot execute as the database principal because the principal "DOMAIN\msa-bulkinsert$" does not exist, this type of principal cannot be impersonated, or you do not have permission.

что-то не то пишете.
не может такая ошибка про as the database principal быть
при выполнении AS LOGIN.
у меня выполняется от имени логина без проблем.
естественно, логин должен быть отмаплен в базу с процедурой.
причем база у меня без TRUSTWORTHY
27 май 16, 13:07    [19227648]     Ответить | Цитировать Сообщить модератору
 Re: Impersonate из Application Role в ServiceAccount  [new]
o-o
Guest
я думаю, ошибка у вас там про отсутствие прав на имперсонэйт логина.
а вы нам какую-то предыдущую ошибку скопировали
27 май 16, 13:20    [19227712]     Ответить | Цитировать Сообщить модератору
 Re: Impersonate из Application Role в ServiceAccount  [new]
o-o
Guest
вот такое вы должны получать при вашем опубликованном коде:
Msg 15406, Level 16, State 1, Procedure usp_test_x, Line 3
Cannot execute as the server principal because the principal "l" does not exist, this type of principal cannot be impersonated, or you do not have permission.

и это потому, что у исполнителя процедуры нет имперсонэйта на этот логин.
27 май 16, 13:24    [19227731]     Ответить | Цитировать Сообщить модератору
 Re: Impersonate из Application Role в ServiceAccount  [new]
o-o
Guest
вот ваш же код в моем исполнении.
учетка hd03\ee25989 -- сисадмин на этом сервере,
у ней нет проблем имперсонэйтить логин l.
поэтому все хорошо.

К сообщению приложен файл. Размер - 29Kb
27 май 16, 13:27    [19227742]     Ответить | Цитировать Сообщить модератору
 Re: Impersonate из Application Role в ServiceAccount  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1838
Cannot execute as the server principal because the principal "DOMAIN\msa-bulkinsert$" does not exist, this type of principal cannot be impersonated, or you do not have permission.
Cannot execute as the database principal because the principal "DOMAIN\msa-bulkinsert$" does not exist, this type of principal cannot be impersonated, or you do not have permission.

В зависимости от
EXEC (@SQL) AS LOGIN = 'DOMAIN\msa-bulkinsert$'
EXEC (@SQL) AS USER = 'DOMAIN\msa-bulkinsert$'


Имперсонейт от доменной учетки работает.
Не работает имперсонейт от РОЛИ ПРИЛОЖЕНИЯ которая существует только на уровне БД. На уровне сервера ее нет, там guest идет.
И надо ей имперсонировать учетку уровня сервера чтобы сделать BULK.
Для этого надо в любом случае делать TRUSTWORTHY чтобы база была доверенная для сервера.

Так вот если запускать ХП целиком, то работает.
А если имперсонировать только кусок EXEC внутри ХП - то нет.
27 май 16, 15:29    [19228580]     Ответить | Цитировать Сообщить модератору
 Re: Impersonate из Application Role в ServiceAccount  [new]
Konst_One
Member

Откуда:
Сообщений: 11621
X-Cite
А если имперсонировать только кусок EXEC внутри ХП - то нет.


что простите , какой такой кусок ?
27 май 16, 16:02    [19228811]     Ответить | Цитировать Сообщить модератору
 Re: Impersonate из Application Role в ServiceAccount  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
X-Cite
А если имперсонировать только кусок EXEC внутри ХП - то нет.
У вас для роли приложения нету разрешения impersonate в пользователя БД DOMAIN\msa-bulkinsert$.
27 май 16, 16:05    [19228835]     Ответить | Цитировать Сообщить модератору
 Re: Impersonate из Application Role в ServiceAccount  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1838
Konst_One
X-Cite
А если имперсонировать только кусок EXEC внутри ХП - то нет.


что простите , какой такой кусок ?

ALTER PROCEDURE [dbo].[Test]
AS
BEGIN
  CREATE TABLE #TEMP  ([code] NVARCHAR(MAX) NULL)
  DECLARE
    @SQL NVARCHAR(MAX) = ''
  SET @SQL = N'
                       BULK INSERT #TEMP 
                       FROM ''' + '\\server\BulkInsert\20.05.2016_17.47.37.537.csv' + '''
                       WITH(FIELDTERMINATOR = '';'', ROWTERMINATOR = ''\n'')'
  EXEC (@SQL) AS LOGIN = 'DOMAIN\msa-bulkinsert$' -- Вот кусок. Сама ХП от роли приложения, А Exec в ней от другой учетки
  -- или EXEC (@SQL) AS USER = 'DOMAIN\msa-bulkinsert$'
  DROP TABLE #TEMP
END
27 май 16, 16:08    [19228857]     Ответить | Цитировать Сообщить модератору
 Re: Impersonate из Application Role в ServiceAccount  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1838
invm
X-Cite
А если имперсонировать только кусок EXEC внутри ХП - то нет.
У вас для роли приложения нету разрешения impersonate в пользователя БД DOMAIN\msa-bulkinsert$.

https://msdn.microsoft.com/en-us/library/ms173848.aspx

Собственно я его тут и не вижу.
Однако же
ALTER PROCEDURE [dbo].[Test]
WITH EXECUTE AS 'DOMAIN\msa-bulkinsert$'

Работает
27 май 16, 16:12    [19228883]     Ответить | Цитировать Сообщить модератору
 Re: Impersonate из Application Role в ServiceAccount  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1838
А все.. я наверное перепутал местами.
GRANT IMPERSONATE ON USER :: [DOMAIN\msa-bulkinsert$] TO MyRole
разрешил мне сделать задуманное.
27 май 16, 16:15    [19228916]     Ответить | Цитировать Сообщить модератору
 Re: Impersonate из Application Role в ServiceAccount  [new]
o-o
Guest
X-Cite
Не работает имперсонейт от РОЛИ ПРИЛОЖЕНИЯ которая существует только на уровне БД. На уровне сервера ее нет, там guest идет.
И надо ей имперсонировать учетку уровня сервера чтобы сделать BULK.
[/b]

я не работаю с ролями приложения,
но если у ней нет логина, как вы пишете("существует только на уровне БД"),
и это только DATABASE PRINCIPAL,
то тогда он вообще не может имперсонэйтить логин.

попробуйте просто от имени такого юзера выполнить exec as login = ...,
вывалится та же самая ошибка.
тут не спасет trustworthy, потому что как можно расширить песочницy принципалу, которого нет на уровне сервера?
27 май 16, 16:17    [19228925]     Ответить | Цитировать Сообщить модератору
 Re: Impersonate из Application Role в ServiceAccount  [new]
o-o
Guest
X-Cite
А все.. я наверное перепутал местами.
GRANT IMPERSONATE ON USER :: [DOMAIN\msa-bulkinsert$] TO MyRole
разрешил мне сделать задуманное.

ну так impersonate на ЮЗЕРА.
не на логина же.
нельзя юзеру без логина выдать impersonate на другого *логина*
27 май 16, 16:20    [19228943]     Ответить | Цитировать Сообщить модератору
 Re: Impersonate из Application Role в ServiceAccount  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1838
В общем достаточно иметь такие права:
CREATE LOGIN [DOMAIN\msa-bulkinsert$] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english];
ALTER SERVER ROLE [bulkadmin] ADD MEMBER [DOMAIN\msa-bulkinsert$];
CREATE USER [DOMAIN\msa-bulkinsert$] FOR LOGIN [DOMAIN\msa-bulkinsert$] WITH DEFAULT_SCHEMA=[dbo];
GRANT IMPERSONATE ON USER::[DOMAIN\msa-bulkinsert$] TO [MyApplicationRole];
ALTER DATABASE MyDataBase SET TRUSTWORTHY ON; -- И обязательно вот это


И тогда будет счастье.
ALTER PROCEDURE [dbo].[Test]
AS
BEGIN
		
		CREATE TABLE #TEMP
		(
			[code]      NVARCHAR(MAX) NULL
		)

		DECLARE @SQL NVARCHAR(MAX) = ''
		SET @SQL = N'
			BULK INSERT #TEMP 
			FROM ''' + '\\sqlserver\BulkInsert\20.05.2016_17.47.37.537.csv' + '''
			WITH(FIELDTERMINATOR = '';'', ROWTERMINATOR = ''\n'')'
		
		EXEC (@SQL) AS USER = 'DOMAIN\msa-bulkinsert$'
		
		DROP TABLE #TEMP
END


DECLARE @cookie varbinary(8000);
EXEC sp_setapprole 'MyApplicationRole', 'Password', @fCreateCookie = true, @cookie = @cookie OUTPUT;
BEGIN TRY
  EXEC Test
END TRY
BEGIN CATCH
  PRINT ERROR_MESSAGE()
END CATCH
EXEC sp_unsetapprole @cookie;
31 май 16, 12:15    [19240322]     Ответить | Цитировать Сообщить модератору
 Re: Impersonate из Application Role в ServiceAccount  [new]
o-o
Guest
а в чем смысл этого всего?
если юзеру уже даны права имперсонэйтить другого юзера,
логин которого является bulkadmin,
+ база TRUSTWORTHY,
то юзер(ваше application role) уже всегда в этой базе может исполнять все,
что позволено bulkadmin-у.
зачем заморочки с процедурой и исполнением лишь какого-то куска с такими-то правами?
напрямую execute as user = 'DOMAIN\msa-bulkinsert$' и любой код, подвластный bulkadmin-у, будет выполнен без проблем
31 май 16, 12:25    [19240357]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить