Security in the CLR World Inside SQL Server One of the major benefits of writing .NET code to run in the Common Language Runtime (CLR) hosted in any environment is code access security (CAS). CAS provides a code-based-rather than user-based-authorization scheme to prevent various kinds of luring and other code attacks. But how does that security scheme coexist with SQL Server 2005’s own, newly enhanced security features? By default your .NET code is reasonably secure, but it’s all too easy for the two security schemes to butt heads and cause you grief. In this article I’ll look briefly at the concept behind CAS and a few new security features in SQL Server 2005, then explore how to make the two systems work for you instead of against you as you take advantage of these advanced programming features in SQL Server. The ability to write stored procedures and other code modules in C#, VB, or any other .NET language has long been one of the most enticing features in what was then the upcoming SQL Server 2005. Both developers and DBAs are finally able to break the shackles of Transact-SQL (T-SQL) and C++ in extended stored procedures, and write database code in a real, productive language! | " |
SQLCLR code can’t do anything more in a database than an equivalent T-SQL code module running under the same security context.
| " |
At the same time, the prospect of running .NET code within the database server’s memory space scared some people to death, notably some DBAs who are responsible for protecting the integrity of data and make sure that the server stays up and running as close to 24/7 as possible. The thought of running some developer’s code, code that has full access to the .NET Framework and the Win32 API, caused many a DBA to swear that such code will run in the server over his or her dead body. I speak at conferences and do a lot of training, and I’ve asked both students and clients whether .NET code in the server scares them and why. Here are a few typical concerns: - Vague security issues, mostly dealing with an increased surface area for attacks, but apparently more a nervousness about what is new and not yet understood
- Needing to learn a whole new set of skills in order to be able to assess whether code is safe and secure
- A blurring between data and code, particularly with the new ability to create user-defined types in .NET code
- Yet another way that code can mess up the server, even though the OLE automation (SP_OS*) and command shell system (xp_cmdshell) stored procedures have long been available
As a practical matter, .NET Framework code in SQL Server 2005, often referred to as SQLCLR code because it’s based on the .NET Common Language Runtime (CLR), is just another code module that exists and runs within SQL Server. It’s new and it’s cool, but it’s just code. It isn’t a plug-in replacement for T-SQL, which is still best for data access code. But SQLCLR code opens up whole new possibilities for sophisticated database applications. Sooner or later most every DBA will be faced with a compelling use for it and will have to make a final decision about whether to let it into the database. In this article, I’ll explore one of the biggest concerns about SQLCLR code: just how secure is it? Actually, I’ll deliberately blur two important considerations-security and reliability. Security means keeping data safe and reliability means keeping SQL Server safe; reliability is often confused with security. So while my main focus here is on security, I’ll talk a little about reliability as well. I’ll assume that you are familiar with the benefits and basics of writing .NET code in SQL Server 2005, including these concepts and topics: - Assemblies as the unit of packaging, deployment, and versioning
- .NET code access security basics
- The new security features in SQL Server 2005
In other words, this is not an introductory article on SQLCLR code. See the Resources sidebar to explore these basic concepts in more depth. | & | | 
By: Don Kiely Don Kiely, MVP, MSCD, is senior technology consultant specializing in developing secure desktop and Web applications that integrate databases and related technologies, using tools including SQL Server, Visual Basic, ASP.NET, and XML. When he isn't writing software, he's writing about technology, speaking about it at conferences, and training others about it. Read his blog at http://www.sqljunkies.com/weblog/donkiely/.
donkiely@computer.org | Fast Facts | | The prospect of running .NET Framework code within SQL Server 2005 is exciting to some and a threat to others. This article explores the security issues of SQLCLR code so that both developers and DBAs can make informed decisions about its use. The good news is that Microsoft did a great job bringing together the security systems of SQL Server and the Common Language Runtime, with tools to control code. But there are some interesting features to watch for and to take advantage of! | |
|