SQL PASS 2005
Posts and stuff related to SQL PASS 2005 in Dallas, Texas. See http://www.sqlpass.org/events/summit05/index.cfm for details.
Two of the cool new features that we talk about in Essential SQL Server 2005 and Guerrilla SQL Server 2005 are:
- Data Definition Language (DDL) Triggers, which allow us to
catalog code that gets fired synchronously with event that trigger it. This is
useful in cases where you want to hook into the processing sequence for
commands like CREATE TABLE, ALTER VIEW or DROP ASSEMBLY, potentially logging
who did what or issuing a ROLLBACK to cancel that work.
- EVENT NOTIFICATIONS, which route information about events
asynchronously to Service Broker queue after the event has been processed.
EVENT NOTIFICATIONS are available for 193 specific events, including the events
we can use for DDL triggers.
This processing behavior difference is critical. EVENT NOTIFICATIONS
cannot easily be used to cancel work before it gets done, which a DDL trigger
can do. On the other hand, DDL triggers can’t be written for all 193 possible
events. So there are times we find ourselves wanting to use a synchronous
trigger for non-DDL events. For example, consider the routine situation where --
at the end of the month -- a given SQL Server needs to be dedicated to running
long-running and demanding batches and procedures to close out the financial
records. Normally, a "black-out" period is set by management for some interval (like
8:00 PM on the last day of the month) until work resumes the next day. The
question becomes: how can we easily automate the enforcement of this policy?
Well, if we could inject code into the logon process, it
would be easy. However, SQL Server hasn’t really made it easy to do that. At
least before Service Pack Two for SQL Server 2005. In this service pack a new
type of trigger is introduced: the LOGON TRIGGER. Let’s see how they work by
creating one.
What I’d like to do for auditing and troubleshooting
purposes is first create a database with just one table in it that allows me to
record logon activity. There’s nothing new about coding that up:
use master
go
if not db_id('audit') is null
drop database audit
go
create database audit
go
use audit
go
create schema admin
go
create table admin.LogonAudit(eventData xml,denied bit default 0)
go
use master
go
Let’s also create a user to test with:
if not suser_sid('userLogin') is null
drop login userLogin
go
create login userLogin with password = 'someEasyPassword'
go
Our trigger is actually fairly simple to write:
-- Drop our trigger just in case an older version exists...
if exists(select * from master.sys.server_triggers where name like 'noUserLogins%')
drop trigger noUserLoginsDuringMonthEnd on all server
go
-- Name it
create trigger noUserLoginsDuringMonthEnd
-- Applied at the instance level
on all server
-- Execute this code as the person who cataloged it
with execute as self
-- New, fires when a logon is attempted
for logon
as begin
-- Get the range of the backout period
-- starts on the last day of the month at 8PM
-- and ends at 9 hours later
declare @bob datetime,@boe datetime
select @bob = dateadd(m,1,getdate())
select @bob = convert( datetime,
cast(datepart(yyyy,@bob) as char(4))+'-'+
cast(datepart(MM,@bob) as char(2))+'-'+
'01 00:00:00'+' ', 120)
set @bob = dateadd(hh,-4,@bob)
set @boe = dateadd(hh,9,@bob)
-- If its currently in the back period and
-- a disallowed login is tried...
if (getdate() between @bob and @boe) and
(original_login() = 'userLogin')
begin
-- Stop the login
rollback
-- Record the attempt
insert into audit.admin.logonAudit values (eventData(),1)
-- See what happens to messages...
raisError('Attempted user login during month-end blackout',16,1) with log
end else
-- Record the attempt
insert into audit.admin.logonAudit values (eventData(),0)
end
go
So what happens if somebody tries to logon using the
userLogin we created during the black-out period? They get a message like this:
Personally, I have mixed feelings about this. It is great
because it works and does a nice job NOT displaying my potentially
security-sensitive error message (from RaisError) to the user. However, the
error message shown gives away two pieces of information it doesn’t need to:
the fact that a trigger nixed the log-on ("due to trigger execution") and that
there might very well be some database on the system called "master." I’m not
really sure why the SQL Server team decided to have this dialogue show those,
so I filled a bug about it. If you agree that this isn’t good, you can show
your support by voting for my bug by voting in it at https://connect.microsoft.com/SQLServer/feedback/Vote.aspx?FeedbackID=237008
That said there’s no doubt that this is a very nice addition
to SQL Server 2005 and your bag of tricks.
"order has shipped."
My copy of Bob and Dan's "A Developer's Guide to SQL Server 2005 has finally departed from whatever cave Amazon loaded them into. Can't wait to see how different it is. Its going to be a lot of reading too, its accompanied by Johnson's ADO.NET Advanced Topics book that I'm also looking forward too.
Is it time for me to take up the writing effort again? Maybe. I've got ideas, but what would you be interested in?
Doh! Sorry to be late getting this on the wire but the Heartland Developers' Conference for 2006 is has been announced. This year its in Omaha and runs from 26 October and 27 October. Hopefully I won't find myself in China again...
In its 3rd year, HDC06 is the largest independent annual professional Microsoft development conference in the nation, organized by user groups, and focused on .NET development and other emerging Microsoft technologies. HDC provides regional developers the opportunity to experience sessions usually reserved for Tech-Ed and PDC at a much lower cost while retaining the same nationally known presenters. Over 2 days, 23 sessions, 2 tracks, and several networking opportunities, HDC06 is where you’re find top knowledge experts that can prepare you for next level development.
For only $125 Early Bird/$175 standard, each Attendee Pass includes:
1 Conference pass to 2 keynotes and all 23 ninety minute sessions
- Meals including breakfast, lunch, snacks, and coffee breaks
- Access to the “Developer Lounge” expo and activity center
- Wireless internet access throughout conference area
- Pre conference networking party including drinks and food
- Thursday night “Developer Jam” including food, drink, and a casino for prizes!
- Attendee Kit and a chance to win a monster 64 Bit Alienware computer
The speaker list is looking awesome too: Microsoft's own Jeff Brand, Don Bryner and Jacob Cynamon. MVPs Dave Donaldson, Robert Hurlbut, Rocky Lhotka, Javier Lozano and Andrew Troelsen and community rockstars Craig Utley, Phil Wolfe, Robert Boedigheimer, Tim Gifford, Matt Milner and Nick Parker. I'm planning of giving a couple of talks on SQL Server 2005 for Developers.
Register here. And remember, we're talking a mere $125 one of the best Microsoft-focused technical conferences in the Midwest.
File this under this how to know what you have fix before you fix an XML Schema Collection
When I’m introducing folks to XML Schema Collections in SQL Server 2005, conversation usually goes something like this:
Blah blah blah blah use to make sure that XML instances are valid according to an XML Schema blah blah blah and you create them using CREATE XML SCHEMA COLLECTION name AS blah blah blah. Naturally, you can delete such a schema collection using DROP XML SCHEMA COLLECTION but you have to make sure there are no dependent objects on that before doing so. Some more talk and the then I have to it. The part of the talk I like the second least.
“So there is an alter for XML schema collections and to nobody’s surprise that starts with ALTER XML SCHEMA COLLECTION followed by the qualified name of the schema collection and a verb. In SQL Server 2005 there is one supported verb: ADD.”
That means that the alter for an XML schema collection is really only good for doing either of things: adding additional elements to default namespace, or adding a schema for a new namespace. And that’s basically it. There’s really not a good way to fix a broken or out of date schema for an existing namespace. And that means – oh joy – that you need to alter all of the objects that use the schema to not do so before you go about fixing the schema. And the first step in that is, of course, getting an inventory of the objects you’ll need to touch.
Continued here, due to length.
Conor Cunnigham kicks it off with:
On behalf of the Query Optimization Team for Microsoft's SQL Server product, welcome to our humble virtual abode. We decided that we'd start a blog to help people better understand query plans, physical schema design in databases, making your application perform better, and anything else related to query optimization. Furthermore, we'll be posting tips and tricks that we learn from our work with customers so that others can benefit from those exchanges.
They are coming out of the gate strong, too. There's already an article up about usimg Computed Columns to optimize certain types of queries in SQL Server 2005.
RSS Feed Here.
As usual, some of the best questions come from the newsgroups. And yet again, there was one that piqued by attention. The question essentially boiled down to “can you put a trigger on a login?” That is, the questioner wanted to execute some arbitrary T-SQL code whenever a user logged into a given instance of SQL Server. I’m not sure how you’d go about doing this in SQL Server 2000, but in 2005, two features make this possible – if you don’t mind building a bit of your own plumbing. These features are EVENT NOTIFICATIONS and the Service Broker.
Event Notifications are essentially fixtures that your create – using T-SQL – that allows SQL Server to dispatch messages about system events like logins, data definition language statements, and some trace events to Service Broker Services and Queues. The Service Broker Service acts as an address for the messages, while the Queue holds the messages until some a process like a Stored Procedure process them. Some number of instances of that Stored Procedure will be spawned by a process called Activation when a message arrives in a Queue. So these two features work together to deliver information about system events to a stored procedure that gets executed in response to that event, much like how a trigger gets fired in response to events.
What started out as fairly short post turned into a fairly long item, so I've contued this over here, while the annotated source code (with bonus features) is over here.
There’s an interesting note in the SQL Server 2005 SP1 March CTP, and yes, this is really a bug fix. I’m surprised, however, that more people didn’t get caught by it. Find the topic “Performing Operations on User-defined Types” in Books-On-Line and look at the change history. You’ll see a note reading:
“Added information about the following behavior change in SQL Server 2005 Service Pack 1: Strings that are returned by user-defined type methods assume the collation of the database in which the user-defined type was created, regardless of the current database.
In the earlier version of SQL Server 2005, strings that are returned by user-defined type methods assume the collation of the current database.”
If you’re not all that familiar with collations in SQL Server, you might not be sure why this really is a fix and not a… well, something else. It is pretty easy to see, however, if you give it a try on the RTM version and the CTP SP1 version. First, start with a simple enough CLR-based User Defined Type. Here’s an example:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined,MaxByteSize=8000,IsByteOrdered=true)]
public class SqlStr : INullable,IBinarySerialize
{
private bool _IsNull = true;
private string _Str = string.Empty;
public void Read(BinaryReader r)
{
_Str = r.ReadString();
_IsNull = r.ReadBoolean();
}
public void Write(BinaryWriter w)
{
w.Write(_Str);
w.Write(_IsNull);
}
public override string ToString()
{
return _Str;
}
public bool IsNull
{
get
{
return _IsNull;
}
}
public static SqlStr Null
{
get
{
return new SqlStr();
}
}
public static SqlStr Parse(SqlString Source)
{
if (Source.IsNull)
return Null;
SqlStr s = new SqlStr();
s._Str = Source.Value;
s._IsNull = false;
return s;
}
}
Okay, so that’s pretty simple. We also need a bit T-SQL to demonstrate this behavior. Run this first in the RTM version of SQL Server 2005, changing the assembly path to work on your machine:
use master
drop database scratch1
drop database scratch2
go
sp_configure 'clr enabled',1
go
reconfigure
go
create database scratch1
alter database scratch1 collate Latin1_General_CI_AI
create database scratch2
alter database scratch2 collate Latin1_General_CS_AI
go
use scratch1
go
create assembly sqlstrAsm
from 'c:\etc\ktegels\SqlStr\SqlStr\bin\Debug\SqlStr.dll'
go
create type SqlStr
external name sqlstrAsm.SqlStr
go
create table dbo.v(id tinyint identity(1,1),s SqlStr)
go
insert into dbo.v values ('ABC')
insert into dbo.v values ('abc')
insert into dbo.v values ('Abc')
insert into dbo.v values ('abC')
insert into dbo.v values ('aBc')
insert into dbo.v values ('aBC')
insert into dbo.v values ('ABc')
insert into dbo.v values ('AbC')
go
select id,s.ToString() from scratch1.dbo.v order by s.ToString()
go
use scratch2
go
select id,s.ToString() from scratch1.dbo.v order by s.ToString()
go
sp_configure 'clr enabled',0
go
reconfigure
go
Your output should be two result sets, the first called from the Scratch1 database, the second, from Scratch2.
id / v
1 / ABC
2 / abc
3 / Abc
4 / abC
5 / aBc
6 / aBC
7 / ABc
8 / AbC
id / v
2 / abc
4 / abC
5 / aBc
6 / aBC
3 / Abc
8 / AbC
7 / ABc
1 / ABC
That makes sense when stop to consider that the second result is being order case sensitively, where as the first set is case insensitive. If you run that same script in SQL Server 2005 Service Pack 1 instances, you get different results. And that’s because the case insensitive ordering of Scratch1 gets used in Scratch2.
id / v
1 / ABC
2 / abc
3 / Abc
4 / abC
5 / aBc
6 / aBC
7 / ABc
8 / AbC
id / v
1 / ABC
2 / abc
3 / Abc
4 / abC
5 / aBc
6 / aBC
7 / ABc
8 / AbC
So, why isn’t this a change actually a bug? To understand that, you have know what SQL Server does with “normal” string data in cross-collation situations. And that’s easy enough. Change the script to this and run it in the RTM version:
use master
drop database scratch1
drop database scratch2
go
create database scratch1
alter database scratch1 collate Latin1_General_CI_AI
create database scratch2
alter database scratch2 collate Latin1_General_CS_AI
go
use scratch1
go
create table dbo.v(id tinyint identity(1,1),s char(3))
go
insert into dbo.v values ('ABC')
insert into dbo.v values ('abc')
insert into dbo.v values ('Abc')
insert into dbo.v values ('abC')
insert into dbo.v values ('aBc')
insert into dbo.v values ('aBC')
insert into dbo.v values ('ABc')
insert into dbo.v values ('AbC')
go
select id,s as 'v' from scratch1.dbo.v order by s
go
use scratch2
go
select id,s as 'v' from scratch1.dbo.v order by s
go
When you look at the output, you’ll get it. All this change does is make a UDT’s string-returning function behave like another string functions in a cross-collation situation.
So with that sorted out, I’m sort of back to my normal sort of self.
As Greg and Wally have reported, I've been thinking about bringing back Take Outs as a PodCast. I'm pleased to announce with the the hosting support offered by Steve Wynkoop of SSWUG, its now a reality. The need-to-knows:
The RSS feed: http://feeds.feedburner.com/sswugorgradio
iTunes friendly feed: http://feeds.feedburner.com/SswugorgRadio?format=pcast
Episode one show notes and download file.
Let's roll!
When Ken Henderson asks, I listen. He's asked this question and there's been some good responses. My favorite is from Jeff Parker because its probably the most honest:
Most DBA's are not .NET programmers.
I'm not so sure that most DBA's aren't programmers at some level, though. Most DBAs do write code in T-SQL. Many of them are versed in scripting. For them programming means creating tools to help them do their job, not building applications. And that's why Jeff's statement is the most correct: .NET isn't about building tools, its about building applications. So while they get ideas embodied in the code, its a bit like asking a blacksmith how to build a skyscraper when you ask them about .NET.
And this is precisely why I think off by default isn't just a good security practice, its a good configuration practice. Many of other posters to this thread have noted that in and of itself, the hosted runtime isn't any more dangerous in its typical application and say, T-SQL code. SQLCLR used right doesn't try to much more than extend what SQL Server 2005 is already good at: processing T-SQL Queries. I've long believed that the best use case for SQLCLR is write functions that do complex calculations or that leverage something in the .NET Foundation Class Libraries that T-SQL is missing or is poor at. That includes things like financial functions, regular expressions and XML parsing. The idea is that we're just extending T-SQL via the CLR, not subverting or replacing it. Stored Procedures based on CLR are useful when you need to perform some CRUD operations as a result of procedural logic that's either easier to write and maintain in a .NET language or, again, you need to leverage some functionality in the framework like compression or Web Services. User Defined Types certainly have a place, but its no bridging business objects into in-proc environment. The best use for a UDT I've seen so far is to build composite scalar types, like complex numbers, geographical co-ordinates and so on. CLR triggers simply don't seem to do much for me. I'm hard pressed to think of cases where I wouldn't be better served to write T-SQL triggers that called SQLCLR UDFs when needed instead. Aggregators have some uses: obviously needed for UDTs and I've some other use cases for them.
Where I suspect we'll see the biggest security issues with SQLCLR is that folks won't take the "Extend T-SQL" message to heart. They've move business logic into the server and just assume that things work the they do in a non-hosted runtime. That may or may not be the case. For example, I've heard lots of people who got excited that they'd now be able to call SOAP Services from within SQL Server. And, sure, that's cool. But how do you know if you really trust the data your getting from that service? Or that the service is still the service you thought it was? My concern runs deeper too. Suppose you have a good chunk of business logic lodged in the server and you know its got holes. But fixing them means not only taking the application off-line for a while, but also the whole database. Maybe not so much of a problem for databases that just support a single application. Pretty much "no way" for a busy OLTP data warehouse.
But lets not forget on the best use cases for SQLCLR is to avoid writing something as an Extended Stored Procedure (XP). XPs are typically far more of a security and reliability concern than most developers and DBAs expected. At least SQLCLR gives SQL Server better control over the code and runtime while being nicely meshed into the current security model for other objects. I'd much rather tell somebody to blindly enable SQLCLR before I'd encourage them to use an XP.
This sort of answers Ken's third question: If you see SQLCLR as a security threat, what specific issues are you worried about? How do I answer Ken's second question: Do you think it makes sense for security-conscious users to disable SQLCLR and avoid apps that use it? The answer is yes and no. Yes, I do think that unless you have a good reason to enable CLR support in the first place, leave it off as it is by default (so logically, no, they shouldn't disable it, it's already disabled). Should it be avoided? That depends on the specific case. I'd say that the unless the DBA knows, understands and can support the code that coming onto the server, he or she might be well served to avoid it. But that's not specific to SQLCLR. The same holds true of T-SQL objects and Extended Stored Procedures. The best way that most DBAs will "grok" SQLCLR is if its presented as an simply extending T-SQL.
One of the very cool things about working with DevelopMentor is that I've gotten a lot of airline miles built up. So far this year I've been too:
- New York twice
- Denver
- Atlanta
- Las Angeles three times, one for PDC
- Orlando for TechEd
- Dallas twice, once for PASS and the MVP Summit
But tomorrow is by far my longest trip. I'm off to Shanghai to teach Essential SQL Server 2005 at the Microsoft Offices for five days. While I'm dreading the flight (its at least twenty-hours from Omaha to Shanghai going via Detriot and Toyko), I'm looking forward to being someplace I've never been before. I probably won't be blog much technical for the next few days, but I will be posting my trip notes and photos on this blog. Specifically:
Blog posts about this trip: http://www.sqljunkies.com/WebLog/ktegels/archive/category/1063.aspx (RSS: