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

    @Action,

    @UserNamePattern,

    @LoginName,

    @Password

OPENDATASOURCE and OPENROWSET Disabled By Default

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:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\MSDASQL

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

RECONFIGURE

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',

    N'<domain>',

    N'<user>',

    N'<password>'

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

    N'SET',

    N'',

    N'',

    N''

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

-- For SQL Server Authentication

EXEC xp_sqlagent_msx_account

    N'SET',

    N'',

    N'MyMSXAccount',

    N'MyPassword'

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'

GO

USE msdb

GO

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

GO

 and for SQL Server authentication:

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

GO

USE msdb

GO

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

GO

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:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\SETUP

and for named instances:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Named

Instance>\SETUP

Summary

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.

Downloads:

• SQL Server

  2000 SP3

• Updated

  Books Online (SP3) - The Service Pack does not upgrade the BOL. You'll

  have to do this manually.