In a strange bit of irony, Microsoft 's database rivals supported its Common Language Runtime engine for stored procedures even before the company provided the CLR capabilities itself with the November release of SQL Server 2005.
Les King, program director of DB2 marketing at IBM , said his company was first out of the gate with support for CLR stored procedures two years ago. Oracle followed suit this year as part of its 10g Release 2 database. In both cases, the CLR support is available only when users run their databases on Windows servers.
But despite running behind, Microsoft claims that it will have the upper hand because its implementation of the CLR is built inside the process that manages all the resources for SQL Server 2005.
"What that means -- and this is the key difference between our implementation and our competitors' implementations -- is you have the ability to write CLR code for both stored procedures and triggers, and all with a consistent security model," said Corey Thomas, a group product manager for SQL Server at Microsoft. "However you set the security policies inside SQL Server, those policies will be the same that apply to your CLR code."
That eliminates the need for developers to manually write the security policies at each layer of the application, Thomas said.
An IT architect at a manufacturing company who asked not to be identified said he expects his development group to slowly shift to CLR-supported languages in order to put more database access controls in SQL Server itself. Much of that logic is currently built into the company's applications, and the databases are accessed via application accounts, he said.
"The idea is for the database to protect itself, rather than writing an external application layer to control data access," the architect said. "Then you can give end users direct access to views for end-user reporting, rather than extracting the data into a data warehouse and building your data security controls all over again."
The manufacturer's small group of database administrators could do the work with stored procedures in T-SQL, but they already have their hands full overseeing the company's databases, he added. Keeping the security logic with the application developers, working in the languages they're familiar with, makes more sense because they typically have a better understanding of the business and security rules that apply to the company's data, he said.
In addition, they will have access to a rich set of .Net components that could, for instance, help them to get data from an Oracle database as part of an authorization check, the architect said.
"T-SQL and stored procedures are not the best tools for accessing non-SQL Server data," he noted. "But sometimes you need data from other sources to securely process a query."