CLR - Pro SQL Server Internals - page 256

Listing 13-5. Creating a login with EXTERNAL ACCESS permissions from the key pair file

use master
go

-- Creating master key if it does not exist
if not exists
(
select *
from sys.symmetric_keys
where name = '##MS_DatabaseMasterKey##'
)
create master key encryption
by password = '$tr0ngPas$word1'
go

create asymmetric key KeyExternalAccess
from file = 'c:\SQL\CLRSecurity\CLRSecurityKey.snk'
go

create login CLRExtAccessLogin
from asymmetric key KeyExternalAccess
go

grant external access assembly to CLRExtAccessLogin
go

Now you should be able to register successfully the signed assembly that requires the EXTERNAL_ACCESS
permission set.

■ An alternative option to register the assembly with the EXTERNAL_ACCESS or UNSAFE permission set is to mark
the hosting database as TRUSTWORTHY . however, this action violates security best practices.

Note

All of those security requirements must be taken into consideration when you decide to start using CLR
integration in your systems, especially if you are an independent software vendor (ISV) and you are planning to
deploy the software to a large number of customers.

Performance Considerations
It is not easy to compare the performance of CLR and T-SQL routines. Both technologies are different in nature, and
they should be used for different tasks. T-SQL is an interpreted language, which is optimized for set-based logic
and data access. CLR, on the other hand, produces compiled code that works best with imperative logic.
Even with imperative code, you need to decide if you want to implement it in CLR or on the client-side, perhaps
running on application servers. CLR works within the SQL Server process. While on one hand, it eliminates network
traffic and can provide you with the best performance due to its “closeness” to the data as compared to the code
running on the application server, CLR adds to the load of SQL Server. It is usually easier and cheaper to add more
application servers rather than upgrading the SQL Server box.