Cryptographic Functions in SQL 2005

The following functions support encryption, decryption, digital signing, and the validation of digital signatures.

Symmetric Encryption and Decryption

• EncryptByKey
• DecryptByKey
• EncryptByPassPhrase
• DecryptByPassPhrase
• Key_ID
• Key_GUID

Asymmetric Encryption and Decryption

• EncryptByCert
• DecryptByCert
• Cert_ID
• AsymKey_ID
• CertProperty
• EncryptByAsmKey
• DecryptByAsmKey
  
  

Signing and Signature Verification

• VerifySignedByAsmKey
• SignByCert
• VerifySignedByCert
• SignByAsymKey
  

Symmetric Decryption with Automatic Key Handling

DecryptByKeyAutoCert

Security Functions in SQL Server 2005

Lists the functions that return information that is useful in managing security

The following functions return information that is useful in managing security.

• CURRENT_USER (Transact-SQL)
• SETUSER (Transact-SQL)
• sys.fn_builtin_permissions (Transact-SQL)
• SUSER_ID (Transact-SQL)
• Has_Perms_By_Name (Transact-SQL)
• SUSER_SID (Transact-SQL)
• IS_MEMBER (Transact-SQL)
• SUSER_SNAME (Transact-SQL)
• IS_SRVROLEMEMBER (Transact-SQL)
• sys.fn_builtin_permissions (Transact-SQL)
• PERMISSIONS (Transact-SQL)
• SYSTEM_USER (Transact-SQL)
• SCHEMA_ID (Transact-SQL)
• SUSER_NAME (Transact-SQL)
• SCHEMA_NAME (Transact-SQL)
• USER_ID (Transact-SQL)
• SESSION_USER (Transact-SQL)
• USER_NAME (Transact-SQL)

Security Stored Procedures in SQL Server 2005

Following Store Procedure are useful while manageing the SQL server 2005

• sp_addalias
• sp_droprolemember
• sp_addapprole
• sp_dropserver
• sp_addgroup
• sp_dropsrvrolemember
• sp_addlinkedsrvlogin
• sp_dropuser
• sp_addlogin
• sp_grantdbaccess
• sp_addremotelogin
• sp_grantlogin
• sp_addrole
• sp_helpdbfixedrole
• sp_addrolemember
• sp_helpgroup
• sp_addserver
• sp_helplinkedsrvlogin
• sp_addsrvrolemember
• sp_helplogins
• sp_adduser
• sp_helpntgroup
• sp_approlepassword
• sp_helpremotelogin
• sp_changedbowner
• sp_helprole
• sp_changegroup
• sp_helprolemember
• sp_changeobjectowner
• sp_helprotect
• sp_change_users_login
• sp_helpsrvrole
• sp_dbfixedrolepermission
• sp_helpsrvrolemember
• sp_defaultdb
• sp_helpuser
• sp_defaultlanguage
• sp_MShasdbaccess
• sp_denylogin
• sp_password
• sp_dropalias
• sp_remoteoption
• sp_dropapprole
• sp_revokedbaccess
• sp_dropgroup
• sp_revokelogin
• sp_droplinkedsrvlogin
• sp_setapprole
• sp_droplogin
• sp_srvrolepermission (Transact-SQL)
• sp_dropremotelogin
• sp_validatelogins
  
  
  
  

SQL Server 2005 security best practices

SQL Server 2005 security best


Authentication

During the installation process, set the security mode to Windows authentication mode.
SQL or Mixed-mode authentication is not allowed

Password Policy

All SQL logon accounts must follow strong password policy
This policy applies to all SQL authentication accounts, even when the SQL server is set to use NT authentication only.The password policy support is for Win2k3 only. On other OS versions, the password check is hardcoded.

Password Policy Check

The password complexity policy flag must be ON.
By default, for new SQL logins, SQL 2005 will check the password policy (password strength and expiration).

Password expiration - A Flag to indicate whether the password expiration aspects of the local policy will be applied to the login (the default is yes).

Enforcing password expiration - To indicate whether this new expiration policy will apply to a login we use a property of the login called ‘Check expiration’.The default here is to always check the expiration policy. However, administrators can choose to not use this API during authentication checks and can reset this setting.Disabling password policy checks is in violation of corporate policy.

The Password policy can be checked by using:

SELECT name,is_policy_checked,s_expiration_checkedFROM sys.sql_logins

If the Value is 1, the flag is ON.

The Flag can be turned ON by issuing the following command:

ALTER LOGIN SET Check Policy = ON


Review Expired SQL Account passwords

Ensure that all SQL accounts are either Active or Disabled.
Determine the State of each account with regards to the password policy.

SELECT loginproperty(LoginName, 'property')

If the Property value is "IsExpired", remove the account if it is no longer used or change the password for login account.

Auditing

Auditing must be set at a minimum to "Failed Logins Only".

Logging and monitoring of failed SQL access is required. This provides an audit trail for administrators in the event of an attack.

Setup a scheduled task to run:findstr /c:"Login Failed" \mssql9\log*.* .Note: You may need to change the path for the log files based on your installation and SQL Server version. Check the Log path and modify the above statement accordingly.

SQL Services
Leave the following services OFF unless there is a need to turn them ON -

• - SQL Broker Service
• - Agent Service
• - Analysis Services
• - SQL Mail
• - Database Mail (SQLiMail)
• - Replication
• - DTS Service
• - Notification Services

The services are turned OFF by default.

For each unnecessary service:1. Stop the service if it is currently running.2. Configure the service startup type to either "Manual" or "Disabled". This will prevent the service from starting when the server is rebooted.

SQL CLR feature

The CLR feature must be disabled unless the application requires it
If you are not planning to use CLR feature on your SQL Server, you can turn off the SQL CLR support through the clr_enabled server configuration option. All normal DB operations will continue but you cannot create and execute CLR database objects. The user assemblies will not be allowed to loaded.

The way you can determine whether CLR is enabled is by executing:

SELECT * FROM sys.configurations WHERE name = 'clr enabled'

Turn off CLR support by executing:

EXEC sp_configure ‘clr_enabled’ 0



Use SQL Roles

Do not grant SQL object permissions directly to the SQL login accounts. Instead, grant permissions only to custom SQL database roles with SQL logins added to the SQL role.

Create custom database roles for each type of user. Grant to the role only the minimum permissions necessary for that type of user.User defined roles are only available as DB-scoped principals. Also, consider using domain security groups to grant permissions.

Administrators access

All administrators of the SQL Server should be granted access to the server through Windows 2k3 domain security group membership. BUILTIN\Administrators should not be a member of the sysadmin server role.

Users requiring SQL System Administration privileges shall be added to the Sysadmin fixed server role, either directly or through the use of Domain Security Groups. This prevents people from logging in as SA and only being known as SA. Machine Local Groups may not be used to grant Sysadmin privileges.

DB objects ownership

The db objects are owned by the sql dba, but the sa must have admin access to all the objects. No other account should have ownership rights over the objects.
Restrict create object permissions either to dbo, or to a custom role, which is not mapped to any Login or user account.

Application Accounts

Login accounts used by an application must not belong to any server roles (such as sysadmin).Login and user accounts used by an application must not belong to any built-in database roles (such as db_owner, db_datareader, etc.).

Applications must run with least user privilege. Granting blanket permissions through default server roles or default database roles does not follow this security principle.

Guest Account

Remove or deny access to the Guest account in each database on the server.

Any person with a valid SQL login can access any database that permits the guest account. This is not acceptable from a security perspective. Drop the guest user from production databases using sp_dropuser. The exception to this is the master and tempdb databases as the guest account is required in these databases.

The following scripts will drop and disable the guest account from your DB:

DROP USER guest

or

EXEC sp_revokedbaccess 'guest'

To check if the guest user has access to your DB:

SELECT * FROM sys.sysusers

If the hasdbaccess column value = 0, the user is disabled. If the hasdbaccess column value = 1 then the user has access to the DB. If the guest user is diasbled and if you try to drop it, you will get the following message: User 'guest' cannot be dropped, it can only be disabled. The user is already disabled in the current database.

ACL's on SQL Server files

Remove default access to SQL Server files and registries. Limit accessibility of the SQL server service account. Limit modifications to the SQL Server directories.

SQL Server files and directories should be protected from modifications by unauthorized users. Users will require read access to GUI binaries. Administrators will require modify access to SQL registries. The service account(s) used to run the SQL services will require read access to all SQL registries and directories, as well as write access to the data and log file directories, and some of the SQL registry.

The following exploitable stored procedures MUST be restricted to the sysadmin.

• xp_regaddmultistring,
• xp_regdeletekey,
• xp_regdeletevalue,
• xp_regremovemultistring,
• xp_regenumvalues,
• xp_regenumkeys,
• xp_regread,
• xp_regwrite,
  

The best practice is to not grant access to any extended stored procedures, but rather call them from a custom stored procedure, taking care to do the appropriate ACL's and validations within the sproc

xp_cmdshell,

sp_OA*

Do not enable SQLMail.

Instead use Database Mail.
The SQL mail uses SQL Server to send and receive emails and uses extended store procedures. The Database mail uses SMTP.

The database mail uses the following XP's:

• xp_activate_sqlimail
• xp_sqlimail_attachment_load
• xp_sqlimail_format_query
  

Profile security: Database Mail enforces security for mail profiles. You choose the database users that have access to a Database Mail profile. You can grant access to either specific users, or all users in a database. A private profile restricts access to a specified list of users. A public profile is available to all users in a database.Attachment size governor: Database Mail enforces a configurable limit on the attachment file sizeProhibited file extensions: Database Mail maintains a list of prohibited file extensions. Users cannot attach files with an extension that appears in the list.Off by default: Database Mail messaging objects are not installed by default. To use Database Mail, you must explicitly enable Database Mail by creating a mail host database.

LinkedServers

Delete the linked server entries that are no longer needed.

This is to prevent unauthorized access to linked servers. Pay special attention to the login mapping between the local and remote servers. Use logins with the bare minimum privileges (Read only if data is read from other servers. Read and Write only if data is read and written to the other server) for configuring linked servers.

To list all active linked server use the

sp_LinkedServers sp

To drop a Linked server Login info use the

sp_dropLinkedSrvLogin sp

To drop a linked server, use the

sp_dropserver sp

Run SQL Server under a Domain Service or Local Service account.

1) Domain Service account -

Advantage - Can access network resources. Can be granted lesser privileges on a box? DisAdvantage – Password required update after every 70 days. Master Keys must be backed up.

2) Local Service –

Advantage - Low level account than the Local system.

DisAdvantage – Cannot access network resources. Will not work in Linked server scenario. Since most of our SQL servers are linked to other SQL servers, this account will not work, unless you run your linked server under a domain account?

3) Network Service (Machine $ account) –

Advantage - Lesser permissions than the Local System and Local service.

The Password is updated automatically after 70 days.

DisAdvantage – Can access network resources as a computer account. Anyone on the machine can access the SQL Server resources. Master Keys must be backed up.
If you run your SQL Server service using a domain account, a malicious SQL Server user could take advantage of these domain privileges. However sometimes the service account needs to be a domain account. This may occur when the SQL server is linked to other servers, or when the SQL Server establishes a connection with another server. The key to securing this account is to grant as few permissions as possible to the account, both on this machine, as well as any others. The account must not be added to the local machine administrators group, and should not be an administrator on any other server either.