In the past we have used the classic nTier design with COM+, SQL Server

and MFC. We are currently working on a new project with an opportunity

to use .NET, ADO.NET, etc. One of the areas where I don't find a clear

migration path is with security. In the classic method, the server
side tiers were installed under COM+ and run as a known user. SQL
Server then gave full access to this known user so ADO simply needed to

make standard calls (no user or password required). This of course
also allowed the holy grail of connection sharing across the data
access layer since the same user account was used to access data. All
roles were handled by COM+ and Windows Security at the business layer.
How would this be handled with ADO.NET? This is an intranet only
application and we plan on using WSE, ADO.NET, SQL Server 2000. How do

I securely access my data without embedding user names and passwords.
Naturally I want to continue to use connection sharing. I have been
considering running the WSE host application under a known user but
that seems a bit kludgy.


Thanks,
Todd

SOA or not?

SOA:
Set up a "web service" (can be Remoting or ASMX) on a server other than the
web server and use it to serve data. This does reduce perf a bit, as you have
a network call to get data. Because of this, you should not do small CRUD
statements, but should design the data transfer around messages (objects, if
you wish). For an Intranet, getting rid of the HTTP requirement (i.e. use
Remoting, not ASMX) is a better option. You still could have network latency
reducing perf, but this is not a normal concern on most Intranets, as
networks are fast in most orgs.

Not:
You can still keep a COM+ layer on your application. There are caveats
(primarily if you are contact other .NET assemblies in the process), but it
is a viable way to reduce the security risk that many end up with by allowing
the IUSR (or network service) account full access to SQL Server.

As we move to SQL Server 2005, you end up with an even stronger option,
which is to add all Intranet users to public, allow public to access your
sprocs and have the sprocs run as a priveleged account. You also can create
HTTP endpoints in SQL Server 2005 and remove the C# data layer from the
equation completely. This will not help with apps you are building today,
however.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************


"(E-Mail Removed)" wrote:

> In the past we have used the classic nTier design with COM+, SQL Server
>
> and MFC. We are currently working on a new project with an opportunity
>
> to use .NET, ADO.NET, etc. One of the areas where I don't find a clear
>
> migration path is with security. In the classic method, the server
> side tiers were installed under COM+ and run as a known user. SQL
> Server then gave full access to this known user so ADO simply needed to
>
> make standard calls (no user or password required). This of course
> also allowed the holy grail of connection sharing across the data
> access layer since the same user account was used to access data. All
> roles were handled by COM+ and Windows Security at the business layer.
> How would this be handled with ADO.NET? This is an intranet only
> application and we plan on using WSE, ADO.NET, SQL Server 2000. How do
>
> I securely access my data without embedding user names and passwords.
> Naturally I want to continue to use connection sharing. I have been
> considering running the WSE host application under a known user but
> that seems a bit kludgy.
>
>
> Thanks,
> Todd
>
>