SQLServerCentral Article

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

To do this:

  1. Ask around, many companies have security administrators and one thing they are always on top of is password structure and expiration
  2. ensure application logic forces these minimum requirements
  3. consider how password expiration will work
  4. 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).
  5. 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
  6. 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.
  7. ensure password “resetting” is properly managed in terms of
    1. when a reset can occur
    2. how it will be done
    3. what is it set to
    4. 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).

General Notes:

  1. You can hack passwords by re-creating the sp_addlogin routine and adding code to record the password before its encrypted
  2. You can use pwdencrypt and pwdcompare yourself (unsupported of course) for password management
  3. Login information is stored in syslogins or sysxlogins in the master database

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

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:

  1. Deny SELECT access to PUBLIC against syscomments
  2. Deny create and drop procedure commands
  3. 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. 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 procedures.

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

All information schema owned objects – select only

All views – select

Most functions

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

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 & Authentication

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.

Note: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 database.

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.
  • Integrated, Digest and Basic Authentication

  • 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 3)
  • b)

    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:

    1. index audit tables accordingly
    2. b)

      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)

    You rated this post out of 5. Change rating




    5 (1)

    You rated this post out of 5. Change rating