Horkay Blog
The postings on this site are my own and do not represent my Employer's positions, advice or strategies.
Friday, 23 October 2009

The patching game.

Better give me a whole lotta lumps.....

Last week Microsoft released a patch for a SQL Server Security vulnerability.  This week Microsoft released SQL Server 2005 CU 6 (which includes the security patch and some other fixes).

Test those "other fixes" carefully.

We just ran into a change in behavior for how SSIS evaluated variables that are passed into Execute SQL Tasks as parameters, Not sure exactly which patch introduced the change as they are cumulative and we were running CU 2 previously, so anything from CU 3 on up could have been the cause.

We were running on version 9.00.4211, which is SQL 2K5, SP3 CU2.  We upgraded to the Security Fix (9.00.4262), and the SSIS package broke.  Hoping not to have to trouble shoot the issue and needing to be on CU 6 anyway, we applied it, hoping it would fix the issue...no luck, time to trouble shoot.

The issue is really one of poor coding practice.

There are some variables passed into an execute sql task to dynamically create a where clause. 

The variable in question was declared as an Integer

The variable was than passed into the execute sql task, but it was of a varchar type, below is the corrected parameter mapped as a numeric.

The execute sql task is shown here, the parameter is the first one evaluated with the if "? <> 2".

This execute sql task passed the parameter in is as '', blank, if the parameter mapping was not set properly.  I confirmed this behavior behind the scenes with a profiler trace as well.  Interestingly even with the parameter mapped properly as numeric, it is still passed into sql server as a varchar !  But with the parameter mapped as numeric, the varchar is properly populated with value of the variable being passed in and the statement completes successfully.

Test those SQL Server patches carefully, there is always the off chance of unintended behavior change.  Ultimately it was "sloppy coding" to be passing an integer variable in ssis, to a parameter mapped as varchar.  But it has been running that way for 14-16 months without issue.

Test carefully.

9.00.4211.00 - 2005 SP3 CU 2
9.00.4262.00 - QFE Security Fix
9.00.4266.00 - 2005 sp3 cu 6

In response on where to get sp3 cu 6:

http://support.microsoft.com/kb/974648/en-us


 

Friday, 23 October 2009 08:04:41 (Central Standard Time, UTC-06:00) | Comments [1] | SQL Server |  SSIS#
Friday, 02 October 2009

I'm working on creating a robust login auditing system for sql server 2005 and 2008.  Part of that process is scraping the error log for Error 18456 to keep counts and provide reports on failing logins.  Works great, but it depends on the two line entry in the error log of the following:

2009-10-01 00:02:00.34 Logon     Error: 18456, Severity: 14, State: 8.

2009-10-01 00:02:00.34 Logon     Login failed for user '<user name>'. [CLIENT: <ip address>]

 

Suddenly the process fails today, which is ok, that's why we test, test and soak test.

 

Seems it is possible to generate a 18456 error that is not followed by login failed for user message.

 

Good coding practices would have avoided an error (by checking to ensure the next line did contain a login failed message).  We were working on the assumption that the two entries always happen together.  Not so.  Kind of makes me wonder where it went!

 

This happened on a SQL Server 2005, EE, 64 Bit, SP3, CU 4.

 

The specific entry is below:

 

Error: 18456, Severity: 14, State: 16.
CHECKDB for database '<removed>' finished without errors on 2009-10-01 04:05:02.370 (local time). This is an informational message only; no user action is required.

 

Friday, 02 October 2009 13:57:37 (Central Standard Time, UTC-06:00) | Comments [0] | SQL Server#
Search
Popular Posts
Unpatched Vulnerabiltiy discovered ...
Spring Fornicator brewed...
DTA - Failed to initialize MSDB dat...
SQL Server Security, not where it n...
Check the Uptime of a Windows Serve...
Recent Posts
Archive
May, 2017 (2)
April, 2017 (1)
March, 2017 (1)
February, 2017 (1)
December, 2016 (2)
October, 2016 (2)
September, 2016 (1)
August, 2016 (1)
July, 2016 (1)
March, 2016 (2)
February, 2016 (3)
December, 2015 (4)
November, 2015 (6)
September, 2015 (1)
August, 2015 (2)
July, 2015 (1)
March, 2015 (2)
January, 2015 (1)
December, 2014 (3)
November, 2014 (1)
July, 2014 (2)
June, 2014 (2)
May, 2014 (3)
April, 2014 (3)
March, 2014 (1)
December, 2013 (1)
October, 2013 (1)
August, 2013 (1)
July, 2013 (1)
June, 2013 (2)
May, 2013 (1)
March, 2013 (3)
February, 2013 (3)
January, 2013 (1)
December, 2012 (3)
November, 2012 (1)
October, 2012 (1)
September, 2012 (1)
August, 2012 (1)
July, 2012 (4)
June, 2012 (3)
April, 2012 (1)
March, 2012 (3)
February, 2012 (3)
January, 2012 (4)
December, 2011 (3)
October, 2011 (2)
September, 2011 (2)
August, 2011 (8)
July, 2011 (4)
June, 2011 (3)
May, 2011 (3)
April, 2011 (1)
March, 2011 (2)
February, 2011 (3)
January, 2011 (1)
September, 2010 (1)
August, 2010 (2)
May, 2010 (2)
April, 2010 (3)
March, 2010 (1)
February, 2010 (4)
January, 2010 (1)
December, 2009 (3)
November, 2009 (2)
October, 2009 (2)
September, 2009 (5)
August, 2009 (4)
July, 2009 (8)
June, 2009 (2)
May, 2009 (3)
April, 2009 (9)
March, 2009 (6)
February, 2009 (3)
January, 2009 (8)
December, 2008 (8)
November, 2008 (4)
October, 2008 (14)
September, 2008 (10)
August, 2008 (7)
July, 2008 (7)
June, 2008 (11)
May, 2008 (14)
April, 2008 (12)
March, 2008 (17)
February, 2008 (10)
January, 2008 (13)
December, 2007 (7)
November, 2007 (8)
Links
Categories
Admin Login
Sign In
Blogroll