Здесь намешано аж 3 разные проблемы:

1) Проблема с ad-hoc и app-role

В некоторых ситуациях ADO оборачивает вызовы запросов в "обертки" типа sp_prepare <ваш запрос>, sp_executesql <ваш запрос>. A sp_setapprole не может быть вызвано изнутри любой другой процедуры, о чем и говорит типа "это вам не ad-hoc query".

2) спустя 10 секунд после закрытия коннекта и открытия заново не дает активировать роль приложения.

Это из-за пула коннектов. После закрытия соединения, оно на самом деле не закрывается, а остается висеть в спец. пуле 1 минуту вместе с активированой ролью. При попытке подключится заново, коннект не создается заново, а берется из пула вместе с ролью. Это бага от MS. MS об этом не слишком сожалеет, судя по тому что не пытаются исправить.

3) Создание дополнительных соединений.

ADO так работает. Если основной коннект чем-нибудь занят, например, выборкой недофетченного запроса или держанием серверного курсора, ADO запросто создает доп. временные соединения при необходимости выполнить еще один запрос. Проблема, в основном, проявляется при использовании временных таблиц.