A couple of weeks ago, I discussed SQL Server 7.0 Service Pack 3 (SP3). If you haven't installed it yet, here are two tips that might prevent a few headaches when you do find time to apply the patch.
The first tip comes from a reader: "Here's a little tidbit that your readers should be aware of when they install SP3. In the early stages of the installation, a dialog window pops up saying that one of the files is attempting to overwrite a read-only file. It's a normal 'Yes, No, Overwrite all' type of window, but it pops up BEHIND the setup screen. Unless you minimize the setup screen to see the warning, you won't see the warning. It looks like the upgrade has frozen. I've upgraded two test servers, and this happened both times."
Another gotcha, having to do with a new security check, might puzzle you if you aren't prepared. Here's some background information about why the gotcha happens. We all know that it's dangerous to leave the sa password set to the default, which is, of course, blank. But I've visited many client sites that have blank sa passwords, so I know firsthand that the practice is disturbingly commonplace. Often, sites protect production servers with a strong sa password, whereas development boxes have a blank sa password to "make it easy for the development team to work." Unfortunately, the developers haven't thought through the implications of what sa can do through the xp_cmdshell command on a SQL Server system running under a Domain Administrator account (a regular practice). Microsoft tries to help us enforce better security through a new check that the SP3 installation performs. The SP3 readme file says, "When Setup connects to SQL Server 7.0 or MSDE 1.0 to run the .sql scripts updating system stored procedures, it displays an Authentication Mode dialog box if it detects that the installation is using mixed mode authentication with a blank password for the sa login. This is to give users a chance to address the potential security issue of running a system with a blank password for the sa login."
This explanation seems reasonable, but the readme file goes on to explain that the Authentication Mode dialog box doesn't default to current settings for the existing SQL Server installation. On computers running Windows 2000 or Windows NT 4.0, the SP3 setup defaults to NT Authentication, which is the new default for SQL Server 2000 installations. However, an unwary administrator might accept this default for a server that was previously set to mixed-mode security, effectively locking out applications and users who depended on mixed-mode security to connect to the SQL Server. Re-enabling the SQL Server for mixed-mode security is easily done through the Security tab in the Server Properties dialog box, but figuring out what happened and why might be bothersome if you haven't read the readme file. As I pointed out 2 weeks ago, it's called a readme file for a reason. Shame on you if this gotcha got you.