【SQL CLR】DBから外部リソースにアクセスするための設定

投稿日：

【SQL Server 2005】

SQL CLRを利用してストアドプロシージャからPOPサーバにアクセスするプログラムを書いたときのメモ。POPアクセスのコードはここを参考に。

ストアドプロシージャでSQL CLRを利用する場合、.NETで実現できることは基本できるようですが、外部のPOPサーバにアクセスしないといけないので、気にしないといけないのがセキュリティ権限。ＳＱＬ CLRの作り方はここを参考に。

データベースから外部へのアクセス権限に関して参考になるのが、こことここ。

最初はコンパイル時に下のようなエラーが出てきたけど、よく分からず。

アセンブリ 'SqlServer_dbPOP' の CREATE ASSEMBLY が失敗しました。 PERMISSION_SET が EXTERNAL_ACCESS の場合、アセンブリ 'SqlServer_dbPOP' は許可されません。

アセンブリが許可されるのは、データベース所有者 (DBO) に EXTERNAL ACCESS ASSEMBLY 権限があり、 データベースの TRUSTWORTHY データベース プロパティが有効になっている場合、 または、アセンブリが証明書または非対称キーで署名されており、 それに対応するログインに EXTERNAL ACCESS ASSEMBLY 権限がある場合です。

このデータベースを復元またはアタッチした場合は、 データベース所有者がこのサーバー上の正しいログインにマップされていることを確認してください。 マップされていない場合は、sp_changedbowner を使用して問題を解決してください。

調べた結果、まずデータベース側の設定はManagement Studioでデータベースを右クリック→プロパティで「信頼可能」が「True」になっている必要がある。

image

ＧＵＩで変更できなかったので、下記SQLを実行してTrueにする。データベースから外部にアクセスするときにはこの設定が必要。

ALTER DATABASE (データベース名) SET TRUSTWORTHY ON

次はVisual Studio側でSQL CLRプロジェクトのプロジェクト→プロパティでアクセスの許可レベルを外部に設定。

image

これでデータベースから外に出れるようになるはず。

さらに登録したストアドプロシージャを他のデータベースにコピーする場合はManagement Studioでデータベースを右クリック タスク→スクリプトの生成 を選んで、スクリプト生成ウィザードを起動。

オプションは「If NOT EXISTSを含める」と「スクリプトの削除」をTrueにしとくと、もし既に登録していた場合、削除してから登録してくれるので便利。

image

もちろんアセンブリとストアドプロシージャを選んでスクリプトを生成。

このままのスクリプトを実行してもVisual Studioで設定した「アクセス許可のレベル」は設定してくれないので、アセンブリを登録するSql文に下記を追記

CREATE ASSEMBLY [sp_mail_netpop] FROM 0x4D5A WITH PERMISSION_SET = EXTERNAL_ACCESS

詳しくはここ。

アセンブリのセキュリティを設定しないと

Request for the permission of type 'System.Net.DnsPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=

と怒られ続ける・・・。これでちょっとはまった。