Tracking stray connections using profiler.  Times when this can come into play is in multithreaded apps, or apps where you may be supporting simultanious users like in an ASP.NET application. 

 

Generally any time you are asking resources from the server it should be using the Try... Catch... Finally pattern for allocating server resources and releasing them as soon as you know you can do without them.  (Nod to Andy Rich on Deterministic Finalization)

One thing that is going to help with this however is to set the appname in your connection string (Application Name={1};) for filtering the Profiler results.

So here is a Profiler trace useful for keeping an eye logins, logouts and stored procs executed inbetween:

Download: deepdark.net_Connection_Monitor_20060112.zip

Something I have been giving thought to is that the .NET Framework exposes perfmon counters for all manner of useful stats. 

 

An unlikely tool?  Not so much... Of particular use is the feature where perfmon can track exceptions thrown throughout all managed apps or, alternatively for any chosen running managed application.  You can then compare the number of Finally blocks hit after an exception was reached (there is a counter specifically for this!) vs. exceptions thrown.  In normal conditions I don't think I'd like these todiffer significantly.

 

Really this is using perfmon to track stats from .NET apps provided by the framework; but performance isn't far from the surface.  Throwing and Catching exceptions is a very costly operation for the Framework and is a significant performance hit to a running application.

 

Three things, and some links: 

 

1. You don't have to be looking at your dev machine.  If you have administrative access to a test server you can remotely watch any perfmon counters.  Useful if your application runs as a service or for ASP.NET applications. 

Also, create a shortcut to perfmon.exe in your System32 directory to get to quick access to the Run As... command for those running their developer environment with least privilege (nod on this to Don Kiely, Michael Howard).

 

2. If you need to track these in a running app like a Windows Service or a ASP.NET application consider using MRTG to graph

the results for you.  I've used this before as a make-shift NMS console monitoring server system health. It's more secure than SNMP and very low on resource use.

The official MRTG site has the basics for setting up MRTG on Windows, but Castellan has a _{(slightly dated)} but much more relevant guide.

 

3. Consider logman to configure the Performance Monitor service.  Again this is worth a blog post by itself.  Open a command prompt and logman /? for more

 

Where to next?  One day I'd like to automate perfmon logging as part of an NUnit test harness to give another measure of quality parallel to Nunit.  I'll keep you posted.

Well first of all thanks to NETGEAR.  I'm back.  This time it should be for good.  But tell me this; why can't I get a router that will take some moderate load on a hot Sydney day and not explode?!?!  Even my new (expensive!) Netgear hardware runs hot (50oC+) under *no* load.  I'm gonna have to casemod this sucker to add a fan if it's gonna last me until Feb '06.

Anyway, authentication.  It's not the first time Crypto-Gram has been mentioned on my blog.  This time it relates to a post therein about the kind of fingerprint readers that have become popular in Keyboards/mice or as USB peripherals, and how with some skill and the right tools and circumstance you can foil them in about the same time it takes to make a sandwitch.

What it comes down to is this (I am drawing from many sources here).  Authentication comes in three factors:

• Things you know - passwords/passphrases*, PINs, mothers maiden name (how many web sites will ask this!) and so on...
• Things you are - your retina, your fingerprint^, etc
• Things you have - Smartcards, USB dongles, key cards, your passport yadda yadda...

If you require 2 forms of authentication and draw from only one pool, then you have only one factor!

(I have a secret about OfficeWorks SAP system, remind me to tell you about it some time...)

Anyway, that's enough for tonight :)

* Jesper on Passphrases here (1/3), here (2/3) and here (3/3)
^ Wikipedia, The Register

Transmission has been up and down like a fiddlers elbow of late as my damn router is giving me grief.  Please stay tuned...

After blog mint [?]:  Seems I suffered a sudden case of F.E.R.S.  (a slight variation on the standard E.R.S. a.k.a Exploding Router Syndrome)

Quick post today about my favourite SQL Server “feature”.  This is when you create a user account the default database is master.

The only sensible reason for installing the Northwind database I think is so that you can set it to be the default database for logins! 

I wonder how many times in history people have just opened the Query Analyser and ran a script accidentally against master rather than the database they intended. 

(Humph!  What ever that number is... add one to it)

It's been a little while between posts, so I thought I'd share a tip about using Fiddler to debug .NET SOAP Web Service Clients.

Once you install Fiddler it sets itself up as a proxy on port 8888.  You then use the Fiddler UI to inspect sessions made from your application to the IIS hosting the web service.  Fiddler lets you inspect the raw HTTP traffic that is exchanged in a SOAP call, and you can even modify an old session and resubmit it with modified data!  Great for debugging.

Below is a sample (VB.Net 2003) adapted for readability from an actual project I'm working on.  The point of the sample is setting Proxy property of the web service reference to a WebProxy object.


Dim NewUser As RemoteWebHost.User
Dim UserServices As RemoteWebHost.DatabaseSync

' Adding this line lets Fiddler track the HTTP Sessions.
UserServices.Proxy = New WebProxy("http://127.0.0.1/", 8888)

With NewUser
   .UserName = "fred"
   ' [...]
End With

Try
   UserServices.UserAdd(NewUser, Nothing)

Catch ex As SoapHeaderException
   ' [...]

Catch ex As SoapException
   ' [...]

Catch ex As Exception
   ' [...]

End Try

Looks like it's about time for another post!

This one is about the SQL Server system tables.  These are a fav of mine because I find them so useful in scripts. 

The caveats when dealing with them are you need to be mindful of SQL Server versions.  Everything that worked on SQL Server 7.0 will work on SQL Server 2000, but there are some minor tweaks in SQL Server 2000 that are not valid in SQL Server 7.0.  Now is not a good time to mention SQL Server <= v6.5 because the system tables had an overhaul for 7.0, and I haven't checked any of this code on Yukkon/SQL Server 2005 yet.

My fav thing to use the tables for is dealing with object existance in scripts.  In my books a good T-SQL script can be run over and over without damaging the database.  Put another way, if your SQL Script throws an error if it is run twice against the same database it's not a good T-SQL script.

Consider we are dealing with the following table:

CREATE TABLE testing_data (
    pkey INT IDENTITY (1,1) NOT NULL,
    created DATETIME DEFAULT getdate() NOT NULL,
    modified DATETIME NULL,
    deleted BIT DEFAULT 0 NOT NULL,
    testing_val_1 NVARCHAR(15) NOT NULL,
    testing_val_2 NVARCHAR(255)
)

For whatever reason we want to drop and recreate this table each run, you could put the following statement before it:

IF Exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[testing_data]')
    AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
    DROP TABLE testing_data
GO

This is the syntax you will see if you choose to include Drops in scripts you generate from the Enterprise Manager, but I don't use it much, mainly because I can never remember the OBJECTPROPERTY() syntax!

Typically I do the following:

IF Exists(SELECT [id] FROM sysobjects
    WHERE sysobjects.[name] = N'testing_data'
    AND sysobjects.[type] = N'U')
    DROP TABLE testing_data
GO

I feel that Microsoft are hinting us towards using OBJECTPROPERTY() for future version compatability, but I still favor this syntax because apart from being easy to remember it's easy to adapt for other kinds of objects, e.g:

IF Exists(SELECT [id] FROM sysobjects
        WHERE sysobjects.[name] = 'prGetTestingDataRows'
        AND sysobjects.[type] = 'P')
    DROP PROC prGetTestingDataRows
GO

So if the Type column in sysobjects is 'U' for user tables, and 'P' for procedures then there are no prizes for guessing what TR, D & V might mean.

For a more complex example, lets say you want to change the type of the fileds testing_val_1 to NVARCHAR(35) ONLY if it has not been changed before, you could wrap the ALTER TABLE stateement in the following BEGIN... END:

IF Exists(SELECT syscolumns.[name]
    FROM syscolumns
    LEFT JOIN sysobjects
        ON syscolumns.[id] = sysobjects.[id]
    LEFT JOIN systypes
        ON syscolumns.[xtype] = systypes.[xtype]
    WHERE syscolumns.[name] = 'testing_val_1'
    AND systypes.[name] = 'nvarchar'
    AND sysobjects.[name] = 'testing_data')
BEGIN
    ALTER TABLE -- ... Implementation ommited for clarity
END

So having only touched two or three sys tables we have a couple of good tools that are easy to use.  I'll cover more at a later date, in the mean time enjoy the extra metadata!

After blog mint [?]:

Here's an actual practical example from a script I have been working  on recently (user name changed).  This script makes a dozen or so procs, the svcapp account is a login used by a service that only has rights to exec these procs, and no rights granted to the base tables.  This automates granting access to the created procs, and it much quicker than doing it in the SQL EM:

PRINT 'PART 4 - Granting access to user account'
GO

DECLARE @sql NVARCHAR(512)
DECLARE @name NVARCHAR(128)
DECLARE @usernm NVARCHAR(128)
DECLARE cr CURSOR FOR
    SELECT [name] FROM sysobjects
    WHERE type='P'
    ORDER BY [name]

SET @usernm = 'svcapp'

OPEN cr
FETCH NEXT FROM cr INTO @name

WHILE @@fetch_status = 0
BEGIN
    SET @sql = 'grant exec on ' + @name + ' to ' + @usernm
    EXEC sp_executesql @sql
    PRINT 'Granting EXEC on ' + @name + ' to user: '
    FETCH NEXT FROM cr INTO @name
END

CLOSE cr
DEALLOCATE cr

PRINT 'Done.'
GO

Firstly, the credits.  This post would not have been possible without Jesper Johansson.  Look at his notes no Anatomy Of A Hack.  He's an authority on securing Windows networks, and has given me a lot to think about.

Prime among them is the problem that while running secure web applications on secure Windows servers has never been more possible, it's still too much of a black art.  I'm just scared that too many apps will be bumped up to run as administrative accounts because it's just too much hard work to get them to run.

Even following good quality (but slightly dated) patterns and practices guides like Building Secure ASP.NET Applications: Authentication, Authorization, and Secure Communication (2002, extra comma theirs) aren't the full story.  You can still be forced to resort to tools like Filemon and Regmon to work out why your least-priv account cannot be started as an application pool.

This isn't meant to be a Windows is insecure rant, because they are 10-a-penny.  This is just meant to be a heads-up and link-fest.

Final link for the bandwidth-endowed.  You can watch a couple of sessions presented by Jesper at the Tech.Ed 2005 Australia site.

 

After Blog Mint [?]:

I thought I'd post some more links to resources I can personally recommend:

• Crypto-Gram mailing list.
• patterns and practices Secirity Wiki.
• A book called Innocent Code.  ( [ELX] [Dymocks] )
• A more general book called  Practical Guidelines & Best Practices for MS Visual Basic & MS Visual C#. ( [ELX] [Dymocks] )

I was just thinking, it's about time I talked about SQL Server on my blog LOL

Here are 5 tips for creating well designed tables in Microsoft SQL Server.  They are in no particular order and it's far from an exhaustive list, but it makes for a good standard I think.

1.  Keep your tables narrow:  I don't just mean about the number of columns per-table, but also the width of those columns.  Normalization is your friend here.  I won't go into it here yet, but shoot for 3nf by default. 

By keeping an eye on table width and keeping your rows as narrow as you can you will get more rows on a page which will help you in case a query misses and index and requires a table scan.

2.  Add a deleted bit column to your tables.  Design your tables with an extra column called "deleted" or something like that.  Instead of removing the row from the table if there is a delete, set this flag = True. 

Some reasons you should do this are:

• It opens up more options for implementing an undelete function and it helps archiving/unarchiving old data.
  
• It's non-destructive!
  
• It helps index fragmentation.
  

 

3.  Maintain aggregate tables.  This one probabbly applies more to transactional system than others but by using maybe triggers or a scheduled job to maintain seperate tables that store common aggregates for your main tables you will for make your reports more efficient and can simplify queries and joins. 

Two things to remember: 

• These tables may not be indexed the same as the transactional tables, but this is a good thing.  Choose your indexes on these tables to suit exactly the queries you need.
• If you require the aggregate tables to be absolutely up-to-date with the main data tables then using triggers may be the best solution but don't over do it!  Triggers can be costly and have an adverse effect on transaction log throughput.
  

4.  Maintain created and modified timestamps on each row.  Design your tables with columns called created and deleted and store the timestamp of when the row was created and also the last time the row was modified.  Do this as well as an audit trail. 

This is the kind of gift that just keeps giving!  Some examples:

• Once your little back room app has 'grown up' and now needs to feed the data warehouse?  No problems! 
  
• Need to validate your aduit log or a point-in-time restore?  No problems.
  
• Users always love to see these details on the screen, or give your app a history feature like a web browser has so users can backtrack to what they were doing before the phone rang?  No problems.
  

5.  Don’t allow null unless it actually valid.  Don't be scared of Null values, but only allow them where they make sense.  Remember Null doesn’t equal "" or 0 - it means undefined. 

Consider the mod timestamp (from point 4 above) on a newly created row.  It has never been modified so Null actually makes sense.  The worst thing to do to these kinds of fields is to use "magic" values to avoid null columns,  for example giving "01/01/1970" the special meaning of "unknown" is just a waste of effort and makes your database usable only by applications that know these specific facts, seriously harming interoperability.  Also consider a table that joins to itself to represent a hierarchy - null can be valid in the parent field.

That said, don't allow nulls anywhere that they don't make sense.  If you find yourself thinking about optional fields consider normalizing, or using an attribute-collection pattern instead!   The thing to avoid is creating tables that has a column for each of the potential fields.  The typical example is having one field for each of business phone, business direct line/ext, home phone, mobile phone, fax number - each of them  with the same validation rules, and most of the rows  don't fill all columns!

So there you go!  5 steps closer to peace in the database world!

Jesper Johansson has a good post on his blog this morning about what to do with the Built-In Adminstrator account.
I think this quote could become a new mantra: The built-in Administrator is basically a setup and disaster recovery account. Hallelujah!