SQL Server Security Part 2


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:


-- 'sp_addlogin'

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_optionoption 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:

Master DB’s


information schema owned objects – select only

                        All views –




                        Execute to

virtually all stored procedures and selected extended procedure           

User DB’s


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

previous point).

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 organisation.



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



Authentication Example



  • 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.

    • Basic
      • 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).

    • Digest
      • 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.

    • Integrated
      • 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)


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:


  1. 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.

  2. 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


  3. 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. 

  4. 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.

  5. 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. 

  6. 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.

  7. COM+
    1. De-encrypt routines (ideally

      talking to a lower level crypto API) to de-encrypt and pass connection

      strings and passwords to establish connectivity.

    2. COM+ supports shared memory areas

      that can use used to collectively manage connectivity settings and logins

      amongst packages and associated methods.

    3. 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

stored procedure:


xp_instance_regwrite N'HKEY_LOCAL_MACHINE',




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 )


18453 :

Login succeeded for user 'NT

AUTHORITY\SYSTEM'. Connection: Trusted.

For more

information, see Help and Support Center at



SQL Server Log


Login succeeded

for user 'sa'. Connection: Non-Trusted.

Login succeeded

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

these columns:


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



aud_date, datetime


aud_user, varchar(50)


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

“aud” user


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

aud.* tables


Be aware that this type of auditing has

some problems:


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

“I forgot”)


remember the text, ntext and image restrictions and communicate these to

the analysts


Web References
















5 (1)




5 (1)