SQL Server - Otherwise
Think of it as a default constraint
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.
I think of one the most mystifying error messages that you can get in SQL Server 2005 is when try to catalog the same assembly twice into the same database:
Msg 6285, Level 16, State 1, Line 1
CREATE ASSEMBLY failed because the source assembly is, according to MVID, identical to an assembly that is already registered under the name "...".
This raises a couple of interesting questions.
a.) What the heck is an MVID and why do we care?
b.) How can we check these MVIDs for ourselves?
a.) MVID stands for Module Version ID and is documented in section 21.27 of ECMA-335 as "simply a Guid used to distinguish between two versions of the same module." Although an Assembly can contain many modules, there is a principal MVID assigned to it in the manifest -- the Manifest MVID. One of the checks that SQL Server performs when cataloging an new assembly is to get it's Manifest MVID and compare that to all of the other Manifest MVIDs for assemblies already cataloged in the current database. When a duplicate is found, SQL Server 2005 rejects the inbound assembly.
b.) Checking any non-database assembly for its Manifest MVID is fairly simple. Here's a short snip of code that returns it:
Assembly asm = Assembly.Load(BUF);
Module m = asm.ManifestModule;
return m.ModuleVersionId;
Where BUF is assumed by an array of bytes holding the assembly you want the MVID for. You could also use Assembly.LoadFromFile to an exsiting Assembly-as-DLL file into the snip, however, that method is not allowed within SQLCLR. An easier way to get the MVID for an Assembly already cataloged into SQL Server 2005 is to use the AssemblyProperty statement in T-SQL, e.g.:
select assemblyproperty(assembly name,'mvid')
One of the three best PSS SQL Server Engineers -- Bob Ward -- has written up a great set of white papers on SQL Server I/O. More about them on the SQL Blog.
On Sunday I confessed an interest in things GIS. Recently that interest has been rekindled by the ESRI podcasts and because the MapPoint team was kind enough to send the MVPs a copy of MapPoint 2006. I’ve used MapPoint for things like Driving Directions in the past. MP2004 as pretty good for that, but you could tell the road maps were getting a bit long in the tooth. I also felt it was a good tool for team driving: one person piloting the car, one using MP to navigate.
My first experiment with MP2006 was as an in-drive navigation aid like TomTom or NeverLost. I really only have nits to pick. First, it is hard to hear the voice guidance on my lap given where the speakers are, so I found myself pulling over and cranking up the audio. It still wasn’t as loud as I’d like, but it worked. Having used NeverLost, it really liked its tone telling you that you made correct turn or needed to make a turn now. MP doesn’t give that kind of feedback and I wished it would.
But one thing that I like MP2006 for is that it can record a trip in a trail. You can save the trail right now, but that’s it. I have noticed, however, that you can copy and paste the trail to the clipboard and with a little programming, you can get the data in the trail from the clipboard. What I’ve not fully figured out is how to parse this into useful data. Ideally I’d like to get the position and time data and I do think that’s possible. If and when I figure out how, I’ll post that here.
The other cool thing in MP2006 I’m enjoying the exportability of demographic data to Excel format. For example, I’ve been doing a couple of demos where I’ve extracted the number of households in a given zip code that frequent flyer plans and used SSIS to massage that into SQL Server. It makes for a good test of a lot of data at least.
"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.
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!
You know that song. Yes, that song. The beeping. The arm flailing. The Safety Dance. I so wanted it stay in the 80s – along side the uncounted Wild Turkey inflicted hangovers. It just won’t. Every now and then that damn beeping rhythm creeps into my conscious thought when I least need it to. Like today. I’ve been trying to figure out how to use the last-minute-added ability to catalog signed assemblies that need External Access or Unsafe permission without having to set the database trustworthy bit. Getting frustrated with the interesting example in Books Online, the beeping started.
So here’s what you really need to know:
- The first you need is a certificate that can establish a chain of trust to some trusted root certificate authority on the target machine. If you’ve already got one thanks to having Certificate Server on your network or you’ve purchased one, great. If not, you can make one for yourself as we’ll do there.
- You need to understand the *interesting* inter-play of certificates, logins and signing assemblies. It is not hard once you understand that you can use a single certificate to do all of that.
- You will have to comfortable using the Command Shell and a couple of tools in the .NET Software Development Kit (SDK), namely SignTool and MakeCert.
Continued here due to size...
The start of the new year always makes me think I want to do new things. This year I'm trying to eat more healthily, drink less and blog more. While you probably don't care so much about the first two of those, you're probably interested in the third. Afterall, consider what your doing right now...
Effective today, I'm shifting my blog focus from SQLJunkies (what you're reading right now) over to GeeksWithBlogs. Here's the new feeds and their RSS URLs.
I'll keep posting references to stuff about .NET and SQL Server on this site for some time to come, of course.
The other day there was a posting in the SqlServer.XML newsgroup asking about Serialization. While the topic can get hairy quickly, there's a fairly simple solution if you're willing to live with a few... uh... interesting side affects. There's a fair amount of code in the example, so I've posted into an article over here.
Thanks to fellow DevelopMentor "Brain" Kevin Jones (RSS) for pointing this one out.
These problems left me stranded for days, then today, on another machine, I hit a different set of problems I kept getting the following "Failed to generate user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed." and if I set "User Instance" to false the following "An attempt to attach an auto-named database for file C:\[app_path]\ASPNetDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."
..
If you don't want to follow the link, the short answer is that SQLExpress creates a directory per user in "c:\Documents and Settings\[user]\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS" that it uses to store information. Deleting this directory has fixed both of my problems.
Keith Brown has a meaty posting on his blog talking about channel security for SQL Server. The highlight, at least for me, is this:
Why am I bugged by this? Why don't I just use SSL and get in line with everyone else? Because setting up SSL connections is considerably slower than setting up Kerberos connections (think public key operations in SSL versus purely symmetrical operations with Kerb), and it requires certificates and PKI where Kerberos is already built right in to my domain infrastructure.
I really like the post, but there is something that bugs me about it. It feels like he's complaining about the glass being half-empty.
Sure, I'll go along with his contention that in your in a fully Active Directory integrated environment that using the Kerberos would be a great solution and for exactly the reason he gives. But frankly, I don't see Microsoft changing their point-of-view that SSL is a better choice, and no, I don't expect the story for Yukon (SQL Server 2005) will make Keith any happier. These are best demonstrated when you think about situations where identity isn't shared, but trust explicitly is. And what kinds of situations are those?
- SQL Server Replication of data between business partners who may or may not have their own Active Directories. In this case, the need to exchange data securely is easily understandable, but the sharing of an identity as the basis of key and ticket exchange becomes problematic. However, a good PKI implementation partial solves this problem by enabling the easy exchange and validation of certificates.
- Data exchange between Compact Device applications and domain member SQL Servers. The typical Compact Device usually lacks the ability to participate in the Active Directory and thus cannot drive the Windows Integrated context needed for such a login. However, these devices typically do support SSL out-of-the box.
- SQL Server 2005 Service Broker applications distributed over many hosting domains. One of the reasons that Service Broker appeals to me is that in integrated business scenarios, different parts of an application can be hosted on servers in mutually non-trusting domains. For example, a TeleServices company may "own" and host service programs that deal with the generation of sales orders, while a second company "owns" the service programs for fulfillment and third "owns" the service programs for executing the financial aspects of an order. Sharing an Active Directory between these three parties might prove to be both expensive and difficult to manage correctly. However, since Service Broker uses certificate authentication of endpoints, this should be much easier and less expensive to achieve.
These cases demonstrate how SQL Server transcends many of Microsoft's other products in terms of secured inter-operation by specifically avoiding being overly dependent on the Network Operation System for security. So am I saying that Keith's desire better integration with Kerberos security is invalid? Not at all -- it has obvious value in many cases. But I would say that I, for one, am glad that the SQL Servet Team embraces SSL and puts their focus on that technology. Its a great example of how flexible SQL Server can be, albeit with some performance hit. Seems like a small price to pay to be able to achieve this kind of flexibility with a minimally shared surface area. I'd rather have Microsoft work on enhancing the reach and performance of SSL rather than expend that effort on making Kerberos work better, at least as far as SQL Server goes.
Then again, maybe I'm just used to seeing the glass as half-full instead of half-empty.
Looking at about 1,000 posts from last week, rather a high percentage
of them say that SQL Server 2005 and Visual Studio 2005 will be
released on November 7, 2005.
Maybe.
The actual press release reads as follows:
As part of the keynote address, Paul Flessner, senior vice president of Server
Applications at Microsoft, showed the company’s continued momentum in
preparation for the launch of SQL Server 2005, Visual Studio 2005
and BizTalk Server 2006, and announced that these products will be
formally launched during the week of Nov. 7.
So that could mean 12:00 AM Redmond Standard Time on November 7th, or
it could be as late as 23:59 on the 11th. And even then its going to
take time for those bits to make it to all of the distribution points
that are needed. That said, I noticed that the Visual Studio
Connections runs that week. Humm, and there's an 8:00 AM keynote
scheduled for the 8th.
Make of that what you will...
So I’m sitting at gate C11 at O’Hare Airport waiting for my final flight of the week – back home to Omaha. Having a few minutes, I thought I’d crank out a quick post about these events and my experiences at them.
The nice thing (at least from the presenter’s point of view) is that we have both a fixed list of content and times -- I do ~75 minute talks on SQLCLR, Service Broker and SQLXML in SQL Server 2005 -- as well as an hour of “as the experts” Q&A as the show wraps up. We’ve had some really good questions in the Q&A part, like:
- Can one call a UDF on a linked, remote Server with SQL Server 20005, which no, doesn’t appear to work.
- Can a function based on a SQLCLR hosted assembly query data on a linked-to server, which, yes, does appear to work.
- Have there been any improvements to calling T-SQL-based UDFs in terms of performance, which I’m still testing out.
Bill Baker emcees the Q&A session and he’s thrown out a few interesting answers of his own:
- On Thursday he mentioned that we should see SQL Server 2000 Reporting Services Service Pack 2 released and available for download by Monday.
- The SQL Server Team is working on an enhancement to the networking layers of Service Broker that will let you interact with it using the “Indigo” technologies as well as the traditional SNAC and HTTP Endpoint features of SQL Server 2005.
- He mentioned that he believes the plan is to allow SQL Management Studio to be used again