SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL Server Security Part 2

By Chris Kempster, (first published: 2004/01/30)

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:

a)      Ask around, many companies have security administrators and one thing they are always on top of is password structure and expiration

b)      ensure application logic forces these minimum requirements

c)      consider how password expiration will work

d)      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).

e)      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

f)        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.

g)      ensure password “resetting” is properly managed in terms of

a.       when a reset can occur

b.      how it will be done

c.       what is it set to

d.      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: 

a)       You can hack passwords by re-creating the sp_addlogin routine and adding code to record the password before its encrypted

b)       You can use pwdencrypt and pwdcompare yourself  (unsupported of course) for password management

c)       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 www.SecuriTeam.com:




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:


a)      Deny SELECT access to PUBLIC against syscomments

b)      Deny create and drop procedure commands

c)      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 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 over.
      • 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 Database


Here the objective is to:

a)      Store connection strings securely at the webserver

b)      Encrypt communication between webserver and database server

c)      Ideally, encrypt data where appropriate between client and webserver (out of scope for this section)

d)      Use only Windows Authentication for SQL Server login, deny local server login for these accounts.

e)      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 security.
  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:

1)      None (default) – audit level 0

2)      Success – audit level 1

3)      Failure – audit level 2

4)      All – audit level 3


This will run the following extended stored procedure:


xp_instance_regwrite N'HKEY_LOCAL_MACHINE',

   N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',N'AuditLevel', REG_DWORD,2


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 http://go.microsoft.com/fwlink/events.asp.


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 Tables


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:

a)      tables and or views to be audited

b)      minimum set of data to keep in regard to the audit

a.       date time, login user etc

c)      expectations of historical data to be kept

d)      impact the audit will have on your OLTP system

e)      how will it be viewed? (do you need to index the audit tables, join to other audit tables?)

f)        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:

a)      aud_id, integer : unique audit ID (identity)

b)      aud_type, tinyint : 1 = insert, 2 = delete, 3 = update

a.       consider a small lookup table as a reference for the above (you will forget!)

c)      aud_date, datetime

d)      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 change)

last_updated_on (datetime can map to aud_date)

last_update_count  (used to assist with concurrency issues in stateless environments)


I use a small custom built utility via SQL-DMO for generating the audit tables.  The setup is:

a)      create “audit” user call “aud”, create table access for this user.

b)      create new file-group for the DB to hold our audit tables

c)      run audit utility

a.       this will create the 1:1 audit table in the audit file-group owned by the “aud” user

d)      revoke aud user create table and all other DDL access

e)      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:


            user_audit_cr_access, 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:

a)      easy to identify all audit tables via the owner

b)      audit tables have their own filegroup to easy of maintenance

c)      can quickly deny select against the aud.* tables via the role

d)      apart from those with dbo access, slightly more difficult to access the aud.* tables


Be aware that this type of auditing has some problems:

a)      SQL Server does not support ntext, text, image for the inserted and delete trigger tables.

a.       Can be resolve with instead-of triggers but this means additional programming.

b)      You can not manipulate timestamp columns

c)      Lost initial update problem

a.       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:


a)      Using views to hide underlying physical implementation (discussed in Part 3)

b)      Utilising 3rd party audit tools (why code your own?)

c)      Application level auditing rather than

d)      Auditing to a single, generic table that suits all audit requirements

e)      Transaction log reading/extraction tools (faster and more efficient auditing)


Do not forget to:


a)      index audit tables accordingly

b)      determine business rules for clearing/dumping audit records

c)      schema changes must be reflected in the triggers and audit tables (common “I forgot”)

d)      remember the text, ntext and image restrictions and communicate these to the analysts


Web References















Total article views: 35121 | Views in the last 30 days: 15
Related Articles

Stairway to SQL Server Security Level 11: Auditing

By defining server- and database-level audits, you can record just about any kind of event that occu...


SQL Server System Audit Report

Ensuring that your SQL Server is secure is the job of every Database Administrator. In this article ...


How to secure SQL Server audit log

How do I secure the SQL Server audit log to prevent tampering, deleting, clearing, etc?


Auditing "sysadmin" access to SQL Server 2005

Security Auditing Requirements for "sysadmin"


Linked Server to secure Access DB from SQL Server 2005

Linked Server to secure Access DB from SQL Server 2005

sql server 6.5    
sql server 7