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.

 

 

ASP.NET Custom Error Pages Lock Violation

Notes to self:

refs:

IIS7 - Lock Violation error, HTTP handlers, modules, and the <clear /> element

Forcing custom 404 pages for pages in URL Routing

Edit the file:

 

"C:\Windows\SysWOW64\inetsrv\Config\applicationHost.config"

Edit the line taking off ,defaultPath from the allowAbsolutePathsWhenDelegated,defaultPath

<httpErrors lockAttributes="allowAbsolutePathsWhenDelegated">
<error statusCode="401" prefixLanguageFilePath="%SystemDrive%\inetpub\custerr" path="401.htm" />
<error statusCode="403" prefixLanguageFilePath="%SystemDrive%\inetpub\custerr" path="403.htm" />
<error statusCode="404" prefixLanguageFilePath="%SystemDrive%\inetpub\custerr" path="404.htm" />
<error statusCode="405" prefixLanguageFilePath="%SystemDrive%\inetpub\custerr" path="405.htm" />
<error statusCode="406" prefixLanguageFilePath="%SystemDrive%\inetpub\custerr" path="406.htm" />
<error statusCode="412" prefixLanguageFilePath="%SystemDrive%\inetpub\custerr" path="412.htm" />
<error statusCode="500" prefixLanguageFilePath="%SystemDrive%\inetpub\custerr" path="500.htm" />
<error statusCode="501" prefixLanguageFilePath="%SystemDrive%\inetpub\custerr" path="501.htm" />
<error statusCode="502" prefixLanguageFilePath="%SystemDrive%\inetpub\custerr" path="502.htm" />
</httpErrors>

In IIS manager top level, unlock the sections for allowAbsolutePathsWhenDelegated & defaultpath

image

Use the lock /unlock Actions on right of screen.

image

Then for the site do the same

image

Unlock the default path and allow absolute paths when delegated

image

 

Also worth setting the modules to unlocked in the config and in the UI.

Something worked – just don’t know what!

Schedule restore of live company to test company Dyanamics GP

To keep a fresh copy of our test database always available, I scheduled a restore from the live database into the test company database to occur once a week.
[Please also checkout my updated related post Automated restore of Live Company to Test Company in GP2013 CHANGES 21 Jan 2015]

This is how I do that:

Credit to the stack overflow question for how to find latest backup

Sql to get most recent full backup file for a specific database in sql server

This Microsoft Document shows official GP guide for restoring a live db into test. CHECK THIS AS YOUR INSTALL MAY USE MODULES WE DONT- THAT WILL REQUIRE EXTRA STEPS!!

Set up a test company that has a copy of live company data by using SQL Server

Create a SQL Job and schedule it to run once a week (or whatever you frequency you choose). To this job add the following steps.

Step 1 Restore from Live company into Test company

This step should be set to run from the Master database when configuring it in SQL agent (updated for GP2013 21 Jan 2015).

if exists (select 1 from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'SY00100') begin
declare @Statement varchar(850)
select @Statement = 'declare @cStatement varchar(255)
declare G_cursor CURSOR for
select case when UPPER(a.COLUMN_NAME) in ('
'COMPANYID'',''CMPANYID'')
then '
'update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''+ cast(b.CMPANYID as char(3))
else '
'update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''''''+ db_name()+'''''''' end
from INFORMATION_SCHEMA.COLUMNS a, '
+rtrim(DBNAME)+'.dbo.SY01500 b
where UPPER(a.COLUMN_NAME) in ('
'COMPANYID'',''CMPANYID'',''INTERID'',''DB_NAME'',''DBNAME'')
and b.INTERID = db_name() and COLUMN_DEFAULT is not null
and rtrim(a.TABLE_NAME)+'
'-''+rtrim(a.COLUMN_NAME) <> ''SY00100-DBNAME''
order by a.TABLE_NAME
set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
exec (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
close G_cursor
DEALLOCATE G_cursor
set nocount off'

from SY00100
exec (@Statement)
end
else begin
declare @cStatement varchar(255)
declare G_cursor CURSOR for
select case when UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID')
then 'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '+ cast(b.CMPANYID as char(3))
else 'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '''+ db_name()+'''' end
from INFORMATION_SCHEMA.COLUMNS a, DYNAMICS.dbo.SY01500 b
where UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID','INTERID','DB_NAME','DBNAME')
and b.INTERID = db_name() and COLUMN_DEFAULT is not null
order by a.TABLE_NAME
set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
exec (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
close G_cursor
DEALLOCATE G_cursor
set nocount off
end

Step 2 Restore the company ID or database id name value

There are references in GP to the company ID in the company database. The company ID is held by the DYNAMICS database. To correct the freshly restored database so it thinks it is the test company introduce the following step. Configure this to run from the test company database when working with it in SQL agent.

/******************************************************************************/

/* Description: */

/* Updates any table that contains a company ID or database name value */

/* with the appropriate values as they are stored in the DYNAMICS.dbo.SY01500 table */

/* */

/******************************************************************************/

if not exists(select 1 from tempdb.dbo.sysobjects where name = '##updatedTables')
create table [##updatedTables] ([tableName] char(100))
truncate table ##updatedTables
declare @cStatement varchar(255)
declare G_cursor CURSOR for
select
case
when UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID')
then 'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '+ cast(b.CMPANYID as char(3))
else
'update '+a.TABLE_NAME+' set '+a.COLUMN_NAME+' = '''+ db_name()+''''
end
from INFORMATION_SCHEMA.COLUMNS a, DYNAMICS.dbo.SY01500 b, INFORMATION_SCHEMA.TABLES c
where UPPER(a.COLUMN_NAME) in ('COMPANYID','CMPANYID','INTERID','DB_NAME','DBNAME', 'COMPANYCODE_I')
and b.INTERID = db_name() and a.TABLE_NAME = c.TABLE_NAME and c.TABLE_CATALOG = db_name() and c.TABLE_TYPE = 'BASE TABLE'
and a.TABLE_NAME <> 'UPR70501' and a.TABLE_NAME <> 'MPOSMAIN'
set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
insert ##updatedTables select
substring(@cStatement,8,patindex('%set%',@cStatement)-9)
Exec (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor
select [tableName] as 'Tables that were Updated' from ##updatedTables

Step 3 Change the current database owner

Configure to run against the test company.

sp_changedbowner 'DYNSA'

Step 4 Fix the Next Note ID

The next note ID is held against each company in the DYNAMICS database. This needs changing for the test company to represent the freshly restored data. Run the following to correct the IDS. Configure to run against the test company.

/*
** FindMaxNoteIndex.SQL
**
** Purpose:
**
** Find the max value of NOTEINDX from all tables.
**
** This script must be run against the company in which the notes are incorrect.
** it will automatically update your SY01500 for you to the correct next note index.
**
** Modified by MRO
*/

if exists (select * from tempdb..sysobjects where name = '##GPSMaxNote')
drop table dbo.##GPSMaxNote

set nocount on
create table ##GPSMaxNote (MaxNoteIndex numeric(19,5) null)
go


declare @cStatement varchar(255) /* Value from the t_cursor */
declare @noteidx numeric(19,5)
declare @database as varchar(5)
set @database = cast(db_name() as varchar(5))

/* Get the tables that have a column name of NOTEINDX. */
declare T_cursor cursor for
select 'declare @NoteIndex numeric(19,5) select @NoteIndex = max(NOTEINDX) from ' + o.name + ' insert ##GPSMaxNote values(@NoteIndex)'
from sysobjects o, syscolumns c
where o.id = c.id
and o.type = 'U'
and c.name = 'NOTEINDX'

/* Ok, we have the list of tables. Now get the max value of NOTEINDX from each table. */

open T_cursor
fetch next from T_cursor into @cStatement
while (@@fetch_status <> -1)
begin
exec (@cStatement)
fetch next from T_cursor into @cStatement
end
deallocate T_cursor

select 'Max Note Index:', max(MaxNoteIndex) from ##GPSMaxNote where MaxNoteIndex is not null
use DYNAMICS
set @noteidx = (select max(MaxNoteIndex) from ##GPSMaxNote where MaxNoteIndex is not null)

update SY01500 set NOTEINDX = (@noteidx + 1.0) where INTERID=@database
set nocount off