SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL Server 2000 SP 3: What's New in Security

By Brian Kelley, (first published: 2003/06/05)

SQL Server 2000 SP 3: What's New in Security

Service Pack 3 for SQL Server 2000 is huge. There are significant changes in it and they apply to all three products: the core SQL Server, MSDE, and Analysis Services. In the core SQL Server service pack are a whole host of new security options from cross-database ownership chaining to changes to multi-server administration (Windows authentication is now possible) to stored procedure changes like with sp_changedbowner. In addition to the security changes, improvements have been made to debugging, distributed queries, and replication. Because there are so many changes, this article will only focus on the security changes with the exception of those related to replication, because I'll cover all the replication changes in a forthcoming article. I'll also be following up this article with another one on some of the functionality changes in the core SQL Server not related to replication (improved debugging and linked server functionality).

The Install

When I first started up the SP3 install, I knew I was in a different world. This service pack prompts you to set some things that are brand new. It even checks to see if your SA password is blank! The install begins with the standard prompts (the licensing agreement, which instance to upgrade, what account do you want to use to connect to SQL Server, etc.) that come with all the SQL Server service packs but then follows with a few that require your immediate action.

Left the SA Password Blank?

The Readme.htm file indicates that if you have a SQL Server with a blank SA password, you'll be prompted to set it, even if you have the SQL Server set to Windows authentication only. The reason for this is simple: if an attacker can access and modify the registry, it's a simple matter to toggle the server to Mixed Mode, at which point the SA account is now insecure. So just to see what would happen, I installed SP3 on a test instance with a blank SA password. Sure enough, I was greeted by the dialog box shown in Figure 1.

Figure 1: Prompted to set SA password

This is a good thing. The first two SQL Server worms that came out and hounded us worked by connecting via port 1433 (the default) and then trying to logon using the SA account with a blank password. If you get this prompt, set the password! That goes even if you are using Windows Authentication. If you don't see this warning, it means you have a password set on your SA account.

Cross-Database Ownership Chains (No Full Text)

The next dialog box is for cross-database ownership chains. (Figure 2).

Figure 2: Enable Cross-Database Ownership Chains

If you haven't heard about cross-database ownership chains, I'll talk about that shortly. If you know what they are and plan on using them on the particular SQL Server across all databases, check the box. There are some important security considerations that have to be made when enabling cross-database ownership chains, so if you aren't sure or have no plans to use cross-database ownership chains or don't plan on using them on all databases, leave the check box blank. If at some later point you want to enable them, you'll be able to do so.

Cross-Database Ownership Chains (with Full Text)

The only reason I differentiate between having and not having Full Text installed for SQL Server is because the dialog boxes change. As Figure 3 shows, not only does the setup program prompt whether or not you want to use cross-database ownership chains, but it also has a checkbox for upgrading the Full-Text Service. If you don't check the box beside Upgrade Microsoft Search and apply SQL Server 2000 SP3 (required) the Continue button will be grayed out. 

Figure 3: Cross-Database Ownership Chains and a Full-Text upgrade

Service Pack 3 requires that you upgrade your Full Text Service. If you don't want to, you can't apply Service Pack 3. Because Service Pack 3 upgrades the Full Text Service, all full-text catalogs are rebuilt during the install process. This is a full rebuild and will involve all the resource usage you'd normally expect from such a process. So if you have full-text catalogs, expect the rebuild to hammer your server until this process completes.

One thing the Readme points out is that users may get back no results or impartial ones from Full-Text until the rebuild completes. Microsoft has some suggested workarounds in the following Knowledge Base article (327217):

INF: Installing SQL Server 2000 Service Pack 3 Rebuilds All Full-Text Catalogs

 The article also covers some of the errors you might see in the Application Event Log due to the upgrade and rebuild of the full-text catalogs. Basically, these errors indicate that Microsoft Search has started, it's detected corruption in the indexes (because they were created by an earlier version of Microsoft Search), and that it has completed the rebuild of the catalogs. If you rely heavily on Full-Text in your environment, be sure to review the article. 

Error Reporting Feature

The next dialog box (Figure 4) you'll see is one where you can send error reports back to Microsoft. If you want Microsoft to receive such a report, check the box. Now I'll readily admit that I'm on the paranoid side so I leave the box unchecked. I understand its purpose, to allow Microsoft to collect what's causing errors and what's running at the time, but basically I don't want any of my systems communicating with others unless I've authorized it. Your mileage may vary. But since we're talking about talking to a downstream system that's not under your direct control, I've included it in with security. Like I said, I'm paranoid when it comes to that particular subject.

Figure 4: Send Reports on Fatal Errors to Microsoft

One nice thing that I saw in the dialog box, though, is a link to information on how to setup my own Corporate Error Reporting server (http://oca.microsoft.com/cerintro.asp). Basically, you can redirect the error reports to a file share and then choose later what reports you want to send off. If you decide not to go with Error reporting, keep in mind you can always turn it on. The same is true if you want to turn it off. 

Security Changes

There are quite a few significant security changes in Service Pack 3, some of them changing functionality quite a bit. I'll go down the list in the same order as they are presented in the Readme.

System Stored Procedure: sp_change_users_login

The system stored procedure sp_change_users_login is used to try and reconcile orphaned users in a database. One of the options is to run the stored procedure with @Action = 'Auto_Fix' though this is generally not recommended. With this option, if SQL Server can't find the login to match the user, it'll create it. Previously, these logins were created without a password. You'd have to go in manually and set the passwords. Now you can define a password and any login added by this stored procedure will automatically be set to that password. This ensures you don't create new accounts without passwords. The syntax is:

[EXEC] sp_change_users_login


Microsoft calls this "Ad hoc access to OLE DB Providers" and what these two commands allow for is a user to create a temporary linked server connection (for the query only) using OLE DB. An administrator doesn't have to defined a linked server connection prior to the query being executed. The way SQL Server knows whether or not to allow these ad hoc connections is by querying for the DisallowAdHocAccess value under the following registry key:


or for a named instance:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\<Instance Name>\Providers\MSDASQL

Previously, if the key didn't exist or if it was set to 0, the behavior was to allow the ad hoc connection. However, with Service Pack 3 this has been tightened down. Now if SQL Server doesn't find the registry key, it assumes ad hoc access has been disallowed. For more information on how to use DisallowAdHocAccess, see the following Microsoft Knowledge Base article (327489):

HOW TO:  Use the DisallowAdHocAccess Setting to Control Access to Linked Servers

Cross-Database Ownership Chaining

This one is a big security concern and Microsoft even makes such warnings in the updated Books Online. Here's why. Prior to SP 3, ownership chains worked on the user accounts within a database. There was no way to cross from one database to another and maintain the ownership chain. Cross-database ownership chains still check to see if the owner is the same from object to object, but instead of relying on the user mapping which is at the database level, it uses the login of the owner, which is at the server level. I said before you can turn cross-database ownership chains off and on and you can, at two levels. The first is server-wide. To do this, go to the Security tab of the Server Properties in Enterprise Manager (Figure 5).

Figure 5: Ownership Chaining at the Server Level

Alternately, you can use the sp_configure command to set cross-database ownership chains. For instance:

EXEC sp_configure 'Cross DB Ownership Chaining', 1

If you don't have cross-database ownership chaining turned on server-wide, you can turn it on for individual databases. You can set it under the Options tab of the Database Properties dialog box (Figure 6) through Enterprise Manager.

Figure 6: Ownership Chaining at the Database Level

Using T-SQL, you can use sp_dboption to set whether or not a database allows cross-database ownership chaining. Here's an example where I set the pubs database to use cross-database ownership chaining:

EXEC sp_dboption 'pubs', 'db chaining', 'true'

You can turn cross-database ownership chaining on for every database but master and model. You cannot turn it off (and it's set automatically) on msdb. These are rules built into the sp_dboption stored procedure now. Part of the reason master and model is blocked is because of security concerns. Since cross-database ownership chaining means SQL Server doesn't perform a permissions check when crossing database lines if the owner is the same on all objects (or more accurately they have the same login as the owner), this means someone could use cross-database ownership chaining to get at information they wouldn't normally be able to access.

Let me give an example. I have a user database, MyDatabase, which allows cross-database ownership chaining. I have another database, Private, which also allows cross-database ownership chaining. Let's say the objects in Private are owned by dbo (which maps to the SA login). I have a user who is a db_owner of MyDatabase but who is mapped to a user in Private, but is severely restricted in what he can see or do. There is a particular table, Secrets, this user wants to look into. All the user has to do is create a view (I'll call it ViewSecrets) that does a

SELECT * FROM Private.dbo.Secrets

and make that view owned by dbo. Since both objects map back to SA as the owner and both databases are set to use cross-database ownership chaining, SQL Server won't check the permissions when this user queries against ViewSecrets and it crosses over to the Private database. The user has access to a table he shouldn't have! This is why Microsoft recommends you only use cross-database ownership chaining on a server where all databases require its use.

There is one saving grace with cross-database ownership chaining, and that is the login executing the query requiring cross-database ownership chaining must have a mapped user in all referred databases, even if it defaults to the guest user. So if the user mentioned in my example didn't have a mapped user in the Private database and I didn't have the guest user enabled, SQL Server will return an error code on the view creation (Figure 7):

Figure 7: Not a Valid User

So if a particular user doesn't have a mapped user into the database, cross-database ownership chaining won't work.

System Stored Procedure: sp_changedbowner

Prior to SP3, both a member of the sysadmin fixed server role and the current database owner could execute the sp_changedbowner statement. However, as of SP 3 this stored procedure has been tightened down to where only a member of the sysadmin role can execute the query. If the current database owner tries to do so, SQL Server won't permit it. In Figure 8 I've queried against sysdatabases to show that MyUser is the owner of MyDatabase but even with this, SQL Server still says MyUser doesn't have sufficient permissions to change the object ownership.

Figure 8: Can't Change DB Ownership

If you have personnel assigned to the db_creator server role creating databases for users then reassigning the database ownership, you'll have to change your procedures. Only sysadmins can reassign ownership.

Multi-Server Administration

Prior to Service Pack 3 if you wanted to use multi-server administration, you had to setup your SQL Servers in Mixed Mode. This is because the connecting piece was all based on a SQL Server login. With Service Pack 3, the connecting login can be a Windows authenticated login. This means that if you have SQL Servers in your environment that are set for Windows authentication only, you can now use multi-server administration to manage SQL Agent jobs on those servers.

With the addition of Windows authentication to multi-server administration comes a new extended stored procedure: xp_sqlagent_msx_account. The syntax of xp_sqlagent_msx_account is:

[EXEC] xp_sqlagent_msx_account
    { N'SET' | N'GET' | N'DEL',

The <domain> parameter is reserved for future use. If you want multi-server administration to run under the context of the SQL Server Agent service account, leave all of the parameters after N'SET' blank. For instance:

-- For Windows Authentication
EXEC xp_sqlagent_msx_account

To use a SQL Server account, specify the user and password. For instance:

-- For SQL Server Authentication
EXEC xp_sqlagent_msx_account

When you change the MSX account, you'll need to stop and restart SQL Server Agent. That's all there is to it. Otherwise, Multi-Server Administration works as it always has with one key exception: Multi-Server Administration is not backward compatible with target servers running a version of SQL Server prior to SQL Server 2000 SP 3 (this include SQL Server 7 target servers).

Because of this, Microsoft recommends upgrading all of the target servers first and then prepping the master server by ensuring the account you'll be using for multi-server administration has the appropriate rights to function in such a manner. For Windows authentication:

EXEC sp_grantlogin 'MyDomain\MyUser'

USE msdb

EXEC sp_adduser 'MyDomain\MyUser', MyDomain\MyUser', 'TargetServersRole'

 and for SQL Server authentication:

EXEC sp_addlogin 'MyLogin', 'MyPassword', 'msdb'

USE msdb

EXEC sp_adduser 'MyLogin', 'MyLogin', 'TargetServersRole'

If you follow the procedure outlined in the Readme (make sure the account is added properly on the Master server and upgrading all of the target servers before upgrading the Master server), you should be okay. Keep in mind that backward compatibility break, though. Either all of your target servers will have to be upgradable to SQL Server 2000 SP 3 or you'll have to leave the master server in a version prior to SP 3. If it's the latter, you'll still be unable to use Windows authentication for the MSX account.

Job Owner Verification for Writing Log Files

In Service Pack 3, SQL Server Agent now verifies whether or not the job owner for a particular SQL Server Agent job has the proper permissions to write a log file. If it does not, the job still succeeds, but no log file is written. If the job owner does have rights and a log file is called for, the log file will be written. In either case, all errors should make it to the SQL Server Agent Error Log. There are three scenarios SQL Server Agent looks at:

  • If the job owner is a member of the sysadmin role, then the job output can automatically be written and it will be.
  • If the job owner is a Windows user, SQL Server tests to see if the user has rights to append or overwrite the log file in the location specified. If it can, the log file will be written.
  • If the job owner is a SQL Server user, SQL Server checks to see if the SQL Server Agent proxy account has rights. If it doesn't or there is no proxy account set, no log file is written.

The log file will still be written using the SQL Server Agent credentials, but the security check occurs so that SQL Server Agent will write the log files only when the job owner has the right to do so.

DTS Packages Cannot Be Saved to Meta Data Services by Default

Prior to SP 3, you could write a DTS package to SQL Server, to a structured storage file, to a Visual Basic file, and to Meta Data Services. However, with SP 3 the save to Meta Data Services is disabled by default, though you can turn it on. If you want to activate it, right-click on Data Transformation Services and choose Properties from the pop-up menu. In the Package Properties (I think it's misnamed), you can turn on saving to Meta Data Services (Figure 9).

Figure 9: Enabling Save to Meta Data Services

The reason SP 3 disables the save is because a save to Meta Data Services isn't considered secure. In fact, if you decide to turn on the option to save to this location, SQL Server will prompt you to make sure you are aware that Meta Data Services isn't considered secure (Figure 10).

Figure 10: Security Warning for Meta Data Services

I don't typically save to Meta Data Services, but if you have a need to, be aware that you'll need to activate the ability to save to that location once you apply Service Pack 3.

Error Reporting

I mentioned that if you need to adjust your error reporting you could do so after the service pack installation. In Enterprise Manager you can toggle error reporting by viewing the Server Properties. The General tab has a checkbox for Error Reporting (Figure 11).

Figure 11: Enable Error Reporting

The value for Error Reporting is stored in the Registry in the EnableErrorReporting value for the default instance at:


and for named instances:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Named Instance>\SETUP


Service Pack 3 has a lot of security changes built in; it's truly a phenomenal list. Some of the new security features, such as cross-database ownership chains and the ability to use Windows authentication for Multi-Server Administration come with some "gotchas" that you really need to understand fully before implementing in your environment. Hopefully I've covered all of them in enough detail to give you a good fundamental understanding of what's going on with this new service pack. With that said, nothing beats getting it in a development environment and testing each option out fully. I know I spent several hours testing cross-database ownership chains when the first beta build came out. I repeated my testing when the public version of Service Pack 3 came out just to be sure nothing had changed. Get your hands on it and play. That's the best advice anyone can give.


Total article views: 29978 | Views in the last 30 days: 6
Related Articles

Ownership Chaining

Security in SQL Server is not too complex, following a fairly simple framework for allowing and prev...


Stairway to SQL Server Security Level 7: Security Across Databases with Cross-Database Ownership Chaining

Sometimes you need to reach outside a database and access data and objects from multiple databases, ...


SQL Server Database Ownership: survey results & recommendations

 SQL Server Datenbankbesitz: Umfrageergebnisse und Empfehlungen   (en) You may remember the su...


Database ownership and TRUSTWORTHY

Database ownership is an old topic for SQL Server pro's. Check this simple lab to learn the risks yo...

service packs    
sql server 7