Does SQL Server have any built-in error checking mechanisms with regards to receiving an sql statement?

Question

This question is a follow-up/in relation to my previous question here: http://stackoverflow.com/questions/3518498/sql-server-is-there-a-need-to-verify-a-data-modification

I did some googling, and the paper (which I can't access) "When the CRC and TCP checksum disagree" indicates the occurrence of an unchecked error rate of 1 in 16 million to 10 billion packets. Hence, for a erroneous insert/update/delete to succeed, the error will have to affect value(s) or keyword(s) (in a syntactically correct way) in a sql statement. This means that the probability of SQL Server receiving and executing an erroneous sql statement is even much lower than that indicated in the paper.

What I would like to know is whether are there anything else which will further lower the probability of an erroneous sql statement being received or allow it to be detected:

• Does an sql statement contain a checksum which SQL Server checks to verify if the integrity of the statement?
• Is it possible to retrieve the last sql statement received by SQL Server for comparison with the sql statement sent? This is computationally less expensive than querying the database to check if the sql statement sent is received correctly, although unlike the latter technique, it cannot check if the sql statement is executed correctly or not.
• Any other thing which I have left out and you think may be useful.

In case you were wondering, what I'm working on is a military application, which explains the high level of integrity they require.

Thank you.

Answer 1

Check out the TDS protocol as used by SQL Server. I can't find any reference to checksums or CRCs in there - so I would say that's a "no" to question one.

It's not possible AFAIK to acquire 'the last query executed' from SQL Server directly - you'll need to log these somehow if this is a requirement - you may find that you'll get some mileage from the Dynamic Management Views - you can certainly use these to get SQL statements that have are or have been executed for a particular SPID, but I don't know that this will be useful to you.

Suppose your query was not malformed - what's to stop a memory corruption or disk corruption from breaking the data? Of course, you can have error checking / recovery for these 'layers' as well - but nothing will be 100% guaranteed.

Answer 2

The simple answer is either it works, or every few billion calls it doesn't for some reason. There is no sane mechanism for checking at this level with these kind of error rates.

You can guarantee neither 100% uptime nor 100% reliability. You have disk, memory, network, CPU etc errors between your client and SQL Server.

To be frank and having worked on military software (and have the war stories to match), I would suggest that you should ask what platform they want to use rather then you having to justify the use of SQL Server to them. Or simply say "it's not possible" and see what happens: either they want software or they don't.

If the software also requires this kind of integrity then it should be written in ADA too, rather than COTS.

It sounds like you have someone who does not want to use SQL Server for some reason, that's all...

Answer 3

You are asking about transport level message integrity, and this is guaranteed by SSL/TLS. See Encrypting Connections to SQL Server. SSL/TLS protects not only about somebody ear dropping on the client-to-server channel, it also protects against man-in-the-middle, against message spoofing and against message alteration, in other words it guarantees message integrity. With an SSL/TLS encrypted channel you are guaranteed that the text processed by the server is the text sent by the client.

And about TCP checksum: you have a much higher chance (quite hugh actually) to encounter a random memory bit-flip or a CPU bit-flip (ie. a hardware error in the server after it received the package) than to encounter an message alteration that preserves the checksum and results in valid SQL.

Answer 4

As an alternative, use the OUTPUT clause to return the results of each DML operation. The application then confirms that the values returned match the values supplied, and responds appropriately.

The problem comes with testing. How do you adequately test for something that only occurs once in every billion calls? You don't. You simulate the errors, but then you are testing a simulation, not the real thing.