Today is my last day at Microsoft.  I wanted to thank everyone for joining me in my Security talks over the last few years. There will no longer be any entries at this blog, but please visit the Security Four Canadian Developers blog as this blog will be continued to be updated with great security information by the MSDN Canada team. http://blogs.msdn.com/S4CD Thanks  Dan    

I would recommend that you share this post on the http://blogs.msdn.com/S4CD with anyone that automatically cite resources as an excuse for not writing secure code.  This  is an extremely well documented example of how a small team can developer secure code and also makes a good point how the smaller business are putting themselves at greater risk. Well worth the reading!!  http://blogs.msdn.com/s4cd/archive/2006/09/19/763109.aspx

The ASP.NET User Principal (HTTPContext.User) clearly depends upon the Authentication Mechanism that you selected in IIS 6.0 "Authenication Tab" and if you use Integrated Windows Authentication then it is dependant on the IIS impersonation token that get handed off in the extension control block via the ASP.NET 2.0 ISAPI API.   Part two of IIS 6.0 and ASP.NET 2.0 credentials is now complete at the S4CD blog site..  This now ties in ASP.NET Principal Object and how it relates to the the OS thread and the impersonate token.

The one area that many developers do not have good grasp at is how Authentication tokens from IIS 6.0 is passed to ASP.NET 2.0 and how these tokens can subsequently be used for Authorization in an ASP.NET 2.0 Web Application. The one question that arises quite often is when I click on “Integrated Windows Authentication” in IIS 6.0 “Authentication tab” how does this information get passed to ASP.NET 2.0 and when it is passed to my Web Application how do I flow the client identity between different Services such as a Web Service or a database like SQL Server or what happens when I want to do impersonation? I have created part one of a two part blog at my new security blog site: http://blogs.msdn.com/s4cd/archive/2006/08/24/718656.aspx

Wow, it has been a while since I did my last blog post.  It may look like I disappeared but I am still around and was just keeping a low profile for the summer months.  Based upon your feedback I have recently created a new blog, but this time, the blog will be dedicated 100% to Security for Canadian Developers, hence the initials I used in the blog name of “S4CD”.  The new blog will be located at http://blogs.msdn.com/S4CD.  This will make it alot easier to find interesting information strictly on Security for Canadian Developers. Not only will I create interesting post on security but I will also have guest bloggers from top Security Matter Experts in the Canadian Community. I will still post blogs here periodically on other topics of interest, such Windows Live Writer, which I used to created this post entry.  Very nice tool for writing blog posts.

With the ever increasing regulatory requirements for organizations, many of the applications we write may need to meet certain compliances.  Here is an interesting read for developers:  Regulatory Compliance Demystified: An Introduction to Compliance for Developers

The on-demand Webcast of SQL Server 2005 for Developers, conducted on March 22, 2006, by Rob Walters--Program Manager, SQL Server Security--and I, is now available for on-demand viewing. Post Notes from this Webcast can be found at my blog. Look forward to seeing everyone for next week webcast.

At MIX06, it was announced that the "Atlas" March CTP is now available and it has a Go Live license so you can take applications into production.  The atlas team also re-launched the http://atlas.asp.net/ with a new "Atlas" enabled experience, customer profiles, improved "Atlas" documentation and more.    Lastly, MIX06 kicked off the "Mash-it-up with 'Atlas'" contest, a great way for you to get your hands dirty with the bits and win cool prizes.  Build a killer mash-up with "Atlas," share it with us, and you could be the big winner. 

In my latest Webcast on SQL Server 2005 Security one of the questions that came up was: “If some fields of your table are encrypted and you are suspicious that the key has been revealed can you re-encrypt all the fields with the regenerated key”?  Currently, there is no easy way to manage a key lifetime due to the complexity of managing the binding of the keys with the data they are protecting.   Thus, the application writer needs to keep track of everything that is encrypted with any given key, and in case it is necessary to regenerate a key, here are a series of steps that will probably help on SQL Server 2005: Create a temp key Open the old and the temp key Decrypt (old key) the data and replace it using the temp key Close the old key Create a new key with the same name Open the new key Decrypt (temp key) the data and replace it using the new key Close both keys Drop the temp key Unfortunately, this can be potentially error-prone (i.e. potential data loss if any column was omitted or failed to be decrypted on steps 3 or 7).   We are currently looking at developing a tool that will gather the binding information and make this more of transparent solution.

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.

Earlier this week, MSDN Canada announced the realDEVELOPMENT_06 tour. realDEVELOPMENT_06 will feature two sets of sessions; one entitled, "Web Platform" and the other entitled, "Security on the Brain". The Web Platform Sessions will feature talks on technologies that fall into the Web 2.0 camp. Specifically, we'll examine Windows Live and ASP.NET "Atlas". The Security on the Brain Sessions will cover digital identity (through InfoCard) and ASP.NET security (Hack and Defend). In addition, we've allocated a timeslot at the end of the day for a mash-up. Basically, it will be a free-for-all to talk tech and build code.  I will personally be in Vancouver and Calgary presenting the security portion of the afternoon and my colleagues John and Jerome will be doing the other cool stuff. If you haven't had the opportunity to register, please do so now! The realDEVELOPMENT_06 is live, or just click on your City below! _city _dates _times _venue Ottawa May 30, 2006 9:00AM – 5:00PM Coliseum Ottawa Toronto June 1,  2006 9:00AM – 5:00PM Toronto Congress Centre Montreal June 6,  2006 9:00AM – 5:00PM Paramount Montreal Vancouver June 8,  2006 9:00AM – 5:00PM Vancouver Convention and Exhibition Centre Calgary June 13, 2006 9:00AM – 5:00PM Paramount Chinook

Recently, Microsoft released the latest update to Anti-Cross Site Scripting tool which is part of a bigger plan known as the Microsoft IOSEC—an internal library.    The IOSEC library currently implements encoding protection against XSS attacks conducted through vectors such as HTML, URLs, JavaScript, HtmlAttributes and Visual Basic Script.  The Anti-Cross Site Scripting Library currently provides protection for a subset of those vectors.  Read the latest entry by Kevin Lam--who recently co-presented with me on last week Security’s webcast.

The on-demand version of the Visual Studio 2005 and Code Scanning Tools, conducted on March 15, 2006,by Kevin Lam and I, is now available for on-demand viewing. Look forward to seeing everyone for next week webcast.

You may not have heard it here first, but Rick LaPlante announced today, at SDWest 2006, that Microsoft is releasing Visual Studio 2005 Team Foundation Server tomorrow—March 17, 2006.  This was reported in Rob Carron’s blog.  Team Foundation Server should be available for download on MSDN within 3 – 5 days.

The Beta Experience is the new testing platform with tailor-made information for Microsoft developers. By registering to the Beta Experience you will be provided with the opportunity to download or order the latest Beta versions of Microsoft software for testing and the Beta Experience newsletter (6-weekly, terminated with the launch of the final version of the software) as an exclusive source of information.   Great way to get started on Windows Vista and Office 2007!   English Canada Website:  http://www.microsoft.com/betaexperience/encaFrench Canada Website: http://www.microsoft.com/betaexperience/frca