Contained Database Authentication: How to control which databases are allowed to authenticate users using logon triggers

   With the release of Microsoft SQL Server code-name “Denali” Community Technology Preview 1 (CTP1) and the introduction of Contained Database (CDB) (http://msdn.microsoft.com/en-us/library/ff929071(SQL.110).aspx ), we also introduced the capability of  database authentication (http://msdn.microsoft.com/en-us/library/ms173463(v=SQL.110).aspx , http://blogs.msdn.com/b/sqlsecurity/archive/2010/12/03/contained-database-authentication-introduction.aspx, http://blogs.msdn.com/b/sqlsecurity/archive/2010/12/04/contained-database-authentication-monitoring-and-controlling-contained-users.aspx).     Since the configuration setting  that governs CDB & database authentication is a server scoped setting and the option to… Read more

How To: Share a Single EKM Credential among Multiple Users

   SQL Server Extensible Key Management (EKM) requires the authentication information (user/password) to be stored in a credential mapped to the primary identity. This version of EKM cannot be used under an impersonated context; that is, you cannot access the EKM while running a module with the EXECUTE AS clause.    However, some customers want… Read more

Accessing the calling context in modules that use EXECUTE AS

  In many occasions, marking a module (i.e. SP, trigger, etc.) with execute as can be really useful as it allows a controlled impersonation during the module execution; but at the same time there are many cases that it is necessary to access information using the caller’s execution context (i.e. revert to the default behavior),… Read more

The TRUSTWORHY bit database property in SQL Server 2005

  In SQL Server 2005 we introduced a new database property named TRUSTWORTHY bit (TW bit for short) at the database level in order to work as a safeguard to reduce the default surface area regarding some powerful new features: EXECUTE AS USER and CLR assemblies. These new features are really powerful, but without the… Read more

OPEN SYMMETRIC KEY scope in SQL Server

  Recently I have heard a few questions regarding the scope of the SYMMETRIC KEY key-ring, especially when using modules (i.e. stored procedures) to open a key. One particular topic that got my attention is the impression that the OPEN SYMMETRIC KEY call may “leak outside the module” (i.e. the key will remain opened) if… Read more