The material in this section covers security issues specific to Microsoft SQL Server.
Don't use the LocalSystem account in Windows 2000 because it is a local administrator account and has control over all system resources. In addition, don't use an account that's a member of the local administrator's group; use an ordinary user account.
The user account will need
In addition, these permissions in Table F-4 are required for certain functionality to work.
Table F-4. Permissions required for certain SQL Server tasks.
Service | Permission | Example Functionality |
---|---|---|
SQL Server | Network write privileges | Write to a mail slot using xp_sendmail |
SQL Server | Act as part of operating system and replace process level token | Run xp_cmdshell for a user other than a SQL Server administrator |
SQL Server Agent | Member of the Administrators local group | Create CmdExec and ActiveScript jobs belonging to someone other than a SQL Server administrator Use the auto-restart feature Use run-when-idle jobs |
It's very important that you use the NTFS file system to take full advantage of the security features built into Windows 2000. Also, check that the default ACLs for the Everyone group are not set to Full Control. The only ACLs that should be set to Full Control are the service account under which SQL Server is running.
You might also enable the Encrypted File System (EFS) on your data files. If SQL Server is not running, someone could theoretically grab the data files and then attach them to another instance of SQL Server. If you use EFS, you can encrypt the data files and protect their contents when SQL Server isn't running.
We know it's redundant but wanted to bring it up one more time. The most secure SQL Server is one using Integrated security with Windows 2000. So, set the option to run in Integrated mode.
Don't forget to change the "sa" password before you set yourself in Integrated security mode. All it takes to change back to mixed is to modify the Registry key we mentioned earlier and restart the server. You don't want someone to be able to do this and then log on as the system administrator.
SQL Server 2000 introduces the ability to put a password on a SQL Server backup. You should take advantage of this feature. Additionally, you should treat your backups as you treat your real server-secure the tapes, keep them away from casual users, and have a full accounting for every one of them.