This morning was a jammed filled session covering off a lot of changes made to Microsoft SQL Server 2005. Over the last few weeks we talk exclusively about Front End security issues such as Input trust and the creation of a Development and Design environment to better emulate your production environment. However, today we switched gears by examining the security enhancements made to SQL Server 2005.
A good place to start the talk on was with the Surface Configuration Tool. By default many features including xp_cmdshell, clr and even remote connections have been turned off in SQL Server 2005. Therefore, if you were to immediately start developing in SQL Server 2005 you might start experiences security errors when invoking some of these features. Thus, the Surface Configuration Tool is an excellent way to examine which features are disabled and also provides the ability to enable the particular features required. The SQL Server Surface Area Configuration tool is located under All Programs, Microsoft SQL Server 2005, Configuration Tools.
Authentication has changed in SQL Server 2005 with the support of Windows Password Policies enforcements when creating SQL Server login accounts. This is an excellent way to enforce strong passwords and an expiration policy on passwords depending upon your security needs. This can be configured on a per-login basis as demonstrated with the script below:
create login foo with password='@#Hkjsdf#$#VDSVSQ@!',
select * from sys.sql_logins
declare @name nchar(100)
SET @name ='foo'
SELECT LOGINPROPERTY( @name, 'PasswordLastSetTime' ) AS PasswordLastSetTime,
LOGINPROPERTY( @name, 'IsExpired' ) AS IsExpiried,
LOGINPROPERTY( @name, 'IsLocked' ) AS IsLocked,
LOGINPROPERTY( @name, 'IsMustChange' ) AS IsMustChange,
LOGINPROPERTY( @name, 'LockoutTime' ) AS LockoutTime,
LOGINPROPERTY( @name, 'BadPasswordCount' ) AS BadPasswordCount,
LOGINPROPERTY( @name, 'BadPasswordTime' ) AS BadPasswordTime,
LOGINPROPERTY( @name, 'HistoryLength' ) AS HistoryLength,
LOGINPROPERTY( @name, 'PasswordHash' ) AS PasswordHash
drop login foo
Schemas have been added as an abstract between the database and the owner of the objects. Therfore, by assigning objects to schema it is possible to drop users without rewriting your applications as the name resolution is no longer depend upon the user or principals names. We can continue to use the default schema of dbo similar to what we are used to in SQL Server 2000. However, if your application creates objects in the database and you want those objects to be created under the dbo schema then you must grant your application dbo privileges when connecting to the database. This will increase the attack surface of your application as well increasing the severity if your application is vulnerably to SQL Injection attacks. Schemas are also a nice mechanism to scope your permissions. For example, you can grant select permission on a schema. This will grant select permission to all the tables in that schema alone but not the other tables in other schemas.
Execute Content provides an excellent mechanism to have your modules such as functions, proc and triggers to run under a different user context then the caller of the module. The permissions that one can assign in SQL Server 2005 is very granular especially compared with SQL Server 2000, however, if you are unable to provide a single permission attribute to some database users then you can use execute content. Therefore, the proc can run under a privilege account that has certain permission such as truncate table, and the caller only has to be granted execute permission on the proc itself and not truncate table. Highlighted in the demo script below:
--This example will create 3 users
-- User1 will have a table MyTable
-- User2 will have a stored proc that select's the table
-- User3 will have execute permissions on User 2's stored proc
--This demo show the use of EXECUTE AS functionality
--Create our users
create login Login1 with password='(*&sdf87786sdf'
create login Login2 with password='(*&sdf87786sdf'
create login Login3 with password='(*&sdf87786sdf'
create database ExampleDB
--User 1 will have a table
create user User1 for login Login1 with default_schema=User1
create schema User1 authorization User1
--User 2 will have SELECT access and write a proc to access
create user User2 for login Login2 with default_schema=User2
create schema User2 authorization User2
--User 3 will have the right to exec the proc
create user User3 for login Login3 with default_schema=User3
create schema User3 authorization User3
grant create table to User1
grant create proc to User2
execute as login='Login1'
create table User1.MyTable
insert into MyTable values (1)
insert into MyTable values (2)
insert into MyTable values (3)
grant select on MyTable to User2
execute as login='Login2'
--create a stored proc that will return the rows in our table
create proc ViewMyNumbers
select * from User1.MyTable
grant execute on ViewMyNumbers to User3
execute as login='Login3'
--Can't access table directly
--I can't execute the proc since I don't have permissions on the unlying table
--What I can do is alter the proc and set it to "execute as owner"
ALTER PROCEDURE ViewMyNumbers
WITH EXECUTE AS OWNER
--Still can't access table directly
--Now I can access it and I didn't have to give User1
--any permissions on MyTable
SQL Server now provides build DDL statements and functions for encrypting and decrypting data inside the database and not necessary in manage code anymore. Encryption has never really been difficult but rather the management of the keys becomes the overwhelming issue. Therefore, SQL Server 2005 supports key management solution including the ability to manage the protection of the keys through a password that must be supplied by an application or an user. Or the protection of all keys in a database can be rooted under the Database Master key which is protected by default of the Service Master Key using the DPAPI for the instance of the SQL Server. I would highly recommend that you visit this excellent blog on certificates and Keys in SQL Server 2005. Demo script is provided below:
--Encrypt content demo
--Create Database, Users, Schemas, Table Object--
Create Database AccountsDB
--create two logins that will be used
--logins for two consultants in a investors office
Create login Sheila with password='Capucci4!'
Create login Jon with password='Capucci4!'
--create users with Default schema
Create user Sheila with DEFAULT_SCHEMA=Fin
Create User Jon with DEFAULT_SCHEMA=Fin
Create SCHEMA Fin Authorization Sheila
--Create Table to client table
Create table Fin.Clients (Id int, clientname nvarchar(30),
SIN varbinary(100), Portfolio varbinary(100))
--Assign permission to table
grant select, insert on Fin.Clients to Sheila
grant select, insert on Fin.Clients to Jon
--Now create a certificate for each consultant
Create certificate SheilaCert
authorization Sheila with subject='SheilaCert'
--Notice error, we need to create a database master key first
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='Capucci4!'
--Now lets create the certificates
Authorization Sheila with subject = 'SheilaCert'
Create Certificate JonCert
Authorization Jon with subject = 'JonCert'
--create symmetric keys for each of the consultants
CREATE SYMMETRIC KEY SheilaKey AUTHORIZATION Sheila
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE SheilaCert;
CREATE SYMMETRIC KEY JonKey AUTHORIZATION JON
ENCRYPTION BY CERTIFICATE JonCert;
--View the list of the keys in the database
Select * from sys.symmetric_keys
--SIMULATE CONNECTING AS SHEILA--
--Login and Insert Client Data--
Execute As login='Sheila'
SELECT SUSER_NAME() as LoginName, USER_NAME() as DBUserName;
--open your symmetric key for usage
OPEN SYMMETRIC KEY SheilaKey
DECRYPTION BY CERTIFICATE SheilaCert;
--use the key to insert investor's client records
--including encrypted values into the table
insert into Fin.Clients values (1,'Neo','Sheila',
insert into Fin.Clients values (2,'Smith','Sheila',
--close all open keys
close all symmetric keys
--logging out of sheila's context
--SIMULATE CONNECTING AS JON--
execute as login ='Jon'
OPEN SYMMETRIC KEY JonKey
DECRYPTION BY CERTIFICATE JonCert;
insert into Fin.Clients values (3,'WhiteRabbit','Jon',
insert into Fin.Clients values (4,'Trinity','Jon',
--simulate logging out of Jon's context
--TEST THE RESULTS--
Select * from Fin.Clients
--open the relevant keys for usage
--Select from the table including decrypting
select id, clientname, investor,
convert(varchar,decryptbykey(SIN)) as SIN,
convert(varchar,decryptbykey(Portfolio)) as Portfolio
--simulate logging out
Close all symmetric keys
Execute As login='Jon'
Note: It is also possible to achieve row level security without encryption as depicted in this article. I also recommend you check out this blog entry as well for a tool to assist with row level security.
With impersonate capabilities, you can now flow a single SQL context to the middle tier in the connection object, however, as part of the Command object you can now pass the end user –ASP.NET Principal Object which is derived from the HttpContext.User—to the database and have your middle account impersonate the end user. This provides the ability to flow just a single context in the connection object to ensure connection pooling for performance and by impersonating the end user and having the middle tier account context switch to the end use in the database auditing now be achieved at the database level. As shown in the illustration below:
--*****WHO AM I******
--******Create Login Account SQLUser and Dan******
Create login SQLUser with password='Capucci4!' --Middle Tier Account
Create login Dan with password='Capucci4!' --User to Impersonate
--*****Create Both Users in Database******
Create user SQLUser with DEFAULT_SCHEMA=HumanResources
Create user Dan with DEFAULT_SCHEMA=HumanResources
--*****Grant Permission to Middle Tier Account*****
Grant Select on HumanResources.Employee to SQLUser
Grant Select on HumanResources.Employee to Dan
--*****Allow SQLUser to Impersonate Dan******
GRANT IMPERSONATE ON USER:: Dan TO SQLUser
--*****Login as MiddleTier Account*****
--*****Connect to AdventureWorks Database*****
--*****Through the SQLConnection Object*******
Execute As Login='SQLUser'
--****Auditing Under SQLUser****************
Select EmployeeID, LoginID, SickLeaveHours
Where SickLeaveHours > 75
--****Pass End User account ie.Dan--HttpContext.User—in
--****the command object***********
Declare @username varchar(25)
Execute as User=@username;
--****Auditing Under Dan****************
--*****Now reverted back to MiddleTier Account for
--*****connection pooling benefits******
--****Now we are back as Middle Tier Acount********
On a final note, SQL Server 2005 releases a Community Technical Preview (CTP) of Service Pack 1 which can be downloaded here.