Programming Microsoft SQL Server 2005: Enabling CLR Integration

1/22/2011 4:20:47 PM
Before you can learn how to use SQL CLR features, you need to know how to enable them. As with many new products in the Microsoft Windows Server System family, many advanced features of SQL Server 2005 are disabled by default. The reasoning behind this is sound: Each additional feature that is enabled provides extra “surface area” for attacks on security or integrity of the product, and the added exposure is inexcusable if the feature goes unused.

The SQL CLR features of SQL Server 2005 are sophisticated and can be very useful, but they are also, technically, nonessential. It is possible to build high-performance databases and server-side programming logic without SQL CLR integration, so it is turned off by default.

Don’t be discouraged, though: Turning on the feature is easy. Microsoft provides both a user-friendly GUI tool (aptly named the SQL Server Surface Area Configuration tool) and a system stored procedure for enabling or disabling SQL CLR integration. We’ll cover both approaches.

To use the Surface Area Configuration tool, simply start it from the Configuration Tools subgroup in the Microsoft SQL Server 2005 programs group on the Windows start menu. Figure 1 shows the tool as it appears upon startup.

Figure 1. The SQL Server 2005 Surface Area Configuration tool

To configure CLR integration, click the Surface Area Configuration For Features link at the bottom of the form. After a short pause, the Surface Area Configuration For Features dialog box appears; a tree view-style list of features appears on the left, and the Ad Hoc Remote Queries feature is preselected. Click the CLR Integration node immediately below it, and you will see an Enable CLR Integration check box on the right of the form. (This is shown in Figure 2.) To enable SQL CLR features, make sure that the check box is checked, and click OK to close the Surface Area Configuration For Features window. (You can also clear the check box to disable SQL CLR integration.) Close the Surface Area Configuration tool by clicking its close box in the upper-right corner of the window.

Figure 2. The Surface Area Configuration For Features window

If you’d prefer a command-line method for enabling or disabling SQL CLR functionality, open up SQL Server Management Studio and connect to the server you’d like to configure. Then, from a query window, type the following commands, and click the Execute button on the Management Studio SQL Editor toolbar.

sp_configure 'clr enabled', 1

That’s all there is to it! To disable SQL CLR integration, just use a value of 0, instead of 1, as the second parameter value in the sp_configure call.


Don’t forget that this will work from any tool that can connect to SQL Server, not just Management Studio. In fact, you could issue the previous command text from your own code using the ADO.NET SqlCommand object’s ExecuteNonQuery method as long as your code can connect to your server and your sever can authenticate as a user in the sysadmin server role.

With SQL CLR integration enabled, you’re ready to get started writing SQL CLR code. Before we dive in, we need to discuss Visual Studio/SQL Server integration and when to use it.

Top 10
SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
The latest Audi TT : New angles for TT
Era of million-dollar luxury cars
Game Review : Hearthstone - Blackrock Mountain
Game Review : Battlefield Hardline
Google Chromecast
Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
Michael Kors Designs Stylish Tech Products for Women
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
Popular Tags
Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone