In part one we covered a variety of
security recommendations primarily for production systems. In part two we
continue to explore security at a variety of levels. We will cover:
- SQL Agent Service Account
- Password management and “blank”
- DTS – More Information on Security
- Issues with using the WITH ENCRYPTION
option (de-encrypting the encrypted)
- The PUBLIC role
- DBO access in production
- Statement Privilege Revocation
- Application Security – Connection
Strings & Authentication
- SQL Server Auditing
- Application Auditing – User Defined
Triggers and Audit Tables
SQL Agent Service Account
I came across a problem the other day in
relation to altering the BUILTIN/Administrators account (as discussed in Part 1)
that was not mentioned. This is to do with the default set up of the SQL Agent
service account. By default, the service will be started via the local
system account and its SQL Server connection via “windows authentication”.
As such, altering the BUILTIN/Administrators account and revoking sysadmin
access will prevent the service from running jobs. Funny enough, you will see
no errors in the SQL Server log or SQL Agent log in relation to this. Jobs will
simply not start and their “next run date” will be “unknown”. In the BOL, it
clearly highlights the fact that the SQL Agent server connection must have
sysadmin access. If you alter the BUILTIN/Administrator login and revoke
sysadmin, remember that this adversely affects the SQL Agent database connection
that must be altered accordingly.
Password management and “blank” passwords
The SA account should always have a
password set if Windows Authentication is not being used as the primary means of
connecting to gain sysadmin privileges. The “blank” password problem does not
stop at the SA account, the DBA should be very strict in mixed mode security
environments and force well structured form and length requirements for
To do this:
Ask around, many companies have security administrators and one thing
they are always on top of is password structure and expiration
ensure application logic forces these minimum requirements
consider how password expiration will work
consider utilising Active Directory and Windows authentication for
end-user access to your web-site code, then uses embedded logic within COM+ to
connect to SQL Server (see later).
if you are using active directory, revise password creation and
expiration with the system administrator, ensure the application developers look
for expiration times in their code
try not to modify the internal SQL Server sp_ procedures for login
creation. It can mean problems when service packs are applied, and difficult to
properly maintain without affecting the overall integrity of the DBMS.
ensure password “resetting” is properly managed in terms of
when a reset can occur
how it will be done
what is it set to
is the process auditable
When tracing SQL Server via Profiler,
you will notice sp_addlogin commands are not included with the following:
was found in the text of this event.
-- The text has
been replaced with this comment for security reasons.
By default, sp_addlogin has the [@encryptopt
=] 'encryption_option' option set to NULL (ie.
encrypt login password). This does not mean your application programmers will
be using it. Programmers and DBA’s should leave it as default where possible.
Funny enough, the sp_addlogin routine and many other master stored procedures
are not encrypted, and you will see that SQL Server uses a pwdencrypt
routine to do the encryption. What this is exactly I am not sure so I cannot
comment on its robustness (see WITH ENCRYPTION section below).
You can hack passwords by re-creating the sp_addlogin routine and adding
code to record the password before its encrypted
You can use pwdencrypt and pwdcompare yourself (unsupported of course)
for password management
Login information is stored in syslogins or sysxlogins in the master
DTS – More Information on Security
In part one I briefly mentioned DTS
security. It is important to remember that SQL Servers access model for DTS
packages is, well, limited. Meaning that only members of sysadmin can
edit and save any package they like, but all other users are restricted only to
the packages they initially created themselves. This holds true even when you
try and give users dbo access to the MSDB database in a vain attempt to “share”
packages with other database users. In the end, there is not way (yet) to get
around this problem of package sharing (if there is, I would love to hear it!).
Using the WITH ENCRYPTION option
I always wanted to believe that SQL
Server 2k under Windows 2000 was using the Windows Crypto API and as such, was
using a key complex enough to make hacking very difficult. Then I came across
this article from
I have to say that security specialists
and hackers are some of the most sarcastic people I know! but with good
reason. The articles example, which I have tested under SQL Server 2k, shows
you all the code of a stored procedure created with the WITH ENCRYPTION option.
Here is an extract from this article
regarding SQL Server 2k encryption:
Here is how stored procedure
(and view and trigger) encryption works on SQL Server 2000:
1. Take the database's GUID (generated when the db is created), the object id
(from sysobjects) and the colid (from syscomments) and concatenate them.
2. Hash the key using SHA.
3. Use the SHA hash as an RC4 key, generate a sequence of bytes equal in length
to the stored procedure text.
4. XOR this stream of bytes against the stored procedure text.
The example given in the article works
with any user that has priviligies to create and alter procedures and select
access against syscomments. To help mitigate the problem:
Deny SELECT access to PUBLIC against syscomments
Deny create and drop procedure commands
See previous notes on restricting DBO access and sysadmin access
to your database.
With all this said, I believe it is
still worth the effort to encrypt code. It is though, imperitive that your test
t-sql code execution thoroughly to determine the CPU impact this may have on
execution (though I would imagine it to be very small).
The PUBLIC role
After reading a variety of articles,
this role and its defaults can be a real problem and hole for hackers. A
classic example is provided in this article
http://www.securiteam.com/windowsntfocus/6N0010U0KW.html concerning buffer
overflow vulnerabilities with extended stored procedures. The fix being the
application of a service pack or hot fix from Microsoft or simply denying
execute access to PUBLIC in the master database for selected extended stored
You have to firstly ask yourself how did
someone gain access to your master database and to gain execute permission, and
even more so if your application is a web-based one. The security
recommendations in part one and this article cover some recommendations on how
this can be prevented.
The PUBLIC role by for your default,
will give you access to:
information schema owned objects – select only
All views –
virtually all stored procedures and selected extended procedure
All sys tables except sysfile1, sysfulltextnofity, sysproperties
You cannot remove the guest user
from the PUBLIC role in the master database. No user should exist in the master
database other than dbo (sa). For all custom created database users that really
do require access, restrict access to critical tables that hackers may use to
gain further information from your database (or de-encrypt your code - see
DBO access in production
I can think of no valid reason what so
ever for DBO privilege access for any standard (ie. non sysadmin or
backup admin) user in production. There should be no DDL changes, the
auditing is all in place, and any calls to a predefined SQL Server extended
stored procedure has been sorted out the appropriate access granted. With
proper role management DBO is not an option for any application.
The only problem here can be third party
software that utilises SQL Server as its security or other repository in order
to run. I have come across a few products that prompt to for the SA password,
then end up creating a database with no other user but SA, only to spend hours
reading documentation and testing to change it.
Statement Privilege Revocation
In the test and production environments,
user accounts do not require create table, stored procedures, create view,
create function. These and other statement permissions can only be granted
to database users by the DBO or sysadmin privileged users. By default, they are
not assigned but through the course of development access may have been given,
therefore, the DBA should revoke the privileges.
I like to create two roles early in the
development cycle with the following privileges so I can control user DDL
An alternate option is to use the
pre-defined database role db_ddladmin which allows all DDL (except
grant, revoke or deny statements) and can also use sp_changeobjectowner,
sp_dboption, sp_procoption, sp_recompile, sp_rename, and sp_tableoption. What
ever your method of managing DDL access, revoke all privileges in TEST and PROD
as they are simply not required for database users.
Application Security – Connection Strings &
The following suggestions are based on
this server set-up:
The first aim is to hide and/or secure
the connection string used at the Web-Server in order to connect to the
database. This does not stop a hacker from installing their own COM/COM+
objects that talk to your existing methods and utilise the business logic
exposed through them. But you do want to stop at all costs complete access to
your user database through an insecure connection string placed in an INI file
on the server. The second aim is to determine the access method to the
database. We can either use mixed mode or windows authentication.
It is important to understand that as
remotely possible it may seem to the programmers, security is of utmost
importance in production and a text file based INI file is not acceptable.
Altering data over a period of time (or even once!) is the worst type of hacking
as it can span many backups and it the implications can be extremely costly to
The ideas below can be cumulative, ie. they do not necessary describe a complete
solution, but a variety of ideas for securing general connectivity to the
- Client to Web Server
- Consider HTTPS where appropriate.
I have worked with the Athelon SSL accelerator (hardware) with great
reliability and performance. Such hardware can selectively encrypt/decrypt
and supports hundreds of simultaneous SSL connections per second but is a
very expensive option and is another “point of failure”. Remember that once
on the Internet, network sniffers can make short work of passwords and
sensitive business data (to and from your server).
- The authentication mode at the
webserver and the associated web-pages and folders is the key to web-site
security (authentication modes summarised below). In the example above, a
common method of authentication is via basic or integrated authentication to
the web-site and all associated pages. The user has an account in Active
Directory to facilitate the login and may be internal and external users
(typically grouped and segregated). Authentication to SQL Server may be via
common userid/password combination (shared account) for all users or fully
- Integrated, Digest and Basic
- Under IIS, if all are selected then
the highest to the lowest level of security will be used in order based on
what the client initially accepts. An NTFS partition must be used for
Digest and Integrated.
- Standard HTTP over readable text
stream (base 64 encoded string) to the server unless HTTPS is used or a
lower level protocol encryption is employed.
- User is only prompted for login
ID and password
- Used by many organisations to
prevent from being promoted with domain (see integrated security).
- Sends a hashed password over the
network rather than the encoded password in Basic.
- User/password prompted must be a
valid NT user and has appropriate NTFS permissions on the object(s) it is
trying to access.
- Works via a challenge/response
scheme and checksum.
- Uses underlying Windows
cryptographic data exchange with IE. This enabled single-sign-on where
appropriate if the user is already logged into the domain IIS is talking
- User prompted for login, password
and domain. This can be confusing for end-users where we are
authenticating via a Domain. I have yet to see a program that utilises
integrated security and pre-enters the domain field in the login prompt.
- COM+ (from a security context - there
are also numerous other performance and feature benefits to the programmer)
- Roll-based security and
- Client authentication
- Client impersonation and delegation
- Software restriction policies
Obtaining the DB Connection string and Connecting to the
Here the objective is to:
Store connection strings securely at the webserver
Encrypt communication between webserver and database server
Ideally, encrypt data where appropriate between client and webserver (out
of scope for this section)
Use only Windows Authentication for SQL Server login, deny local server
login for these accounts.
Avoid using the global.asa
Presented below are a series of diagrams
that provide a catalyst for ideas in achieving this security. The examples all
assume a COM+ (Component Services) business layer as a default for Enterprise
Computing. Substitute this for COM as required. I do not recommend straight DB
connection from ASP (global.asa variables etc) to the database for any sort of
internet based application.
General notes on the examples below:
- Consider application roles in SQL
Server to set and configure security context. Remember though only 1 role at
a time as security privileges are not additive.
- Add registry entries and protect via
regedit32.exe for database connection strings. The COM+ package will run as a
specific local domain user and connects to the database via integrated
- As per 1, but rather than using the
registry with encrypt the INI file via Windows 2k encrypted file system (EFS).
As the encryption is based on a specific user profile this can be a little
more difficult to manage if the COM+ package runs as a different user.
- Set-up a series of User DSN’s that
are only accessible by the Windows user that created them. These typically
map to a 1:1 SQL Server login.
- Rather than using the registry, use
Active Directory (Win 2k) for other 3rd party LDAP directory to
store connection strings. Access is restricted via Windows Authentication and
is always read-only.
- Consider a global “security or
management” database. Such a database includes a range of information for
connectivity, login/password (encrypted) combinations, security contexts and
lookups to establish connections and use the application.
- De-encrypt routines (ideally
talking to a lower level crypto API) to de-encrypt and pass connection
strings and passwords to establish connectivity.
- COM+ supports shared memory areas
that can use used to collectively manage connectivity settings and logins
amongst packages and associated methods.
- Supports integrated security at a
package level to individual users and/or groups.
SQL Server Auditing
Selecting the properties of the server
via Enterprise Manager and the security tab, you have four audit options:
None (default) – audit level 0
Success – audit level 1
Failure – audit level 2
All – audit level 3
This will run the following extended
This is the only audit option you
have. The audit entries will be written to the NT event log and the SQL Server
error log, an example of an audit entry is shown below:
NT Event Log (under application )
Login succeeded for user 'NT
AUTHORITY\SYSTEM'. Connection: Trusted.
information, see Help and Support Center at
SQL Server Log
for user 'sa'. Connection: Non-Trusted.
for user 'NT AUTHORITY\SYSTEM'. Connection: Trusted.
At a minimum you should track login
failures. If applications have been tested then you should never get failures.
Take care with the event log for the OS as it can fill quickly. You also need a
method of notifying you of strange, multiple, login failures.
In Part 3 we will discuss C2 and SQL
Profiler auditing as the next form of SQL Server auditing options.
Application Auditing – User Defined Triggers and Audit
Unlike Oracle, SQL Server does not
include any inbuilt mechanisms to assist with user database auditing. To
resolve this, the DBA turns to triggers at the database level to track
insert/delete/update changes to all or a selected number of tables. It is
important that you force the analysts to clearly outline the audit requirements
at the business level and stick to it, namely the:
tables and or views to be audited
minimum set of data to keep in regard to the audit
date time, login user etc
expectations of historical data to be kept
impact the audit will have on your OLTP system
how will it be viewed? (do you need to index the audit tables, join to
other audit tables?)
is there a “cascaded” audit requirement?
The analyst should write this down for
you, do not accept any verbal descriptions of the auditing process.
Once identified, you need to setup the
audit tables and the table structure. As a general rule I always run with a 1:1
copy of the table to be altered for all insert/delete/update conditions and
aud_id, integer : unique audit ID (identity)
aud_type, tinyint : 1 = insert, 2 = delete, 3 = update
consider a small lookup table as a reference for the above (you will
Under v7, be careful with the aud_id
column that utilises an IDENTITY. If your application, after a successful
insertion, uses @@IDENTITY to retrieve the last identity value inserted you will
pick up the aud_id identity value and not the p.key identity value for the table
(if used). There was no way to get around this problem. In SS2k, this has been
resolved via the SCOPE_IDENTITY() function but makes programming cross DB
compatible applications difficult to program. Therefore, carefully consider and
test the use of identity columns on audit tables.
The aud_user column can be populated
from SYSTEM_USER. This is fine when we have a physical person mapped to a
single login, but in many cases this is not possible. As such, the application
programmers will include standard columns for all tables such as:
last_updated_by (is the “real” user making the
last_updated_on (datetime can map to aud_date)
last_update_count (used to assist with concurrency issues in stateless
I use a small custom built utility via
SQL-DMO for generating the audit tables. The setup is:
create “audit” user call “aud”, create table access for this user.
create new file-group for the DB to hold our audit tables
run audit utility
this will create the 1:1 audit table in the audit file-group owned by the
revoke aud user create table and all other DDL access
naming convention is easy to use, ie. <table-name>_audit
[dbo].person (myapp_data filegroup)
[aud].person_audit (myapp_audit filegroup)
In order for the triggers to work, I
create a role called:
insert/select access to all aud.* tables
and grant it to all database users.
Only the “aud” user (owner) can delete and update data in the aud tables. I
like this method because:
easy to identify all audit tables via the owner
audit tables have their own filegroup to easy of maintenance
can quickly deny select against the aud.* tables via the role
apart from those with dbo access, slightly more difficult to access the
Be aware that this type of auditing has
SQL Server does not support ntext, text, image for the inserted and
delete trigger tables.
Can be resolve with instead-of triggers but this means additional
You can not manipulate timestamp columns
Lost initial update problem
If you do not fire the trigger for every single row in the table
(insert), then any initial update will mean the audit record is useless as you
will know what the record looked like before this first ever update.
Other options include:
Using views to hide underlying physical implementation (discussed in Part
Utilising 3rd party audit tools (why code your own?)
Application level auditing rather than
Auditing to a single, generic table that suits all audit requirements
Transaction log reading/extraction tools (faster and more efficient
Do not forget to:
index audit tables accordingly
determine business rules for clearing/dumping audit records
schema changes must be reflected in the triggers and audit tables (common
remember the text, ntext and image restrictions and communicate these to