SEO

Hi,

It appears that binary_checksum can give the same checksum for
different strings, which is a bit worrying. (I guess the algorithm is
the problem in the context of a repeating pattern.)

e.g.
select binary_checksum('A')
,binary_checksum('AAAAAAAAAAAAAAAAA')
,binary_checksum('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA A')

,binary_checksum('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAA')

My question...
Is this approach to generating checksums adequate for managing the
object scripts in the SQL Server to ensure that they haven't changed. I
guess that the probability of somebody making a change to a script and
ending up with the same checksum is almost negligible. Has anybody used
this approach in an FDA validated production environment, i.e. 'no ifs,
no buts'? Would it stand up to scrutiny?

Any experiences, thoughts?

Regards

Liam

Checksums do not guarantee unique values for different string, it is a
rotating add algorithm, since it is based on XOR ing values and only returns
a very limited set of possible values (the range of int), so you will get
collisions. You will also find that the amount of collisions it is very
depending on the collation sequence used. This is why SQL Server 2005
introduces the HashBytes function.

GertD@SQLDev.Net

Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.

"Liam Caffrey" <liam.caffrey@gmail.com> wrote in message
news:1117749763.058901.177080@g49g2000cwa.googlegr oups.com...
> Hi,
>
> It appears that binary_checksum can give the same checksum for
> different strings, which is a bit worrying. (I guess the algorithm is
> the problem in the context of a repeating pattern.)
>
> e.g.
> select binary_checksum('A')
> ,binary_checksum('AAAAAAAAAAAAAAAAA')
> ,binary_checksum('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA A')
>
> ,binary_checksum('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAA')
>
> My question...
> Is this approach to generating checksums adequate for managing the
> object scripts in the SQL Server to ensure that they haven't changed. I
> guess that the probability of somebody making a change to a script and
> ending up with the same checksum is almost negligible. Has anybody used
> this approach in an FDA validated production environment, i.e. 'no ifs,
> no buts'? Would it stand up to scrutiny?
>
> Any experiences, thoughts?
>
> Regards
>
> Liam
>

So an unchanged checksum does not guarantee that the data has not
changed. That seems to be clear.

However, I would guess that the chances of (a) someone making a change
to a script and (b) that change delivering functionality that can be
compiled and be meaningful, is fairly, if not very remote, as to be
irrelevant.

Does anybody use the SQL Server checksum for maintaining audit control
of database objects?

Regards

Liam

Hi Liam,

Yes, I have seen the checksum used for audting database changes on one
of my previous projects. It was used as a 'belt and braces'
verification mechanism to ensure that the database change management
solution (DB Ghost www.dbghost.com) worked as expected. i.e. it
upgraded a target database to be the same as a source database that was
built from scripts in a source control system.

I'm happy to report (because I now work for Innovartis, the makers of
DB Ghost) that a) the checksum function itself never gave any false
positives during the entire time (2 years) that I worked there and b)
DB Ghost never failed either.

The only false positives produced were due to the way SQL Server
stores/reproduces the text for objects such as Stored Procedures/views
etc. in syscomments Scripting the same object on two databases gives
annoying whitespace differences that are suprisingly difficult to
remove. However, that is not to say this is unsurmountable, it was
just deemed as a waste of time to investigate given that it only
occured once in a blue moon.

As you seem to understand the need for ultimate auditability and
traceability for changes to SQL Server then it would be worth your
while looking at DB Ghost. It's the final piece that makes full
lifecycle configuration management possible for SQL Server code changes
i.e. it will allow you to trace a change to the production schema all
the way back through development to the actual business case that
caused it. This assumes that you already have in place a configuration
management system of some kind.

Regs,

Malc

> I would guess that the chances of (a) someone making a change
> to a script and (b) that change delivering functionality that can be
> compiled and be meaningful, is fairly, if not very remote, as to be
> irrelevant.

You would be wrong. It's trivially easy to find checksum collisions.

SELECT BINARY_CHECKSUM('ABA'), BINARY_CHECKSUM('ACQ')

Result:

----------- -----------
17761 17761

(1 row(s) affected)

As a further test I ran the following query on the syscomments table of
a database containing about 200 procs, views, etc. and got 3 rows
returned:

SELECT DISTINCT OBJECT_NAME(T1.id), BINARY_CHECKSUM(T1.text)
FROM syscomments AS T1
JOIN syscomments AS T2
ON BINARY_CHECKSUM(T1.text) = BINARY_CHECKSUM(T2.text)
AND T1.text <> T2.text

This result is perfectly natural and expected when you consider that
there are "only" 2^32 possible checksums - many orders of magnitude
fewer than the number of syntactically valid and useful pieces of
script.

--
David Portas
SQL Server MVP
--

Liam Caffrey (liam.caffrey@gmail.com) writes:
> So an unchanged checksum does not guarantee that the data has not
> changed. That seems to be clear.
>
> However, I would guess that the chances of (a) someone making a change
> to a script and (b) that change delivering functionality that can be
> compiled and be meaningful, is fairly, if not very remote, as to be
> irrelevant.
>
> Does anybody use the SQL Server checksum for maintaining audit control
> of database objects?

When I was building a set of tables for our load tool, one thing I
looking for, was a way to track if someone had loaded an object outside
the load tool. That is, the load tool would track all loads in a table,
but would of course not track if someone tried to load an object through
Query Analyzer or somesuch.

I was indeed considering using
CHECKSUM_AGG(BINARY_CHECKSUM(syscomments.text)). The reason I eventually
didn't was that I wanted to support all three of SQL 6.5 and SQL 7 and
SQL 2000, and the troublesome here was SQL 7, which does not have
the checksum functions, but does have ALTER PROCEDURE which does not leave
a trace in sysobjects.

So eventually, I found no other way to rely on the columns crdate and
schema_ver in sysobjects. I found that for each ALTER PROCEDURE,
schema_ver is incremented with 16. This is not very documented, though.
Since I had to go with this on SQL 7, I figured I could just as well
use it on SQL 2000 as well.

On SQL 2005 it's easier. There is now a sys.objects.modified_date,
horray!

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp