Although security is a major component of database administration, it is sometimes overlooked in favour of convenience. User accounts are given elevated permissions to save time, patches and hot-fixes are not applied timely and best practices are often not followed. Over time, the server becomes vulnerable to potential breaches of security.
As the DBA, you need to know how vulnerable your SQL Servers are before you can start securing them. To get this answer, you will first need to conduct a “security audit”. This audit should give you a baseline picture and help you find potential loopholes. Once you have the report and management approval, you can start locking down where necessary.
In this series of articles, I will try to provide a guideline for a security audit. This can be discussed under three broad categories, namely Server Level Audit, Database Level Audit, Operating System Level Audit. Accordingly each will be discussed in a separate article.
Server level audit
At this level, you are primarily interested in the properties of the SQL Server itself and its vulnerabilities.
Service pack level
Start by looking at the service pack level of the SQL Server. SQL Server service packs are cumulative and can contain cumulative updates or hot-fixes related to security. If the database server does not have the latest service pack installed, don’t automatically assume it is a security threat: in fact there may be a good reason it is not there (perhaps the latest SP causes an application to fail). However you should still need to note this down, and ask questions later.
SQL Server security properties
Your security audit report should include the type of authentication mode used in the SQL Server (Windows or Mixed). You can also note how login attempts are logged and if any other security specific options have been set (e.g. C2 audit mode). You can find out this information from the Security tab of the SQL Server property window.
Service account
Previous to SQL Server 2005, the account used to run the SQL Service needed administrator privilege in the local machine. This is no longer the case: a SQL service account does not need to be a local administrator anymore. The principle of least privilege dictates that the service account should be just like any other domain account with minimal permissions.
Unfortunately, many organisations still grant the SQL service account local and even domain administrator privileges. If your network is a mixture of 2000 and 2005 (and perhaps 2008), chances are that you will have machines running the newer versions of SQL Server with the old service account that has administrator privilege.
Your security audit should identify the account(s) used for running SQL Server, Agent and other related services like SSIS. You can check with your network administrators to see if the account is a domain administrator. You should also check the local administrators group to see if the account is a member there (more on it later).
Sysadmin server role
Auditing the sysadmin server role will probably be the most important part of your audit. This is because SQL Server system administrators have almighty rights on the database server. They can add or drop databases, logins, linked servers, jobs or change any server configuration parameter. Unfortunately, there is a tendency in many SQL Server shops to grant unnecessary elevated permissions to individual user accounts. Applications accounts needing only database creation permission at installation time are made sysadmin members instead of being granted dbcreator privilege. Active Directory accounts of people who have left the company are often still found under the sysadmin fixed server role.
For SQL Server 2005 and latter versions, you can execute a script like the following to find out what login accounts are members of this role:
SELECT c.name AS Sysadmin_Server_Role_Members
FROM sys.server_principals a
INNER JOIN sys.server_role_members b
ON a.principal_id = b.role_principal_id AND a.type = ‘R’ AND a.name =’sysadmin’
INNER JOIN sys.server_principals c
ON b.member_principal_id = c.principal_id
For SQL 2000, you can execute a command like the following:
SELECT loginname AS Sysadmin_Server_Role_Member
FROM syslogins
WHERE sysadmin = 1
There are a number of accounts which are members of sysadmin role by default. The “sa” account is one of them. SQL Service accounts become members of local Windows groups created at installation time. These groups are given sysadmin privilege in turn. Other service or application specific accounts often need this privilege as part of their operation. However, if you find accounts under this role that should not be there in the first place, make notes.
Server level permission for logins
Instead of making a login a member of a fixed server role like the sysadmin, SQL Server now allows the DBA to assign specific server level rights to it. This allows a more granular control over an individual login’s access rights. Examples of such rights are IMPERSONATE, TAKE OWNERSHIP, VIEW DEFINITION, CONTROL, CONNECT, CONTROL SERVER etc. Some of these rights apply at the server level (SERVER scoped), some to endpoints (more on it later) while others to logins.
You can run a simple query like the following to get a list of server level permissions for logins:
SELECT a.name AS Login_Name,
a.type_desc AS LoginType,
b.permission_name AS Permission
FROM sys.server_principals a
INNER JOIN sys.server_permissions b
ON a.principal_id = b.grantee_principal_id
WHERE a.type <> ‘R’ — Excludes Server Role
AND a.name NOT LIKE ‘##MS_%’ — Excludes system level certificate mapped logins
AND a.name <> ‘sa’ — Excludes the obvious system administrator
AND b.state = ‘G’ — Granted
ORDER BY a.name