Profiling SQL Server2005 SQLCLR code security

The feed runs within SQL Server 2005. NET Framework code is an exciting thing or a threat? This series of articles will explore the security aspects of such SQLCLR code in a comprehensive way that developers and DBAs can learn from.

First, the introduction

One of the main advantages of writing. NET code that runs in the CLR under any environment in the host is Code access security (CAS).

CAS provides a code based rather than a user based authentication model to prevent intrusion of various code. But how does this security model coexist with the new, enhanced security features of SQL Server 2005 itself? By default, your. NET code is relatively secure, but these two security patterns are easy to conflict with and can easily cause problems. In this article, we will briefly analyze the concepts behind CAs and some of the security features introduced in SQL Server 2005, and then, in the next few, analyze how to implement the advanced programmable features provided by SQL Server while working together on both systems.

The good news is that in order to implement the security system provided by SQL Server and the common language runtime library, Microsoft has provided some tools to implement code control. However, there are many interesting problems!

The ability to write stored procedures and other code modules in C#,VB or any other. NET language has long been expected, and this is one of the most exciting features of SQL Server 2005. Ultimately, developers and DBAs can break the shackles of Transact-SQL (T-SQL) and C + + that exist in extended stored procedures and write database code in a truly highly productive language!

Also, run in the memory space of the database server. NET code scares some people, especially the DBAs who are responsible for protecting data integrity and ensuring that the servers run day and night. Run some developer code (with full access.) NET Framework and the Win32 API) has led many DBAs to insist that the maintenance of such code running on the server is beyond their capabilities.

By giving lectures at conferences and doing a lot of training, and I ask my classmates and clients, "Is it on the server?" NET code terrified them and why. " Finally, there are some of the following typical issues of concern:

· Vague security issues. Most of these are related to the current attack problem, but it is clear that more attention is being paid to what new content does not understand.

· Need to learn a new skill to assess whether the code is safe.

· There is a lot of ambiguity between the data and the code, especially for use. NET code to create a user-defined type this new ability.

· There is another way to "mix" code with the server, although OLE Automation (sp_os*) and command shell system (xp_cmdshell) stored procedures can be used to get people to run external code.

In fact, in SQL Server 2005. NET Framework code, often referred to as SQLCLR code, because it is based on. NET Common Language runtime library (CLR). In fact, it's just another code module that exists and runs inside SQL Server. It's new, it's cool, but it's still just code, but it's not a plug-in substitute for T-SQL (which is still the preferred way to implement the data access code); Instead, SQLCLR code opens up new possibilities for complex database applications. Sooner or later, most DBAs would use it and would have to make a final decision-whether to keep it in the database.

In this article, I'll explore one of the most concerned questions about SQLCLR code: How secure Is it? In fact, I will deliberately blur two important concepts-security and reliability. Security means keeping data secure, while reliability means keeping SQL Server secure; Reliability is often confused with security. So while I'm mainly talking about security, I have to deal with some reliability issues.

I will assume that you are familiar with writing in SQL Server 2005. NET code, the advantages and the basic knowledge. To summarize, include the following:

· Assembly, as a package, publish, and version-managed unit

· . NET code Access Security Basics

· New security features for SQL Server 2005

In other words, this article is not an introductory article about SQLCLR code.