Developer .NET UPDATE--The CLR in SQL Server 2005--February 4, 2005

This Issue Sponsored By
This email newsletter comes to you free and is supported by the following advertisers, who offer products and services that might interest you. Please take a moment to visit these advertisers' Web sites and show your support for Developer .NET UPDATE.

VMware Workstation 4.5
http://www.vmware.com/wl/offer/1294/0

ALERT: How A Hacker Breaks Into Your Web Apps
https://download.spidynamics.com/1/ad/dev.asp?cs1_ContSupRef=I-N-.net1.25.05

Win a Harley at Visual Studio Connections
http://www.vsconnections.com

1. Developer .NET Perspectives

2. Announcements

3. New and Improved

Sponsor: VMware Workstation 4.5
Join the Virtual Revolution--try VMware(R) Workstation now, free! With award-winning VMware Workstation 4.5, you can:

VMware Workstation is powerful virtual machine software for developers and system administrators who want to revolutionize software development, testing and deployment in their enterprise. Essential features include: virtual networking, live snapshots, drag and drop, shared folders and PXE support, making VMware Workstation the most powerful and indispensable tool for enterprise IT developers and system administrators. Get your free trial now!
http://www.vmware.com/wl/offer/1294/0

1. Developer .NET Perspectives

by Bill Sheldon, [email protected]

The CLR's Inclusion in SQL Server 2005
SQL Server 2005 includes the Windows .NET Framework 1.1. Including the .NET Common Language Runtime (CLR) environment under the SQL Server 2005 runtime process isn't a trivial change. Had the SQL Server 2005 developers handled this change incorrectly, SQL Server 2005 could have been totally destabilized. Let's look at the risks that the SQL Server 2005 developers mitigated, then discuss why integrating the CLR in SQL Server 2005 was worth the trouble.

The SQL Server 2005 developers had to mitigate stability and security risks when they were integrating the CLR. Before I discuss these risks, though, let me clarify one point upfront: Having the CLR running within SQL Server 2005 doesn't open any new potential security holes in SQL Server 2005. There's nothing inherent in the CLR that creates or exposes new security problems.

With that said, let's use a simplified scenario to examine the stability and security risks that the SQL Server 2005 developers alleviated. In Windows, the CLR runs under Microsoft IIS. Suppose that, for some reason, the CLR locked up. Under IIS, the most likely solution is to simply restart IIS. As with any problem, this solution isn't ideal, but all things considered, even this worst-case scenario is relatively painless. At most, the reset event causes the loss of a few user sessions. On a load-balanced site, the reset event wouldn't even be noticed by anyone but the person whose request had caused the lockup.

Now let's apply that same scenario in SQL Server 2005. If the CLR were to lock up under SQL Server 2005, restarting might be the only solution. However, for a database server, unplanned downtime of this sort isn't acceptable. Even the best solutions for failover aren't designed to support a restart. The type of load balancing supported by SQL Server enterprise installations would never accept this type of risk.

So, the SQL Server 2005 development team worked with the .NET Framework CLR team to prevent this scenario from occurring. In short, these teams ensured that SQL Server 2005's existing mechanisms for controlling a transaction and tracking deadlocked processes could take control when something bad happened. This required an evaluation of .NET method calls to ensure that SQL Server 2005's built-in reliability wouldn't be compromised. The goal wasn't just to review the calls' code but to determine which of those calls could be safely controlled within the framework of a CLR that might not be able to be reset for years.

Using the .NET development languages, the CLR can make calls to either the file system or an XML Web service. Thus, a process internal to SQL Server 2005 could suddenly access code that wasn't running within SQL Server. The problems that might result are beyond any runtime process's ability to control.

Fortunately, calls to external resources such as a file system or XML Web service are associated with specific .NET capabilities, so the SQL Server 2005 developers used a common model to expose .NET capabilities in SQL Server 2005. The basic model has three Code Access Security (CAS) tiers. The lowest tier is the SAFE permission set, which allows access to those .NET capabilities that don't expose SQL Server 2005 to any risks greater than the risks associated with using native T-SQL. Thus, this permission set should trigger a green flag in your mind. In fact, I recommend that you use only the SAFE permission set when taking advantage of the .NET technology in SQL Server 2005.

The next CAS tier is the EXTERNAL_ACCESS permission set, which allows access to those .NET capabilities that call the registry and file system. By definition, these capabilities trigger security holes, which under some unknown condition might cause an unresolvable problem external to the database or provide a path that might lead to system damage. The reality is these capabilities should be managed in business objects, so this permission set should trigger a yellow warning flag in your mind. There will be some developers who feel a need to use these capabilities. Although these capabilities can be used safely, doing so implies some error of logic in the project's overall architecture and design.

The third and final CAS tier should trigger a red flag in your mind. This tier is known as the UNSAFE permission set, but you could easily justify calling it UNSECURED or just plain INSANE. This tier allows for full trust of managed code and permits calls to unmanaged code. Only the database system administrator can configure this permission set, but any one doing so should probably be asked to find a new job. The best analogy I have is how you respond to stop signs when driving. There's nothing preventing you from running a stop sign, and you could run every stop sign for weeks without having an accident. But rest assured, if you keep running stop signs, at some point you're going to have an accident. And that accident will likely cause you a lot of pain.

So why did the SQL Server 2005 developers go to so much trouble to integrate the .NET technology? According to Microsoft, they did it to gain the ability to create user-defined types (UDTs), aggregates, and better performing user-defined functions (UDFs). Underlying those features is the ability to define and better manipulate custom data structures. More specifically, at the core is the ability to manipulate XML within the database. The idea behind having the .NET CLR running under SQL Server 2005 is to support the complex data structures you'll be able to create by storing XML data within SQL Server 2005.

ALERT: How A Hacker Breaks Into Your Web Apps
Learn why 70% of today's successful hacks involve Web Application attacks such as: SQL Injection, XSS, and Parameter Manipulation. All undetectable by Firewalls and IDS! Get the Top Most Critical Code-Based Web Application Vulnerabilities
https://download.spidynamics.com/1/ad/dev.asp?cs1_ContSupRef=I-N-.net1.25.05

2. Announcements

(brought to you by SQL Server Magazine)

Nominate Yourself or a Friend for the MCP Hall of Fame
Are you a top-notch MCP who deserves to be a part of the first-ever MCP Hall of Fame? Get the fame you deserve by nominating yourself or a peer to become a part of this influential community of certified professionals. You could win a VIP trip to Microsoft and other valuable prizes. Enter now - it's easy:
http://www.windowsitpro.com/mcphalloffame/index.cfm?code=sqlUPDATE0203

SQL Server Magazine Gives Developers What They Need!
Order SQL Server Magazine today and get 30% off the cover price! Along with these great savings you will receive free access to every article ever published in the magazine – online! That's a library containing over 5 years of code listings, querying tips, and exclusive how-tos! Click here to subscribe:
https://secure.pentontech.com/nt/sql/index.cfm?PromoCode=00ep2152ds

Hot Spot: Win a Harley at Visual Studio Connections
March 20-23, Orlando, FL. Over 100 sessions presented by Microsoft and industry gurus. Registration includes an exclusive day on Visual Studio 2005 by the Microsoft Visual Studio 2005 product team. Attendees receive the latest SQL Server 2005 and Visual Studio 2005 beta. 800-438-6720 or 203-268-3204.
http://www.vsconnections.com

3. New and Improved

(by Karen Bemowski, [email protected])

The Localization Process Made Easy
Lingobit Technologies released Lingobit Localizer 3.5 for Windows. This updated version of the company's flagship product is a full-featured localization and internationalization solution designed to simplify the way in which Win32, .NET, and Java applications are localized. Key new features include Crash Finder (which finds crash-inducing strings), a command-line interface (which lets you integrate localization into a scripted building process), and import/export support for a broad range of translation memory formats. Pricing starts at $115 for the Express Edition. For more information, contact Lingobit Technologies at 206-309-5258.
http://www.lingobit.com

Print Barcode Labels and Encode RFID Tags Simultaneously
IDAutomation.com released the IDAutomation RFID Component for the Windows .NET Framework. The component encodes radio frequency identification (RFID) tags on RFID-capable printers from custom .NET applications. When used with IDAutomation Forms Control, the component simultaneously prints the barcode label and encodes RFID tags. When you integrate this component directly into .NET applications, you don't need to export data to a proprietary label program to print barcodes and encode RFID labels. IDAutomation RFID Component is compatible with the .NET Framework, Visual Studio .NET, Borland C#, and Borland Delphi for .NET. The component works with any RFID printer that has a Windows print driver. Pricing starts at $199. For more information, contact RegNow (IDAutomation.com's order-fulfillment partner) at 952-646-5331, 877-353-7297, or [email protected].
http://www.idautomation.com

Contact Us

Developer .NET UPDATE is brought to you by SQL Server Magazine, the only magazine devoted to helping developers and DBAs master new and emerging SQL Server technologies and issues. Subscribe today.
http://www.sqlmag.com/rd.cfm?code=00ep214xeb

View the SQL Server Magazine Privacy Policy
http://www.winnetmag.com/aboutus/index.cfm?action=privacy

SQL Server Magazine is a division of Penton Media, Inc. 221 East 29th Street, Loveland, CO 80538, Attention: Customer Service Department

Copyright 2005, Penton Media, Inc. All Rights Reserved.