Progress messages from SQL Server to VB.NET/C# application

When executing long running queries on SQL server, the messages from PRINT statements do not return to the caller sometimes until the whole process is finished, or intermittently at best.

If running a long running process from the GUI or debugging in SQL server management studio, it can be desirable to let the user know where things are at and prevent them force quitting your application prematurely.

By using the following statement messages can be sent back immediately, note the NOWAIT, this forces the message back right away:

RAISERROR (N'working', 10,1) WITH NOWAIT

In the above example, 10 is the severity level of the error we are raising  and 1 the state. This is not severe enough to stop the statement running.

I use this all the time now for getting a feeling of security from seeing something happening on long running scripts.

ADO.NET

With C#/VB.NET using ADO.NET to connect to the procedure, we have a “infoMessage” event that is raised whenever a message comes back from SQL. By handling this event we can proceed to update the user GUI with that message, or another message as required.

cn.InfoMessage += delegate(object sender, SqlInfoMessageEventArgs e)
{
txtMessages.Text += "\n" + e.Message;
};

 

References:

Regarding hashing passwords .NET & Rfc2898DeriveBytes

I live outside the world of oAuth and need to hash passwords for authentication

For goodness sake don’t be tempted to write your own hash! Research the latest advise as it changes as machines and cracking advances. Swathes of passwords have been stolen from compromised sites in the past, and have been cracked and sold or given away. Don’t let your site be the source of misery!

Use well known hashing algorithm

Use the .NET class Rfc2898DeriveBytes that implements PBKDF2 for password hashing. This uses iterations to make it computationally expensive for any brute force attacks.

Use salt

To prevent attackers reverse engineering your users’ passwords using rainbow tables, and other colourful techniques, use the above class with a random salt per user hash. You need to store the salt in your data store, its ok to store it concatenated with password hash.

Use good random salt with high amount of entropy

Use a decent random generator such as that provided by the RNGCryptoServiceProvider  in the System.Security.Cryptography namespace to generate your salt, there are degrees of randomness, remember random is pseudo random in the random class in .NET.

Load the CPU with iterations

Choose a good number of iterations when generating the hash. To future proof your implementation, also store the number of iterations used to generate the hash against each user, with the hash and salt, in the data store. This makes it possible to “turn up” the number of iterations as machines get faster in the future and not “spoil” the existing hashes (obviously the hashes would be upgraded after login in this scenario).

Risks of Denial of Service (Dos)

Denial of Service, be aware that for this protection you are on purpose introducing a computationally expensive routine to the authentication methods of the site. This exposes a possible denial of service attack by bombarding the login method with authentication requests, some sort self healing technique to limit such an attack is required. Perhaps if high traffic is detected, temporarily add a capture to the login page to limit the DoS impact.

 

 

VEIS Webservice matchcode error

Something changed this week with the VEIS web service that provides validation of European tax registration numbers.

http://ec.europa.eu/taxation_customs/vies/checkVatService.wsdl

All of a sudden we get an error! The SOAP error on deserialization is:

'Invalid enum value '3' cannot be deserialized into type 'canford.checkVATWebservice.matchCode'

I regenerated the SOAP reference in Visual Studio, -still not working.

It seems the soap response is containing an invalid (according to the WSDL) value of 3 for the type match code that is returned.

I’ve emailed the address provided for support, no response yet, so to get us running again I’ve added in this as a valid enum, however I have no idea of what the value of 3 actually means!

To fix, edit the Reference.vb file generated after refreshing the reference, found under your service reference directory,  (this example in VB.NET)

<System.CodeDom.Compiler.GeneratedCodeAttribute("System.Runtime.Serialization", "4.0.0.0"),  _
System.Runtime.Serialization.DataContractAttribute(Name:="matchCode", [Namespace]:="urn:ec.europa.eu:taxud:vies:services:checkVat:types")> _
Public Enum matchCode As Integer

<System.Runtime.Serialization.EnumMemberAttribute(Value:="1")> _
_1 = 0

<System.Runtime.Serialization.EnumMemberAttribute(Value:="2")> _
_2 = 1

End Enum

 

Adding

<System.Runtime.Serialization.EnumMemberAttribute(Value:="3")> _        _3 = 2
to give this:
<System.CodeDom.Compiler.GeneratedCodeAttribute("System.Runtime.Serialization", "4.0.0.0"),  _
System.Runtime.Serialization.DataContractAttribute(Name:="matchCode", [Namespace]:="urn:ec.europa.eu:taxud:vies:services:checkVat:types")> _
Public Enum matchCode As Integer

<System.Runtime.Serialization.EnumMemberAttribute(Value:="1")> _
_1 = 0

<System.Runtime.Serialization.EnumMemberAttribute(Value:="2")> _
_2 = 1

<System.Runtime.Serialization.EnumMemberAttribute(Value:="3")> _
_3 = 2
End Enum

Now it will work again. I expect this will be fixed sometime after this post, but for those of you struggling with this issue at least you know what to do now!

update 9th Jan 2015

Refresh of references today brought in the “missing” enum – I guess it is fixed.

<xsd:enumeration value="3">
<xsd:annotation>
<xsd:documentation>NOT_PROCESSED</xsd:documentation>
</xsd:annotation>
</xsd:enumeration>

Visual Studio Auto Shelve Extension

Quality and finance audits

In your annual audits, as a software manager the question always crops up of how is work in progress protected? All the project source code is normally safely locked away in team foundation server. The Team Foundation Server is in turn backed up and backup shipped off site. What about drive failure on the local developer machine? Normally hours or days of work could be lost since the last check was performed!
My answer to this is to use Auto Shelve extension and a weekly machine backup of my development machines.

Auto Shelve protects work loss from drive failure since last checkin

Auto shelve is a Visual Studio extension that periodically & automatically shelves your pending changes into source control as you work. It does this in background and so has minimal impact. Should my SSD drive fail catastrophically, work since my last check in will have been preserved in a shelf set in the team foundation server.

image 

After setting it up, Auto Shelve keeps overwriting the shelf set with the latest pending changes. After using it for some time now I can say it is effortless backup and I love it. I will love it even more when the day comes that I have a disaster on my hands.

Install

Install it through Tools>>Extensions and Updates

Search on the online node to the left, for autoshelve and install.

Go to the settings to set up your shelf set name and the frequency of shelving.

image