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:
- 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_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).
General Notes:
- 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 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:
- Take the database's GUID (generated when the db is created), the object id (from sysobjects) and the colid (from syscomments) and concatenate them.
- Hash the key using SHA.
- Use the SHA hash as an RC4 key, generate a sequence of bytes equal in length to the stored procedure text.
- 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. 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
<
Integrated, Digest and Basic Authentication
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).
- 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
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.
are also numerous other performance and feature benefits to the programmer)
- Roll-based security and
authentication
- Can administratively construct
authorisation policies for an application down to a method level
- Enables you to manipulate
security information from all upstream callers in the execution chain of
your component. (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cossdk/htm/pgservices_security_9lo9.asp)
- Can administratively construct
- Client authentication
- Client impersonation and delegation
- Software restriction policies
Obtaining the DB Connection string and Connecting to the
Database
Here the objective is to:
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.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cossdk/htm/pgwritingcomponents_security_2e7n.asp
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
security.
- 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.
- COM+
- 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.
- De-encrypt routines (ideally
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:
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:
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:
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:
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:
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:
- 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
http://local.15seconds.com/issue/971130.htm
http://www.microsoft.com/ntserver/techresources/webserv/iissecure.asp
http://www.informnavigator.com/index.asp?ap=xp_crypto&am=about#xp_crypto
http://ciac.llnl.gov/ciac/bulletins/k-026.shtml
http://www.pwcrack.com/SQL/sql.html
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_2epl.asp
http://www.sqlsecurity.com/mb/view_message.asp?message_id=72
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=14035
http://www.sqlchile.cl/tips70.htm
http://www.xephon.com/oldcode/Q016A01