SQL Server Security Tips

Greg Robidoux, a Microsoft SQL Server security expert, is also the chairman of the SQL Server database administrator Special Interest Group PASS DBA SIG.

"It usually takes time to make the application run, and security issues are not even discussed," Robidoux said. "Database administrators need to pay close attention and say that security is indeed a very important issue ."

1. Do not use the system administrator account

Using the system administrator account will mask the identity of the actual user accessing SQL Server. It is more difficult to track who has changed or who has accessed the Server. In addition, if the password is weak or there is no password at all, this is the first place that hackers are concerned about when they want to access the server. Use the role of the system administrator to acknowledge the system management permissions of the database. Create a strong system administrator password and lock the password in an emergency.

2. Only grant the required permissions at work to the user

When the time needed to study the problem is limited, the first thing to do is to grant users more access permissions. This usually solves the problem, but it also opens a security vulnerability. Efforts should be made to standardize User-Defined database roles and grant those roles sufficient permissions to allow them to log on directly. Also, never try to use server roles as a regular user. If you need to confirm a separate additional permission, ensure that the reason is fully recorded and includes everything you have done so that access can be declared invalid at a given time ), you can also scroll these changes to the User-Defined database role.

3. Delete the BUILTINAdministrators Group

BUILTINAdministrators gives everyone local management permissions on a server and has full control over the database. This account should be deleted from SQL Server to better control who accessed the database. The database administrators create a new domain group, add the database administrators to the group, and add the group to the database. This new group can have full permissions to access the system administrator role. Then, you should delete the BUILTINAdministrators group. In this way, you can add or delete personnel in the Group to better control those who have the permission to manage and access the database system.

4. Delete the GUEST account

The GUEST Account provides database user access permissions for those who have not logged on to the database. A user who does not have the database permission will be granted the permission of the GUEST account. Delete the GUEST account from all your databases. The only inside and outside is Master and TempDB, because the accounts in these two databases cannot be deleted.

5. Disable or disable any unnecessary access.

Many services are bundled with Windows and other applications. Running additional services on the database server will cause additional vulnerabilities in the database. The Server should only be used for SQL Server. This allows you to run only the least services required by Windows and the services required by SQL Server. When a security warning is published, the read range should be limited, so you will not feel forced to read all the content, or, worse, ignore all the content.

6. Avoid dynamic SQL

From a security perspective, dynamic SQL provides an opportunity for SQL injection. SQL Injection allows hackers to embed additional SQL statements in user-run statements. When the code is dynamically compiled, any additional statements embedded in the application or stored procedure may cause damage to the system. Predefine the SQL statements to be run in the system, instead of dynamically constructing statements embedded in SQL and stored procedures. In addition, ensure that the transmitted data is valid before the statement is executed.

7. delete unused XP

SQL Server is bound with some extended stored procedures to access information other than SQL Server. The reason is to help SQL Server Management, but unfortunately, these processes also bring security risks. Review installed XP and delete those XP that have never been used.
8. Use stored procedures to operate data

Using Stored Procedures to operate data allows you to control how data is updated, deleted, and inserted. If you have the permission to directly access the table, you give up the permission to change the data and create an environment in the case of a large number of updates and executions. Create a stored procedure for all your data updates, inserts, and deletes. If the stored procedure is well written, in addition to protecting your data security, it is also good for improving the global system.

9. Check Logon

Do you know who is accessing your database or, further, who is attempting to access your database? It is right to review the login. A simple measure will show you who is trying to access your database server. You should minimize the security settings to capture failed logins for all your database servers. This can be easily done through the Enterprise Manager on the Security page of the server Properties window.

10. Protect Database Backup

Local backup files are stored in plain text, so anyone who can access one of your backup files can use a text editor to open the file and read the data. Data is not as easy to read in all circumstances, but the stored procedure is as clear as it is during the day. Even if you store the file again with a password, the file is still readable. It only means that you need a password for re-storage. Make sure that the disk to be written to the backup cannot be obtained by anyone except the database administrator and your tape backup administrator. If possible, use a third-party tool to encrypt the backup. Finally, make sure that you have good practices for transferring and defending backup files.

(