Editor's Note: Send your SQL Server questions and comments to SQL Server MVP Brian Moran at firstname.lastname@example.org.
Our custom application uses a SQL Server User ID (UID) and password to connect to SQL Server. In other words, we're using a custom security model that persists logon and password information in tables maintained in SQL Server. Users don't know this UID and can access the database only through the application. We recently installed SQL Server 7.0 Service Pack 3 (SP3), and users can no longer connect to SQL Server using standard security. The only way we can give users access is by letting them access the database through their Windows NT UIDs, which opens the whole database to users accessing the database outside the security and business rules that are encapsulated within the application code.
You may be running into a change of behavior that the installation of SP3 introduced and that can be very puzzling if you haven't carefully read the entire readme.txt file. Before I describe the change that SP3 probably made, let me provide some background about why SP3 is making the change in the first place.
We all know that it's dangerous to leave the systems administrator (sa) password set to the default, which is blank. Unfortunately, I've visited many client sites that still have a blank sa password, so I know firsthand that the practice is disturbingly commonplace. Often, production servers are protected with a strong sa password, while development boxes have a blank sa password to "make it easy for the development team to work." Administrators at these sites haven't considered what an sa can do through the xp_cmdshell command if a SQL Server is running under a DOMAIN ADMINISTRATOR account. To help SQL Server developers implement tighter security, Microsoft designed SP3 to perform a new security check, as the following quote from the readme.txt file explains: "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."
The change that SP3 introduces seems perfectly reasonable, but readme.txt goes on to explain that the Authentication Mode dialog box doesn't default to the existing SQL Server installation's current settings. On computers running Windows 2000 or NT 4.0, SP3 setup defaults to NT authentication (which is the new default for SQL Server 2000 installations). An unwary administrator might accept this default for a server that was previously set to mixed-mode security, effectively locking out applications and users that might have depended on mixed-mode security to connect to SQL Server. It sounds as if this might have happened to you. In Enterprise Manager, use the Security tab of the Server Properties dialog box to re-enable the SQL Server for mixed-mode security.
I have an instance of SQL Server 7.0 that was installed using a sort order different from the SQL Server 7.0 default. Now the instance is different from every other SQL Server we're running, which has been causing some annoying problems. How can I change the SQL Server sort order?
Unfortunately, I don't have an easy answer if you're using SQL Server 7.0. Under SQL Server 7.0, the sort order you choose during installation is set for the entire server, and you can't change it for an individual database. You can't change the sort order after SQL Server has been installed without rebuilding the Master database and choosing a new sort order, unless you simply start over with a fresh installation. Both approaches require you to export your data, perhaps to flat files, and reimport the data into new databases after you rebuild the server or reinstall it with a new sort order.
Alternatively, you could upgrade to SQL Server 2000, which supports multiple collations (a fancy word for sort orders) within the same server. In fact, different columns within the same table can have different collations.
SQL Server 2000 doesn't provide a command that lets you globally change the collations of a database's existing columns, but you can change collations on a column-by-column basis by using the ALTER TABLE command. You'll find plenty of information about this topic if you type the word collations into the Index tab of the SQL Server Books Online (BOL) search mechanism.