Подписание внешней или небезопасной сборки


Любопытно, что если в название этого поста добавить слова SQL Server: "Подписание внешней или небезопасной сборки в SQL Server", при публикации возникает ошибка The "Name" of a post and/or article must be unique. Your post was not saved or updated. Кто-нибудь знает, почему блоги MSDN так не любят SQL Server?


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


Теперь по сабжу. Как известно со времен SQL Server 2005, можно писать на .NET-языках хранимые процедуры, функции, триггеры, агрегаты и типы. Содержащую их сборку требуется продеплоить на  SQL Server, где она будет выполняться внутри Common Language Runtime, но не того, который действует на уровне операционки, а некоторой его подкопии, которую разработчики SQL Server засунули в sqlservr.exe. В зависимости от криминальности действий, выполняемых вашей сборкой, ей задаются уровни доступа safe (по умолчанию, доступ к ресурсам в пределах данного SQL Server), external_access (SqlClientPermission, не ограничиваясь Context connection = true, а также доступ к файловой системе, переменным окружения, реестру, SMTP, DNS, X.509, я знаю еще много умных аббревиатур, которые почерпнул здесь - http://msdn.microsoft.com/ru-ru/library/ms345101.aspx) и unsafe (неограниченный доступ к внешним ресурсам, включая неуправляемый код). При деплойменте на SQL Server external_access или unsafe сборки выдается следующее сообщение:


CREATE ASSEMBLY for assembly 'SqlClassLibrary' failed because assembly 'SqlClassLibrary' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS.  The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.


 


<Поскольку я не знаю, как сюда закачивать картинки, рисунков пока не будет>


рис.1


 


В случае пользовательской (несистемной) базы данных можно воспользоваться первой рекомендацией и попытаться установить с ней доверительные отношения:


 


Первая рекомендация:


alter database CLRLinqDemo set trustworthy on


 


Это свойство базы описано здесь: http://msdn.microsoft.com/ru-ru/library/ms187861.aspx. Если вы хорошо знаете базу и не оставляли ее без присмотра с момента создания, вы, в принципе, можете ей доверять, а вместе с вами и SQL Server, на котором она крутится. Если же доступ к ней получали сомнительные граждане, а тем паче им дозволялось ее детачить/бэкапить и уносить с собой под благовидным предлогом что-то там сделать и вернуть, это чревато различными неприятностями, обнаружить которые вы рискуете слишком поздно – см., например, "Засада на админа" (http://sqlclub.ru/forum/viewtopic.php?t=961). В таких ситуациях trustworthy лучше оставить в off – оно здоровее выйдет. Кроме того, поменять свойство trustworthy, по определению, не удастся у системных баз, к коим относится, в частности, tempdb. В этих случаях нужно воспользоваться второй рекомендацией и подписать сборку сертификатом или асимметричным ключом, которому на SQL Server соответствует логин с правами EXTERNAL ACCESS ASSEMBLY.


 


Несмотря на то, что в Т-SQL имеется команда CREATE ASYMMETRIC KEY (http://msdn.microsoft.com/ru-ru/library/ms174430.aspx), созданный с ее помощью асимметричный ключ нельзя экспортнуть в файл для разных надобностей. Например, чтобы скинуть на флешку и держать при себе для вящей безопасности под подушкой. Или, скажем, подписать сборку. К сожалению, команда бэкапа асимметричного ключа в SQL Server 2008 в принципе отсутствует, хотя в то же время команда бэкапа сертификата BACKUP CERTIFICATE имеется (http://msdn.microsoft.com/ru-ru/library/ms178578.aspx). Более того, имеются также команды бэкапа мастер-ключа базы BACKUP MASTER KEY (http://msdn.microsoft.com/ru-ru/library/ms174387.aspx), бэкапа мастер-ключа всего сервиса BACKUP SERVICE MASTER KEY (http://msdn.microsoft.com/ru-ru/library/ms190337.aspx), но простой команды BACKUP ASYMMETRIC KEY почему-то нет, хотя напрашивается. К счастью, команда создания асимметричного ключа умеет импортировать его из файла пары ключей CREATE ASYMMETRIC KEY [...] FROM  FILE = 'c:\keys\myKey.snk', либо из уже подписанной сборки CREATE ASYMMETRIC KEY […] FROM EXECUTABLE FILE = 'c:\dba\bin\Assembly.dll'. Его – я имею в виду открытый ключ. Закрытый, вообще говоря, на стороне SQL Server при данном раскладе не нужен. Он нужен только для того, чтобы подписать им сборку, а чтобы ее выполнить, открытого ключа достаточно.


Прежде, чем создавать ключи/сертификаты в базе, база должна иметь мастер-ключ. Это симметричный ключ, который используется для защиты закрытых ключей сертификатов и асимметричных ключей внутри базы. Он создается с использованием алгоритма Triple DES с длиной ключа 128 и защищается паролем. Соответственно, посмотреть его можно при помощи


 


select * from  sys.symmetric_keys


 


Выглядит он так:


name   principal_id     symmetric_key_id      key_length      key_algorithm algorithm_desc           create_date            modify_date    key_guid          key_thumbprint          provider_type cryptographic_provider_guid            cryptographic_provider_algid


##MS_DatabaseMasterKey##            1          101      128      D3        TRIPLE_DES     2009-05-10 21:32:08.487            2009-05-10 21:32:08.500        05965C01-E84A-483C-9677-7F2B0FD56D1A NULL    NULL    NULL    NULL


 


Соответственно, создать его можно так:


if not exists (select 1 from sys.symmetric_keys where name = '##MS_DatabaseMasterKey##')


 create master key encryption by password = 'abra cadabra'


 


Вторая рекомендация.


 


Шаг 1


Создать файл с парой ключей можно, например, средствами Visual Studio, в SDK которой имеется утилита для создания крепкого имени sn.exe (http://msdn.microsoft.com/ru-ru/library/k5b5tt23.aspx), генерящая пару ключей длины 1024 бит по умолчанию.  Выполняем из командной строки "%ProgramFiles%\Microsoft Visual Studio 8\SDK\v2.0\Bin\sn.exe" -k c:\Temp\PublicPrivateKeyFile.snk


 


 


<Поскольку я не знаю, как сюда закачивать картинки, рисунков пока не будет>


рис.2


 


и получаем пару открытый/закрытый ключ в файле c:\Temp\PublicPrivateKeyFile.snk.


 


Шаг 2


Подписываем сборку закрытым ключом. Например, открываем файл AssemblyInfo.cs и добавляем в него атрибут AssemblyKeyFile


[assembly: AssemblyKeyFile(@"c:\Temp\PublicPrivateKeyFile.snk")]


из пространства имен System.Reflection


 


<Поскольку я не знаю, как сюда закачивать картинки, рисунков пока не будет>


рис.3


 


Строим сборку (меню Build).


 


Шаг 3


Переходим в SQL Server. Нам нужно создать логин на основе асимметричного ключа и грантовать ему право выполнять небезопасные сборки. Тогда сборки, подписанные этим ключом, будут выполняться без лишних вопросов от имени этого товарища, а не сервисного эккаунта. Для создания логина на основе асимметричного ключа, этот ключ должен иметься в БД master.


 


use master


if exists (select 1 from sys.server_principals where name = 'SQLCLRLogin') drop login SQLCLRLogin


if exists (select 1 from sys.asymmetric_keys where name = 'SQLCLRKey') drop asymmetric key SQLCLRKey


create asymmetric key SQLCLRKey from file = 'c:\Temp\PublicPrivateKeyFile.snk'


create login SQLCLRLogin from asymmetric key SQLCLRKey


grant external access assembly to SQLCLRLogin


 


Шаг 4


Создаем assembly и UDF из нее


 


use tempdb


if exists (select 1 from sys.objects where type_desc = 'CLR_TABLE_VALUED_FUNCTION' and name = 'Dir')


 drop function dbo.Dir


if exists (select 1 from sys.assemblies where is_user_defined = 1 and name = 'MyAssembly')


 drop assembly MyAssembly


go 


create assembly MyAssembly from 'C:\Temp\SqlServerProject1\bin\Debug\SqlClassLibrary.dll' with permission_set = external_access


select * from sys.assembly_files


go


create function dbo.Dir(@folder nvarchar(1000)) returns table (fullName nvarchar(1000), dateModified datetime2, size bigint, isDir bit, parent nvarchar(1000)) as external name MyAssembly.UserDefinedFunctions.InitMethod


go


 


Шаг 5


Выполняем функцию и убеждаемся, что все работает.


 


select * from dbo.Dir('c:\Temp')


 


 


<В качестве картинки здесь предлагают сослаться на URL> 


рис.4


 


<То есть опубликовать ее где-то в другом месте. Это неудобно.>  


рис.5


 


Теперь о некоторых модификациях Рекомендации 2.


 


Модификация 1.


 


Шаг 3


Выше говорилось, что для выполнения сборки иметь private key пары на стороне SQL Server необязательно. Поэтому его можно удалить.


 


use master


if exists (select 1 from sys.server_principals where name = 'SQLCLRLogin') drop login SQLCLRLogin


if exists (select 1 from sys.asymmetric_keys where name = 'SQLCLRKey') drop asymmetric key SQLCLRKey


create asymmetric key SQLCLRKey from file = 'c:\Temp\PublicPrivateKeyFile.snk'


alter asymmetric key SQLCLRKey remove private key


create login SQLCLRLogin from asymmetric key SQLCLRKey


grant external access assembly to SQLCLRLogin


 


Модификация 2.


 


Внешний ключ можно выделить из файла пары в отдельный файл и только на основе него создавать асимметричный ключ в SQL Server.


 


Шаг 1.


"%ProgramFiles%\Microsoft Visual Studio 8\SDK\v2.0\Bin\sn.exe" -k c:\Temp\PublicPrivateKeyFile.snk


"%ProgramFiles%\Microsoft Visual Studio 8\SDK\v2.0\Bin\sn.exe" -p c:\Temp\PublicPrivateKeyFile.snk c:\Temp\PublicKeyFile.snk


  


<Есть закладка My Photos, но она не дает туда ничего аплоудить> 


рис.6


 


Шаг 3


Используется только public key:


use master


if exists (select 1 from sys.server_principals where name = 'SQLCLRLogin') drop login SQLCLRLogin


if exists (select 1 from sys.asymmetric_keys where name = 'SQLCLRKey') drop asymmetric key SQLCLRKey


create asymmetric key SQLCLRKey from file = 'c:\Temp\PublicKeyFile.snk' 


create login SQLCLRLogin from asymmetric key SQLCLRKey


grant external access assembly to SQLCLRLogin


 


Шаг 5


Будет плакать, что Msg 15563, Level 16, State 1, Line 5. The asymmetric key has no private key set for it, будет колоться, но продолжать работать.


 


Модификация 3.


 


Также выше говорилось, что командой CREATE ASYMMETRIC KEY можно вытащить открытый ключ из подписанной dll'и. Поэтому можно опустить Шаг 1 создания файла пары ключей, подписав сборку непоср-но в св-вах проекта.


 


Шаг 2


Вместо атрибута [assembly: AssemblyKeyFile(@"c:\Temp\PublicPrivateKeyFile.snk")], заходим в свойства проекта, закладка Signing 


 


<Сейчас-то ладно, а как быть, если на картинках важные моменты изложения?> 


рис.7


 


Файл PublicPrivateKeyFile.snk будет создан в фолдере проекта (C:\Temp\SqlServerProject1). Либо выбрать не New, а Browse и указать имеющийся файл с парой ключей.


 


Шаг 3


Асимметричный ключ создается на основе публичного ключа из файла сборки


use master


if exists (select 1 from sys.server_principals where name = 'SQLCLRLogin') drop login SQLCLRLogin


if exists (select 1 from sys.asymmetric_keys where name = 'SQLCLRKey') drop asymmetric key SQLCLRKey


create asymmetric key SQLCLRKey from executable file = 'C:\Temp\SqlServerProject1\bin\Debug\SqlClassLibrary.dll'


create login SQLCLRLogin from asymmetric key SQLCLRKey


grant external access assembly to SQLCLRLogin


 


Далее Шаги 4, 5, как уже рассматривалось.

 


Comments (3)
  1. SQLClub says:

    Довелось читать разработку под SQL Server для разработчиков, прежде не использовавших технологии Microsoft.

  2. SQLClub says:

    Следующим логичным стремлением было превратить " Расширенные свойства файлов "Скрипт 1 в хранимую процедуру

Comments are closed.

Skip to main content