One of the things folks would always ask during the Ascend program was "anything new for application roles? do they support connection pooling yet?". Well, it the most recent CTP (June, July?) there is.
You can unset application roles now in addition to setting them. To unset you need to create a cookie, using an alternate form of sp_setapprole. It looks like this:
sp_addapprole 'myapp', 'StrongPW1'
GO
DECLARE @theCookie varbinary(256)
EXEC sp_setapprole 'myapp', 'StrongPW1',
@fCreateCookie = true, @cookie = @theCookie OUTPUT
— Check user, should be myapp
SELECT USER_NAME()
— now, unset it
EXEC sp_unsetapprole @theCookie
— Check user should be original user
SELECT USER_NAME()
GO
So does it now support connection pooling with ADO.NET or OLE DB/ODBC? I'd think the capability exists, but because this is a recent feature addition and they'd have to store the cookie in the pooling code somewhere, to use with sp_reset_connection, I don't think its built in yet. But, if you remember to store the cookie and unset it yourself…