Search results matching tags 'SQL Server 2005' and 'SQL Server 2008'

MS12-070 : Security Updates for all supported versions of SQL Server

AaronBertrand — Wed, 10 Oct 2012 16:57:00 GMT

This week there was a security release for all supported versions of SQL Server. Each version has 32-bit and 64-bit patches, and each version has GDR (General Distribution Release) and QFE (Quick-Fix Engineering) patches. GDR should be applied if you are at the base (RTM or SP) build for your version, while QFE should be applied if you have installed any cumulative updates after the RTM or SP build. (More details here.)

SQL Server 2005

RTM, SP1, SP2, SP3 - not supported
SP4 - GDR = 9.00.5069, QFE = 9.00.5324

SQL Server 2008

RTM, SP1 - not supported
SP2 - GDR = 10.00.4067, QFE = 10.00.4371
SP3 - GDR = 10.00.5512, QFE = 10.00.5826

SQL Server 2008 R2

RTM - not supported
SP1 - GDR = 10.50.2550, QFE = 10.50.2861
SP2 - not affected

SQL Server 2012

RTM: GDR = 11.00.2218, QFE = 11.00.2376
SP1 - not yet supported; should not be affected once SP1 is released.

Now, a couple of oddities you might have noticed:

The security bulletin mentions something about SQL Server instances with Reporting Services installed. Yet the KB articles for individual updates state that all instances of SQL Server are eligible for the update. And the update does, in fact, update sqlservr.exe and @@VERSION, even for systems where SSRS is not installed. So until there is some clarification on this point, I'm going to treat this as a patch for all instances.
Both the GDR and QFE KBs for multiple patches state that the preceding cumulative updates are included. I believe this is a copy & paste error and that the cumulative updates for a specific branch are only included with the QFE patch. I will update here if I get any confirmation on this.

Even if they come back and say, whoops, our bad, the KBs should mention it is SSRS only, and the GDRs do not affect sqlservr.exe and do not include the CU updates, I'm still going to apply the patch everywhere. Why? Well, for consistency, I'd rather have all of my instances at @@VERSION = x, than have the SSRS instances at x and the non-SSRS instances at < x.

More information on the Patch Tuesday updates for SQL Server

AaronBertrand — Sun, 19 Jun 2011 20:06:00 GMT

Last week, Microsoft released a series of patches for all supported versions of SQL Server (from SQL Server 2005 SP3 all the way to SQL Server 2008 R2). The reason for the patch against SQL Server installations is largely a client-side issue with the XML viewer application, and for SQL Server specifically, the exploit is limited to potential information disclosure. A very easy way to avoid exposure to this exploit is simply to never open a file with the .disco extension (these files are likely already blocked by your e-mail system, but you can never be too careful). Still, Microsoft's recommendation is to patch all systems. You can read more about your course of action, depending on your current build, in my previous blog post.

After the patches were released, some pertinent questions came up, and I sought answers from an internal source -- after my questions during a security webinar, which you can now view online, proved useless.

Why is sqlservr.exe affected at all?

As mentioned, this is a patch specifically for an application that is part of the client tools, so it made little sense to me why sqlservr.exe (and @@VERSION) would be updated. My answer from the patch team is that, while sqlservr.exe has not updated since the previous Cumulative Update release for each branch, some of its dependencies were updated, so the build number was increased for consistency with other files in the branch.
What other fixes are included in the GDR and QFE updates, aside from the XML viewer fix?

This question was raised because the build number increased significantly in some cases - for example, in SQL Server 2005 SP4, the build number on top of Cumulative Update #2 (9.0.5266) jumped to 9.0.5292 once the update was applied. This caused concern amongst some customers, who expect from such a delta that they have other regression testing to do. The reason cited here is that only the security bulletin update is in the GDR and QFE updates, and that the build number gap is intentional, so that the fix may be applied to all customers. If I'm reading between the lines, that tells me that either (a) some customers had interim (non-public) hotfixes after the latest CU, or (b) they are leaving room so that customers at the previous CU can still apply hotfixes even if they don't apply the security bulletin update. I have no way to confirm either of those hypotheses, but right now the word from Microsoft is that there should be no further regression testing required.

That said, for build number continuity, the packages *do* contain the previous public cumulative update. So, for example, both the GDR and QFE update for SQL Server 2008 R2 contains Cumulative Update #7 and the fix itself. So "which fixes are in the package" depend on what build you are at before you apply the security update. If you are not at the most recent CU, then you will benefit from other fixes (and may have further regression testing to perform) -- but these are not due to the security update itself.
Will the security fixes be included in the next CU for each branch?

Since the next cumulative update for SQL Server 2008 R2 is due in the near future, and since we have learned from experiences with applying service packs that don't always include the fixes from the most recent cumulative update (even though the build numbers would imply that they are included), I wanted to know if this set of fixes has made it into all of the cumulative update branches. The answer is that, yes, the next cumulative update for each branch will include this security fix (though those updates will likely have even higher build numbers than the security update for each branch). I take it by extension that the upcoming Service Pack 1 for SQL Server 2008 R2 will also include this fix, but I did not ask that question explicitly, so if you are currently running the CTP of Service Pack 1 (10.50.2418 or 10.50.2425), I cannot honestly tell you how long you will be waiting for this fix (see the above comment about being careful with .disco files).

In addition to these questions, I'm also seeing some reports of bad behavior on systems where automatic updates are applied or where certain cumulative updates were already in place. Please see the following items and use caution when applying this update:

http://darrenmyher.wordpress.com/2011/06/17/microsoft-sql-server-update-gdr-1617-leaves-sql-server-unusable/
http://connect.microsoft.com/SQLServer/feedback/details/675794/sql-2008-r2-security-hotfix-2494088-fails-if-ucp-installed
http://support.microsoft.com/kb/2163980
http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/447354c8-56df-46b2-a198-abbbecb4b1ff

Now, I am running Windows 7 x64, and I am only using Windows Update (I have never turned on Microsoft Update, and didn't find much reason to, since I don't run Windows-based Office applications - I didn't realize that Microsoft Update covered SQL Server products also). So the patch was never offered to me; I applied it manually, and did not experience these issues. If you have either of these problems (or others that haven't been brought to my attention), hopefully I will have some information for you soon.

Security updates for all supported versions of SQL Server

AaronBertrand — Tue, 14 Jun 2011 19:35:00 GMT

It's patch Tuesday!

[UPDATE June 19 : Please see my follow-up post about this security update.]

Today Microsoft released a security bulletin covering several issues that could potentially affect SQL Server; these exploits include remote code execution, denial of service, information disclosure and elevation of privilege. You should test these patches on all machines running SQL Server, including those running only client tools (e.g. Management Studio or Management Studio Express). The updates affect the following versions of SQL Server:

SQL Server 2005 SP3
SQL Server 2005 SP4
SQL Server 2008 SP1
SQL Server 2008 SP2
SQL Server 2008 R2

So, depending on your SQL Server version (run SELECT @@VERSION;), here is what you should do:

If you are running... And your build number is... Your best course of action is probably to...

SQL Server 2005 Less than 9.0.4035
Upgrade to Service Pack 3 (9.0.4035) or Service Pack 4 (9.0.5000), then come back for the GDR

Exactly 9.0.4035 (SP3) Install the SP3 GDR (9.0.4060) from KB #2494113

Between 9.0.4036 and 9.0.4339 (a) Upgrade to Service Pack 4 (9.0.5000), then come back for the GDR
OR
(b) Install the SP3 QFE (9.0.4340) from KB #2494112

Exactly 9.0.5000 (SP4) Install the SP4 GDR (9.0.5057) from KB #2494120

Greater than 9.0.5000
Install the SP4 QFE (9.0.5292) from KB #2494123

SQL Server 2008 Less than 10.0.2531
Upgrade to Service Pack 1 (10.0.2531) or Service Pack 2 (10.0.4000), then come back for the GDR

Exactly 10.0.2531 (SP1) Install the SP1 GDR (10.0.2573) from KB #2494096

Between 10.0.2532 and 10.0.2840 (a) Upgrade to Service Pack 2 (10.0.4000), then come back for the GDR
OR
(b) Install the SP1 QFE (10.0.2841) from KB #2494100

Exactly 10.0.4000 (SP2) Install the SP2 GDR (10.0.4064) from KB #2494089

Greater than 10.0.4000 Install the SP2 QFE (10.0.4311) from KB #2494094

SQL Server 2008 R2 Exactly 10.50.1600 (RTM) Install the GDR (10.50.1617) from KB #2494088

Between 10.50.1601 and 10.50.1789 Install the QFE (10.50.1790) from KB #2494086

Greater than 10.50.1790
(e.g. 10.50.2418 or 10.50.2425)
Wait for the final release of Service Pack 1
Watch for cumulative update or updates to MS11-049
At this time there is no fix for the CTP of SQL Server 2008 R2 SP1

If you are running...	And your build number is...	Your best course of action is probably to...
SQL Server 2005	Less than 9.0.4035	Upgrade to Service Pack 3 (9.0.4035) or Service Pack 4 (9.0.5000), then come back for the GDR
Exactly 9.0.4035 (SP3)	Install the SP3 GDR (9.0.4060) from KB #2494113
Between 9.0.4036 and 9.0.4339	(a) Upgrade to Service Pack 4 (9.0.5000), then come back for the GDR OR (b) Install the SP3 QFE (9.0.4340) from KB #2494112
Exactly 9.0.5000 (SP4)	Install the SP4 GDR (9.0.5057) from KB #2494120
Greater than 9.0.5000	Install the SP4 QFE (9.0.5292) from KB #2494123
SQL Server 2008	Less than 10.0.2531	Upgrade to Service Pack 1 (10.0.2531) or Service Pack 2 (10.0.4000), then come back for the GDR
Exactly 10.0.2531 (SP1)	Install the SP1 GDR (10.0.2573) from KB #2494096
Between 10.0.2532 and 10.0.2840	(a) Upgrade to Service Pack 2 (10.0.4000), then come back for the GDR OR (b) Install the SP1 QFE (10.0.2841) from KB #2494100
Exactly 10.0.4000 (SP2)	Install the SP2 GDR (10.0.4064) from KB #2494089
Greater than 10.0.4000	Install the SP2 QFE (10.0.4311) from KB #2494094
SQL Server 2008 R2	Exactly 10.50.1600 (RTM)	Install the GDR (10.50.1617) from KB #2494088
Between 10.50.1601 and 10.50.1789	Install the QFE (10.50.1790) from KB #2494086
Greater than 10.50.1790 (e.g. 10.50.2418 or 10.50.2425)	Wait for the final release of Service Pack 1 Watch for cumulative update or updates to MS11-049 At this time there is no fix for the CTP of SQL Server 2008 R2 SP1

What is the difference between a GDR and a QFE? A GDR (general distribution release) is one that Microsoft support deems is necessary for all systems running SQL Server. A QFE (quick fix engineering) is one that does not affect everyone. Why are there two releases for this important fix? Well, one reason is that after a QFE is installed, it is no longer possible to install a GDR. So, if you have a system that has had previous cumulative updates or QFEs applied, the GDR might not work for you. If you have a system that is exactly at one of the levels described above, then the GDR is probably the better choice, because it will allow you to install either a GDR or a QFE in the future, whereas installing a QFE on such a system kind of paints you into a corner.

There is also a GDR available if you are running Management Studio Express 2005 (but none seem to be listed at this time for the 2008 or 2008 R2 versions):

KB #2546869

As an aside, even if you are not running SQL Server, you should review the grander bulletin to see how else these issues may affect you... and be sure to register to tune in to tomorrow's webcast.

You want to grant someone permissions to do WHAT?!?!

Lara Rubbelke — Mon, 24 Jan 2011 02:32:00 GMT

Have you ever heard of these types of requests? True story! I have had each of these and many more:

A customer needed to grant a business user the rights to issue a KILL command – without giving them sysadmin or CONTROL SERVER.
A customer wanted to grant a user the rights to update a job – just one job – without any other changes to the job.
There was the case where a customer wanted to give a set of junior admins the rights to unlock a set of logins – without granting any additional rights to alter logins.
And of course, there are many, many customers who are facing internal and external regulations that dictate the DBAs should not have rights to view sensitive data. Period.

Managing security is never easy, and these additional requirements can cause a lot of distress to those who are trying to provide the right level of security while protecting their data, databases, and server infrastructure. Grant too many privileges, and you open up your environment to a host of potential issues. Grant too few privileges, and the users and administrators are unable to do their jobs.

Enter the Separation of Duties Framework. The Separation of Duties Framework was originally designed to address the separation of DBA from sysadmin, but this framework may also be used to temporarily grant users elevation of privileges in a controlled and auditable environment. The SQL Server Separation of Duties Framework will ease the process of setting up a restrictive environment while providing a predefined set of processes a DBA may use to manage restricted instances and sensitive databases. The Separation of Duties Framework is designed to empower the DBA team (or users) to be productive and responsive with processes that are auditable, secure, and extensible while being easy to implement and manage.

The Separation of Duties Framework was originally released in November 2010. Brian Davis (blog and twitter) and I just released v2.0 of the framework. The framework will create database roles, signed stored procedures, and the securables needed to support the environment. The framework is set up in the following steps:

Define the roles and tasks. Each organization will have different regulations that stipulate the security boundaries for individuals and groups. Prior to installing the Separation of Duties Framework, it is necessary to define the types of roles that will engage with SQL Server and the tasks that each role is permitted to execute.
Create folders to represent the defined roles. Create folders in a Procedures directory that will mimic the security roles you identified in the previous step. Remember that these folders are hierarchical, and each folder level will inherit the privileges of the parent folders. The Separation of Duties Framework will create roles based on the folder structure under the Procedures directory.
Add stored procedures sql files to the folders created in the previous step. Create procedures or use existing example procedures available in the framework that represent the tasks each role is allowed to execute. Place these in the appropriate folder which represents the users who are permitted to execute the task. The Separation of Duties Framework install script will create each procedure, sign the procedures with a certificate, and grant EXECUTE permissions to the appropriate roles.
Execute the PowerShell install script.
Place the appropriate users and groups into the newly created Database Roles.

More details on the installation process are available with the download. Brian Davis and I will also be following up with some additional blogs with details on the framework over the next few weeks.

Now we're getting somewhere : the MSXML6 issue

AaronBertrand — Fri, 05 Nov 2010 17:35:00 GMT

Last February / March, I wrote several posts about an issue with MSXML6 on Windows XP which prevented the installation of SQL Server 2005, SQL Server 2008, and even service packs or cumulative updates in some cases:

2009-02-20 : The XP SP3 / MSXML6 SP2 / SQL Server debacle
2009-03-16 : More info / good news : installing SQL Server on XP SP3 with MSXML6 SP2
2009-03-20 : One more post about MSXML6 SP2, XP SP3 and SQL Server : I've been duped!

There are also a couple of Connect items talking about the error itself or a potential workaround:

#361660 : SQL Server Express 2008 Installation - Update msxml6r error
#435245 : Update the SQL Server 2005 SP3 Express installers to bypass MSXML6 SP2

The latter article was recently updated by Peter Saddow of Microsoft, who said, "Just providing an update on this issue. We are planning to address in SP4." I guess this will ultimately help Express users to some degree, but not regular edition customers - SQL Server 2005 does not support slipstream, so if MSXML6 is blocking the engine installation, SP4 is not going to do you much good. (I posted yesterday that a public preview of SQL Server 2005 SP4 is available.)

For a short while, the KB article developed as a response to this issue (KB #968749) had instructions on using the Windows Installer Cleanup Utility to remove MSXML6 completely, and then reinstall it. This utility has since disappeared from Microsoft's web site, so now the KB article just says the following:

I've recently been told that there is a new EXE that has been published by Microsoft specifically to clean up MSXML6, on Windows XP systems only, so that SQL Server can be installed. Please use this file at your own risk, and if possible, wait until its use is fully documented in KB #968749. But if you can't wait, the file is here:

http://download.microsoft.com/download/E/3/F/E3F51FFB-505D-480E-9F67-0DD3A9680DEE/MSXMLFix.EXE

When you run this file, you'll have a EULA to agree to, and then a self-extracting WinZip archive:

Make a new folder before clicking Unzip because, if you dump this in your standard temp folder, you'll have no idea which files were extracted.

Now, when you unzip the installer, you end up with these files, and no instructions:

(Note the last modified dates of the important files - this will come up later.)

If you quickly scan the VBS file, you'll see that it references the MSP file, so it is clear that to get the ball rolling, you can double-click the VBS file. I don't have an XP system, so on Windows 7, I can't tell you exactly what the EXE does, as I get the following error:

It would be easy enough to edit the VBS file to bypass the local operating system check, but that kind of defeats the purpose. You should ONLY try to apply this fix on a machine that is the correct operating system and is clearly exhibiting the symptoms in KB #968749.

See, my concern (well, let's call it after-the-fact disappointment) is that these files were created LAST JUNE... and then Microsoft sat on them for almost a year and a half. By now, I would bet that 99% of the people affected by this issue have either (a) found another workaround because they were sick of waiting, or (b) subsequently upgraded to a more recent operating system which, as an added bonus, is not affected by this issue in the first place. So from my point of view this fix - even if it works - is too little, too late.

SQL Server updates - from Microsoft and from 3rd parties

AaronBertrand — Tue, 17 Aug 2010 17:37:00 GMT

Ola's Index Maintenance scripts

On August 3rd, Ola Hallengren (blog) released a substantial update to his Backup, Integrity Check and Index Optimization scripts - allowing you to more finely tune the object selection process. Over this past weekend he also updated the error handling to get around some blocking issues. For information about Ola's free solution, go here:

http://ola.hallengren.com/Versions.html

SQL Server 2005 SP3 Cumulative Update #11

Last night, Microsoft released a new cumulative update for SQL Server 2005 SP3. This will bring you to build 9.00.4309. You can read about the fixes and request the download in KB #2258854:

http://support.microsoft.com/kb/2258854

SQL Server 2008 R2 Cumulative Update #3

Last night Microsoft also released the third update for R2, which brings the build number up to 10.50.1734. Check out the fixes and obtain the bits in KB #2261464:

http://support.microsoft.com/kb/2261464

Mladen Prajdić's SSMS Tools Pack

Mladen (blog | twitter) quietly announced today on twitter that next week we will see the 1.9 release of his popular SSMS Tools Pack, a free add-in for Management Studio 2005 and above. In Mladen's own words, the update will contain "mostly a lot of bug fixes. window coloring by regexes, snippet printing, rewritten regions and debug sections, that's about it :)" In the meantime, you can download version 1.8 here:

http://www.ssmstoolspack.com/

Microsoft Assessment and Planning (MAP) Toolkit 5.0 Beta

Lara Rubbelke — Tue, 25 May 2010 03:19:00 GMT

Do you know where SQL Server is installed - everywhere it is installed? Do you really know where SQL Server is installed? Are you looking for a tool that will help you discover any rogue instances so you can better manage these instances?

The Beta 2 for the Microsoft Assessment and Planning (MAP) Toolkit 5.0 is now open. Join the beta review program and help influence the development of the toolkit. To participate, register for the MAP Toolkit 5.0 Beta 2 at Microsoft Connect.

The MAP Toolkit 5.0 is an agentless tool designed to simplify and streamline the IT infrastructure planning process across multiple scenarios through network-wide automated discovery and assessments. This Solution Accelerator performs an inventory of heterogeneous server environments and provides you with usage information for servers in the Core CAL Suite and SQL Server, SQL Server 2008 discovery and assessment for consolidation, Windows 2000 Server migration recommendations, and a readiness assessment for the most widely used Microsoft technologies—now including Office 2010.

Is your organization spending valuable resources planning its IT infrastructure? Participate in the MAP Toolkit 5.0 Beta 2. Take an early look at this release, and provide timely feedback to help ensure that the MAP development team builds the tool to best meets your needs.

Next steps:

· Join the MAP Toolkit 5.0 Beta 2. Then tell the MAP team what you think!

· Send your comments to the MAP dev team.

· It's Free! Download MAP 4.0 for planning Windows 7, Windows Server 2008 R2, and Hyper-V deployments.

· Visit the MAP Team blog for more news on the MAP Toolkit and other IT planning Solution Accelerators from Microsoft.

The Beta 2 will run through mid-June. That means now is the time to join the beta program, preview this toolkit, and provide the MAP team with your feedback.

What's new with MAP Toolkit 5.0 Beta 2 (What’s in it for you and SQL Server)?

Software usage tracking for Exchange Server and SQL Server

Right-size your IT environment with MAP Toolkit 5.0 and simplify software license management and compliance processes. MAP 5.0’s new usage tracking feature provides consistent software usage reports for key Microsoft server products: Windows Server, SharePoint Server, System Center Configuration Manager, Exchange Server, and SQL Server. Run updated reports whenever you need to accurately assess current software usage and client access history in your environment. This reduces time and administrative costs for managing your server and client access licenses (CALs) and helps you to streamline the management of your software assets.

SQL Server discovery and assessment for consolidation

MAP 5.0’s new database discovery feature gives you the information you need to optimize your database resources and investments. MAP helps you simplify database administration and provides wide-ranging details of databases and server instances—information you can leverage for consolidation. Use the MAP Toolkit’s proposals to better utilize hardware and database resources, reduce administrative costs and streamline your software licensing needs— all essentials for cost effective IT planning and operations.

Windows 2000 Server migration assessment

As support for Windows 2000 Server ends soon, MAP 5.0’s Migration Assessment feature helps you prepare for migration to Windows Server 2008 R2 by assessing the Windows 2000 Server environment and legacy workloads in the form of proposals and reports. The MAP Toolkit’s actionable recommendations help you to understand the potential business impact of maintaining legacy workloads and the benefits of migrating to the robust Windows Server 2008 R2 environment. With migration to Windows Server 2008 R2, you’ll be able to utilize the increased IT flexibility and efficiency from such technologies as Hyper-V and Remote Desktop Services, as well as tap into power-savings features to decrease TCO.

Keeping up with SQL Server cumulative updates

AaronBertrand — Tue, 20 Apr 2010 13:20:00 GMT

Yesterday, a conversation on twitter reminded me that I haven't been keeping up with posting cumulative updates. I missed these updates for SQL Server 2008 on March 15:

Cumulative Update #7 for SQL Server 2008 SP1 (10.00.2766)

Cumulative Update #10 for SQL Server 2008 RTM (10.00.1835)

And yesterday Glenn Berry (blog | twitter) was the first I know of to blog about Cumulative Update #9 for SQL Server 2005 SP3 (9.00.4294). He also shares some interesting information about changes to the support policy - most importantly, SQL Server 2008 RTM is no longer a "supported service pack," so you can probably expect CU #10 to be the last update for that branch. There is an upside to this: now that R2 is off the books, this must mean that work on SP2 is under way.

All of which made me echo a question asked in the community: "why can't I get notified when new updates are posted?" Someone joked that they should wait for me to tell them about it. Obviously I have failed at that, but there is still hope. While they are much more haphazard and inconsistent than I would expect from a mature product, here are some resources that will let you stay on top of these updates without putting too much thought into it.

Microsoft SQL Server Release Services blog

http://blogs.msdn.com/sqlreleaseservices/default.aspx

Subscribe to this blog's RSS feed. This is currently your #1 source for information about updates to both SQL Server 2005 and 2008. Don't expect them to blog within 5 minutes of a public release, but if it's more than 12 hours, it's an anomaly.

SQLServerCentral.com build lists

SQL Server 2008 Build List

SQL Server 2005 Build List

Steve Jones has been maintaining these lists but, like me, he is not always on top of it (right now both lists are one cumulative update behind).

The official RSS feed for SQL Server 2005 KB articles

http://support.microsoft.com/common/rss.aspx?rssid=2855

When this feed first came out, I really felt like they had answered a big problem with the communication about cumulative updates. If you are still using SQL Server 2005 and keeping it updated, you can subscribe to this feed and just watch for the title to include the text "Cumulative Update" -- note that I posted about this feed once before. The problem is that I am still trying to find equivalent feeds for SQL Server 2008 and SQL Server 2008 R2.

The SQL Server web site

In response to this Connect request, they have tucked some cumulative update information up into the top right corner of this page:

http://msdn2.microsoft.com/en-us/sqlserver/aa336368.aspx

Unfortunately, this page is only worth the amount of effort they put into keeping it current. Right now, it thinks the "latest cumulative update for SQL Server 2008" is CU #6, which was released last summer and has been superceded by a service pack and 4 newer cumulative updates in each branch. The link for the "latest" SQL Server 2005 also points to a CU from last summer. A for effort, E for execution.

Convert binary value to string value

Peso — Wed, 27 Jan 2010 08:41:00 GMT

With SQL Server 2008, we can easily use

DECLARE @bin VARBINARY(MAX)
SET     @bin = 0x5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8

SELECT CONVERT(VARCHAR(MAX), @bin, 2)

But how can we do this in SQL Server 2005?
You can't use a simple CAST or CONVERT, because you get the ASCII representation of the binary values.

So, here is how you can do the conversion with SQL Server 2005 by using XML.

-- Prepare value
DECLARE @bin VARBINARY(MAX)
SET     @bin = 0x5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8

-- Display the results
SELECT @bin AS OriginalValue,
        CAST('' AS XML).value('xs:hexBinary(sql:variable("@bin"))', 'VARCHAR(MAX)') AS ConvertedString

Mis-steps in the publication of Cumulative Updates

AaronBertrand — Wed, 20 Jan 2010 17:31:00 GMT

It used to be very difficult to obtain hotfixes for SQL Server (sometimes even to learn about their existence), and they were often unsupported. They have made extremely great strides in this area, and in general, I find the new procedure much more convenient : just go to the KB article, select the fix(es) you want, and you get an almost immediate e-mail with download links and brief instructions. No longer do I have to raise an issue with customer support and prove to them that I am both affected by the issue and responsible enough to handle the patch correctly. But there are still some holes in the process.

1. Trying to be "smart" about platform

The hotfix download page determines your platform and language and offers you the patch files for your current local environment. While this might be a good idea in some cases (such as client patches), for SQL Server this makes no sense at all. This methodology assumes that I am downloading patches only for my immediate machine, when in reality I am usually downloading for a variety of instances, many of which are not even on the same subnet as my workstation. Add to this the fact that in my experience the code just doesn't work - this is a pure x64 machine, and yet because I am using Firefox, the download page for every cumulative update thus far has limited my view to the x86 files. It is only a minor piece of extra work to get access to the x64 files I'm really after (simply click on the "Show hotfixes for all platforms and languages" link), but ideally I'd like to see a setting where (via a cookie, or LiveID, or something) I could say "always show all platforms" or "always show both x64 and x86 files." Or take away the "smarts" that hide the other platforms from me in the first place - presumably this is just a way to avoid actually documenting the files in sections, so silly people don't download files for the wrong platform.

2. Publication of correct files and filenames

While the screen shot will only highlight the repeating spelling error ("cumlative" vs. "cumulative") that has afflicted the last few cumulative updates, there was a pretty serious mistake in this most recent CU where the SP1 download actually contained the patch for RTM. Now a lot of people are extracting the file, trying to run it against their SP1 instances, and it finds there is nothing to update. Rather than assume a simple case of mistaken identity, many will assume that either the patch or their environment is broken. Right now you'll notice in the screenshot below that the core file has been pulled from the download page, so they are fixing the problem - in fact it has probably been corrected by the time you are reading this. But why was it wrong in the first place? Are the download packages not tested even once before they are published?

3. Being vague about the included files

There are always several files in each CU download, and they are not explained anywhere either on the download page or the original KB page. What is a "SapBi" or "RB2ClickOn" file? Do I need it? Who knows? I really believe that for each CU they need to have some section on the download page describing the use case where you would want to download any of the smaller files individually.

The proof

The proof is, as they say, in the pudding. You can see the highlighted areas in the screenshot from the points above (click to embiggen):

What to do?

Without direct interaction with those involved, I am not sure what else we can do to make corrections to these processes. The feeling of a CU is already inherently "rushed"; then when the files are published clearly without testing or attention to detail, that rushed feeling is exaggerated. I have commented about this problem on Connect and in a previous bog post. I've also stressed the need to fully document CUs before releasing them (but somehow left out the "testing" part). After today's incident, it is obvious there are still some items that have room for improvement.

At least from the outside, they look like relatively simple things to fix. Fixing them would lead to a lot less confusion among the customer base, and a lot more confidence in the general approach and attitude toward releasing updates. A lot of that is just about better communication. For example, while they were fixing this problem, they did not make any announcements whatsoever; they simply pulled the core files from the download page.