Любопытно, что если в название этого поста добавить слова 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, я знаю еще много умных аббревиатур, которые почерпнул здесь - https://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

Это свойство базы описано здесь: https://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 (https://msdn.microsoft.com/ru-ru/library/ms174430.aspx), созданный с ее помощью асимметричный ключ нельзя экспортнуть в файл для разных надобностей. Например, чтобы скинуть на флешку и держать при себе для вящей безопасности под подушкой. Или, скажем, подписать сборку. К сожалению, команда бэкапа асимметричного ключа в SQL Server 2008 в принципе отсутствует, хотя в то же время команда бэкапа сертификата BACKUP CERTIFICATE имеется (https://msdn.microsoft.com/ru-ru/library/ms178578.aspx). Более того, имеются также команды бэкапа мастер-ключа базы BACKUP MASTER KEY (https://msdn.microsoft.com/ru-ru/library/ms174387.aspx), бэкапа мастер-ключа всего сервиса BACKUP SERVICE MASTER KEY (https://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 (https://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, как уже рассматривалось.

Автор: Алексей Шуленин