SQL Server Security

==========================================================
Welcome to SQL Server 2005 Security Part

==========================================================

SQL Server Security

OBJECTIVE

SQL Server includes a variety of precise, configurable security features. These features empower administrators to implement defense-in-depth that is optimized for the specific security risks of their environment. This topic provides links to help you locate the security information that you need in the SQL Server Database Engine.

Securing SQL Server

Securing SQL Server can be viewed as a series of steps, involving four areas: the platform, authentication, objects (including data), and applications that access the system. The following topics will guide you through creating and implementing an effective security plan.

Principals

Principals are entities that can request SQL Server resources. Like other components of the SQL Server authorization model, principals can be arranged in a hierarchy. The scope of influence of a principal depends on the scope of the definition of the principal: Windows, server, database; and whether the principal is indivisible or a collection. A Windows Login is an example of an indivisible principal, and a Windows Group is an example of a principal that is a collection. Every principal has a security identifier (SID).

Windows-level principals

• Windows Domain Login

• Windows Local Login

SQL Server-level principal

• SQL Server Login

Database-level principals

• Database User

• Database Role

• Role

The SQL Server SA Login

The SQL Server sa log in is a server-level principal. By default, it is created when an instance is installed. In SQL Server 2005 and SQL Server 2008, the default database of sa is master. This is a change of behavior from earlier versions of SQL Server.

Public Database Role

Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable, the user inherits the permissions granted to public on that securable.

Client and Database Server

By definition, a client and a database server are security principals and can be secured. These entities can be mutually authenticated before a secure network connection is established. SQL Server supports the Kerberos authentication protocol, which defines how clients interact with a network authentication service.

Database Users

A database user is a principal at the database level. Every database user is a member of the public role.

Guest user

By default, the database includes a guest user when a database is created. Permissions granted to the guest user are inherited by users who do not have a user account in the database.

The guest user cannot be dropped, but it can be disabled by revoking its CONNECT permission. The CONNECT permission can be revoked by executing REVOKE CONNECT FROM GUEST within any database other than master or tempdb.

Application Roles

An application role is a database principal that enables an application to run with its own, user-like permissions. You can use application roles to enable access to specific data to only those users who connect through a particular application. Unlike database roles, application roles contain no members and are inactive by default. Application roles work with both authentication modes. Application roles are enabled by using sp_setapprole, which requires a password. Because application roles are a database-level principal, they can access other databases only through permissions granted in those databases to guest. Therefore, any database in which guest has been disabled will be inaccessible to application roles in other databases.

In SQL Server, application roles cannot access server-level metadata because they are not associated with a server-level principal. To disable this restriction and thereby allow application roles to access server-level metadata, set the global flag 4616.

Connecting with an Application Role

The following steps make up the process by which an application role switches security contexts:

1. A user executes a client application.
2. The client application connects to an instance of SQL Server as the user.
3. The application then executes the sp_setapprole stored procedure with a password known only to the application.
4. If the application role name and password are valid, the application role is enabled.
5. At this point the connection loses the permissions of the user and assumes the permissions of the application role.

The permissions acquired through the application role remain in effect for the duration of the connection.

Syntax

CREATE APPLICATION ROLE application_role_name

WITH PASSWORD = 'password'

[ , DEFAULT_SCHEMA = schema_name ]

Real Time

CREATE APPLICATION ROLE Emp_salary

WITH PASSWORD = '987G^bv876sPY)Y5m23',

DEFAULT_SCHEMA = Employee; GO

Example:

USE [Employee] GO

CREATE APPLICATION ROLE [EmployeeSelect]

WITH DEFAULT_SCHEMA = [guest],

PASSWORD = N'employee@123' GO

USE [Employee] GO

ALTER AUTHORIZATION ON SCHEMA::[db_owner]

TO [EmployeeSelect] GO

USE [Employee] GO

ALTER AUTHORIZATION ON SCHEMA::[Employee]

TO [EmployeeSelect] GO

Arguments

application_role_name

Specifies the name of the application role. This name must not already be used to refer to any principal in the database.

PASSWORD = 'password'

Specifies the password that database users will use to activate the application role. You should always use strong passwords. password must meet the Windows password policy requirements of the computer that is running the instance of SQL Server.

DEFAULT_SCHEMA = schema_name

Specifies the first schema that will be searched by the server when it resolves the names of objects for this role. If DEFAULT_SCHEMA is left undefined, the application role will use DBO as its default schema. schema_name can be a schema that does not exist in the database.

User-Schema Separation

The behavior of schemas changed in SQL Server 2005. Schemas are no longer equivalent to database users; each schema is now a distinct namespace that exists independently of the database user who created it. In other words, a schema is simply a container of objects. A schema can be owned by any user, and its ownership is transferable.

New Behavior

The separation of ownership from schemas has important implications:

• Ownership of schemas and schema-scoped securable is transferable.
• Objects can be moved between schema's.
• A single schema can contain objects owned by multiple database users.
• Multiple database users can share a single default schema.
• Permissions on schemas and schema-contained securable can be managed with greater precision than in earlier releases.
• A schema can be owned by any database principal. This includes roles and application roles.
• A database user can be dropped without dropping objects in a corresponding schema.
• Code written for earlier releases of SQL Server may return incorrect results, if the code assumes that schemas are equivalent to database users.
• Catalog views designed for earlier releases of SQL Server may return incorrect results. This includes sysobjects.

New Catalog Views

Schemas are explicit entities reflected in metadata; and as a result, schemas can only have one owner but a single user can own many schemas. This complex relationship is not reflected in the SQL Server 2000 system tables, so SQL Server 2005 introduced new catalog views, which accurately reflect the new metadata.

SQL Server 2005 and later catalog view

• sys.database_principals
• sys.schemas
• sys.server_principals
Default Schema's
In order to resolve the names of securables that are not fully qualified names, SQL Server 2000 uses name resolution to check the schema owned by the calling database user and the schema owned by dbo. Beginning with SQL Server 2005, each user has a default schema. The default schema can be set and changed by using the DEFAULT_SCHEMA option of CREATE USER or ALTER USER. If DEFAULT_SCHEMA is left undefined, the database user will have dbo as its default schema.

===================================================================

Managing Logins, Users, and Schemas

The following topics that show you how to manage SQL Server logins, database users, and database schemas.

• Create a SQL Server Login

• Create a Database User

• Create a Database Schema

Create a SQL Server Login

To create a SQL Server login that uses Windows Authentication (SQL Server Management Studio)

1. In SQL Server Management Studio, open Object Explorer and expand the folder of the server instance in which to create the new login.
2. Right-click the Security folder, point to New, and then click Login.
3. On the General page, enter the name of a Windows user in the Login name box.
4. Select Windows Authentication.
5. Click OK.

Examples:

Using Script

Syntax

CREATE LOGIN FROM WINDOWS; GO

Real Time

USE [master] GO

CREATE LOGIN [RESEARCH\Guest]

FROM WINDOWS WITH DEFAULT_DATABASE=[master] GO

To create a SQL Server login that uses SQL Server Authentication (SQL Server Management Studio)

1. In SQL Server Management Studio, open Object Explorer and expand the folder of the server instance in which to create the new login.

2. Right-click the Security folder, point to New, and then click Login.

3. On the General page, enter a name for the new login in the Login name box.

4. Select SQL Server Authentication. Windows Authentication is the more secure option.

5. Enter a password for the login.

6. Select the password policy options that should be applied to the new login. In general, enforcing password policy is the more secure option.

7. Click OK.

Examples:

Using Script

Syntax

CREATE LOGIN [LOGIN NAME] WITH PASSWORD = ''

DEFAULT_DATABASE=[Select Database name],

CHECK_EXPIRATION=ON,

CHECK_POLICY=ON;

GO

Real Time

USE [master] GO CREATE LOGIN [RESEARCH\Guest]

WITH PASSWORD=N'winter@77' MUST_CHANGE,

DEFAULT_DATABASE=[master],

CHECK_EXPIRATION=ON,

CHECK_POLICY=ON

GO

===========================================================

Create a Database User

This session shows how to create a database user mapped to a SQL Server login. The topic assumes that a corresponding SQL Server login already exists.

To create a database user using SQL Server Management Studio

1. In SQL Server Management Studio, open Object Explorer and expand the Databases folder.

2. Expand the database in which to create the new database user.

3. Right-click the Security folder, point to New, and then click User.

4. On the General page, enter a name for the new user in the User name box.

5. In the Login name box, enter the name of a SQL Server login to map to the database user.

6. Click OK.

Using Script

Syntax

USE [database name] GO

CREATE USER [new user name] FOR LOGIN [login name]; GO

Real Time

USE [Employee]

GO

CREATE USER [Abcdef] FOR LOGIN [winter\victor]

GO

================================================================

Create a Database Schema

To create a schema by using SQL Server Management Studio

1.In SQL Server Management Studio, open Object Explorer and expand the Databases folder. 2.Expand the database in which to create the new database schema. 3.Right-click the Security folder, point to New, and then click Schema. 4.On the General page, enter a name for the new schema in the Schema name box. 5.In the Schema owner box, enter the name of a database user or role to own the schema. 6.Click OK.

Using Script

Syntax

USE [database name] GO

CREATE SCHEMA [new schema name]

AUTHORIZATION [new schema owner]; GO

Real Time

USE [Employee] GO

CREATE SCHEMA [Schemavictor]

AUTHORIZATION [ownvictor] GO

===================================================================

SERVER-LEVEL ROLES

To easily manage the permissions on SQL server, SQL Server provides several roles, which are security principals that group other principals. Roles are like groups in the Microsoft Windows operating system.

Server-level roles are also named fixed server roles because you cannot create new server-level roles. Server-level roles are server-wide in their permissions scope. You can add SQL Server logins, Windows accounts, and Windows groups into server-level roles. Each member of a fixed server role can add other logins to that same role. The following table shows the server-level roles and their capabilities.

Server-level roles are also named fixed server roles because you cannot create new server-level roles. Server-level roles are server-wide in their permissions scope.
You can add SQL Server logins, Windows accounts, and Windows groups into server-level roles. Each member of a fixed server role can add other logins to that same role.
The following table shows the server-level roles and their capabilities.

sysadmin
Description: Members of the sysadmin fixed server role can perform any activity in the server.
Example: 
EXEC master..sp_addsrvrolemember @loginame = N'RESEARCH\victor, @rolename = N'sysadmin'
GO

Serveradmin
Description: Members of the serveradmin fixed server role can change server-wide configuration options and shut down the server.
Example: 
EXEC master..sp_addsrvrolemember @loginame = N'RESEARCH\victor', @rolename = N'serveradmin'
GO

securityadmin
Description: Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions. Additionally, they can reset passwords for SQL Server logins.
Example:
EXEC master..sp_addsrvrolemember @loginame = N'RESEARCH\victor', @rolename = N'securityadmin'
GO

processadmin  
Description: Members of the processadmin fixed server role can end processes that are running in an instance of SQL Server.
Example:
EXEC master..sp_addsrvrolemember @loginame = N'RESEARCH\victor', @rolename = N'processadmin'
GO


setupadmin  
Description: Members of the setupadmin fixed server role can add and remove linked servers.
Example:
EXEC master..sp_addsrvrolemember @loginame = N'RESEARCH\victor', @rolename = N'setupadmin' GO

bulkadmin  
Description: Members of the bulkadmin fixed server role can run the BULK INSERT statement.
Example:
EXEC master..sp_addsrvrolemember @loginame = N'RESEARCH\victor', @rolename = N'bulkadmin'
GO

diskadmin 
Description: The diskadmin fixed server role is used for managing disk files.
Example:
EXEC master..sp_addsrvrolemember @loginame = N'RESEARCH\victor', @rolename = N'diskadmin'
GO

dbcreator  
Description: Members of the dbcreator fixed server role can create, alter, drop, and restore any database.
Example: 
EXEC master..sp_addsrvrolemember @loginame = N'RESEARCH\victor', @rolename = N'dbcreator'
GO

Public 
Description: Every SQL Server login belongs to the public server role. When a server 

principal has not been granted or denied specific permissions on a securable 

object, the user inherits the permissions granted to public on that object. 

Only assign public permissions on any object when you want the object

to be available to all users.

===================================================================
Database-Level Roles

  
To easily manage the permissions in your databases, SQL Server provides several roles which are security principals that group other principals. They are like groups in the Microsoft Windows operating system. Database-level roles are database-wide in their permissions scope.
There are two types of database-level roles in SQL Server: fixed database roles that are predefined in the database and flexible database roles that you can create.
Fixed database roles are defined at the database level and exist in each database. Members of the db_owner and db_security admin database roles can manage fixed database role membership. However, only members of the db_owner database role can add members to the db_owner fixed database role. There are also some special-purpose fixed database roles in themsdb database.
You can add any database account and other SQL Server roles into database-level roles. Each member of a fixed database role can add other logins to that same role. 

db_owner

Description: Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database

db_securityadmin
Description: Members of the db_securityadmin fixed database role can modify role membership and manage permissions. Adding principals to this role could enable unintended privilege escalation.


db_accessadmin 
Description: Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.

db_backupoperator 
Description: Members of the db_backupoperator fixed database role can back up the database.

db_ddladmin
Description: Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.

db_datawriter
Description: Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.

db_datareader
Description: Members of the db_datareader fixed database role can read all data from all user tables.

db_denydatawriter 
Description: Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.

db_denydatareader 
Description: Members of the db_denydatareader fixed database role cannot read any data in the user tables within a database.

==================================================
Password Policy
SQL Server provides several tools for configuring SQL Server features, connections, and services. This topic explains the tools that you can use to enable and disable features, such as stored procedures, Windows services, Web services, and remote client connectivity.
When it is running on Windows Server 2003 or later, SQL Server can use Windows password policy mechanisms.
SQL Server can apply the same complexity and expiration policies used in Windows Server 2003 to passwords used inside SQL Server. This functionality depends on theNetValidatePasswordPolicy API, which is only available in Windows Server 2003 and later versions.


Password Complexity
Password complexity policies are designed to deter brute force attacks by increasing the number of possible passwords. When password complexity policy is enforced, new passwords must meet the following guidelines:
• The password does not contain all or part of the account name of the user. Part of an account name is defined as three or more consecutive alphanumeric characters delimited on both ends by white space such as space, tab, and return, or any of the following characters: comma (,), period (.), hyphen (-), underscore (_), or number sign (#).
• The password is at least eight characters long.
• The password contains characters from three of the following four categories:
• Latin uppercase letters (A through Z)
• Latin lowercase letters (a through z)
• Base 10 digits (0 through 9)
• Non-alphanumeric characters such as: exclamation point (!), dollar sign ($), number sign (#), or percent (%)
Passwords can be up to 128 characters long. You should use passwords that are as long and complex as possible.


Password Expiration
Password expiration policies are used to manage the lifespan of a password. 

When SQL Server enforces password expiration policy, users are reminded 

to change old passwords, and accounts that have expired passwords are disabled.

Policy Enforcement
The enforcement of password policy can be configured separately for each SQL Server login. Use ALTER LOGIN (Transact-SQL) to configure the password policy options of a SQL Server login. The following rules apply to the configuration of password policy enforcement:

• When CHECK_POLICY is changed to ON, the following behaviors occur:
• CHECK_EXPIRATION is also set to ON unless it is explicitly set to OFF.
• The password history is initialized with the value of the current password hash.
• When CHECK_POLICY is changed to OFF, the following behaviors occur:
• CHECK_EXPIRATION is also set to OFF.
• The password history is cleared.
• The value of lockout_time is reset.
Some combinations of policy options are not supported.
• If MUST_CHANGE is specified, CHECK_EXPIRATION and CHECK_POLICY must be set to ON. Otherwise, the statement will fail.
• If CHECK_POLICY is set to OFF, CHECK_EXPIRATION cannot be set to ON. An ALTER LOGIN statement that has this combination of options will fail.

Important

CHECK_EXPIRATION and CHECK_POLICY are only enforced on Windows Server 2003 and later versions.
A known issue in Windows Server 2003 might prevent the bad password count from being reset after Lockout Threshold has been reached. This might cause an immediate lockout on subsequent failed login attempts. You can manually reset the bad password count by briefly setting CHECK_POLICY = OFF, followed by CHECK_POLICY = ON.

When SQL Server is running on Windows 2000, setting CHECK_POLICY = ON will prevent the creation of passwords that are:
• Null or empty
• Same as name of computer or login
• Any of the following: "password", "admin", "administrator", "sa", "sysadmin"

The security policy might be set in Windows, or might be received from the domain. To view the password policy on the computer, use the Local Security Policy MMC snap-in (secpol.msc).

Script:
USE [master]
GO
CREATE LOGIN [victor] WITH PASSWORD=N'winter123' MUST_CHANGE,
DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=ON,
CHECK_POLICY=ON
GO

Strong Password

Passwords can be the weakest link in a server security deployment. You should always take great care when you select a password. A strong password has the following characteristics:

• Is at least 8 characters long.
• Combines letters, numbers, and symbol characters within the password.
• Is not found in a dictionary.
• Is not the name of a command.
• Is not the name of a person.
• Is not the name of a user.
• Is not the name of a computer.
• Is changed regularly.
• Is significantly different from previous passwords.

Microsoft SQL Server passwords can contain up to 128 characters, including letters, symbols, and digits. Because logins, user names, roles, and passwords are frequently used in Transact-SQL statements, certain symbols must be enclosed by double quotation marks (") or square brackets ([ ]). Use these delimiters in Transact-SQL statements when the SQL Server login, user, role, or password has the following characteristics:
• Contains or starts with a space character.
• Starts with the $ or @ character.
If used in an OLE DB or ODBC connection string, a login or password must not contain the following characters: [] {}(),;? * ! @. These characters are used to either initialize a connection or separate connection values.


Conclusion

The SQL Server Security offers many core tasks and options for Server, database, table...etc. It’s very quick and intuitive to use.

===================================================================