Post Webcast’s Notes: Securing SQL Server 2005 for Developers

  • Article

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@!',

 CHECK_EXPIRATION=ON,CHECK_POLICY=ON

go

select * from sys.sql_logins

GO

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

GO

--cleanup

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'

go

create login Login2 with password='(*&sdf87786sdf'

go

create login Login3 with password='(*&sdf87786sdf'

go

create database ExampleDB

go

use ExampleDB

go

--User 1 will have a table

create user User1 for login Login1 with default_schema=User1

go

create schema User1 authorization User1

go

--User 2 will have SELECT access and write a proc to access

create user User2 for login Login2 with default_schema=User2

go

create schema User2 authorization User2

go

--User 3 will have the right to exec the proc

create user User3 for login Login3 with default_schema=User3

go

create schema User3 authorization User3

go

grant create table to User1

go

grant create proc to User2

go

execute as login='Login1'

go

create table User1.MyTable

(ANumber int)

go

insert into MyTable values (1)

insert into MyTable values (2)

insert into MyTable values (3)

go

grant select on MyTable to User2

go

revert

go

execute as login='Login2'

--create a stored proc that will return the rows in our table

create proc ViewMyNumbers

AS

BEGIN

select * from User1.MyTable

END

grant execute on ViewMyNumbers to User3

go

revert

go

execute as login='Login3'

--Can't access table directly

select * from User1.MyTable

--I can't execute the proc since I don't have permissions on the unlying table

exec User2.ViewMyNumbers

--What I can do is alter the proc and set it to "execute as owner"

revert

go

execute as login='Login2'

go

ALTER PROCEDURE ViewMyNumbers

WITH EXECUTE AS OWNER

AS

BEGIN

select * from User1.MyTable

END

revert

go

execute as login='Login3'

--Still can't access table directly

select * from User1.MyTable

--Now I can access it and I didn't have to give User1

--any permissions on MyTable

exec User2.ViewMyNumbers

go

revert

go

 

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

USE Master

GO

--Create Database, Users, Schemas, Table Object--

Create Database AccountsDB

GO

Use AccountsDB

GO

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

GO

Create SCHEMA Fin Authorization Sheila

--Create Table to client table

Create table Fin.Clients (Id int, clientname nvarchar(30),

investor varchar(20),

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

Create certificate SheilaCert

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

WITH ALGORITHM = TRIPLE_DES

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',

encryptByKey(Key_GUID('SheilaKey'),'111-111-111'),

encryptByKey(Key_GUID('SheilaKey'),'$150,000,000'))

insert into Fin.Clients values (2,'Smith','Sheila',

encryptByKey(Key_GUID('SheilaKey'),'222-222-222'),

encryptByKey(Key_GUID('SheilaKey'),'$200,000'))

--close all open keys

close all symmetric keys

--logging out of sheila's context

REVERT

--SIMULATE CONNECTING AS JON--

execute as login ='Jon'

--open your symmetric key for usage

OPEN SYMMETRIC KEY JonKey

DECRYPTION BY CERTIFICATE JonCert;

insert into Fin.Clients values (3,'WhiteRabbit','Jon',

encryptByKey(Key_GUID('JonKey'),'333-333-333'),

encryptByKey(Key_GUID('JonKey'),'$50,000'))

insert into Fin.Clients values (4,'Trinity','Jon',

encryptByKey(Key_GUID('JonKey'),'444-444-444'),

encryptByKey(Key_GUID('JonKey'),'$300'))

--close all open keys

close all symmetric keys

--simulate logging out of Jon's context

REVERT

--TEST THE RESULTS--

SELECT SUSER_NAME() as LoginName, USER_NAME() as DBUserName;

Select * from Fin.Clients

Execute As login='Sheila'

--open the relevant keys for usage

OPEN SYMMETRIC KEY SheilaKey

DECRYPTION BY CERTIFICATE SheilaCert;

--Select from the table including decrypting

select id, clientname, investor,

convert(varchar,decryptbykey(SIN)) as SIN,

convert(varchar,decryptbykey(Portfolio)) as Portfolio

from Fin.Clients

--simulate logging out

Close all symmetric keys

REVERT

Execute As login='Jon'

SELECT SUSER_NAME() as LoginName, USER_NAME() as DBUserName;

--open the relevant keys for usage

OPEN SYMMETRIC KEY JonKey

DECRYPTION BY CERTIFICATE JonCert;

--Select from the table including decrypting

select id, clientname, investor,

convert(varchar,decryptbykey(SIN)) as SIN,

convert(varchar,decryptbykey(Portfolio)) as Portfolio

from Fin.Clients

--simulate logging out

Close all symmetric keys

REVERT

 

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:

 

--*****IMPERSONALIZATION DEMO*******

--*****WHO AM I******

SELECT SUSER_NAME() as LoginName, USER_NAME() as DBUserName;

--******Create Login Account SQLUser and Dan******

USE Master

Go

Create login SQLUser with password='Capucci4!' --Middle Tier Account

Create login Dan with password='Capucci4!' --User to Impersonate

--*****Create Both Users in Database******

Use AdventureWorks

Go

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*******

Use AdventureWorks

Go

Execute As Login='SQLUser'

SELECT SUSER_NAME() as LoginName, USER_NAME() as DBUserName;

--****Auditing Under SQLUser****************

Select EmployeeID, LoginID, SickLeaveHours

FROM HumanResources.Employee

Where SickLeaveHours > 75

--****Pass End User account ie.Dan--HttpContext.User—in

--****the command object***********

SELECT SUSER_NAME() as LoginName, USER_NAME() as DBUserName;

Declare @username varchar(25)

Set @username='Dan'

Execute as User=@username;

SELECT SUSER_NAME() as LoginName, USER_NAME() as DBUserName;

--****Auditing Under Dan****************

Select EmployeeID, LoginID, SickLeaveHours

FROM HumanResources.Employee

Where SickLeaveHours > 75

--*****Now reverted back to MiddleTier Account for

--*****connection pooling benefits******

REVERT

--****Now we are back as Middle Tier Acount********

SELECT SUSER_NAME() as LoginName, USER_NAME() as DBUserName;

On a final note, SQL Server 2005 releases a Community Technical Preview (CTP) of Service Pack 1 which can be downloaded here.