SQL Server Security Considerations

[Previous] [Next]

The material in this section covers security issues specific to Microsoft SQL Server.

Check the SQL Server Service Account

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

  • Full Control permissions on the SQL Server directory (by default \Program Files\Microsoft SQL Server in SQL Server 2000, \MSSQL7 in SQL Server 7.0)
  • The Log In As A Service right (this right is added automatically by the Service Control Manager in Windows 2000).
  • Full Control over the following Registry keys:
    • For a default instance of SQL Server 2000, or for SQL Server 7.0, HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer.
    • For a named instance of SQL Server 2000, HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server
    • HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSSQLServer, and HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\SQLServerAgent.
    • For a named instance of SQL Server 2000, HKEY_LOCAL_MACHINE\System\CurrentControlset\Services\MSSQL$Instancename and SQLAgent$Instancename HKEY_LOCAL_MACHINE\Software\Microsoft\Windows NT\CurrentVersion\Perflib.

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

Use the NTFS File System

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.

Run in Integrated Mode

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.

Secure Your Backups

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.



Designing Secure Web-Based Applications for Microsoft Windows 2000 with CDROM
Designing Secure Web-Based Applications for Microsoft Windows 2000 with CDROM
ISBN: N/A
EAN: N/A
Year: 1999
Pages: 138

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net