Security
- Kerberos support (delegation). "Account is trusted for delegation" must be set
for the SQL Server service account. Server must be set to "Computer is trusted for
delegation" option. "Account is sensitive and cannot be delegated" must not
be set for any users connecting.
- Kerberos. SQL Server must have a "Service Principal Name (SPN) assigned by a
Windows 2000 domain admin. Can be set using the RK tool SETSPN (or if use localsystem then
will register at service startup).
- Delegation. All accounts must be in the same domain or trusted tree. Can’t use
dynamic tcp-ip ports for named-instance as tcp port number is used in the SPN.
- C2 compliant
- Much stronger encryption (RC4 based) for encrypted triggers, sp’s etc.
- Level of auditing configurable. With 7.0 SP2 it is either on or off. When fully on the
overhead on the system is a lot.
- Default security is integrated
- Check box on setup to stop you having blank password.
- Auditing. Shutdown SQL Server IMMEDIATELY when audit file full. SQL Profiler can be used
to read Audit file. No separate Audit UI (will have with Yukon). Sp_configure ‘c2
audit mode’,1. Finer level granularity available via auto-started traces.
- C2 audit audits everything, 200MB file roll-over size, files called
audit_yyyymmddhhmmss_x
- Must shutdown and restart SQL for C2 audit mode
- Sockets is default net-lib for all installs now.
- Profiler now broken into SQL Trace (server bits) and Profiler (client bits)
- Install locks down NTFS directories and registry entries so only service accounts and
local admins can get to them
- All net-libs can encrypt via SSL/TLS. Must have a server certificate and the client must
trust that certificate server. Can be forced to encrypt at client or server. If turned on
at the server then all attempts (even local ones) must have a certificate or you
won’t connect.
- BulkAdmin now a new fixed server role. Need insert rights to table you’re inserting
to, but bulk insert no longer needs sysadmn.
- Security admin can now change passwords (except for SysAdmin members which it
can’t).
- ServerAdmin can now run sp_addmessage, sp_dropmessage, sp_altermessage
- Crypto Api now used for strong encryption of objects/passwords
- Suid column removed from all system tables (sysdatabases, syslogins, sysremotelogins,
sysusers, sysprocesses). Sysalternates completely removed. Suser_id(), suser_name() now
removed.
- Backup media now password protected
Networking
- New combined single net-lib. Uses winsock and ssl for encryption.
- MDAC 2.6 change for doing prepare/unprepared on exec/prepare reduces N+2 network
round-trips to N. Must use bind before exec to see the improvement.
- MetaData cached on client and only re-sent if schema changes. The more columns the more
saving. Saves cpu on processing. Reduces cpu cost of select with > 50 columns by 10%.
No app changes necessary.
- Sockets netlib supports WSD (Winsock Direct) on DataCentre server
- Servernet and Giganet VI netlibs supported – less cpu than going through tcp-ip.
Good for application to SQL server inter-connects. Reduced kernel time by 1/3rd
in SAP workload – 20% increase in users.
SQL Server 2000 Preview 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
