You need to create a login on the Windows machine that COM+ is
installed on that you will use to connect to SQL Server. You need to
grant it the same range of permissions you would have granted the
application role. In the COM+ application properties, you change the
Identity from "Interactive User" to this new account that you just
created. On the Security tab, you select the enforce access checks
option. You can then optionally configure role-based security at the
method level in Roles node. This actually gives you much more flexible
security than application roles, which do not grant multi-access level
permissions at the method level. If you're using VB6, your client code
doesn't need to change--use Integrated Security=SSPI. When users
connect, they'll be going to the MT installed in COM+, which will
transparently connect to SQLS using the Windows login you created.
HTH,
--Mary
On Sat, 19 Jun 2004 18:43:02 -0700, "Kristian Aasbjerg"
[quoted text, click to view] <KristianAasbjerg@discussions.microsoft.com> wrote:
>I have for some months by now tried to decide where to enforce security on a new application we are starting up. The suggestion is - of course - to enforce security in the middle tear (COM+), but what REALLY bugs me is, that there is no guidelines on how to make your connection in Visual Basic! I can easily authenticate my users in the COM+ layer, but if I want to use Windows Authentication to the SQL Server, the users would also need to have wide privileges on the database, making abuse easy using MS Access or Query Analyzer.
>
>What to do?
>
>I thought the solution was Application Roles (I've digged out quite a bit of information on this concept...), but when I try to enable the roles from my application, the return error is:
>
>*** START ***
>
>Microsoft OLE DB Provider for SQL Server (GetNewConnection) (User - Login) error '80040e14'
>
>The procedure 'sp_setapprole' cannot be executed within a transaction.
>
>//global.asa, line 45
>
>*** STOP ***
>
>Another solution is the old fashioned SQL Server login, but this is not recommended anymore I believe??
>
>
>Thank you in advance,
