===================================================================================:
http://www.experts-exchange.com/Security/Misc/Q_21044157.html
----------------------------------------------------------------
1. security group
2. domain local group.
I understand the whole concept of adding a user to a global group.
but it becomes blurry when you add a global group to domain local group.
what are they really intended for , please dont link me to any site explaining it.
i want it in lamen terms and some examples would be helpful. i am studying 70-215
what i know.
in a multiple domain you add a global group to a domain local group so you can access resoures in
another domain. "parent and child domains"
microsoft recommends you use a g dl p , but why.
there are also some conflicts on using native mode or mixed mode , and how it applies to domain local groups.
any form of explaining this very simple would help.
rafael_acc:
Hi. Congratulations for the mcse track ... and GOOD LUCK. Congratulations also for trying to understand how things works and not memorizing the concepts. This is the right way to go, I think. However, there are some concepts for witch you most find a way to memorize them. There isn't also a realy simple way of explaining some concepts but I will try by giving some examples. Now, on your questions ...
Prior to fully understand groups you must first understand each group type. There are 4 types of groups:
1. local group
2. domain local group
3. domain global group
4. domain univesal group
Remember that "local", "global", "universal" reffers to where these groups may be assigned permissions and are not related to the group membership itself. But let's define each of them now:
1. Local group: The members of this group type can be assigned permissions only locally on the computer where the group exist. It cannot be used to assign permissions on the domain and this group is not known by other computers. (So..’isntgroup=0’)—My Notes
However, it can contain local security accounts (created on the same computer - in the local security database SAM) or other domains members accounts (when the machine/computer is part of a domain) from any domain.
Example: when you join the computer to a domain, the domain administrator account is automatically added to the local Administrators group. That's why a domain administrator can handle administrative tasks on any domain client computer. But you cannot use this local "Administrators" group to assign permissions on any other computer/resource on the domain. You will use this group only locally on the computer it exists. "Administrators" membership could be domain1\user1, domain2\user2, localuser3, etc.
2. Domain Local group: can have as their members, accounts, global groups, and universal groups FROM ANY DOMAIN, as well as domain local groups from the same domain. This group can belong to another domain local groups and assigned permissions only in the same domain. Can be converted to universal scope, as long as it does not have as its member another group having domain local scope.
Example: Suppose you were the network administrator for a three domains network. Now you have to give permissions to some users from domain2 and domain3 to resources located in domain1. What you do? Well ... You can create a domain local group in the domain1 and then assign the respective users from domain2 and domain3 to the group you have just created in domain1. Then use this group to assign permissions to resources inside domain1 only. You will not be able to assign permissions for this group in other domains resources!! However, you did add users from other domains.
3. Domain Global group: can have as their members’ accounts and global groups FROM THE SAME DOMAIN. Can be converted to universal scope, as long as it is not a member of any other group having global scope.
Example: You create a "group1" in the "domain1" domain. Then you add accounts, other global groups from the same domain to it. You can use this group to assign permissions to resources located on other domains or for other domain administrators to assign permissions to your domain users in their own domains resources.
4. Domain Universal group: can have as their members accounts FROM ANY DOMAIN, global groups from any domain and universal groups FROM ANY DOMAIN. This group cannot be converted to any other group scope.
Example/Some more detail: This is the less restrictive group. For some administrators it might be a better solution for easy administration. However the use of Universal Group have a big impact on active directory performance because the universal group membership is stored on the global catalog. There might also occur problems with the login. When a user login, if the user belongs to a Universal group, a global catalog must be found otherwise the user would not be able to login. If the user doesn't belong to a universal group, cached credentials are used if a global catalog cannot be found. In that case, if the user had never before logged in, cached credentials doesn't exist, therefore the user will not be able to login also.
The following conversion can be made between groups types:
• Global to universal:: this is only allowed if the group is not a member of another group having global scope.
• Domain local to universal:: However, the group being converted cannot have as its member another group having domain local scope.
Note1: If you have multiple forests, users defined in only one forest cannot be placed into groups defined in another forest, and groups defined in only one forest cannot be assigned permissions in another forest.
Note2: Group nesting is available only when in Native mode. In mixed mode, universal groups cannot be used. There are also some differences about the way the PDC emulator works. But you will learn about it ... It's related with AD authentication and with the master operation roles.
Now, why add a global group to domain local group? This is because it's a best practice to always assign permissions to groups and not to users individually. I will try to give you a practical approach.
Let's assume again that you are a network administrator for a company with 50 users. If you were to assign permissions for a network resource it's better (from the administrator's point of view) to define permissions only once (for a group) than assigning permissions for every time a user have to be given access. So, that's the reason why even having a single user, you put it inside a group and then assign permissions to the group itself. In the future when you need to assign permissions to other users, you just add them to the group and usually, they will have to log-off and log-in again. Don't you agree? :)
Now the A G DL P concept goes all around this. It's just a bit more generic and does make a lot sense when in an environment with multiple domains!! So you put ACCOUNT inside a GLOBAL GROUP. Then the GLOBAL GROUP you put it inside the DOMAIN LOCAL GROUP and finally, you assign PERMISSIONS.
One practical example: There are 2 domains (dom1 and dom2). You are administering dom1. If the dom2 administrator tells you that some of his users need access to some resources located in your domain (in dom1). You say to the dom2 administrator to create a global group in domain dom2 and put the users inside it. Then you take that global group in put it inside a domain local group and assign permissions for this domain local group to the specified resources in your domain. Now, for every time the dom2 administrator wants to add/remove a user he just needs to remove the user/users from the global group in his domain (because he doesn't have permission on dom1 domain).
If you were assigning users to a domain local group, every time the dom2 administrator wanted to add a user, he will have to contact you and only you would be the person allowed to make the change because only you have permissions in your domain (it might be the case).
Some advantages:
1. You, as dom1 domain administrator assign permissions only once.
2. You will not have to worry in the future if the dom2 domain administrator wants to add/remove users from the users list witch might have access to specified resources.
Let me know if I was not clear enough or if I miss in explaining some more doubts you might have.
Cheers.
Accepted Solution
----------------------------------------------
Overview of SQL Server security model and security best practices:
=================================================================
http://vyaskn.tripod.com/sql_server_security_best_practices.htm
----------------------------------------------------------------:
This article discusses the security model of Microsoft SQL Server 7.0/2000 and security best practices to help you secure your data. Special thanks to my friend Divya Kalra for her valuable input and content review.
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 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. 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.
SQL Server 2000 SP3 Security Features and Best Practices: SQL Server 2000 Security Model:
==================================================================================
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec01.mspx
----------------------------------------------------------------------------:
Introduction:
============
This paper introduces Microsoft® SQL Server™ administrators and developers to the security features of SQL Server 2000 Service Pack 3 (SP3). It discusses security best practices, giving detailed recommendations, and includes source code examples. It concludes with a security best practices checklist. This paper focuses on the SQL Server engine. For detailed discussions of Replication, Analysis Services, or Data Transformation Services, see SQL Server 2000 Books Online (Updated - Service Pack 3).
Assumptions
SQL Server 2000 security is based on the Microsoft Windows NT® 4.0 and Windows 2000 security model; therefore, a basic understanding of Windows NT 4.0 and Windows 2000 security is assumed throughout this paper. An understanding of the concept of domains, global groups, local groups, and user accounts as they apply in the context of Windows NT 4.0 security, as well as Microsoft Active Directory® in Windows 2000, is also assumed.
For those interested in the code examples, exposure to Microsoft Visual Basic® and Structured Query Language (SQL) will enhance understanding. In addition, experience using SQL Distributed Management Objects (SQL-DMO) provides a significant advantage.
Even those who have had limited exposure to Windows NT 4.0, Windows 2000, or SQL Server may, however, gain significant understanding about security when using these products.
SQL Server 2000 Security Model
The SQL Server 2000 security model is based on the Windows security model. For detailed discussion of Windows security best practices, see Windows 2000 Server Security Center.
SQL Server 2000 should be secured as outlined in the following diagram (Figure 1).
Figure 1: Windows users and groups offer a powerful and flexible security model for SQL Server administrators
See full-sized image.
The steps in the diagram can be summarized as follows:
1. Users in each domain are assigned to Windows global groups.
2. The Windows global groups from the various domains are placed into a Windows local group.
3. The Windows local group is granted rights to log in to SQL Server 2000.
4. The Windows local group is granted access rights to the appropriate databases. This Windows local group may not be the same one as was used for granting login rights in Step 3. Therefore, Steps 1 and 2 are often repeated to group the users by access permissions required.
5. The Windows local group is assigned permissions on the specific database objects.
The other approach to security is based on the use of roles, and is usually implemented in the following way (Figure 2).
Figure 2: Role-based security is another security option in SQL Server 2000
See full-sized image.
When using roles to assign object permissions, individuals must still be granted permissions on the server and the database using the recommended approach.
Steps 1 through 4 are the same for both diagrams, except that multiple Windows global and local groups would probably not be created. Windows 2000 Universal groups are fully supported as well.
Step 5: Individual Windows accounts and Windows groups are assigned to a role.
Step 6: Object permissions are then assigned to the roles.
Roles reduce the requirements of grouping users within Windows, by grouping the users within SQL Server 2000.
Authentication Modes
SQL Server 2000 provides two authentication modes for securing access to the server: Windows Authentication Mode and Mixed Mode.
Windows Authentication Mode
Windows Authentication Mode is the default authentication mode in SQL Server 2000. In Windows Authentication Mode, SQL Server 2000 relies solely on the Windows authentication of the user. Windows users or groups are then granted access to the SQL Server. Connections made to the server using this mode are known as trusted connections.
When Windows Authentication Mode is used, the database administrator allows users to access the computer running SQL Server by granting them the right to log in to SQL Server 2000. Windows security identifiers (SIDs) are used to track Windows authenticated logons. As Windows SIDs are used, the database administrator can grant logon access directly to Windows users or groups.
Mixed Mode
In Mixed Mode, users can be authenticated by Windows Authentication or by SQL Server Authentication. Users who are authenticated by SQL Server have their username and password pairs maintained within SQL Server.
Just like in Windows Authentication Mode, SQL Server connecting in Mixed Mode relies on Windows to authenticate users if the client and server are capable of using NTLM, or Kerberos logon authentication protocols. If the client is unable to use a standard Windows logon, SQL Server requires a username and password pair, and compares this pair against those stored in its system tables. Connections that rely on username and password pairs are called non-trusted connections, or SQL connections.
Using Security Identification Numbers Internally
SQL Server tracks trusted logins using SIDs. Windows users and groups can be granted access to databases or specific database objects directly. For example, Jane is a member of the SALES and MARKETING groups in Windows. The SALES group has been granted permission to log in to SQL Server, and also to access the pubs database. An administrator could grant access to the authors table for Jane by her Windows name, REDMOND\Jane. The Windows account must be referenced by domain and username. In this case, Jane's SID would be stored in the system tables of the pubs database. SQL Server 2000 does not support User Principal Names (UPNs). For example, if my Windows login is domain SALES, user SOMEONE, the login to SQL Server would be SALES\SOMEONE, and you could not use the login in the form of SOMEONE@MYCOMPANY.COM as supported by Windows 2000 Active Directory.
Roles:
======
Roles are used much like Windows groups are used. Roles allow users to be collected into a single unit against which permissions can be applied. Permissions granted, denied, or revoked from a role also apply to any members of the role. Roles can represent a job performed by a class of workers in an organization. Permissions can then be granted to that role. As workers rotate into the job, they are made members of the role; as they rotate out of the job, they are removed. This removes the requirement to repeatedly grant, deny, and revoke permissions to or from individuals as they accept or leave a job.
There are a number of key concepts that make roles so powerful. First, with the exception of fixed server roles, they are implemented within a database. This means that the database administrator does not depend on the Windows administrator for the grouping of users. Second, roles can be nested. There is no restriction on the depth of nesting, but circular nesting is not permitted. Third, a database user can be a member of more than one role simultaneously.
This allows a database administrator to arrange permissions in hierarchies that reflect the administrative structure of the organization that uses the database. For example, a Finance Department might contain separate groups responsible for accounts payable and accounts receivable. The database administrator could create separate database roles for APEmployees and AREmployees, assigning to each role only those permissions needed by employees in the corresponding job. The database administrator could then create a role FinManagers, containing both of the more limited roles (APEmployees, and AREmployees). FinManagers would thus have all the permissions of employees who report through them. When an employee is promoted from accounts payable to management, the database administrator need only add her to the FinManagers role.
Public Role:
===========
The public role exists in every database, including the system databases master, msdb, tempdb and model. The public role provides the default permissions for users in a database and cannot be deleted. Functionally, it can be compared to the Everyone group in the Windows NT 4.0 environment. Every database user is a member of this role automatically; therefore, users cannot be added or removed from this role.
Predefined Roles
SQL Server 2000 includes several predefined roles. These roles have predefined implied permissions, which cannot be granted to other user accounts. There are two types of predefined roles: fixed server roles and fixed database roles.
Fixed Server Roles:
==================
Fixed server roles are server-wide in their scope. They exist outside of the databases. Each member of a fixed server role is able to add other logins to that same role.
Note:
-----
All members of the Windows BUILTIN\Administrators group (the local administrator's group) are members of the sysadmin role by default.
The following table (Table 1) lists the fixed server roles found in SQL Server 2000.
Table 1: SQL Server 2000 fixed server roles
Fixed Server Role Description
Sysadmin Performs any activity in SQL Server.
Serveradmin Configures server-wide configuration options, shuts down the server.
Setupadmin Adds and removes linked servers, and executes some system stored procedures, such as sp_serveroption.
securityadmin Manages server-wide security settings, including linked servers, and CREATE DATABASE permissions. Resets passwords for SQL Server authentication logins.
processadmin Terminates processes running in SQL Server.
dbcreator Creates, alters, drops, and restores any database.
diskadmin Manages disk files.
Bulkadmin Allows a non-sysadmin user to run the bulkadmin statement.
To add users to the fixed server roles, use the following Transact-SQL statement:
/* Add Bob to the sysadmin server role */
exec sp_addsrvrolemember "REDMOND\Bob", "sysadmin"
Windows users and groups can be added to server roles. The following code shows how a user is added to a server role using the SQL Distributed Management Objects (DMO) collection:
' Declare variables
Dim oServer As SQLDMO.SQLServer
' Create a server object and connect
Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.Connect ("SERVERNAME")
' Add Bob to the sysadmin server role
oServer.ServerRoles("sysadmin").AddMember ("REDMOND\Bob")
For more information about the use of fixed server roles, see SQL Server Books Online.
Fixed Database Roles:
====================
Fixed database roles are defined at the database level and exist in each database. Members of the db_owner and db_security administrator roles can manage fixed database role membership; however, only the db_owner role can add others to the db_owner fixed database role.
The following table (Table 2) lists the fixed database roles found in SQL Server 2000.
Table 2 SQL Server 2000 fixed database roles
Fixed database role Description
db_owner Performs all maintenance and configuration activities in the database.
db_accessadmin Adds or removes access for Windows users, groups, and SQL Server logins.
db_datareader Reads all data from all user tables.
db_datawriter Adds, deletes, or changes data in all user tables.
db_ddladmin Runs any Data Definition Language (DDL) command in a database.
db_securityadmin Modifies role membership and manages permissions.
db_backupoperator Backs up the database.
db_denydatareader Cannot read any data in user tables within a database.
db_denydatawriter Cannot add, modify, or delete data in any user tables or views.
For more information about the use of fixed database roles, see SQL Server Books Online.
User-Defined Roles
User-defined roles provide an easy way to manage permissions in a database when a group of users performs a specified set of activities in SQL Server 2000. In situations in which there is no applicable Windows group, or the database administrator does not have permissions to manage the Windows user accounts, user-defined roles provide the database administrator the same flexibility as Windows groups.
User-defined roles apply only at the database level, and are local to the database in which they were created.
Application Roles
Application roles allow a database administrator to grant access to data only to those users who are using a particular application.
The process works as follows. A user connects to a database through an application. The application then proves its identity to SQL Server by executing the sp_setapprole stored procedure, which takes two parameters: application role name and password. (The application role password is known only to the application.) If the application role name and password are valid, the application role is activated. At this point all the permissions currently assigned to the user are dropped, and the security context of the application role is assumed. Since only the application (not the user) knows the password for the application role, only the application can activate this role and access objects to which the role has permissions.
After application roles are activated, they cannot be deactivated. The only way for a user to re-acquire its original security context is to disconnect and reconnect to SQL Server.
Application roles work with both authentication modes, and contain no members. Users cannot be associated with application roles, as the application requests the application role's security context using the sp_setapprole stored procedure.
Similar to user-defined roles, application roles exist only within a database. If an application role attempts to access another database, that application role will only be granted the privileges of the guest account in that database. If the guest account has not been specifically granted access to the data, or does not exist, the objects cannot be accessed.
Another key concept in the use of application roles is that the user who is running the application is audited within SQL Server 2000. In other words, application roles provide the security context within which the database object permissions are checked, but the identity of the actual user is not lost.
Here is an example of an implementation using application roles. If Jane is a member of the ACCOUNTING group, and the ACCOUNTING group members are permitted to gain access only to the data in SQL Server through the accounting software package, an application role could be created for the accounting software. The ACCOUNTING application role would be granted access to the data, while the ACCOUNTING Windows group would be denied access to the data. Thus, when Jane attempts to access the data using SQL Query Analyzer, she will be denied access; but when Jane uses the accounting software, she will be able to access the data.
This procedure outlines how an application can make use of application roles. To use application roles:
1. Create an application role.
2. Assign permissions to the application role.
3. Ensure that the end user connects to the server through the application.
4. Ensure the client application activates the application role.
The first two steps of this process are usually separated from the last two steps. Therefore, two code fragments will follow for Transact-SQL and Microsoft Visual Basic®, respectively.
The Transact-SQL script is as follows:
/* Create the application role. */
EXEC sp_addapprole "AccAppRole", "ABC"
/* Grant permissions to SELECT. */
GRANT SELECT
ON authors
TO AccAppRole
GO
Here is the code to activate the role:
/* Activate the role. */
EXEC sp_setapprole "AccAppRole", {ENCRYPT N "ABC"}
The encryption of the password is optional, but ensures greater security when the password has to pass through a wide area network (WAN).
Here is the code in Visual Basic:
' Declare variables.
Dim oServer As SQLDMO.SQLServer
Dim oDbRole As SQLDMO.DatabaseRole
' Create a server object and connect.
Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.Connect ("SERVERNAME")
' Create the Role object.
Set oDbRole = CreateObject("SQLDMO.DatabaseRole")
' Set the appropriate properties.
oDbRole.Name = "AccAppRole"
oDbRole.AppRole = True
oDbRole.Password = "45$#Jxew&fd2$Dw53987"
' Add the Role object to the servers Role collection.
oServer.Databases("pubs").DatabaseRoles.Add oDbRole
To use the role:
' Declare variables.
Dim oConnection As ADODB.Connection
' Create the connection object and connect.
Set oConnection = CreateObject("ADODB.Connection")
oConnection.Provider = "sqloledb"
oConnection.Open "Server=SERVERNAME;Database=pubs;Trusted_Connection=yes"
' Activate the application role. There is no error handling for this sample.
oConnection.Execute "EXEC sp_setapprole 'AccAppRole', {ENCRYPT N '45$#Jxew&fd2$Dw53987'}, 'ODBC'"
The encryption style (last parameter) must be set for OLE DB and ODBC data sources. No other data sources can explicitly encrypt the password. In these cases, you must use an encrypted communications protocol with the server. See the discussions of SSL and IPSec later in this paper.
Application roles are implemented per session. If your application opens multiple sessions and all sessions are required to use the same role, each session must first activate the role.
Implementing application roles can be used to provide much more granular security than ever before. For example, a client application could use the user's security context on some connections, while using an application role on another.
When using application roles, executing SELECT USER returns the name of the application role currently being used. If the identity of the logged-on user is required, use the following Transact-SQL statement: SELECT SYSTEM_USER.
Note: Using application roles requires storing passwords. Be sure to use appropriate encryption and ACLs. See the topic "Storing Credentials" later in this paper.
Top of page
Securing Access to the Server
Access to the server is controlled differently by the two authentication modes in SQL Server 2000. However, after a user gains access to the server, the authentication modes are identical. SQL Server 2000 security defaults to Windows Authentication when it is installed.
Windows Level
When securing access at the Windows level, administrators should create a Windows domain login account for each user who will be accessing SQL Server (if the user does not already have an account).
At the Windows domain level, create global groups with permissions appropriate to specific job functions in your organization. Add Windows users to the appropriate global groups. On the computer running SQL Server 2000, create Windows local groups with permissions appropriate to the kinds of work users will do on SQL Server. Finally, add the appropriate Windows global groups to the Windows local groups.
The goal of this process is to gather rights and users into groups that can be managed collectively. Although the preliminary process may appear excessively complex, it is highly recommended. An initial investment in careful security planning will yield compound interest in the form of improved system security and ease of administration in the future.
For a detailed discussion of Windows-level security, see Microsoft Solution for Securing Windows 2000 Server.
Top of page
Securing Access to the Database
Successful login does not automatically allow a user access to all databases on SQL Server 2000. Permissions must be granted to allow users to access a database.
In this section, we do not differentiate between non-trusted users, Windows users, and Windows groups. When reference is made to Windows users or groups, they can also be users or global groups in trusted domains, or domains within the same tree or forest.
Within each database, a user is created and is linked to a SQL Server login, a Windows user, or a Windows group.
SQL Server Enterprise Manager (which is a Microsoft Management Console [MMC] snap-in for administering SQL Server 2000) does not allow the creation of users who do not have specific logon permissions. The MMC creates a list of all accounts that have been granted the permission to log on to the server, and a selection needs to be made from this list. The same applies to the SQL-DMO object model.
With Transact-SQL, any valid SQL Server logon, Windows user, or Windows group can be granted the rights to access the database, whether or not a specific logon exists in the sysxlogins table in the master database.
Note: Although not a technical requirement, if you are using trusted connections, it is strongly recommended that you create users with the same username in each database as the logon name.
Some examples for the Transact-SQL statements required to grant permission to use a database are:
/* Grant access to Bob. */
exec sp_grantdbaccess 'REDMOND\Bob'
/* Grant access to Wendy, referring to her by first name within this database. */
exec sp_grantdbaccess 'REDMOND\WendyH', 'Wendy'
Only one modification would be required to make this example work with non-trusted clients. Instead of the domain username, use the username that SQL Server 2000 uses to authenticate the user.
Using SQL-DMO, the equivalent functionality would be achieved by the following code:
' Declare variables.
Dim oServer As SQLDMO.SQLServer
Dim oUser As SQLDMO.User
' Create a server object and connect.
Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.Connect ("SERVERNAME")
' Create the User object.
Set oUser = CreateObject("SQLDMO.User")
' Set the appropriate properties.
oUser.Name = "Bob"
oUser.Login = "REDMOND\Bob"
' Add the User object to the servers Users collection.
oServer.Databases("pubs").Users.Add oUser
SQL Server Level
At the SQL Server 2000 level, you must grant permissions for the created Windows local groups to log in to SQL Server. You can also grant permission to log in to SQL Server directly to users, but is not as practical to administer except for the smallest of environments. Permissions to log on to the server can be granted with Enterprise Manager or implemented programmatically using Visual Basic or Transact-SQL.
Database administrators working with Transact-SQL should familiarize themselves with the Books Online entries for these security-related stored procedures:
sp_addalias sp_droprole
sp_addapprole sp_droprolemember
sp_addgroup sp_dropserver
sp_addlinkedsrvlogin sp_dropsrvrolemember
sp_addlogin sp_dropuser
sp_addremotelogin sp_grantdbaccess
sp_addrole sp_grantlogin
sp_addrolemember sp_helpdbfixedrole
sp_addserver sp_helpgroup
sp_addsrvrolemember sp_helplinkedsrvlogin
sp_adduser sp_helplogins
sp_approlepassword sp_helpntgroup
sp_change_users_login sp_helpremotelogin
sp_changedbowner sp_helprole
sp_changegroup sp_helprolemember
sp_changeobjectowner sp_helprotect
sp_dbfixedrolepermission sp_helpsrvrole
sp_defaultdb sp_helpsrvrolemember
sp_defaultlanguage sp_helpuser
sp_denylogin sp_password
sp_dropalias sp_remoteoption
sp_dropapprole sp_revokedbaccess
sp_dropgroup sp_revokelogin
sp_droplinkedsrvlogin sp_setapprole
sp_droplogin sp_srvrolepermission
sp_dropremotelogin sp_validatelogins
The following Transact-SQL statement grants login rights to the SALESLG local group:
/* Grant login. */
exec sp_grantlogin 'REDMOND\SALESLG'
Alternatively, login rights can be granted with the following Visual Basic code:
' Declare variables.
Dim oServer As SQLDMO.SQLServer
Dim oLogin As SQLDMO.Login
' Create a server object and connect.
Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.Connect ("SERVERNAME")
' Create the Login object.
Set oLogin = CreateObject("SQLDMO.Login")
' Set the appropriate properties.
oLogin.Name = "REDMOND\SALESLG"
oLogin.Type = SQLDMOLogin_NTGroup
' Add the Login object to the server's Logins collection.
oServer.Logins.Add oLogin
To allow a user access to SQL Server 2000 using non-trusted connections, you must create user accounts.
Note: When SQL Server 2000 is installed on Windows and configured to use Mixed Mode, capable clients can still make trusted connections.
The following Transact-SQL script creates a login for a non-trusted connection:
/* Add a login. */
exec sp_addlogin 'Bob', '45$#Jxew&fd2$Dw53987', 'pubs'
This statement adds a user called Bob and sets the password to password. The default database becomes pubs. The default database is the database to which the user is switched when attempting to log in. A user must still create a user account in the default database for this to work; sp_addlogin does not add a user account in the referenced database.
Alternatively, the above can also be achieved using Visual Basic:
' Declare variables.
Dim oServer As SQLDMO.SQLServer
Dim oLogin As SQLDMO.Login
' Create a server object and connect.
Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.Connect ("SERVERNAME")
' Create the Login object.
Set oLogin = CreateObject("SQLDMO.Login")
' Set the appropriate properties.
oLogin.Name = "Bob"
oLogin.Type = SQLDMOLogin_Standard
oLogin.SetPassword "","45$#Jxew&fd2$Dw53987"
' Add the Login object to the server's Logins collection.
oServer.Logins.Add oLogin
Securing Access to the Database Objects
You can grant permissions to roles and users, and assign the permissions to allow users to execute certain statements and to access certain database objects. Statement permissions restrict who can execute statements such as CREATE DATABASE, CREATE TABLE, or CREATE FUNCTION. Object permissions restrict access to objects such as tables, views, user-defined functions or stored procedures. Object permissions are dependent on the object being referenced. For example, object permission for tables include the SELECT, INSERT, UPDATE, DELETE, and REFERENCES permissions, while the object permissions on a stored procedure include EXECUTE permissions.
User-Defined Database Roles
In an ideal environment, roles would not be necessary. In such an environment, all users would be running SQL Server 2000 on Windows 2000 in Windows Authentication Mode. The database administrator could ask the Windows administrator to place all the users with a specific data access requirement (or role) into one Windows group, and the database administrator would then grant permissions to that Windows group as required. In some environments, however, Windows-level or domain-level management of permissions is not possible, so SQL Server roles are available to group users by their permission requirements.
Any Windows user or group can be assigned to a role, which can then be assigned permissions to database objects the same way as database users are assigned permissions.
Note: User-defined roles can be created only in a database. Fixed server roles and fixed database roles are predefined and cannot be modified.
Roles can be created with the following Transact-SQL code:
/* Add role for Telephone Operators. */
exec sp_addrole "TelephoneOperators"
Alternatively, roles can be created with the following Visual Basic code:
' Declare variables.
Dim oServer As SQLDMO.SQLServer
Dim oDbRole As SQLDMO.DatabaseRole
' Create a server object and connect.
Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.Connect ("SERVERNAME")
' Create the Database Role object.
Set oDbRole = CreateObject("SQLDMO.DatabaseRole")
' Set the appropriate properties.
oDbRole.Name = "TelephoneOperators"
' Add the Role object to the servers Role collection.
oServer.Databases("pubs").DatabaseRoles.Add oDbRole
After a user-defined database role is created, users, groups, or other roles are added to it. Roles can be nested, although not in a circular manner, as this would not be productive.
This sample Transact-SQL code adds a Windows user, a Windows group, and a database role to the newly created role:
/* Add a Windows user to the TelephoneOperators role. */
exec sp_addrolemember "TelephoneOperators", "REDMOND\Bob"
/* Add a Windows group to the TelephoneOperators role. */
exec sp_addrolemember "TelephoneOperators", "REDMOND\Sales"
/* Add HelpDeskOperators role to TelephoneOperators role. */
exec sp_addrolemember "TelephoneOperators", "HelpDeskOperators"
And again with SQL-DMO:
' Declare variables.
Dim oServer As SQLDMO.SQLServer
' Create a server object and connect.
Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.Connect ("MSNZBENTHOM")
' Use with statement for code legibility.
With oServer.Databases("pubs").DatabaseRoles("TelephoneOperators")
' Add the Windows user to the TelehoneOperators role collection.
.AddMember ("REDMOND\Bob")
' Add the Windows group to the TelehoneOperator's role collection
.AddMember ("REDMOND \Sales")
' Add the HelpDeskOperators role to TelehoneOperators role collection.
.AddMember ("HelpDeskOperators")
End With
Permissions System
The permissions system in SQL Server 2000 is based on the same additive model that forms the basis of Windows permissions. If a user is a member of the sales, marketing, and research roles (multiple group memberships), the user gets the sum of the respective permissions of each role. For example, if sales has SELECT permissions on a table, marketing has INSERT permissions, and research has UPDATE permissions, the user would be able to SELECT, INSERT, and UPDATE. However, as with Windows, if a particular role of which the user is a member has been denied a specific object permission (such as SELECT), the user is unable to exercise that permission. The most restrictive permission (DENY) takes precedence.
Granting and Denying Permissions to Users and Roles
Permissions within a database are always granted to database users, roles, and Windows users or groups, but never to SQL Server 2000 logons. The methods used to set the appropriate permissions for users or roles within a database are: granting permissions, denying permissions, and revoking permissions.
The DENY permission allows an administrator to deny an object or statement permission to a user or role. As with Windows permissions, DENY takes precedence over all other permissions.
For example, if some database users are frivolously changing data, it would not be fair to remove permissions from all users, as the majority of the users are using the data responsibly. It is possible to create a new role with a name like trouble_makers, and then DENY the INSERT, UPDATE, and DELETE operations on all tables for this role. As users misbehave, they are put into the trouble_makers role without regard for their other personal, group, or role permissions.
When permissions associated with an object are evaluated, the first step is a check for a DENY permission. If permission is denied, evaluation stops and access is not granted. If DENY is not present, the next step is to compare permissions associated with the object to those of the calling user or process. In this step, the GRANT permission or the REVOKE permission may appear. If permission is granted, evaluation stops and access is granted. When permission is revoked, previous GRANT or DENY permissions are deleted. Revoking permissions is thus not the same as denying permissions. The REVOKE permission deletes a previous GRANT or DENY. The DENY permission prohibits access even when access permissions have been granted because explicit DENY trumps all other permissions.
In this section, each of these methods will be applied in a Visual Basic example and in a Transact-SQL example. The following Transact-SQL code grants Bob and Jane permissions to SELECT from the authors table, and grants Jane permissions to INSERT into the titles table:
/* Grant permissions to SELECT. */
GRANT SELECT
ON authors
TO Bob, [REDMOND\Jane]
GO
/* Grant permissions to INSERT. */
GRANT INSERT
ON titles
TO [REDMOND\Jane]
GO
The previous example shows how the GRANT statement works when permissions are granted to explicit users of the database (Bob) and when permissions are granted to a Windows user (Jane).
Here is the same example in Visual Basic:
' Declare variables.
Dim oServer As SQLDMO.SQLServer
' Create a server object and connect.
Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.Connect ("SERVERNAME")
' Grant Jane and Bob permissions to select from the authors table.
oServer.Databases("pubs").Tables("authors").Grant SQLDMOPriv_Select, "Bob"
oServer.Databases("pubs").Tables("authors").Grant SQLDMOPriv_Select, _
"[REDMOND\Jane]
' Grant Jane permissions to select from the authors table.
oServer.Databases("pubs").Tables("authors").Grant SQLDMOPriv_Select, _
"[REDMOND\Jane]"
In the previous examples, there is little difference between granting access to a user by fully qualifying their domain name, and directly granting access to a user who already has permissions to access the database. Due to these similarities, the following examples will show only the code for existing database users.
The following Transact-SQL statement shows how a user can be denied SELECT permissions:
/* Deny permissions to SELECT. */
DENY SELECT
ON authors
TO Bob
GO
And again using Visual Basic:
' Declare variables.
Dim oServer As SQLDMO.SQLServer
' Create a server object and connect.
Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.Connect ("SERVERNAME")
' Deny Bob permissions to select from authors table.
oServer.Databases("pubs").Tables("authors").Deny SQLDMOPriv_Select, "Bob"
Here is a Transact-SQL example that shows how to revoke permissions from a user:
/* Revoke permissions to SELECT. */
REVOKE SELECT
ON authors
FROM Bob
GO
Here is the Visual Basic code:
' Declare variables.
Dim oServer As SQLDMO.SQLServer
' Create a server object and connect.
Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.Connect ("SERVERNAME")
' Revoke Bob permissions to select from the authors table.
oServer.Databases("pubs").Tables("authors").Revoke SQLDMOPriv_Select, "Bob"
Ownership Chains
When multiple database objects access each other sequentially, the sequence is known as a "chain." Although such chains have no independent existence, when SQL Server is traversing the links in a chain it evaluates user permissions on the constituent objects differently than it would if it were accessing them separately. These differences have important implications for managing security.
How permissions are checked in a chain
When an object is accessed through a chain, SQL Server first compares its owner to the owner of the calling object (the previous link in the chain). If both objects have the same owner, permissions on the referenced object are not evaluated.
Example of ownership chaining
In Figure 3 below, the July2003 view is owned by Mary. She has granted to Alex permissions on the view. He has no other permissions on database objects in this instance. What happens when Alex selects the view?
1. Alex selects * on the July2003 view. SQL Server checks permissions on the view and confirms that Alex has permission to select on it.
2. The July 2003 view needs information from the SalesXZ view. SQL Server checks the ownership of SalesXZ view. Since it has the same owner (Mary) as the view that calls it, its permissions are not checked. Required information is returned.
3. SalesXZ view needs information from InvoicesXZ view. SQL Server checks the ownership of InvoicesXZ view. Since it has the same owner as the previous object, its permissions are not checked. Required information is returned. To this point, all items in the sequence have had a single owner (Mary). This is known as an "unbroken ownership chain."
4. InvoicesXZ view needs information from AcctAgeXZ view. SQL Server checks the ownership of AcctAgeXZ view. Since it has a different owner than the previous item (Sam, not Mary), full information about permissions on this view is retrieved. If AcctAgeXZ view has permissions that allow access by Alex, information will be returned.
5. AcctAgeXZ view needs information from ExpenseXZ table. SQL Server checks the ownership of ExpenseXZ table. Since it has a different owner than the previous item (Joe, not Sam), full information about permissions on this table is retrieved. If ExpenseXZ table has permissions that allow access by Alex, information is returned.
6. When July2003 view attempts to retrieve information from ProjectionsXZ table, the server first checks to see whether cross-database chaining is enabled between Database 1 and Database 2. If it is enabled, the server will check the ownership of ProjectionsXZ table. Since this table has the same owner as the calling view (Mary), its permissions are not checked. Requested information is returned.
Figure 3:
Value of ownership chaining
Ownership chaining makes it possible to manage access to multiple objects (for example, multiple tables) by setting permissions on a single object (for example, a view). It also offers a slight performance advantage in scenarios that permit the skipping of permission checks.
Cross-database ownership chaining
SQL Server can be configured to permit ownership chaining between specific databases or across all databases inside a single instance. Cross-database ownership chaining is disabled by default, and should not be enabled unless it is specifically required.
Potential threats
Ownership chaining is very useful in managing permissions on a database, but it does assume that object owners anticipate the full consequences of every decision to grant permission on an object. In Figure 3 Mary owns most of the July2003 view's underlying objects. Since she has the right to make objects she owns accessible to any other user, SQL Server behaves as though whenever Mary grants access to the first view in a chain she has made a conscious decision to share the views and table it references. In real life, this might not be a valid assumption. Production databases are far more complex than the one in Figure 3 and the permissions that regulate access to them rarely map perfectly to the administrative structures of the organizations that use them.
It is important to understand that members of highly privileged database roles can use cross-database ownership chaining to access objects in databases external to their own. For example, if cross-database ownership chaining is enabled between database A and database B, a member of the db_owner fixed database role of either database can spoof her way into the other database. The process is simple: Diane (a member of db_owner in database A) creates user Stuart in database A. Stuart already exists as a user in database B. Diane then creates an object (owned by Stuart) in database A that calls any object owned by Stuart in database B. Since the calling and called objects have a common owner, permissions on the object in database B will not be checked when Diane accesses it through the object she has created.
Note: Ownership chaining applies to Data Manipulation Language (DML) operations (SELECT, UPDATE, INSERT, DELETE) but not to Data Definition Language (DDL) operations.
SQL Server 2000 SP3 Security Features and Best Practices: Implementation of Server Level Security and Object Level Security
Implementation of Server Level Security and Object Level Security
Updated: May 16, 2003
On This Page
Use of Security Identifiers (SIDs)
Generation of GUIDs for Non-Trusted Users
Renaming Windows User or Group Accounts
Views of the sysxlogins System Table
Implementation of Object Level Security
Secure Server Installation
Secure Server Administration
Use of Security Identifiers (SIDs)
SQL Server 2000 checks whether the security identifier (SID) or group membership SIDs of the user have been specifically denied access to the server. If so, the user will not be granted access to the server. If the user is not specifically denied access, the server checks whether the user has been granted access directly or by virtue of a group membership. If access has been granted, the connection to SQL Server 2000 is maintained. The user then proceeds to the appropriate default database (where the user must also have been granted access). The access rights of the user are then checked for any objects the user is attempting to access. If access has not been granted for a particular set of logon credentials, the connection to the server is terminated.
When a Windows user or group is either granted or denied access to SQL Server 2000, this information is stored in the sysxlogins system table (discussed later). SQL Server 2000 identifies users connecting through a trusted connection by their SID and group membership SIDs.
Top of page
Generation of GUIDs for Non-Trusted Users
For non-trusted connections, such as when SQL Server 2000 is installed on the Windows 98 operating system, Windows SIDs are not available. In this case, SQL Server 2000 generates a 16-byte globally unique identifier (GUID). The generated GUID is then used internally in the same way as Windows SIDs are used for Windows users and groups.
Top of page
Renaming Windows User or Group Accounts
When a Windows user or group is renamed using the User Manager for Domains tool in Windows NT 4.0 or the Active Directory Users utility, SQL Server 2000 is unaware of that change. SQL Server 2000 maintains the fully qualified name of the user or group in the sysxlogins table for performance reasons, as it can be very slow to query the domain controller for this information. This is true when many name lookups are done or the domain controller is connected over a slow WAN link.
The fact that the names of SQL Server 2000 users and groups may differ from those of Windows users and groups does not cause any security problems. The permissions set for the user or the group continue to function correctly, as SQL Server relies only on the SIDs internally.
When the SUSER_SNAME() and SUSER_SID() functions are used to return the login name and SID of the user, respectively, they first query the sysxlogins table. The Windows Local Security Authority (LSA) is queried only if the sysxlogins table does not contain the username or SID.
Another effect of using these functions is that the usernames in system messages may not report an up-to-date name.
Top of page
Views of the sysxlogins System Table
The sysxlogins system table contains information about logins of users. This system table, which exists only in the master database, should be accessed only through these views:
• Syslogins — provides information on SQL Server logins, and interprets the status column so that it can be understood more readily.
• sysremotelogins — contains one row for each remote user allowed to call remote stored procedures on SQL Server.
• sysoledbusers — contains one row for each user and password mapping for the specified linked server.
Top of page
Implementation of Object Level Security
How Permissions Are Checked
SQL Server 2000 uses SIDs to identify Windows users and groups. However, due to the length of SIDs (up to 85 bytes), SQL Server 2000 maps the SIDs to SQL Server user IDs inside each database. The SIDs are mapped to user IDs in the sysusers table. The SQL Server user ID is then used in the sysobjects table to denote the owner of a table. It is also used in the syspermissions table to set permissions on objects, and in the systypes table to denote the owner of a user-defined type.
When a Windows user connects to SQL Server 2000, the server creates a Process Status Structure (PSS) in memory, comprising the SID of the user, group SIDs, and other security and state information. This structure is a snapshot taken when the user connects, and the snapshot is not refreshed. This structure exists per session connecting to the server; a single Windows user establishing multiple sessions with SQL Server 2000 will have multiple PSS structures.
When a user attempts to access a database, SQL Server checks the sysusers table to determine whether the user has been denied access directly or by virtue of being a member of a group that has been denied access. If the user is denied access, this is enforced. Otherwise, the sysusers table is checked again, and all user IDs for which the user qualifies are collected. If a user has been granted access to the database, the sysmembers table is scanned, so that all the role memberships of the user can be established. The user IDs of all the applicable memberships are established so that the appropriate permissions for this user can be applied. Unlike the PSS structures, this information is not persisted.
When the SQL Server user starts accessing objects in the database, the applicable permissions are determined by checking the syspermissions table for entries with matching user IDs (as identified earlier). The system checks DENY permissions first, and if found, the user will not get the requested access to the object. However, if no DENY permissions are found, and entries that give the user the required access exist, access is granted. The effective access permissions are then cached for performance reasons.
Cost of Changing Permissions
As stated earlier, SQL Server 2000 caches the object permissions on a per-session basis to avoid incurring the cost of checking permissions for repeated access of the same objects. Unlike the PSS, which does not change the security information after it is created, the permissions cache is always kept current. This is implemented by versioning.
When permissions are first checked, a version number is assigned. When the permissions on an object are changed, SQL Server 2000 increments the version counter. Whenever an object is accessed, the value of this permission counter is checked. If it differs from the cached value, the content of the cache is discarded, and the object permissions are re-established.
Renaming Windows User or Group Accounts
With SQL Server 2000, you can grant Windows users and groups permissions to access objects in the database directly. In that case, the SID and Windows user or group names are stored in the sysusers table.
When the Windows administrator renames the Windows group or user, the name change is not propagated to SQL Server 2000. It is important to understand the reasons for this.
In SQL Server 2000, as with earlier versions, administrators and developers are writing numerous stored procedures, Transact-SQL scripts, triggers, and so on. Assume that Susie Jones is a user who creates a table in the database. Her login name is SUSIEJ, and her table is named SUSIEJ.SALESDEMO. Susie grants permissions for others to access her table, and several of her colleagues create views and stored procedures based on her table. When Susie gets married to Bob Taylor, her username is renamed to SUSIET. If SQL Server 2000 were to pick up the change, her table would suddenly be SUSIET.SALESDEMO, which is a completely different object. The views, stored procedures, and any code that was written to access this table would break. This is why SQL Server 2000 does not automatically rename user accounts when the Windows account in the Windows User Directory is renamed.
WITH GRANT Option
The WITH GRANT option is optional syntax that can be used with the GRANT statement. This option applies only to object permissions, and allows the recipient of the GRANT statement to pass on that permission.
For example, if Bob granted Jane SELECT permissions and used the WITH GRANT OPTION, Jane would be able to grant SELECT permissions to some of her friends.
When Bob revokes SELECT permissions from Jane, he can use the CASCADE option to revoke the SELECT permissions from those friends to whom Jane had granted SELECT permissions.
sysusers System Table
In some ways, the sysusers table is to the database what the sysxlogins table is to the instance. The sysusers table exists in each database, and contains information about who is granted or denied access to the database.
hasdbaccess Column
The hasdbaccess column of the sysusers table is used in a similar way to the hasaccess column in the sysxlogins table. The value in this column will be set to zero if the corresponding user has not been granted rights to access the database but creates objects, is explicitly granted permissions on database objects, or is added explicitly to a role.
Objects created by a user are normally owned by that user, and are not owned by the group through which the user was granted access to the database. This direct ownership eliminates ambiguity when a user who creates an object is a member of more than one group with the necessary permissions. There is an exception to this rule. An object can be directly owned by a role or Windows group if the user who creates it explicitly assigns ownership to the role or group when she creates the object. For example:
CREATE TABLE [builtin\administrators].test_table(acolumn VARCHAR(2))
In this situation, an entry for the user must exist in the sysusers table so that the object can have the appropriate owner. The entry is created automatically, but the user does not get explicit access to the database automatically because the hasaccess flag is set to zero.
Roles, which are also listed in the sysusers table, have the hasdbaccess column set to zero.
sysmembers System Table
The sysmembers system table is used to record the membership of users in database roles. It is one of the smaller tables, containing only two columns, and one row for each member of a database role.
SQL Server 2000 places a user's first membership of a role into the gid column of the sysusers table. Thus, when SQL Server 2000 tries to identify all the roles to which a member belongs, it does not have to query the sysmembers table if the gid column of the sysusers table contains zero. If the entry in that column is not zero, the entry specifies one of the roles and the sysmembers table must be queried for a complete list of all the roles to which the user belongs.
syspermissions System Table
The syspermissions system table, which exists in every database, is used to track permissions that have been granted or denied to users.
The syspermissions system table consists of very few columns. The id column references the object ID for which the permissions are being granted or denied. For statement permissions, this column is set to zero.
The columns grantee and grantor are self-explanatory. The value used here is the ID of the role, Windows user, or Windows group as it is found in the sysusers table.
The actadd column refers the positive permission (or permissions granted) on all columns (in the case of a table) of the object, while the actmod column refers to the negative (or permissions denied) permissions on all columns (in the case of a table) of the object.
The remaining columns are used only when column-level permissions are implemented. The seladd column is for SELECT granted permissions, and is a bitmap of the columns that have been granted this permission. As column IDs are never reused, the bitmap approach works very well. The selmod column is for SELECT permissions denied.
The next two columns are implemented in the same way as the previous two, except that they apply to UPDATE permissions.
The last two columns refer to the REFERENCES permissions, and are implemented in the same way as the previous four columns.
Named Pipes and Multiprotocol Permissions
The Named Pipes Net-Library is an inter-process communications (IPC) mechanism, which is implemented over the IPC$ share on Windows. Thus, when a client connects to SQL Server using the Named Pipes Net-Library, the connection is made to the IPC$ share, at which point authentication takes place. After Windows has authenticated the client (in the same way as it would for access to any other resource), the Named Pipes session is established over the IPC$ share. This takes place before any attempt is made to pass the connection to SQL Server.
All users who will be connecting to SQL Server 2000 through the Named Pipes Net-Library must have a Windows account and Windows permissions to access the IPC$ share. If you do not want this authentication to take place, switch to another network library such as TCP/IP Sockets or Multiprotocol, as these connections are not validated against the Windows IPC$ share. Again, note that TCP/IP Sockets is the default network library in SQL Server 2000.
When using the Multiprotocol Net-Library, Windows authentication also takes place before SQL Server 2000 passes the connection. This is because the remote procedure call (RPC) runtime services authenticate the client when the connection is requested. In much the same way as with the Named Pipes Net-Library, the Multiprotocol Net-Library requires a valid Windows account.
Note that the multiprotocol network library cannot be used to connect to named instances of SQL Server 2000.
Top of page
Secure Server Installation
Physical and logical isolation make up the foundation of SQL Server security. Machines hosting a database should be in a physically protected location, ideally a locked machine room with monitored flood detection and fire detection/suppression systems. Databases should be installed in the secure zone of your corporate intranet and never directly connected to the Internet. Back up all data regularly and store copies in a secure off-site location. For guidance on backup procedures and other operational best practices, see SQL Server 2000 Operations Guide.
File System
NTFS is the preferred file system for installations of SQL Server. It is more stable and recoverable than FAT file systems, and enables security options such as file and directory access control lists (ACLs) and file encryption (EFS). During installation, SQL Server will set appropriate ACLs on registry keys and files if it detects NTFS. These permissions should not be changed.
With EFS, database files are encrypted under the identity of the account running SQL Server. Only this account can decrypt the files. If you need to change the account that runs SQL Server, you should first decrypt the files under the old account, then re-encrypt them under the new account.
SQL Server 2000 setup automatically grants the service account(s) full control permissions to the SQL Server-related files, as well as full control to the local administrators group.
Firewalls
Divide your network into security zones separated by firewalls. Block all traffic, then selectively admit only what is needed. Before installing SQL Server, you should insure that your perimeter firewall filters out packets addressed to TCP port 1433 (monitored by the default instance) and User Datagram Protocol (UDP) port 1434 (monitored by one of the instances on the computer). Additional ports associated with named instances should also be blocked at the perimeter.
If you are installing the server inside a Windows domain, you should configure interior firewalls to permit Windows authentication. Open ports used by Kerberos or NTLM authentication. If your application uses replication, you should consult the "Configuring a Publisher or Distributor to Listen on TCP/IP" topic in SQL Server 2000 Books Online. If your application uses distributed transactions, you may also need to configure your firewall to allow Microsoft Distributed Transaction Coordinator (DTC) traffic to flow between separate DTC instances, and between the DTC and resource managers such as SQL Server. For more information, see 250367 "Configuring Microsoft Distributed Transaction Coordinator (DTC) to Work Through a Firewall".
Disabling NetBIOS and SMB
Servers in the perimeter network should have all unnecessary protocols disabled including NetBIOS and server message block (SMB). Web servers and Domain Name System (DNS) servers do not require NetBIOS or SMB. These protocols should both be disabled to counter the threat of user enumeration.
NetBIOS uses the following ports:
• UDP/137 (NetBIOS name service)
• UDP/138 (NetBIOS datagram service)
• TCP/139 (NetBIOS session service)
SMB uses the following ports:
• TCP/139
• TCP/445
On servers accessible from the Internet, you should disable SMB by removing File and Printer Sharing for Microsoft Networks and Client for Microsoft Networks using the Transmission Control Protocol/Internet Protocol (TCP/IP) properties dialog box in your Local Area Connection properties.
To disable SMB
1. On the Start menu, point to Settings, and then click Network and Dial-up Connections.
2. Right-click Internet facing connection, and then click Properties.
3. Select Client for Microsoft Networks, and then click Uninstall.
4. Follow the uninstall steps.
5. Select File and Printer Sharing for Microsoft Networks, and then click Uninstall.
6. Follow the uninstall steps.
To disable NetBIOS over TCP/IP
1. Right-click My Computer on the desktop, and then click Manage.
2. Expand System Tools, and then select Device Manager.
3. Right-click Device Manager, point to View, and then click Show hidden devices.
4. Expand Non-Plug and Play Drivers.
5. Right-click NetBios over Tcpip, and then click Disable.
This disables the SMB direct host listener on TCP/445 and UDP 445.
Note: This procedure disables the nbt.sys driver. The WINS tab of the Advanced TCP/IP Settings dialog box contains a Disable NetBIOS over TCP/IP option. Selecting this option only disables the NetBIOS Session Service (which listens on TCP port 139). It does not disable SMB completely. To do so, follow the steps listed above.
Before Running SQL Server Setup
When planning a SQL Server installation, isolate components from each other. This will minimize the chance that if one component is compromised it could be used to further compromise other components in your system. Your goal is to avoid exposing your server to the vulnerabilities of other software, and vice versa. The following constitute best practices:
• Use a dedicated computer as a database server.
• Do not install SQL Server on a domain controller.
• Do not run Microsoft Internet Information Server (IIS) and SQL Server on the same computer.
• If you must run multiple services on a single computer, run each service under its own Windows account.
• Install SQL Server program and database files on a non-system volume, separate from the operating system.
What Does SQL Server Install?
When you install SQL Server, a number of Windows services are installed in addition to program and data files, which by default will be located under \Program Files\Microsoft SQL Server. The following table lists the services and folders that may be created, depending on your choices while running Setup.
Note: The following discussion focuses on SQL Server proper. For detailed discussions of Replication, Analysis Services, or Data Transformation Services, see SQL Server 2000 Books Online.
Item Details
Services MSSQLSERVER
MSSQLServerADHelper
Microsoft Search
SQLSERVERAGENT
Folders \program files\Microsoft SQL Server\mssql\binn (program files)
\program files\Microsoft SQL Server\mssql\data (data files including .mdf, .log and .ndf)
\program files\Microsoft SQL Server\80\tools (shared tools/books online)
\program files\Microsoft SQL Server\mssql\logs (error logs)
\program files\Microsoft SQL Server\mssql\backup (backup files)
\program files\Microsoft SQL Server\mssql\jobs (temp job output files)
For named instances, the instance name is used in the file path:
\program files\Microsoft SQL Server\MSSQL$InstanceName\binn
\program files\Microsoft SQL Server\MSSQL$InstanceName\data
Installing SQL Server
During the SQL Server setup, select the custom setup option. This allows you to choose which items to install. You should install only required components on a production database server. The following items should be omitted.
Tool Purpose
Upgrade tools Used to upgrade SQL Server version 6.5 databases
Performance counters Used for performance monitoring and optimization
Books Online SQL Server documentation
Development tools Includes headers and library files used by C developers, together with MDAC and XML SDKs and an interface for stored procedure debugging
Code samples Sample code used for developer education
Whenever possible, you should require Windows Authentication Mode for connections to SQL Server. This will shield your SQL Server installation from most Internet-based attacks by restricting connections to Windows user and domain user accounts. Your server will also benefit from Windows security enforcement mechanisms, such as stronger authentication protocols and mandatory password complexity and expiration. In addition, credentials delegation (the ability to bridge credentials across multiple servers) is only available when using Windows Authentication Mode and Kerberos. On the client side, Windows Authentication Mode eliminates the need to store passwords, which is a major vulnerability in applications that use standard SQL Server logins.
During installation, you will be required to set a password on the sa account. The sa account should always have a strong password, even on servers that are configured to require Windows Authentication. This will ensure that a blank or weak sa password is not exposed in the future if the server is reconfigured for Mixed Mode Authentication.
Do not plan to use the sa account for day-to-day administration. Microsoft recommends that SQL Server administrators be granted access to SQL Server through membership in a Windows group that is a member of the sysadmin fixed server role in SQL Server. This approach has one minor drawback: Windows administrators can add any user account to a Windows group. It follows that if a Windows group is a member of the sysadmin fixed server role, the Windows administrators will be able to grant anyone sysadmin permissions on SQL Server 2000 by adding their user account to that Windows group. If this is a concern, you should add only individual Windows user accounts to the sysadmin fixed server role.
Services
SQL Server 2000 and SQL Server Agent run as Windows services. Each service must be associated with a Windows account, from which it derives its security context. SQL Server allows members of the sysadmin fixed server role to access operating system features. These operating system calls are made with the security context of the Windows account that owns the server process. If the server is cracked, these operating system calls may be used to extend the attack to any other resource to which the owning Windows process (the SQL Server service account) has access. For this reason, it is important to grant only necessary privileges to SQL Server services. The following settings are recommended.
SQL Server Engine/MSSQLServer
This is the SQL Server database engine and is the only mandatory service. Run as a Windows domain user account with regular user privileges. Do not run as local system, local administrator, or domain administrator accounts. If there are named instances, they will be named MSSQL$InstanceName.
SQL Server Agent Service/SQLServerAgent
This is a support service that allows you to schedule commands and notify operators when errors occur. Disable if not required in your environment; otherwise run as a Windows domain user account with regular user privileges. Do not run as local system, local administrator, or domain administrator accounts. If there are named instances, they will be named SQLAgent$InstanceName.
Important: SQL Server Agent will need local Windows administrator privileges if one of the following is true:
SQL Server Agent connects to SQL Server using SQL Server Authentication (not recommended).
SQL Server Agent uses a multiserver administration master server (MSX) account that connects using SQL Server Authentication.
SQL Server Agent runs Microsoft ActiveX® script or CmdExec jobs owned by users who are not members of the sysadmin fixed server role.
SQL Server Active Directory Helper/MSSQLServerADHelper
This service supports Active Directory integration, including database instance registration. Disable if not required in your environment; otherwise run as a Windows local system account.
Microsoft Search
This service provides full-text search capabilities. Disable if not required in your environment; otherwise run under the local system account.
Microsoft DTC
This service coordinates the processing of transactions distributed across two or more servers. Disable if not required in your environment.
If you need to change the account associated with a SQL Server service, use SQL Server Enterprise Manager. Enterprise Manager will set appropriate permissions on the files and registry keys used by SQL Server. Never use the Services applet of Microsoft Management Console (in Control Panel) to change these accounts, because this requires manual adjustment of dozens of registry permissions, NTFS file system permissions, and Windows user rights — a process that is prone to error.
For more information, see 283811 "HOW TO: Change the SQL Server Service Account Without Using SQL Enterprise Manager in SQL Server 2000,". Changes to account information will take effect the next time the service is started. If you need to change the account associated with SQL Server and SQL Server Agent, you must apply the change to both services separately using Enterprise Manager.
SQL Server Accounts
Authorization in SQL Server is managed through SQL Server logins, database users, and a variety of roles. Logins grant access to SQL Server but not to individual databases. Logins are associated with database users to provide access to individual databases. Database users are generally placed in fixed or user-defined roles, and permissions are granted to the roles to determine the capabilities of the login within the database.
Use the following recommendations to ensure you apply stringent authorization settings in the database or instance of SQL Server.
Remove the BUILTIN\Administrators Server Login
By default, the BUILTIN\Administrators local Windows group is added to the sysadmin fixed server role, the members of which administer SQL Server. This means that Windows domain or system administrators who are members of BUILTIN\Administrators have unrestricted access to the SQL Server database. Most companies assign different employees to the domain and system administrator roles. If you want do this, remove the BUILTIN\Administrators SQL Server login and create a Windows group for database or system administrators.
To add a new login for database administrators
1. Start Enterprise Manager.
2. Expand Microsoft SQL Server, SQL Server Group, and then your SQL Server.
3. Expand the Security folder, select and right-click Logins, and then click New Login.
4. In the Name field, enter a custom Windows group that contains only database administrators.
5. Click the Server Roles tab and select System Administrators.
This adds the new login to the sysadmin server role.
To delete the BUILTIN\Administrators login
1. Start Enterprise Manager.
2. Expand Microsoft SQL Server, SQL Server Group, and then your SQL Server.
3. Expand the Security folder, and select Logins. If BUILTIN\Administrators appears in the list of logins, right-click it, and then click Delete to remove the login.
For more information, see 263712 "How to Impede Windows NT Administrators from Administering a Clustered SQL Server". For more information on re-configuring the SQL service accounts after the installation, see Changing Passwords and User Accounts.
Do Not Enable the SQL Guest User Account
The guest user account allows a login without a user account to access a database. By default, the guest account is not enabled in any of the user databases. Do not change this setting. Enabling the guest user in a database allows authenticated logins that have not been explicitly granted access to the database to enter the database using the guest credentials.
To check the status of the guest account, open the Users folder in the Computer Management tool. The guest account should be displayed with a cross icon. If it is not disabled, open its Properties dialog box and select Account is disabled. You should also remove the guest account from all user-defined databases. Note that you cannot remove guest from the master, tempdb, or distribution databases created during replication.
Do Not Grant Permissions to the Public Role
All databases contain a public database role of which all users, groups and roles are members. Do not grant additional permissions to this role. Granting permissions to public effectively grants them to all users of the database, including application roles, database roles, and individual users. The best practice is to grant specific, required permissions to select users, roles, or groups.
Encrypting Data Files with EFS
If your server is installed on an NTFS file system, you may choose to encrypt some data files for additional security. You should evaluate the performance cost of encryption on a test server, before implementing it on your production server. Usually the performance impact is negligible, because data files are decrypted when the server process starts.
Encrypt only at the file level, not the directory level. While it is often a best practice to encrypt at the directory level when using EFS so that new files added are encrypted, you should encrypt your SQL Server data files at the file level only. This avoids encrypting your log files.
To encrypt a file with EFS
1. Right-click on the file.
2. Select Properties.
3. Click Advanced.
4. Select Encrypt contents to be secure.
Choosing Static Ports for Named Instances
The default instance of SQL Server listens on TCP port 1433 by default. Named instances, however, are dynamically assigned TCP ports by Windows when the SQL Server service is started. It is possible, however, that multiple restarts could change this association of named instances to dynamically assigned TCP ports.
Both Kerberos and the SQL Server delegation functionality require that the SQL Server service have a service principal name (SPN) registered against it. Part of the SPN is the port number. Assigning static TCP ports to named instances is therefore highly recommended. In addition to enabling these important security features, static TCP ports are easier to manage when configuring internal firewalls.
Note: Never open SQL Server ports on your external firewall.
Use the Server Network Utility to assign a static TCP port to each named instance. You must pick TCP ports that are not used by other applications running on the same computer or cluster. For a list of well-known ports registered for use by various applications, see http://www.iana.org/. It is a good idea to include your network administrator in the planning of port assignments.
To assign a static TCP port to a named instance
1. Start the SQL Server Network Utility.
2. Select the General tab.
3. Select the named instance from the drop down menu.
4. In the Enabled Protocols dialog box, click TCP/IP.
5. Click the Properties button.
6. Enter the desired static port number and click OK.
You must restart the server for these changes to take effect.
Delete or Secure Old Setup Files
SQL Server setup files may contain information that can make you vulnerable to attack, such as:
• Credentials that are weakly encrypted.
• Credentials that appear in plain text (no encryption at all).
• Sensitive configuration settings.
In SQL Server 2000, the following files may be affected: sqlstp.log, sqlsp.log, and setup.iss in the
If the current system is an upgrade from SQL Server version 7.0 installations, the following files should be checked as well: setup.iss in the %Windir% folder, and sqlsp.log in the Windows Temp folder.
Microsoft distributes a free utility, Killpwd, which will locate and remove these passwords from your system. To learn more about this free download, see Microsoft Knowledge Base article 263968, "Service Pack Installation May Save Standard Security Password in File".
Top of page
Secure Server Administration
Security is an ongoing responsibility for database administrators. The points covered below are especially important.
Install All Service Packs and Security Patches
The best way to maintain the security of your server is to install all security patches and service packs as they are released. To subscribe to e-mail notifications of new security patches, sign up for the Microsoft Security Notification Service.
Monitor Your Server with Microsoft Baseline Security Analyzer (MBSA)
MBSA is a tool that scans for common insecure configurations in several Microsoft products, including SQL Server and Microsoft SQL Server 2000 Desktop Engine (MSDE 2000). It can be run locally or over a network. It tests SQL Server installations for problems such as:
• Too many members of the sysadmin fixed server role.
• Granting of right to create CmdExec jobs to roles other than sysadmin.
• Blank or trivial passwords.
• Weak authentication mode.
• Excessive rights granted to the Administrators group.
• Incorrect ACLs on SQL Server data directories.
• Plain-text sa password in setup files.
• Excessive rights granted to the guest account.
• SQL Server running on a system that is also a domain controller.
• Improper configuration of the Everyone group, providing access to certain registry keys.
• Improper configuration of SQL Server service accounts.
• Missing service packs and security updates.
MBSA can be run from the graphical user interface (GUI) or the command line. The GUI version is the best option for most users, since it is easier to use and generates a detailed report in XML format.
To execute the command line version type the following at the command prompt:
cd "c:\program files\microsoft baseline security analyzer"
mbsacli
MBSA reports refer to Microsoft Security Bulletins, which describe potential problems and include links to security patches.
Microsoft distributes MBSA as a free download. For complete documentation and the latest version of MBSA, see Microsoft Baseline Security Analyzer.
Auditing
SQL Server supports several levels of auditing of both events and states. At a minimum, you should log failed connection attempts to SQL Server and review the SQL error log regularly. When possible, save the SQL error logs to a different hard drive than the one on which data files are stored. Configure login auditing using SQL Server Enterprise Manager.
Login Auditing
To enable auditing of failed connections with Enterprise Manager in SQL Server:
1. Expand a server group.
2. Right-click a server, and then click Properties.
3. On the Security tab, under Audit Level, click Failure.
You must stop and restart the server for this setting to take effect.
General Auditing
SQL Server 2000 supports definition of audit events and columns, and analysis of audit logs with a GUI-based tool: SQL Profiler. Events that can be captured and analyzed include the following:
• End user activity (all SQL commands, Logout/Login, enabling of application roles).
• Administrator activity (DDL, other than GRANT/REVOKE/DENY and security events, and database or server configuration).
• Security events (GRANT/REVOKE/DENY, login user/role add/remove/configure).
• Utility events (backup/restore/bulk insert/bcp/DBCC commands).
• Server events (shutdown, pause, start).
• Audit events (add audit, modify audit, stop audit).
If you want auditing to start whenever SQL Server starts, you should script the audit, wrap it in a stored procedure, and mark that stored procedure for AutoStart.
For more information, see SQL Server 2000 Auditing, and the "Using Audit Logs" topic at SQL Server 2000 Books Online or Using Audit Logs.
C2 Auditing
SQL Server can be configured to perform the level of auditing required for C2 certification under the Trusted Database Interpretation (TDI) of the Trusted Computer System Evaluation Criteria (TCSEC) of the United States National Security Agency. This level of auditing, known as "C2 audit mode," can be enabled using the "c2 audit mode" advanced option of sp_configure stored procedure. C2-level auditing can produce a very large amount of data in a short period; therefore, this option should not be selected unless absolutely required. To preserve the integrity of the C2-level audit trail, SQL Server will shut itself down if its log files exceed available storage when running in this mode. For more information, see Microsoft SQL Server 2000 C2 Evaluation.
SQL Server 2000 SP3 Security Features and Best Practices: Secure Multi-tier Deployment
Secure Multi-tier Deployment
Updated: May 16, 2003
SQL Server is often deployed as part of a multi-tier system of applications that communicate among themselves over a network. The simplest version of such installations includes three layers:
• Web Server — User-facing Web application or client.
• Application Server — Business logic, pre-processing of batches prior to transmission to database; may include Web server (ASP.NET/IIS). This layer is also known as "the middle tier."
• Database Server — Database (SQL Server). This layer is sometimes called "the back end."
Figure 4 shows security vulnerabilities in a simple multi-tier system.
Figure 4: Simple multi-tier system, indicating major points of vulnerability
See full-sized image.
A full discussion of security for such complex systems is beyond the scope of this paper. For more information on n-tier deployment, see Building Secure ASP.NET Applications: Authentication, Authorization, and Secure Communication.
Master/Target Servers
In a master/target (MSX/TSX) relationship there must be at least one master server and at least one target server. The master server distributes jobs to its target servers. The master server stores the central (authoritative) copy of job definitions for jobs that run on its target servers. The target servers connect periodically to their master server to update their list of pending jobs. When configuring a server to operate as a master or target, observe the following best practices.
• MSX/TSX should only be deployed inside your corporate firewall. Never expose a server running as a master or a target directly to the Internet.
• Run the MSX SQL Server Agent as a Windows domain user without Windows administrator privileges. Enterprise Manager assigns the correct privileges when it designates an account as the SQL Agent service account.
Note: If you include ActiveX® script or CmdExec jobs in your batches, SQL Agent must be a member of the Windows administrator group.
If an MSX server was upgraded from a version of SQL Server prior to SP3, it may retain some jobs owned by old SQL Server Agent probe accounts (
Top of page
SSL and IPSec
To defend the privacy and integrity of data transmitted across a network, you should use either IPSec encrypted communication channels or SSL connections to the database. Figure 5 shows the mapping of OSI layers to the TCP/IP stack.
Figure 5:
See full-sized image.
IPSec is a network-level encryption and authentication protocol designed to protect network traffic from address spoofing, eavesdropping, and session hijacking. Since it operates near the bottom of the OSI stack, it is invisible to SQL Server. No SQL Server-level configuration is required to use IPSec, but an understanding of public and private keys will be helpful in planning your deployment. See Public/Private Key Pairs.
SSL works at the boundary between the Session and Transport layers of the OSI model, allowing clients and servers to negotiate shared session keys. It provides encryption but not authentication. SSL communication is only possible between Application layers that have been specifically designed to use this protocol. Client applications running on top of the MDAC stack (version 2.6 or above) do not need to be SSL-aware, as this functionality is supplied by MDAC.
A best practice is to force connections that are capable of using SSL to do so. This is especially important when using Mixed-Mode Authentication. To force encryption on SSL-aware applications that connect to your server, apply an SSL certificate to the server and select the Force Protocol Encryption option on enabled protocols in the Server Network Utility. For more information about implementing SSL and IPSec, including additional configuration options, see How To: Use IPSec to Provide Secure Communication Between Two Servers and How To: Use SSL to Secure Communication with SQL Server 2000.
Top of page
Authentication
Application tiers that exist within a single Windows domain should always use Windows authentication when communicating with each other.
Use Window Authentication
In this authentication mode, credentials are managed for you and are not transmitted over the network. You also avoid embedding user names and passwords in connection strings.
Secure Your Connection Strings
If you need to use SQL Server Authentication, then your connection contains username and password information. If an attacker exploits a source code disclosure vulnerability on the Web server, or manages to log in to the server, he is able to retrieve the connection strings. Similarly, anyone with a legitimate login to the server is able to view them. Secure connection strings using encryption.
Client and middle tier applications should connect to SQL Server using Windows Authentication whenever possible. When forced to use a less secure authentication mode, application designers should insure that SQL Server credentials are handled with appropriate care. All connections should be made with an account with the least privileges necessary.
Use Windows Authentication to Avoid Credential Management
Windows Authentication does not send credentials over the network. This account must be recognized on the database server by both Windows and SQL Server. It should be granted a SQL Server login with only those permissions needed to access the database. These precautions will limit the scope of damage if your system is compromised by hackers. The following examples illustrate a typical connection string that uses Windows Authentication.
Using the ADO.NET data provider for SQL Server:
SqlConnection pubsConn = new SqlConnection(
"Server=dbserver; database=pubs; Integrated Security=SSPI;");
Using the ADO.NET data provider for OLE DB data sources:
OleDbConnection pubsConn = new OleDbConnection(
"Provider=SQLOLEDB; Data Source=dbserver; Integrated Security=SSPI;" +
"Initial Catalog=northwind");
Protect the Credentials for SQL Server Authentication
If you must use SQL Server Authentication, make sure the credentials are not sent over an unencrypted channel in plain text. You must also encrypt the database connection string before storing it, because it contains credentials. To secure the connection string, use DPAPI. For more information, see "Storing Credentials" later in this paper.
To enable SQL Server to automatically encrypt the credentials sent over a network, install a server certificate on the database server. Alternatively, use an IPSec encrypted channel between Web and database server.
Connect Using a Least Privilege Account
Your application should connect to the database using a least privileged account. If you connect using Windows Authentication, the Windows account should be least privileged from an operating system perspective, and should have limited privileges and limited ability to access Windows resources. Additionally, whether you use Windows Authentication or SQL Server Authentication to the database, the corresponding SQL Server login in the database should be assigned only those permissions required by your application.
Top of page
Authorization
The authorization process establishes what permissions have been granted to a user. There are two lines of defense. Your data access code can perform authorization to determine whether or not to perform the requested operation. Once the calling identity or calling code has been authorized, a command is issued to the database. The database now performs authorization to restrict the capabilities of the SQL Server login used by the application.
With inadequate authorization, users may be able to alter the data of other users, and unauthorized users may be able to gain access to restricted data. To mitigate this threat, you should apply the defense-in-depth security principle (multiple levels of security) to your data access authorization strategy. Specifically:
• Restrict unauthorized callers.
• Restrict unauthorized code.
• Restrict the application in the database.
Figure 6 shows defense-in-depth of user data.
Figure 6:
See full-sized image.
Note that the data access code can use principal permission demands to authorize the calling principal or the calling code. Code identity demands are a feature of common language runtime code access security.
At the SQL Server level, you should create a least-privileged SQL Server login authorized only to execute stored procedures. Users should not be authorized perform Create, Read, Update, Delete (CRUD) operations directly on any table.
Restrict Unauthorized Callers
Middle tier applications should authorize users based on their role or identity prior to connecting to the back-end database. Role checks are usually implemented in the business logic of your application, but if you do not make a clear distinction between business and data access logic, use principal permission demands on the methods that access the database.
The following attribute ensures that only users who are a member of the Manager role can call the DisplayCustomerInfo method.
[PrincipalPermissionAttribute(SecurityAction.Demand, Role="Manager")]
public void DisplayCustomerInfo(int CustId)
{
}
If you need additional authorization granularity and need to perform role-based logic inside the data access method, use imperative principal permission demands or explicit role checks as shown below:
public void DisplayCustomerInfo(int CustId)
{
try
{
// Imperative principal permission role check to check that the caller
// is a manager
PrincipalPermission principalPerm = new PrincipalPermission(
null, "Manager");
// Code that follows is only executed if the caller is a member
// of the "Manager" role
}
catch( SecurityException ex )
{
. . .
}
}
The following code uses an explicit, programmatic role check to ensure that the caller is a manager.
public void DisplayCustomerInfo(int CustId)
{
if(!Thread.CurrentPrincipal.IsInRole("Manager"))
{
. . .
}
}
Restrict Unauthorized Code
By using ASP.NET code access security and specifically code identity demands, you can limit which other assemblies can access your data access classes and methods.
For example, if you only want code written by your company or a specific development organization to be able to use your data access components, use an instance of the StrongNameIdentityPermission class to verify that calling assemblies have a strong name with a specified public key.
using System.Security.Permissions;
. . .
[StrongNameIdentityPermission(SecurityAction.LinkDemand,
PublicKey="002...4c6")]
public void GetCustomerInfo(int CustId)
{
}
To extract a text representation of the public key for a given assembly, use the following command.
sn –tp assembly.dll
Because Web application assemblies are compiled dynamically, there is currently no way to give them strong names. This makes it difficult to restrict the use of a data access assembly to a specific Web application. The best approach is to develop a custom permission and demand that permission from the data access component. Full trust Web applications (or any fully-trusted code) will then be able to call your component. Partial trust code however, will only able to call your data access component if it has been granted the custom permission.
Top of page
Remote Administration
Administrators often need to be able to administer multiple servers. Make sure the requirements of your remote administration solution do not compromise security. The following should be considered best practices:
• Restrict the number of Windows administration accounts. This includes restricting the number of administration accounts as well as restricting which accounts are allowed to log on remotely.
• Restrict the tools. The main options for Windows are Internet Services Manager and Terminal Services. Web administration (using the IISAdmin virtual directory) is not recommended and this option is removed by IISLockdown.exe. Both Internet Services Manager and Terminal Services use Windows security. SQL Server Enterprise Manager can be used for remote SQL Server administration. If you are using SQL Server Authentication and need to store your credentials, you should configure Enterprise Manager to restrict access to a single Windows user by selecting Read/Store User Independent in the General tab.
• Restrict the computers that are allowed to administer the server. IPSec can be used to restrict which computers can connect to your Web server.
Securing Terminal Services
It is possible to use Microsoft Terminal Services securely, to remotely administer your database server.
Terminal Services is based on the proprietary Microsoft protocol known as Remote Desktop Protocol (RDP). RDP uses TCP 3389 port and supports two concurrent users.
Installing Terminal Services
1. In Control Panel, select Add or Remove Programs. Select the Add/Remove Windows Components option. You do not need to install the Terminal Services Licensing service for remote administration.
2. Configure Terminal Services for remote administration mode.
3. Remove the TsInternetUser account that is created during Terminal Services installation. This account is used to support anonymous Internet access to Terminal Services, which should not be enabled on a server.
Configuring Terminal Services
In the Administrative Tools program group, use the Terminal Services configuration MMC snap-in to configure the following:
1. Three levels (Low, Medium, and High) of encryption are available for connections to Terminal Services. Set the encryption to 128bit key. Note that the Windows high encryption pack should be installed on both the server and the client.
2. Configure the Terminal Services session to disconnect after idle connection time limit. Set it to end a disconnected session. Sessions disconnect after ten minutes if the user closes the Terminal Services client application without logging off.
3. Use the RDP permissions tab in the RDP dialog box to restrict access to Terminal Services. By default, all members of the Administrators group are allowed to access Terminal Services. If you do not want all members of the Administrators group to access Terminal Services, then remove the group and add individual accounts that need access. Note that the SYSTEM account must be in the list.
4. Use a secure VPN connection between the client and the server or an IPsec tunnel for enhanced security. This approach provides mutual authentication and the RDS payload is encrypted.
Copying Files over RDP
Terminal Services does not provide built-in support for file transfer. However, you can install the File Copy utility from the Windows 2000 Server Resource Kit to add file transfer functionality to the clipboard redirection feature in Terminal Services. For more information about the utility and installation instructions, see Microsoft Knowledge Base article 244732, "How To: Install the File Copy Tool Included with the Windows 2000 Resource Kit".
Top of page
Stored Procedures vs. Dynamic SQL
Stored procedures offer performance, maintenance, and security benefits. Use parameterized stored procedures for data access where possible. The security benefits include:
• Restricting the application database user to execute only the specified stored procedures. There is no need to grant direct table access. For more information, see the discussion of ownership chaining earlier in this paper.
• Performing length and type checks on all items of input data passed to the stored procedure. Also, parameters cannot be treated as executable code.
If you cannot use parameterized stored procedures and you need to construct SQL statements dynamically, do so using typed parameters and parameter placeholders, to ensure that input data is length and type checked.
Top of page
Use Separate Data Access Assemblies
If your application will be deployed in a multi-tier environment, you should avoid exposing data access logic directly to end-users. In ASP.NET, for example, data access logic should be deployed in its own assembly, separate from the business and presentation logic. Give the assembly a strong name, to reduce vulnerability to tampering. A strong name consists of the assembly's identity — its simple text name, version number, and culture information (if provided) — plus a public key and a digital signature. It is generated from an assembly file (the file that contains the assembly manifest, which in turn contains the names and hashes of all the files that make up the assembly), using the corresponding private key. Microsoft Visual Studio .NET® and other development tools provided in the .NET Framework SDK can assign strong names to an assembly. Assemblies with the same strong name are expected to be identical. The public key component of the strong name will also be useful for configuring code access security policy and granting specific permissions to the assembly, enabling data access methods and classes to authorize calling code.
Figure 7 depicts such a layered defense of a server. It performs principal-based authorization using principal permission demands on business components. It also uses code identity permission demands to authorize the code that calls data access logic.
Figure 7:
See full-sized image.
Top of page
SQL Injection
SQL Injection is an attack in which malicious code is inserted into strings that are later passed to SQL Server for parsing and execution. Any client-side application that returns SQL statements exposes the server that trusts it to such injection attacks, because the server will execute all syntactically valid statements it receives. Injection is easiest when applications construct SQL statements from user input. It is also possible when the client passes user input to server-side stored procedures. The danger to your server is magnified if the application connects with an over-privileged account.
Example of SQL Script Injection
This ASP script builds a SQL query by concatenating hard-coded strings together with a string entered by the user:
var Shipcity;
ShipCity = Request.form ("ShipCity");
var sql = "select * from OrdersTable where ShipCity = '" + ShipCity + "'";
The user is prompted to enter the name of a city. If she enters Redmond, the query assembled by the script looks like this:
select * from OrdersTable where ShipCity = 'Redmond'
But what if the user enters the following?
'Redmond'; drop table OrdersTable--
In that case, the query assembled by the script looks like this:
select * from OrdersTable where ShipCity = 'Redmond';drop table OrdersTable--
The ';' character denotes the end of one query and the beginning of another. And the '--' character sequence means the rest of the current line is a comment, and should be ignored. The user has used your client code to inject a string into the statement ASP will return to SQL Server. When it processes this statement, SQL Server will first select all records in OrdersTable where ShipCity is Redmond. Then it will drop OrdersTable.
Preventing SQL Injection
So long as injected SQL code is syntactically correct, it will be impossible to programmatically detect tampering on the server side. You must therefore validate all user input on the client side, and force server-side type checking by calling parameterized stored procedures. Always validate user input by testing type, length, format, and range. Untested input can cause program errors, and may be used by hackers as a point of entry into your system. When implementing precautions against malicious input, consider the architecture and deployment scenarios of your application. Remember that programs designed to run in a secure environment can be copied to an insecure environment.
Validate All Input
The following suggestions should be considered best practices:
• Make no assumptions about the size, type, or content of the data received by your application. For example, evaluate:
• How will your application behave if an errant, or malicious, user enters a 10-megabyte MPEG file where your application expects a postal code?
• How will your application behave if a DROP TABLE statement is embedded in a text field?
• Test the size and data type of input, and enforce appropriate limits. This can help prevent deliberate buffer overruns.
• Test the content of string variables and accept only expected values. Reject entries containing binary data, escape sequences, and comment characters. This can help prevent script injection and can protect against some buffer overrun exploits.
• When working with XML documents, validate all data against its schema as it is entered.
• Never build Transact-SQL statements directly from user input.
• Use stored procedures to validate user input.
• In multi-tiered environments, all data should be validated before admission to the trusted zone. Data that does not pass the validation process should be rejected, and an error returned to the previous tier.
• Implement multiple layers of validation. Precautions you take against casually malicious users may be ineffective against expert hackers. The best practice is to validate input in the user interface, and then at all subsequent points at which it crosses a trust boundary.
For example, data validation in a client-side application may prevent simple script injection; however, if the next tier assumes that its input has already been validated, any hacker capable of bypassing your client can have unrestricted access to your system.
• Never concatenate user input that is not validated. String concatenation is the primary point of entry for script injection.
• Do not accept the following strings in fields from which file names may be constructed: AUX, CLOCK$, COM1 through COM8, CON, CONFIG$, LPT1 through LPT8, NUL, and PRN.
When possible, reject input that contains the following potentially dangerous characters.
Input character Meaning in Transact-SQL
; Query delimiter
' Character data string delimiter
-- Comment delimiter
/* ... */ Comment delimiters. Text between /* and */ is not evaluated by the server.
Xp_ Begins the name of catalog extended stored procedures such as xp_cmdshell.
Use Type-Safe SQL Parameters
The Parameters collection in SQL Server provides type checking and length validation. If you use the Parameters collection, input is treated as a literal value rather than executable code. An additional benefit of using the Parameters collection is that you can enforce type and length checks. Values outside of the range will trigger an exception. The following code fragment illustrates using the Parameters collection:
SqlDataAdapter myCommand = new SqlDataAdapter("AuthorLogin", conn);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter parm = myCommand.SelectCommand.Parameters.Add(
"@au_id", SqlDbType.VarChar, 11);
parm.Value = Login.Text;
In this example, the @au_id parameter is treated as a literal value rather than executable code. This value is checked for type and length. If the value of @au_id does not conform to the specified type and length constraints, an exception will be thrown.
Use Parameterized Input with Stored Procedures
Stored procedures may be susceptible to SQL injection if they use unfiltered input. For example, the following code is vulnerable:
SqlDataAdapter myCommand =
new SqlDataAdapter("LoginStoredProcedure '" +
Login.Text + "'", conn);
If you use stored procedures, you should use parameters as their input.
Use the Parameters Collection with Dynamic SQL
If you cannot use stored procedures, you can still use parameters, as shown below.
SqlDataAdapter myCommand = new SqlDataAdapter(
"SELECT au_lname, au_fname FROM Authors WHERE au_id = @au_id", conn);
SQLParameter parm = myCommand.SelectCommand.Parameters.Add("@au_id",
SqlDbType.VarChar, 11);
Parm.Value = Login.Text;
Filtering Input
Filtering input may also be helpful in protecting against SQL injection by removing escape characters, but due to the large number of characters that may pose problems it is not a reliable defense. The following snippet searches for the character string delimiter.
private string SafeSqlLiteral(string inputSQL)
{
return inputSQL.Replace("'", "''");
}
LIKE Clauses
Note that if you are using a LIKE clause, wildcard characters still need to be escaped:
s = s.Replace("[", "[[]");
s = s.Replace("%", "[%]");
s = s.Replace("_", "[_]");
Parameter Batching
It is a common misconception that parameters cannot be used when multiple SQL statements are concatenated for batched transmission to the server. In fact, parameters can be used, so long as parameter names are not repeated. That can be easily achieved by adding a number or some other unique value to each parameter name during SQL text concatenation.
Top of page
Storing Credentials
Avoid storing credentials in any form. The best practice is to use only Windows Authentication and never handle credentials at all. If your application must connect with a system outside a trusted domain, however, credential management may become necessary. In this case, the best practice is to encrypt the credentials using the DPAPI and save them in a registry key that uses an ACL. You can use regedt32.exe to apply the following ACL to the key:
Administrators: Full Control
Process Account: Read
With DPAPI encryption, you avoid encryption key management issues. This is because the encryption key is managed by the platform and is tied to either a specific machine or a Windows user account. For more information on DPAPI, see Windows Data Protection.
You may need to create a managed wrapper class to handle DPAPI encryption. For details about how to build a managed wrapper class, see How To: Create a DPAPI Library.
Although potentially less secure than using a secured registry key, ASP.NET applications may need to store the encrypted string in web.config. In this case, use a custom
...
To access the cipher text from the
using System.Configuration;
private static string GetConnectionString()
{
return ConfigurationSettings.AppSettings["connectionString"];
}
If your application stores connection information in a UDL file, use NTFS permissions to restrict access. Use the following restricted ACL.
Administrators: Full Control
Process Account: Read
UDL files are not encrypted. They may contain credentials in plain text. If your application forces users to store credentials in a UDL you should redesign it to use more secure practices.
Top of page
Persist Security Info Property
Never set the Persist_Security_Info property of OLE DB connection strings to "true" or "yes". When you include this attribute in a connection string, it causes the ConnectionString property to strip out the password from the connection string before it is returned to the user. The default setting of "false" discards this information once the connection is made to the database. For more information, see Persist Security Info Property.
Top of page
Encrypting User Data
If you store sensitive user-supplied data such as credit card numbers, encrypt it with a strong symmetric encryption algorithm such as Triple DES (3DES). Encrypt the 3DES encryption key using the Data Protection API (DPAPI), and store the encrypted key in a registry key with an ACL that grants access only to administrators and your application process account. The basic approach is outlined below.
At development time, perform the following tasks:
1. Use the RNGCryptoServiceProvider class to generate a strong (192 bit, 24 byte) encryption key.
2. Back up the encryption key and store the backup in a physically secure location.
3. Encrypt the key with DPAPI and store it in a registry key. Secure the registry key with the following ACL:
Administrators: Full Control
Process Account (for example ASPNET): Read
To encrypt data for storage in the database, do the following:
1. Obtain the data to be encrypted.
2. Retrieve the encrypted encryption key from the registry.
3. Use DPAPI to decrypt the encryption key.
4. Use the TripleDESCryptoServiceProvider class with the encryption key to encrypt the data.
5. Store the encrypted data in the database.
To decrypt the encrypted data, do the following:
1. Retrieve the encrypted data from the database.
2. Retrieve the encrypted encryption key from the registry.
3. Use DPAPI to decrypt the encryption key.
4. Use the TripleDESCryptoServiceProvider class to decrypt the data.
With this process, if the DPAPI account used to encrypt the encryption key is damaged, the backup of the 3DES key can be retrieved from the backup location and be encrypted using DPAPI under a new account. The new encrypted key can be stored in the registry and the data in the database can still be decrypted.
Note: Information encrypted in this way cannot be indexed.
Top of page
Verify Passwords with a One-Way Hash
If you need to verify passwords at the server, consider storing only a hash of the password rather than the password itself. Hashing algorithms map binary values of arbitrary length to small binary values of fixed length. The resulting hash value, also known as a "one-way hash," is a compact representation of the data from which it was generated — a digital fingerprint. Storing the hash of a password is safer than storing the password itself, or even its encrypted equivalent, because hashing is fully deterministic in only one direction.
For more information, see Windows Data Protection, and Using Credential Management in Windows XP and Windows Server 2003.
Top of page
Exception Management
Do not return unprocessed error codes to the user, as they may contain too much information about the structure and contents of your database and its authentication mechanisms. Without proper exception management, error conditions caused by misconfiguration, bugs in your code, or malicious input may reveal connection strings, database meta data, SQL code fragments, and raw data not intended for end users. Error messages may also include such information as software versions and configuration details. Such disclosures assist hackers in profiling your system but add little value for users.
Trap and Log Exceptions
Use try and catch commands around data access code to trap and log exceptions. Connection attempts that fail should always fail gracefully, logging appropriate information in a file secured with an ACL. In addition, connections should always be explicitly closed when they are no longer needed.
The following examples illustrate these best practices when using ADO.NET. Note that the type of exception generated by ADO.NET depends on the data provider.
• The .NET Framework Data Provider for SQL Server generates SqlException objects.
• The .NET Framework Data Provider for OLE DB generates OleDbException objects.
• The .NET Framework Data Provider for ODBC generates OdbcException objects.
This code uses the SQL Server .NET Framework data provider to trap exceptions of type SqlException:
try
{
// Data access code
}
catch (SqlException sqlex) // more specific
{
}
catch (Exception ex) // less specific
{
}
Details of database access errors are exposed through the properties of the SqlException class. These include a message property that describes the error, a number property that uniquely identifies the type of error, a state property that contains additional information. This is usually used to indicate a particular occurrence of a specific error condition. For example, if a stored procedure generates the same error from more than one line, the state property indicates the specific occurrence. Finally, an Errors collection contains a set of SqlError objects that provide detailed SQL Server error information.
The following code fragment shows how to handle a SQL Server error condition by using the data provider.
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
// Method exposed by a Data Access Layer (DAL) Component
public string GetProductName( int ProductID )
{
SqlConnection conn = new SqlConnection(
"server=(local);Integrated Security=SSPI;database=products");
// Enclose all data access code within a try block
try
{
conn.Open();
SqlCommand cmd = new SqlCommand("LookupProductName", conn );
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ProductID", ProductID );
SqlParameter paramPN =
cmd.Parameters.Add("@ProductName", SqlDbType.VarChar, 40 );
paramPN.Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
// The finally code is executed before the method returns
return paramPN.Value.ToString();
}
catch (SqlException sqlex)
{
// Handle data access exception condition
// Log specific exception details
LogException(sqlex);
// Wrap the current exception in a more relevant
// outer exception and re-throw the new exception
throw new Exception(
"Failed to retrieve product details for product ID: " +
ProductID.ToString(), sqlex );
}
finally
{
conn.Close(); // Ensures connection is closed
}
}
// Helper routine that logs SqlException details to the
// Application event log
private void LogException( SqlException sqlex )
{
EventLog el = new EventLog();
el.Source = "CustomAppLog";
string strMessage;
strMessage = "Exception Number : " + sqlex.Number +
"(" + sqlex.Message + ") has occurred";
el.WriteEntry( strMessage );
foreach (SqlError sqle in sqlex.Errors)
{
strMessage = "Message: " + sqle.Message +
" Number: " + sqle.Number +
" Procedure: " + sqle.Procedure +
" Server: " + sqle.Server +
" Source: " + sqle.Source +
" State: " + sqle.State +
" Severity: " + sqle.Class +
" LineNumber: " + sqle.LineNumber;
el.WriteEntry( strMessage );
}
}
Close All Unneeded Database Connections
If an error occurs, it is essential to close database connections and release any other limited resources. Use finally blocks, or the C# using statement to ensure that connections are closed irrespective of whether an exception condition occurs. The next example illustrates the use of the finally block. The C# using statement can also be used as shown below:
using ((SqlConnection conn = new SqlConnection(connString)))
{
conn.Open();
// Connection will be closed if an exception is generated or if control flow
// leaves the scope of the using statement normally
}
Example: Secure Data Access Component
The following code shows how to retrieve a connection string from the registry and decrypt it using the managed DPAPI helper library provided in How To: Create a DPAPI Library on MSDN. It illustrates a sample implementation of a CheckProductStockLevel method that queries a products database for stock quantity. The code illustrates a number of the important security features for data access code discussed above.
public static int CheckProductStockLevel(string productCode)
{
int quantity = 0;
// (1) Code protected by try/catch block
try
{
// (2) Input validated with regular expression
Regex rex = new Regex("^[A-Za-z0-9]{12}$");
if (rex.IsMatch(productCode) == false)
// Error messages should be retrieved from a resource assembly to
// assist localization. The localization code is omitted here for brevity
throw new ArgumentException("Invalid product code" );
//(3) The using statement ensures that the connection is closed
using (SqlConnection conn = new SqlConnection(GetConnectionString()))
{
// (4) Use of parameterized stored procedures is a countermeasure for
// SQL injection attacks
SqlCommand cmd = new SqlCommand("spCheckProduct", conn);
cmd.CommandType = CommandType.StoredProcedure;
// Parameters are type checked
SqlParameter parm =
cmd.Parameters.Add("@ProductCode",
SqlDbType.VarChar,12).Value = productCode;
// Define the output parameter
SqlParameter retparm = cmd.Parameters.Add("@quantity", SqlDbType.Int);
retparm.Direction = ParameterDirection.Output;
conn.Open();
cmd.ExecuteNonQuery();
quantity = (int)retparm.Value;
}
}
catch (SqlException sqlex)
{
// (5) Full exception details are logged. Generic (safe) error message
// is thrown back to the caller based on the SQL error code
// Log and error identification code has been omitted for clarity
throw new Exception("Error Processing Request");
}
catch (Exception ex)
{
// Log full exception details
throw new Exception("Error Processing Request");
}
return quantity;
}
// (6) Encrypted database connection string is held in the registry
private static string GetConnectionString()
{
// Retrieve the cipher text from the registry; the process account must be
// granted Read access by the key's ACL
string encryptedString = (string)Registry.LocalMachine.OpenSubKey(
@"Software\OrderProcessing\")
.GetValue("ConnectionString");
// Use the managed DPAPI helper library to decrypt the string
DataProtector dp = new DataProtector(DataProtector.Store.USE_MACHINE_STORE);
byte[] dataToDecrypt = Convert.FromBase64String(encryptedString);
return Encoding.ASCII.GetString(dp.Decrypt(dataToDecrypt,null));
}
The code shown above exhibits the following security characteristics (identified by the numbers in the comment lines).
1. The data access code is placed inside a try/catch block. This is essential to avoid system level information being returned to the caller in the event of an exception. The calling ASP.NET Web application or Web Service might handle the exception and return a suitably generic error message to the client, but the data access code does not rely on this.
2. Input is validated using a regular expression. The supplied product ID is checked to ensure it only contains characters in the range A-Z and 0-9, and that it does not exceed 12 characters. This is the first in a set of countermeasures designed to prevent SQL injection attacks.
3. The SqlConnection object is created inside a C# using statement. This ensures that the connection is closed inside the method regardless of whether an exception occurs. This mitigates the threat of denial of service attacks that aim to use up all available connections to the database. Similar functionality can be achieved by using a finally block.
4. Parameterized stored procedures are used for data access. This is another countermeasure designed to prevent SQL injection.
5. Detailed error information is not returned to the client. Exception details are logged to assist with problem diagnosis.
6. Encrypted database connection string is stored in the registry. One of the most secure ways to store a database connection string is to encrypt it with DPAPI and store the encrypted cipher text in a registry key secured with an ACL. Depending on which process hosts the component, appropriate ACLs might be Administrators: Full Control; ASP.NET (or Enterprise Services process account): Read.
Top of page
Code Access Security Considerations
Code Access Security (CAS) may be helpful in some deployment scenarios. Client software developed with .NET supports data access with CAS permission checks. Detailed requirements will vary with the implementation of your chosen ADO.NET managed data provider. If your data access code operates in a full trust environment, and, for example, is always called by Web applications configured for full trust, CAS permission demands issued by the managed data provider will always succeed.
However, if you want your data access code to support partial trust callers, you need to be aware of the permission requirements of the managed data provider that you use. For example, if the provider requires permissions that are not granted to a Medium trust Web application (which is a common configuration used by hosting companies), the permission demand will fail and a SecurityException will be thrown when you attempt to access the database. In this scenario, you need to isolate your data access code to encapsulate the additional permission demands. This requires you to place your data access code in its own assembly, and not in a Web application's presentation logic or associated code-behind files.
When you use the .NET Framework Data Provider for SQL Server to access SQL Server, the provider code demands the SqlClientPermission. Any data access component that uses this provider to communicate with SQL Server requires this permission. For more information about the SqlClientPermission and the permission requirements of the other data providers, see Version 1 Security Changes for the Microsoft .NET Framework.
Note: Any code that calls your data access component also requires these permissions unless you sandbox your data access assembly, because the permission demand walks the complete call stack.
Note: The trust level of a Web application is determined by its
SqlClientPermission can also be used to restrict the allowable range of name-value pairs that can be used on a connection string passed to the SqlConnection object. In the following code, the CheckProductStockLevel method has been enhanced with an additional security check to ensure that blank passwords cannot be used in the connection string.
[SqlClientPermissionAttribute(SecurityAction.PermitOnly,
AllowBlankPassword=false)]
public static int CheckProductStockLevel(string productCode)
{
. . .
}
Finally, because the code only requires read access to a specific registry key, the RegistryPermissionAttribute can also be added to ensure the code is not able to access any other area of the registry.
[RegistryPermissionAttribute(SecurityAction.PermitOnly,
Read=@"HKEY_LOCAL_MACHINE\Software\OrderProcessing")]
[SqlClientPermissionAttribute(SecurityAction.PermitOnly,
AllowBlankPassword=false)]
public static int CheckProductStockLevel(string productCode)
{
. . .
}
The following table shows the permissions that must be granted to your data access assemblies (and their callers if you do not sandbox the data access code) for each of the ADO.NET data providers.
ADO.NET Data Provider Required CAS Permissions
SQL Server SqlClientPermission
(Supported by Medium trust Web applications.)
OLE DB OleDbPermission (Currently has no effect; OLE DB provider on .NET Framework 1.0 and 1.1 requires Full Trust callers.)
Oracle OraclePermission (Currently has no effect; Oracle provider on .NET Framework 1.0 and 1.1 requires Full Trust callers.)
ODBC OdbcPermission (Currently has no effect; ODBC provider on .NET Framework 1.0 and 1.1 requires Full Trust callers.)
Top of page
MSDE
SQL Server 2000 Desktop Engine (MSDE 2000) is a version of the SQL Server data engine designed for redistribution with client-side applications. It has the same security architecture and features as SQL Server. If you are using MSDE, the following additional guidance applies.
• When installing an instance of MSDE that will operate only as a local data store, you should disable the Server Net-Libraries. For more information, search for Microsoft Knowledge Base article 814130, "How to Secure Network Connectivity for SQL Server 2000 Local Databases".
• When distributing MSDE to your customers, you should use the Microsoft-supplied installer rather than merge modules.
• If your product includes MSDE, you should make this known to your customers. In the future, they may need to install or accept MSDE-specific software updates.
• Include security best practices in your product documentation.
SQL Server 2000 SP3 Security Features and Best Practices: Security Best Practices Checklist
Security Best Practices Checklist
Updated: May 16, 2003
The following checklist summarizes the best practices discussed in this paper. For details, refer to the discussion above.
On This Page
Administrator Checklist
Developer Checklist
Software Vendor Checklist
Appendix: Finding More Information
Administrator Checklist
Setting Up the Environment Prior to Installation
Physical security • Ensure the physical security of your server.
Firewalls • Put a firewall between your server and the Internet.
• Always block TCP port 1433 and UDP port 1434 on your perimeter firewall. If named instances are listening on additional ports, block those too.
• In a multi-tier environment, use multiple firewalls to create screened subnets.
Isolation of services • Isolate services to reduce the risk that a compromised service could be used to compromise others.
• Never install SQL Server on a domain controller.
• Run separate SQL Server services under separate Windows accounts.
• In a multi-tier environment, run Web logic and business logic on separate computers.
Service accounts • Create Windows accounts with the lowest possible privileges for running SQL Server services.
File System • Use NTFS.
• Use RAID for critical data files.
Installation
Latest version and service pack • Always install the latest service packs and security patches.
Service accounts • Run SQL Server services with the lowest possible privileges.
• Use Enterprise Manager to associate services with Windows accounts.
Authentication mode • Require Windows Authentication for connections to SQL Server.
Strong passwords • Always assign a strong password to the sa account, even when using Windows Authentication.
• Always use strong passwords for all SQL Server accounts.
Configuration Options and Settings After Installation
Delete or secure old setup files • Delete or archive the following files after installation: sqlstp.log, sqlsp.log, and setup.iss in the
• If the current system is an upgrade from SQL Server 7.0, delete the following files: setup.iss in the %Windir% folder, and sqlsp.log in the Windows Temp folder.
Choose static ports for named instances • Assign static ports to named instances of SQL Server.
Set login auditing level • Set login auditing level to failure or all.
Enable security auditing • Enable security auditing of Sysadmin actions, fixed role membership changes, all login related activity, and password changes.
• After selecting appropriate auditing options, you should script the audit, wrap it in a stored procedure,and mark that stored procedure for AutoStart.
Secure sa even in Windows Authentication Mode • Assign a strong password to the sa account, even on servers that are configured to require Windows Authentication.
Remove sample databases • Remove sample databases from production servers.
Secure Operation
Security model • Learn to work with the SQL Server security model.
Backup policy • Back up all data regularly and store copies in a secure off-site location.
• Test your disaster recovery system.
Surface and feature reduction • Reduce the surface area of your system that is exposed to attack by running only those services and features needed in your environment.
Administrator reduction • Restrict membership of the sysadmin fixed server role to a few trusted individuals.
Strong passwords • Ensure that you use complex passwords for all SQL Server accounts.
Cross database ownership chaining • Disable cross database ownership chaining if your system does not use it.
Xp_cmdshell • By default, only members of the sysadmin role can execute xp_cmdshell. You should not change this default.
• Do not grant execute permission on xp_cmdshell to users who are not members of the sysadmin role.
Encryption • Install a certificate to enable SSL connections.
• Certificates should use the fully-qualified DNS name of the server.
• Use the SQL Server service account to encrypt database files with EFS.
• If your application requires data encryption, consider using the products of such vendors as Protegrity and Application Security Inc.
Roles and groups • Collect users into SQL Server roles or Windows groups to simplify permissions administration.
Permissions • Never grant permissions to the public database role.
Distributed queries • When setting up SQL Server in an environment that supports distributed queries, use linked servers rather than remote servers.
• Allow linked server access only to those logins that need it.
• Disable ad hoc data access on all providers except SQL OLE DB, for all users except members of the sysadmin fixed server role.
• Allow ad hoc data access only on trusted providers.
Guest accounts • Do not enable the guest account.
Service accounts • If you need to change the account associated with a SQL Server service, use SQL Server Enterprise Manager.
• If you change multiple services, you must apply the changes to each service separately using Enterprise Manager.
Recommended Periodic Administrative Procedures
Microsoft Baseline Security Analyzer • Add MBSA to your weekly maintenance schedule, and follow up on any security recommendations that it makes.
Scanning logins • Periodically scan for accounts with NULL passwords and remove them or assign them strong passwords.
• Delete unused accounts.
Enumerate fixed role membership • Periodically scan fixed server and database roles to ensure that membership is only granted to trusted individuals.
Start-up procedures • Verify the safety of stored procedures that have been marked for AutoStart.
Login-to-user mapping • Ensure that the mapping between database users and logins at the server level is correct.
• Run sp_change_users_login with the report option regularly to ensure that the mapping is as expected.
Direct catalog updates • Do not allow direct catalog updates.
Cross database ownership chaining • Use sp_dboption to enumerate and validate databases for which cross database ownership chaining has been enabled.
Best Practices for Patching Instances
Instance detection and enumeration • Keep an inventory of all versions, editions, and languages of SQL Server for which you are responsible.
• Include instances of MSDE in your inventory.
• Use SQL Scan and SQL Check, available from the Microsoft Web site, to scan for instances of SQL Server within your domain.
Bulletins • Subscribe to Microsoft security bulletins.
Patch application • Maintain test systems that match the configuration of you production systems, and are readily available for testing new patches.
• Test patches carefully before applying them to production systems.
• Consider patching development systems with relatively little testing.
Top of page
Developer Checklist
In addition to all of the items above, the following should be considered best practices for developers.
General
Use ownership chaining effectively • Use ownership chaining within a single database to simplify permissions management.
• Avoid using cross database ownership chaining when possible.
• If you must use cross database ownership chaining, ensure that the two databases are always deployed as a single administrative unit.
Use roles to simplify permission management and ownership • Assign permissions to roles rather than directly to users.
• Objects may be owned by roles, rather than directly by users, if you want to avoid application changes when the owning user is dropped.
Turn on encryption (SSL or IPSEC) • Enable encrypted connections to your server, and consider allowing only encrypted connections.
• When allowing SQL Server Authentication, you are strongly urged to encrypt either the network layer with IPSec or the session with SSL.
Do not propagate SQL Server errors back to user • Your application should not return SQL Server errors to the end user. Log them instead, or transmit them to the system administrator.
Prevent SQL injection • Defend against SQL injection by validating all user input before transmitting it to the server.
• Limit the scope of possible damage by permitting only minimally privileged accounts to send user input to the server.
• Run SQL Server itself with the least necessary privileges.
Multi-tier Options
Same/trusted domain (complete Windows Authentication) If the application server and the database server are within the same domain, or within trusted domains, you should use Windows Authentication and configure for "full provisioning" in which all client contexts are tunneled to SQL Server. This makes it possible to audit all users who access SQL Server, enables Windows security policy enforcement, and makes it unnecessary to store credentials in the middle tier. In this scenario, the client connects to the application server, which in turn impersonates the client and connects to SQL Server.
• Every user on the application server must have a valid Windows login on the database server and delegation must be enabled.
• All systems interacting in this scenario, including the Domain Controller, must run Windows 2000 or higher.
• The account the application is running under must be trusted for delegation (that is, the Active Directory option Account is trusted for delegation must be turned on for this account).
• The client account must be able to be delegated (ensure that the Active Directory user account option Account is trusted and cannot be delegated is unchecked).
• The application service must have a valid Service Principal Name (SPN).
Note: Full provisioning is not recommended in cross-enterprise or Internet-scale installations, when your security plan calls for minimizing user access to the database server, or in enterprises with policies prohibiting delegation.
Mixed scenario (partial Windows Authentication) When the Internet-facing tier does not have an individual Windows domain account for every possible user, the recommended scenario is to divide authentication into stages. The outer tier (which authenticates users) should use SSL to encrypt at least credentials, if not the entire session. It should connect to the database server using Windows Authentication, forwarding transaction information under a separate security context that is low privileged, with only the permissions necessary to perform its function. This effectively uses the middle tier as an additional layer of defense between your server and the Internet.
Note: Using SQL Server Authentication between the middle tier and SQL Server is not recommended, because of the need to store credentials. If you must use SQL Server Authentication between the middle tier and SQL Server, you should create several accounts, with different levels of privileges corresponding to different classes of users. This requires that you add logic to the middle tier to allocate connections according to the desired privilege level.
Different non-trusted domains or no domains (no Windows Authentication) When Windows Authentication between tiers is not possible, you should require SSL encryption of the login sequence. Encrypting the entire session is preferable.
• You should also use DPAPI to encrypt credentials that must be stored.
• You should store encrypted credentials in a registry key protected with an ACL.
Top of page
Software Vendor Checklist
In addition to all of the items above, the following security development practices have proven useful in increasing the quality and security of code in various development environments.
Security Processes
Understanding various security issues • Ensure that members of your development team understand major security issues: current threats, security trends, changing security environments, and attack scenarios.
• Require relevant security training for all developers and testers.
• Increase the awareness of issues like cross-site scripting, buffer overflows, SQL injection, and dangerous APIs.
• Identify specific categories of threats that apply to your product — for example, denial of service, escalation of privileges, spoofing, data tampering, information disclosure and repudiation.
• Analyze security threats to your product, component-by-component.
• Create a security threat checklist based on your product.
• Add security reviews to all stages (from design to testing) of your product development cycle.
MSDE installations If you distribute MSDE with your application, the following additional guidance applies:
• Install MSDE using "Windows security mode" as the default.
• Never install a blank sa password.
• When distributing MSDE to your customers, you should use the Microsoft-supplied installer rather than merge modules.
• When installing an instance of MSDE that will operate only as a local data store, you should disable the Server Net-Libraries.
• If your product includes MSDE, you should make this known to your customers. In the future, they may need to install or accept MSDE-specific software updates.
• MSDE installs SQL Server Agent by default, but leaves the Service startup type to "Manual." If your application does not use SQL Server Agent, you should change this to "Disabled." Include security best practice information in your product documentation.
Top of page
Appendix: Finding More Information
Recommended Books:
Inside Microsoft® SQL Server™ 2000, by Kalen Delaney. Copyright 2000, Microsoft Press. ISBN: 0-7356-0998-5.
Writing Secure Code, Second Edition, by Michael Howard and David LeBlanc.
This book makes a useful training resource, reviewing the most common security flaws in designing/coding and testing components/applications. It contains security best practices and checklists. It also covers strategies in designing secure applications, writing robust code that can withstand repeated attacks, and testing applications for security flaws.
Hacking Exposed Windows 2000, by Joel Scambray and Stuart McClure.
This book adopts the approach of a hacker. It goes into details of how to break into Windows 2000 machines and thereby increases awareness of how to prevent such attacks. It also has an entire section dedicated to SQL Server and how it can be used to crack the system as a whole.
Designing Secure Web-Based Applications for Microsoft® Windows® 2000, by Michael Howard.
This book gives you a solid foundation in Microsoft Windows 2000, Internet Explorer, Internet Information Services, SQL Server, and COM+ security concepts. It explains the key software design considerations for various categories and levels of security and shows how isolated security "islands" interact. It also explains core security issues such as risk analysis, threats, authentication, authorization, and privacy, and it shows how you can mitigate risks by applying the appropriate security to your environment and applications. Managers, developers, and testers can all use the knowledge in this book to look at components from a security perspective, perform threat analysis and take appropriate responses by enhancing the security of code/design and enhancing test suites appropriately.
Building Secure Microsoft ASP.NET Applications, by J.D.Meier (among others).
This guide presents a practical, scenario-driven approach to designing and building secure ASP.NET applications for Windows 2000 and version 1.0 of the .NET Framework. It focuses on the key elements of authentication, authorization, and secure communication within and across the tiers of distributed .NET Web applications.
Firewalls and Internet Security, second edition, by William R. Cheswick, Steven M. Bellovin, and Aviel D. Rubin. Addison-Wesley, 2003.
This is the standard introduction to Internet security. Highly recommended.
Recommended Tools, Papers, and Presentations
The Microsoft Baseline Security Analyzer Tool. This tool helps analyze the security of the system. It is recommended that administrators run this tool on a periodic basis. This tool performs some SQL Server specific checks.
The SQL Server Critical Update Wizard.
Microsoft SQL Server and Security Sites
The area of the Microsoft Web site dedicated to security.
SQL Server product site with links to download locations for the latest service packs, latest bulletins, and other resources.
TechNet SQL Server Security Center with information regarding Troubleshoot and Maintain, Set Up, Configure, and Administer, Security Topics
MSDN SQL Server resource site
White Papers
SQL Server Developer Center. Contains recent technical white papers and downloads.
SQL Server 2000 Security White Paper This white paper was written when SQL Server 2000 was first released, and forms the basis for parts of this paper.
Top of page
5 of 5
In This Article
•
• SQL Server 2000 Security Model
• Implementation of Server Level Security and Object Level Security
• Secure Multi-tier Deployment
• Security Best Practices Checklist
Geen opmerkingen:
Een reactie posten