It's a similar theme, off by default if it makes sense, minimize surface area.

Recommendations: for new installs, leave stuff off unless you need it.

Minimize connectivity. for upgrades, turn things off if you do not

truly use them.

SQLSAC - Surface Area tool, launch from command line or setup. There's

a SAC command line utility to script out the configuration that you can

apply to other servers.

Service Accounts

BOL says don't use Network Service? Why not? Has more rights than

generally needed. Also a shared account. If penetrated somehow from

another place, control of SQL Server is gained.

Local or domain account is recommended. You can change the password without a shutdown.

Least desirable: Local system

OK: Network Service or Local Service.

Changing the account: Previous advice was go to SQL Service Manager or

Enterprise Manager to change the account. Reason is because large

number of files and folders are ACL'd. Same advice for SQL Server 2005.

Use the tools, not Services applet.

Authentication Mode

Windows is recommended and is the default.

Exceptions are because an application requires its own login. Also DBAs

do not want to have Windows administrators having control over who can

access SQL Server. This is why there are additional features in SQL

Server 2005 for SQL logins. One thing is that the channel from client

to server needs to be encrypted to cover passwords. SQL Server 2005

uses a self-signed certificate to encrypt the password packet to keep

it safe.

Network Connectivity

It's recommended to enable the fewest protocols you need, probably only TCP/IP.

Also default ports should be blocked and changed (1433, 1434)

Endpoints are new entries into SQL Server, but you can secure these to

limited users/roles. You can limit the types of access as well (no ad

hoc, etc.).

DO NOT EXPOSE YOUR SERVER to the Internet. This should be common

knowledge for anyone and it's something you want to do if at all

possible.

System Procedures:

REVOKE execute from permission from XPs and system stored procedures.

Even though public had rights to run a system proc, the procedures had

a security check. In 2005 all were re-examined to be sure rights were

needed, for example sp_help had a security check added. Many XPs

(XP_dirtree, xp_regread, etc) have security checks added.

They are more secure, but removing XPs results in an unsupported configuration.

Password Policy

In previous versions, no validation of SQL auth passwords.

New features added based on OS capabilities. Recommend that

CHECK_POLICY is left on, CHECK_EXPIRATION is left on and MUST_CHANGE

for new logins. Note, policy is only checked when passwords are

changed, not during an upgrade.

If you set MUST_CHANGE, this only works for 2005 clients (.NET 2.0,

Native Client). The reason is they can send a two password packet that

old clients cannot when a change password is required.

Admin privileges

Use when needed. Don't use SA because it's convenient. Also minimize

the number of administrators and provision them explicitly. Don't use

the Windows Administrators group.

Also be sure each administrator has their own account to be sure you can audit and determine who change what.

Vista
User Account Control issues.

Protected Admin - Even if you are an admin, your token does not have

the admin token. A check is made to be sure that you have rights and

want to perform the action.

DB Ownership and Trust

SQL2K SP3 turned off database cross chaining. This ensures that a cross

database query has a security check. If you don't need it, turn it off.

If you do need this, you can trust the dbo of the other database or

sign a stored procedure with a certificate and have the dbo trust the

certificate.

Recommendations are to have distinct owners for each database, not "sa", and only enable trust as needed.

Schemas

It's a namespeace in the container hierarchy

  server -> database -> schema -> object

Can be owned by anyone and can grant permissions at this level. This

was done to isolate applications or groups of objects, separate

administrative grouping from application usage. You can easily change

the user as well.

OK, I'm done. It's too basic a session. I guess if you haven't really

worked with SQL Server, this is a good list of stuff, but I think too

much of this is common knowledge for DBAs. Giving some of the reasons

for changes is good, but for a many of these explanations it sounds

like an excuse or a "ok, we give in". Having a somewhat boring speaker

doesn't help.