Overview of SQL Server security model and security best practices

This article discusses the security model of Microsoft SQL Server 7.0/2000 and security best practices to help you secure your data.  Security is a major concern for the modern age systems/network/database administrators. It is natural for an administrator to worry about hackers and external attacks while implementing security. But there is more to it. It is essential to first implement security within the organization, to make sure right people have access to the right data. Without these security measures in place, you might find someone destroying your valuable data, or selling your company's secrets to your competitors or someone invading the privacy of others. Primarily a security plan must identify which users in the organization can see which data and perform which activities in the database. SQL Server security model 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. Click here to read about row level security implementation in SQL Server databases. Just an FYI, Oracle has a feature called "Virtual Private Databases" (VPD) that allows DBAs to configure permissions at row level. SQL Server security best practicesHere 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. Click here to find out more information about this product.  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.