SQL Server security best practices

Compiled by:

Shri S.B.Singh, 
SIO - Uttar Pradesh
Email sio[at]up[dot]nic[dot]in

Security is an important feature which is to kept in mind while database design. Most of the people ignore this fact and always design their database with default declarations provided by the software vendor. The following listed points will give a brief idea about the security features that are to be kept in mind while designing a database. 

Once after successful Database design, the point that comes into picture is installing this database at user organisations. People mainly tend to worry about external attacks and hackers, ignoring the fact that the attack can also be an internal one. It is essential to implement security, first at organisation level, i.e. make sure, that right people have access to right data. Chalk out a security plan to identify which users in the organisation can see which data and perform which activities on the data.

After taking care of these facts, now comes the important security features which as a database designer everyone should know. The following are the points indented towards SQL Server over Windows, where as the same can be implemented accordingly to other databases such as ORACLE etc.

The ideal implementation of security in a Windows NT/2000 environment with SQL Server 7.0/2000 database server is as follows. 

  • Configure SQL Server to use Windows authentication mode
  • Depending upon the data access needs of your domain users, group them into different global groups in the domain
  • Consolidate these global groups from all the trusted domains into the Windows NT/2000 local groups in your SQL Server computer
  • The Windows NT/2000 local groups are then granted access to log into the SQL Server
  • Add these Windows NT/2000 local groups to the required fixed server roles in SQL Server
  • Associate these local group logins with individual user accounts in the databases and grant them the required permissions using the database roles
  • Create custom database roles if required, for finer control over permissions

Here is a security checklist and some standard security practices and tips:

  • Restrict physical access to the SQL Server computer. Always lock the server while not in use.
  • Make sure, all the file and disk shares on the SQL Server computer are read-only. In case you have read-write shares, make sure only the right people have access to those shares.
  • Use the NTFS file system as it provides advanced security and recovery features.
  • Prefer Windows authentication to mixed mode. If mixed mode authentication is inevitable, for backward compatibility reasons, make sure you have complex passwords for sa and all other SQL Server logins. It is recommended to have mixed case passwords with a few numbers and/or special characters, to counter the dictionary based password guessing tools and user identity spoofing by hackers.
  • Rename the Windows NT/2000 Administrator account on the SQL Server computer to discourage hackers from guessing the administrator password.
  • In a website environment, keep your databases on a different computer than the one running the web service. In other words, keep your SQL Server off the Internet, for security reasons.
  • Keep yourself up-to-date with the information on latest service packs and security patches released by Microsoft. Carefully evaluate the service packs and patches before applying them on the production SQL Server. Bookmark this page for the latest in the security area from Microsoft:
  • If it is appropriate for your environment, hide the SQL Server service from appearing in the server enumeration box in Query Analyzer, using the /HIDDEN:YES switch of NET CONFIG SERVER command.
  • Enable login auditing at the Operating System and SQL Server level. Examine the audit for login failure events and look for trends to detect any possible intrusion.
  • Use Intrusion Detection Systems (IDS), especially on high-risk online database servers. IDS can constantly analyze the inbound network traffic, look for trends and detect Denial of Service (DoS) attacks and port scans. IDS can be configured to alert the administrators upon detecting a particular trend.
  • Disable guest user account of Windows. Drop guest user from production databases using sp_dropuser
  • Do not let your applications query and manipulate your database directly using SELECT/INSERT/UPDATE/DELETE statements. Wrap these commands within stored procedures and let your applications call these stored procedures. This helps centralize business logic within the database, at the same time hides the internal database structure from client applications.
  • Let your users query views instead of giving them access to the underlying base tables.
  • Discourage applications from executing dynamic SQL statements. To execute a dynamic SQL statement, users need explicit permissions on the underlying tables. This defeats the purpose of restricting access to base tables using stored procedures and views.
  • Don't let applications accept SQL commands from users and execute them against the database. This could be dangerous (known as SQL injection), as a skilled user can input commands that can destroy the data or gain unauthorized access to sensitive information.
  • Carefully choose the members of the sysadmin role, as the members of the sysadmin role can do anything in the SQL Server. Note that, by default, the Windows NT/2000 local administrators group is a part of the sysadmin fixed server role.
  • Constantly monitor error logs and event logs for security related alerts and errors.
    SQL Server error logs can reveal a great deal of information about your server. So, secure your error logs by using NTFS permissions.
  • Secure your registry by restricting access to the SQL Server specific registry keys like HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer.
  • DBAs generally tend to run SQL Server service using a domain administrator account. That is asking for trouble. A malicious SQL Server user could take advantage of these domain admin privileges. Most of the times, a local administrator account would be more than enough for SQL Server service.
  • Be prompt in dropping the SQL Server logins of employees leaving the organization. Especially, in the case of a layoff, drop the logins of those poor souls ASAP as they could do anything to your data out of frustration.
  • When using mixed mode authentication, consider customizing the system stored procedure sp_password, to prevent users from using simple and easy-to-guess passwords.
  • Do not save passwords in your .udf files, as the password gets stored in clear text.
  • Install anti-virus software on the SQL Server computer, but exclude your database folders from regular scans. Keep your anti-virus signature files up to date.
  • Windows 2000 introduced Encrypted File System (EFS) that allows you to encrypt individual files and folders on an NTFS partition. Use this feature to encrypt your SQL Server database files. You must encrypt the files using the service account of SQL Server. When you want to change the service account of SQL Server, you must decrypt the files, change the service account and encrypt the files again with the new service account.

Note : NIC officials are requested to send in their articles to wsg[at]up[dot]nic[dot]in , to get featured in the "Knowledge Desk" link. Kindly mention your Name, Designation and Contact Email