Certificate Based Application Roles - by Dave Levy

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


9
0
Sign in
to vote
ID 625551 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 11/29/2010 5:21:11 PM
Access Restriction Public

Description

I am in search of a more perfect application role. What I would like is to be able to sign a Windows executeable or even an individual assembly with a certificate. I would then take that certificate and load it into SQL Server. I would then associate the certificate to Windows users and groups to form an application role. 

The benefit of this approach is that depending on the executeable a user is running they could have different rights. A user that has reader on all tables in a database to query with Access might have execute on certain procedures when running the accounting application but have update on other tables when running the payroll software. 

If the user changes departments then they would change rights via group membership. If they leave the company then all rights would be removed like any other windows login. All activity would take place under the context of the users login, simplifying auditing. 
Sign in to post a comment.
Posted by Microsoft on 12/3/2010 at 5:49 PM
Hi David,

Thanks for your suggestion. There are a few problems with what you've proposed. Signing an executable is primarily for the goal of validating the integrity of the binary and are typically validated during load time. As such, signatures are checked on the local machine and there is no way to transmit them to a remote machine.

This also brings up a more general issue of validating the identity of an application and currently there is no good solution. Here's a forum thread where this issue has been discussed at length. http://social.msdn.microsoft.com/forums/en-US/sqlsecurity/thread/8990ca20-b82c-4519-b411-b38ca84ffcb9/

Thanks,
Il-Sung.