عشرة خطوات لزيادة أمان قاعدتك

To be able to access data from a database, a user must pass through two stages of authentication, one at the SQL Server level and the other at the database level. These two stages are implemented using Logins names and User accounts respectively. A valid login is required to connect to SQL Server and a valid user account is required to access a database.

Login: A valid login name is required to connect to an SQL Server instance. A login could be:

A Windows NT/2000 login that has been granted access to SQL Server

An SQL Server login, that is maintained within SQL Server

These login names are maintained within the master database. So, it is essential to backup the master database after adding new logins to SQL Server.

User: A valid user account within a database is required to access that database. User accounts are specific to a database. All permissions and ownership of objects in the database are controlled by the user account. SQL Server logins are associated with these user accounts. A login can have associated users in different databases, but only one user per database.

During a new connection request, SQL Server verifies the login name supplied, to make sure, that login is authorized to access SQL Server. This verification is called Authentication. SQL Server supports two authentication modes:

Windows authentication mode: With Windows authentication, you do not have to specify a login name and password, to connect to SQL Server. Instead, your access to SQL Server is controlled by your Windows NT/2000 account (or the group to which your account belongs to), that you used to login to the Windows operating system on the client computer/workstation. A DBA must first specify to SQL Server, all the Microsoft Windows NT/2000 accounts or groups that can connect to SQL Server

Mixed mode: Mixed mode allows users to connect using Windows authentication or SQL Server authentication. Your DBA must first create valid SQL Server login accounts and passwords. These are not related to your Microsoft Windows NT/2000 accounts. With this authentication mode, you must supply the SQL Server login and password when you connect to SQL Server. If you do not specify SQL Server login name and password, or request Windows Authentication, you will be authenticated using Windows Authentication.

Point to note is that, whatever mode you configure your SQL Server to use, you can always login using Windows authentication.

Windows authentication is the recommended security mode, as it is more secure and you don't have to send login names and passwords over the network. You should avoid mixed mode, unless you have a non-Windows NT/2000 environment or when your SQL Server is installed on Windows 95/98 or for backward compatibility with your existing applications.

SQL Server's authentication mode can be changed using Enterprise Manager (Right click on the server name and click on Properties. Go to the Security tab).

Authentication mode can also be changed using SQL DMO object model.

Here is a list of helpful stored procedures for managing logins and users:

sp_addlogin Creates a new login that allows users to connect to SQL Server using SQL Server authentication

sp_grantlogin Allows a Windows NT/2000 user account or group to connect to SQL Server using Windows authentication

sp_droplogin Drops an SQL Server login

sp_revokelogin Drops a Windows NT/2000 login/group from SQL Server

sp_denylogin Prevents a Windows NT/2000 login/group from connecting to SQL Server

sp_password Adds or changes the password for an SQL Server login

sp_helplogins Provides information about logins and their associated users in each database

sp_defaultdb Changes the default database for a login

sp_grantdbaccess Adds an associated user account in the current database for an SQL Server login or Windows NT/2000 login

sp_revokedbaccess Drops a user account from the current database

sp_helpuser Reports information about the Microsoft users and roles in the current database

Now let's talk about controlling access to objects within the database and managing permissions. Apart from managing permissions at the individual database user level, SQL Server 7.0/2000 implements permissions using roles. A role is nothing but a group to which individual logins/users can be added, so that the permissions can be applied to the group, instead of applying the permissions to all the individual logins/users. There are three types of roles in SQL Server 7.0/2000:

Fixed server roles

Fixed database roles

Application roles

Fixed server roles: These are server-wide roles. Logins can be added to these roles to gain the associated administrative permissions of the role. Fixed server roles cannot be altered and new server roles cannot be created. Here are the fixed server roles and their associated permissions in SQL Server 2000:

Fixed server role Description

sysadmin Can perform any activity in SQL Server

serveradmin Can set server-wide configuration options, shut down the server

setupadmin Can manage linked servers and startup procedures

securityadmin Can manage logins and CREATE DATABASE permissions, also read error logs and change passwords

processadmin Can manage processes running in SQL Server

dbcreator Can create, alter, and drop databases

diskadmin Can manage disk files

bulkadmin Can execute BULK INSERT statements

Here is a list of stored procedures that are helpful in managing fixed server roles:

sp_addsrvrolemember Adds a login as a member of a fixed server role

sp_dropsrvrolemember Removes an SQL Server login, Windows user or group from a fixed server role

sp_helpsrvrole Returns a list of the fixed server roles

sp_helpsrvrolemember Returns information about the members of fixed server roles

sp_srvrolepermission Returns the permissions applied to a fixed server role

Fixed database roles: Each database has a set of fixed database roles, to which database users can be added. These fixed database roles are unique within the database. While the permissions of fixed database roles cannot be altered, new database roles can be created. Here are the fixed database roles and their associated permissions in SQL Server 2000:

Fixed database role Description

db_owner Has all permissions in the database

db_accessadmin Can add or remove user IDs

db_securityadmin Can manage all permissions, object ownerships, roles and role memberships

db_ddladmin Can issue ALL DDL, but cannot issue GRANT, REVOKE, or DENY statements

db_backupoperator Can issue DBCC, CHECKPOINT, and BACKUP statements

db_datareader Can select all data from any user table in the database

db_datawriter Can modify any data in any user table in the database

db_denydatareader Cannot select any data from any user table in the database

db_denydatawriter Cannot modify any data in any user table in the database

Here is a list of stored procedures that are helpful in managing fixed database roles:

sp_addrole Creates a new database role in the current database

sp_addrolemember Adds a user to an existing database role in the current database

sp_dbfixedrolepermission Displays permissions for each fixed database role

sp_droprole Removes a database role from the current database

sp_helpdbfixedrole Returns a list of fixed database roles

sp_helprole Returns information about the roles in the current database

sp_helprolemember Returns information about the members of a role in the current database

sp_droprolemember Removes users from the specified role in the current database

Application roles: Application roles are another way of implementing permissions. These are quite different from the server and database roles. After creating and assigning the required permissions to an application role, the client application needs to activate this role at run-time to get the permissions associated with that application role. Application roles simplify the job of DBAs, as they don't have to worry about managing permissions at individual user level. All they need to do is to create an application role and assign permissions to it. The application that is connecting to the database activates the application role and inherits the permissions associated with that role. Here are the characteristics of application roles:

There are no built-in application roles

Application roles contain no members

Application roles need to be activated at run-time, by the application, using a password

Application roles override standard permissions. For example, after activating the application role, the application will lose all the permissions associated with the login/user account used while connecting to SQL Server and gain the permissions associated with the application role

Application roles are database specific. After activating an application role in a database, if that application wants to run a cross-database transaction, the other database must have a guest user account enabled

Here are the stored procedures that are required to manage application roles:

sp_addapprole Adds an application role in the current database

sp_approlepassword Changes the password of an application role in the current database

sp_dropapprole Drops an application role from the current database

sp_setapprole Activates the permissions associated with an application role in the current database

Now that we discussed different kinds of roles, let's talk about granting/revoking permissions to/from database users and database roles and application roles. The following T-SQL commands are used to manage permissions at the user and role level.

GRANT: Grants the specific permission (Like SELECT, DELETE etc.) to the specified user or role in the current database

REVOKE: Removes a previously granted or denied permission from a user or role in the current database

DENY: Denies a specific permission to the specified user or role in the current database

Using the above commands, permissions can be granted/denied/revoked to users/roles on all database objects. You can manage permissions at as low as the column level.

Note: There is no way to manage permissions at the row level. That is, in a given table, you can't grant SELECT permission on a specific row to User1 and deny SELECT permission on another row to User2. This kind of security can be implemented by using views and stored procedures effectively.

SQL Server security best practices

Here is an ideal implementation of security in a Windows NT/2000 environment with SQL Server 7.0/2000 database server:

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: http://www.microsoft.com/security/

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.

If it fits your budget, 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.

Take advantage of the fixed server and database roles by assigning users to the appropriate roles. You could also create custom database roles that suit your needs.

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.

If your databases contain sensitive information, consider encrypting the sensitive pieces (like credit card numbers and Social Security Numbers (SSN)). There are undocumented encryption functions in SQL Server, but I wouldn't recommend those. If you have the right skills available in your organization, develop your own encryption/decryption modules using Crypto API or other encryption libraries.

If you are running SQL Server 7.0, you could use the encryption capabilities of the Multi-Protocol net library for encrypted data exchange between the client and SQL Server. SQL Server 2000 supports encryption over all protocols using Secure Socket Layer (SSL). See SQL Server 7.0 and 2000 Books Online (BOL) for more information on this topic. Please note that, enabling encryption is always a tradeoff between security and performance, because of the additional overhead of encryption and decryption.

Prevent unauthorized access to linked servers by deleting the linked server entries that are no longer needed. Pay special attention to the login mapping between the local and remote servers. Use logins with the bare minimum privileges for configuring linked servers.

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.

DBAs also tend to drop system stored procedures like xp_cmdshell and all the OLE automation stored procedures (sp_OACreate and the likes). Instead of dropping these procedures, deny EXECUTE permission on them to specific users/roles. Dropping these procedures would break some of the SQL Server functionality.

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.

To setup secure data replication over Internet or Wide Area Networks (WAN), implement Virtual Private Networks (VPN) . Securing the snapshot folder is important too, as the snapshot agent exports data and object scripts from published databases to this folder in the form of text files. Only the replication agents should have access to the snapshot folder.

It is good to have a tool like Lumigent Log Explorer handy, for a closer look at the transaction log to see who is doing what in the database.

Do not save passwords in your .udf files, as the password gets stored in clear text.

If your database code is proprietary, encrypt the definition of stored procedures, triggers, views and user defined functions using the WITH ENCRYPTION clause. dbLockdown is a tool that automates the insertion of the WITH ENCRYPTION clause and handles all the archiving of encrypted database objects so that they can be restored again in a single click.

In database development environments, use a source code control system like Visual Source Safe (VSS) or Rational Clear Case. Control access to source code by creating users in VSS and giving permissions by project. Reserve the 'destroy permanently' permission for VSS administrator only. After project completion, lock your VSS database or leave your developers with just read-only access.

Store the data files generated by DTS or BCP in a secure folder/share and delete these files once you are done.

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.

SQL Server 2000 allows you to specify a password for backups. If a backup is created with a password, you must provide that password to restore from that backup. This discourages unauthorized access to backup files.

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