Top ten tips for protecting SQLSERVER database

  • Detail

Ten tips for protecting SQL Server Databases

1 Install the latest service pack

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 published security updates

2. Use Microsoft Baseline Security Analyzer (MBSA) to evaluate the security of a server

mbsa is a tool to scan the insecure configurations of a variety of 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 SQL server installation for the following problems:

1) too many sysadmin fixed server role members

2) grant other roles except sysadmin the right to create cmdexec jobs

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 SQL server data directory

7) sa password in plain text is used in the installation file

8) grant too many rights to the guest account

9) run SQL Server on a system that is also a domain controller

10) incorrect configuration of the everyone group, providing access to specific registry keys

11) incorrect configuration of SQL ser operation simple ver service account

12) necessary service packs and security updates are not installed

microsoft provides a free download of MBSA

3. Use windows authentication mode

whenever possible, you should require windows authentication mode for connections to SQL server. It protects SQL server from most Internet tools by restricting connections to Microsoft Windows users and domain user accounts. Moreover, your server will also benefit from windows security enhancements, such as stronger authentication protocols and mandatory password complexity and expiration times. In addition, credential delegation (the ability to bridge credentials between multiple servers) can only be used in Windows authentication mode. On the client side, windows authentication mode no longer requires a stored password. Storing passwords is one of the major vulnerabilities in applications that use standard SQL server login. 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 then click properties

3) on the security tab, in authentication, click windows only

4. Isolating your server and regularly backing up

physical and logical isolation forms the basis of SQL server security. The machine hosting the database should be in a physically protected place, preferably a locked machine room, equipped with flood detection and fire detection/fire protection systems. The database should be installed in the internal security zone of the enterprise. Do not connect directly to the Internet. Back up all data regularly and keep copies in a secure off-site location

5. Assign a robust sa password

sa accounts should always have a robust password, even on servers configured to require windows authentication. This will ensure that no blank or vulnerable SAS will appear when the server is later reconfigured for Mixed Mode authentication

to assign an sa password, follow these steps:

1) expand the server group, and then expand the server

2) expand security, and then click login

3) in the details pane, right-click SA, and then click properties

4) in the password box, enter the new password

6. Restrict the permissions of SQL server services

sql Server 2000 and SQL server agent run as windows services. Each service must be associated with a Windows account from which the security context is derived. SQL Server allows SA logged in users (and sometimes other users) to access operating system features. The system calls made by these operations in the development process of China's new energy materials industry are created by the security of the account with the server process. If the server is broken, these operating system calls may be used to attack other resources, as long as the owned procedures (sql

server service account) can access them. Therefore, it is very important to grant only the necessary permissions to SQL server services

we recommend the following settings:

1) SQL Server engine/mssqlserver

if there are specified instances, they should be named mssql$instancename. Run as a Windows domain user account with general user privileges. Do not run as a local system, local administrator, or domain administrator account

2) SQL server agent service/sqlserveragent

if it is not required in your environment, please disable the service; Otherwise, run as a Windows domain user account with general user rights. Do not run as a local system, local administrator, or domain administrator account

important: if one of the following conditions is true, the SQL server agent will need local windows administrator privileges:

sql server agent uses standard SQL server authentication to connect to SQL Server (not recommended)

sql server agent uses multiple servers to manage the master server (MSX) account, which connects using standard SQL server authentication

sql server agent runs Microsoft ActiveX owned by non sysadmin fixed server role members? Script or cmdexec job

if you need to change the account associated with the SQL Server service, use SQL Server Enterprise Manager. Enterprise manager sets the appropriate permissions for the files and registry keys used by SQL server. Do not use the services of the Microsoft Management Console (in the control panel) to change these accounts, because this requires manual modulation of a large number of registry keys and NTFS file system permissions, as well as Micorsoft Windows user permissions

the change of account information will take effect the next time the service is started. If you need to change the accounts associated with SQL server and SQL server agent, you must use

enterprise manager to change the two services respectively

7. Disable the SQL server port on the firewall

the default installation of SQL server will monitor TCP port 1433 and UDP port 1434. Configure your firewall to filter packets arriving at these ports. Also, other ports associated with the specified instance should be blocked on the firewall

8. Using the most secure file system

ntfs is the most suitable file system for installing SQL server. It is more stable and easier to recover than fat file systems. It also includes security options such as file and directory acls

and file encryption (EFS). During installation, if NTFS is detected, SQL server will set appropriate ACLS on registry keys and files. These permissions should not be changed

through EFS, the database files will be encrypted under the account running SQL server. Only this account can decrypt these files. If you need to change the account under which SQL server is running, you must first decrypt these files under the old account and then re encrypt them under the new account

9. Delete or protect old installation files

sql server installation files may contain plain text or simply encrypted credentials and other sensitive configuration information recorded during installation. The location where these log files are saved depends on the installed version of SQL server. In SQL Server 2000, the following files may be affected: in the default installation: program filesmicrosoft SQL servermssqlinstall folder, and in the specified instance: program filesmicrosoft SQL se install according to the provisions and requirements of the design drawing. G, G and s

in the server mssql$ install folder. If the current system is upgraded from SQL Server 7.0 installation, Then you should also check the following files:%s in the windir% folder and G in the windows temp folder

microsoft has released a free utility, killpwd, which will find and remove these passwords from your system

10. Audit connections to SQL Server

sql server can record event information for system administrator audit. At a minimum, you should log failed SQL Server connection attempts and review this log periodically. 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 then click properties

3) Click fail in the audit level of the security tab

4) it fuses aluminum with lithium, copper, silver and other metals. To make this setting take effect, you must stop and restart the server. (end)

Copyright © 2011 JIN SHI