SQL Server 2000 database security tips

1. Use a robust and secure password policy
The passwords of many database accounts are too simple, which is the same as the system password. Do not write the sa account password in an application or script. Robust passwords are the first step to security! When installing SQL Server in hybrid mode, you need to enter the sa password unless you confirm that you must use a blank password. This is better than the previous version. At the same time, develop a good habit of regularly changing passwords. The database administrator should regularly check whether there are accounts that do not meet the password requirements.
For example, Use the following SQL statement: Use masterSelect name, Password from syslogins where password is null
To assign a sa password, follow these steps: 1) Expand the server group and then expand the server. 2) expand security, and then click log on. 3) In the details pane, right-click SA and click Properties. 4) In the Password box, enter the new password.
2. Use a secure account policy and Windows Authentication Mode
Because SQL Server cannot change the sa user name or delete this super user, we must provide the strongest protection for this account, including using a very strong password, it is recommended that you do not use the sa account in database applications. SAS is used only when no other method is available to log on to the SQL Server instance (for example, when other system administrators are unavailable or forget the password. It is recommended that the database administrator create a new super user with the same permissions as sa to manage the database. Secure account policies also include preventing account flooding with administrator permissions.
SQL Server authentication modes include Windows Authentication and Hybrid Authentication. Whenever possible, you should require the Windows Authentication Mode for the connection to the SQL Server.
Windows Authentication mode is superior to hybrid mode for the following reasons:
1) It protects SQL Server from most Internet tools by limiting connections to Microsoft Windows users and domain user accounts.
2) The server will benefit from Windows security enhancement mechanisms, such as stronger authentication protocols and forced Password Complexity and expiration time.
3) use Windows Authentication and do not need to store the password in the connection string. Password Storage is one of the major vulnerabilities in applications that use standard SQL Server to log on.
4) Windows authentication means that you only need to store the password in one place.
To install Windows Authentication Mode in Enterprise Manager of SQL Server, follow these steps: 1) Expand the Server group. 2) Right-click the server and click Properties. 3) on the Security tab, click Windows only.
3. Disable the SQL Server port on the firewall
By default, SQL Server monitors TCP port 1433 and UDP port 1434. Configure your firewall to filter out the packets that reach these ports. In addition, the firewall should also block other ports associated with the specified instance.
4. Review the connection to SQL Server
SQL Server can record event information for System Administrator review. At least you should record failed SQL Server connection attempts and regularly view this log. If possible, do not save these logs and data files on the same hard disk.
To Audit Failed Connections in Enterprise Manager of SQL Server, follow these steps: 1) Expand the Server group. 2) Right-click the server and click Properties. 3) In the Security tab's audit level, click fail. 4) To make this configuration take effect, you must stop and restart the server.
5. Manage Extended Stored Procedures
Perform major operations on the stored procedure, and exercise caution when calling the account to extend the stored procedure. In fact, most applications do not use much system stored procedures. SQL Server's multi-system stored procedures are only used to meet the needs of users. Therefore, delete unnecessary stored procedures, because the stored procedures of some systems can be easily exploited to escalate permissions or destroy them. If you do not need to extend the Stored Procedure xp_mongoshell, remove it. Use this SQL statement: use master sp_dropextendedproc 'xp _ export shell'
Xp_mongoshell is the best way to enter the operating system and a large backdoor left by the database to the operating system. If you need this stored procedure, use this statement to restore it. Sp_addextendedproc 'xp _ external shell', 'xp sql70. dll
If you do not need to discard the OLE Automatic stored procedure (which may cause some features in the manager to be unavailable), these procedures include: Sp_OACreate Sp_OADestroy Sp_OAGetErrorInfo Sp_OAGetPropertySp_OAMethod Sp_OASetProperty Sp_OAStop
Remove unnecessary registry access stored procedures. The Registry Stored Procedures can even read the operating system administrator password as follows: Xp_regaddmultistring Xp_regdeletekey Xp_regdeletevalue Xp_regenumvalues Xp_regread prepare Xp_regwrite
There are also some other extended stored procedures that you 'd better check. When processing the stored procedure, check to avoid damage to the database or application.
6. Use views and storage programs to assign users the right to access data
Use views and stored programs to assign users the right to access data, rather than asking users to write special query statements that directly access tables. In this way, you do not need to allocate access rights to users in the table. Views and stored programs can also restrict the data to be viewed. For example, if your employee table contains confidential salary information, you can create a view that skips the Salary Bar.
7. Use the safest File System
NTFS is the most suitable File System for SQL Server installation. It is more stable and easier to restore than the FAT file system. It also includes some security options, such as file and directory ACLs and file encryption (EFS ). If NTFS is detected during installation, SQL Server sets an appropriate ACL on the registry key and file. You should not change these permissions.
Through EFS, database files are encrypted under the Account identity that runs SQL Server. Only this account can decrypt these files. If you need to change the account for running SQL Server, you must first decrypt these files under the old account and then re-encrypt them under the new account.
8. Install the upgrade package
To improve Server security, the most effective method is to upgrade to SQL Server 2000 Service Pack 3a (SP3a ). In addition, you should install all released security updates.
9. Use Microsoft Baseline Security Analyzer (MBSA) to evaluate server security
MBSA is a tool that scans insecure configurations of multiple Microsoft products, including SQL Server and Microsoft SQL Server 2000 Desktop Engine (MSDE 2000 ). It can run locally or through the network. This tool detects the installation of SQL Server against the following issues: 1) Too many sysadmin fixed Server role members. 2) grant other roles other than sysadmin the right to create a CmdExec job. 3) Empty or simple password. 4) Fragile Authentication mode. 5) grant too many rights to the Administrator group. 6) incorrect access control table (ACL) in the SQL Server data directory ). 7) use the plain text sa password in the installation file. 8) grant excessive permissions to the guest account. 9) run SQL Server in a system that is also a domain controller. 10) if the owner (Everyone) group is incorrectly configured, access to the specific registry key is provided. 11) the SQL Server service account is incorrectly configured. 12) do not install necessary service packages and security updates.
Microsoft provides free download of MBSA.
10. other security policies
In addition, some tips are worth noting when installing SQL Server.
Use TCP/IP as the network library of SQL Server. This is a library recommended by Microsoft and has been tested. If the server is connected to the network, the use of non-standard ports will be damaged by some people with ulterior motives.
Use a low-level account to run SQL Server, rather than a management account. This protects the system from crashes.
Do not allow guests without security permission to access any database including security data.
Protect the database in a "locked room ". Remember, a lot of harassment is from internal sources.