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


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.

Comments (4)

  1. This morning was a jammed filled session covering off a lot of changes made to Microsoft SQL Server 2005….

  2. The on-demand version of SQL Server 2005 for Developers, conducted on March 22, 2006, by Rob Walters–Program…

  3. The on-demand Webcast of SQL Server 2005 for Developers, conducted on March 22, 2006, by Rob…