n-tier Database Security Question

  • Thread starter Thread starter Edward J. Stembler
  • Start date Start date
E

Edward J. Stembler

I have an n-tier .Net application where a data-access
layer (DAL) object connects to a SQL Server database.
The DAL object physically lives on a separate computer
from the database and presentation layer (clients). The
client application would like to use integrated NT
security. The DAL object has it's own connection string
which it uses to connect to the database, however, I am
considering passing the user name to the DAL object from
the client.

My question is, how do you honor integrated NT Security
when there's a middle layer (DAL)?

Ideally, the client doesn't want to have to log in again
since the OS already authenticated them. The database is
setup to use integrated security. But how do I avoid it
using the security info of the machine that the DAL
object is running on? I want it to use the client's info.
 
¤ I have an n-tier .Net application where a data-access
¤ layer (DAL) object connects to a SQL Server database.
¤ The DAL object physically lives on a separate computer
¤ from the database and presentation layer (clients). The
¤ client application would like to use integrated NT
¤ security. The DAL object has it's own connection string
¤ which it uses to connect to the database, however, I am
¤ considering passing the user name to the DAL object from
¤ the client.
¤
¤ My question is, how do you honor integrated NT Security
¤ when there's a middle layer (DAL)?
¤
¤ Ideally, the client doesn't want to have to log in again
¤ since the OS already authenticated them. The database is
¤ setup to use integrated security. But how do I avoid it
¤ using the security info of the machine that the DAL
¤ object is running on? I want it to use the client's info.

If you're using integrated security then the DAL would know who the authenticated user is. Not sure
why the DAL would need to know in any event. I don't really know the details of your DAL
implementation so I can't really provide much more information.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
The DAL object, or DataHelper, is a .Net remoted
singleton. It exposes specific methods pertainting to
its domain, so to speak. Internally it uses
Microsoft.ApplicationBlock.DataAccess.SqlHelper.
Originally, the DataHelper had a connection string
property, which was set by the remoting host and unknown
to the clients. However, a new business requirment arose
which dictated that integrated security must be used. My
first thought was to keep the datasource portion(s) of
the connection string on the DAL computer and somehow
pass in the user info from the client. To keep the DAL
server from using it's info when connecting to SQL Sever
via integrated security. Maybe I'm missing something
here, but how would the authenticated client be known to
SQL Sever if the connection string is coming from the
middle DAL computer? Would SQL Sever think it was the
login from whatever the DAL computer was logged in as?
Or is there someway to mix-and-match integrated security
in a connection string along with user info?

Thanks.


If you're using integrated security then the DAL would
Click to expand...
know who the authenticated user is. Not sure
why the DAL would need to know in any event. I don't
Click to expand...
really know the details of your DAL
 
I have an n-tier .Net application where a data-access
layer (DAL) object connects to a SQL Server database.
The DAL object physically lives on a separate computer
from the database and presentation layer (clients). The
client application would like to use integrated NT
security. The DAL object has it's own connection string
which it uses to connect to the database, however, I am
considering passing the user name to the DAL object from
the client.

My question is, how do you honor integrated NT Security
when there's a middle layer (DAL)?
Click to expand...

Although the tiers are separated, they are loaded IN the process
space of the caller, the actual application, be it a winforms executable,
a console app or a webapplication process. Thus code in your middle tier
and DAL is executed using the credentials of the application. If that
application is a webapplication, ASPNET is most likely the user ID.

THis is also the reason why you have to store your connection
string in the app.config of the .exe / webapp and not in an app.config
stored with the DAL tier assembly, because teh executable's appdomain will
read it, even though the DAL code does the actual work.
Ideally, the client doesn't want to have to log in again
since the OS already authenticated them. The database is
setup to use integrated security. But how do I avoid it
using the security info of the machine that the DAL
object is running on? I want it to use the client's info.
Click to expand...

Erm... a class library can't run on its own. Do you run the DAL in
a webservice on another machine?

FB
 
¤ The DAL object, or DataHelper, is a .Net remoted
¤ singleton. It exposes specific methods pertainting to
¤ its domain, so to speak. Internally it uses
¤ Microsoft.ApplicationBlock.DataAccess.SqlHelper.
¤ Originally, the DataHelper had a connection string
¤ property, which was set by the remoting host and unknown
¤ to the clients. However, a new business requirment arose
¤ which dictated that integrated security must be used. My
¤ first thought was to keep the datasource portion(s) of
¤ the connection string on the DAL computer and somehow
¤ pass in the user info from the client. To keep the DAL
¤ server from using it's info when connecting to SQL Sever
¤ via integrated security. Maybe I'm missing something
¤ here, but how would the authenticated client be known to
¤ SQL Sever if the connection string is coming from the
¤ middle DAL computer? Would SQL Sever think it was the
¤ login from whatever the DAL computer was logged in as?
¤ Or is there someway to mix-and-match integrated security
¤ in a connection string along with user info?
¤

If you're using integrated security across the board (including SQL Server) then that would be
specified in the connection string (as Andy mentioned).

When implementing integrated security the credentials are automatically transferred to each
component of the tier. There is no need for your code to handle user credentials. It's set up
primarily through system and database configuration.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Have you actually tried this though? I do not believe
that it is possible. What's stopping the middle-tier
computer from using it's security info instead of the
client, when connecting to the SQL Server computer using
intergrated security?

I cross-posted my original post to the dotnet.security
newsgroup as well. A Microsoft employee had this to say:

"it sounds like you need an integrated doule-hop
authentication client->middle_tier->database.
Unfortuantely this is not possible unless you use
Kerberos."

-----Original Message-----
If you're using integrated security across the board
Click to expand...
(including SQL Server) then that would be
specified in the connection string (as Andy mentioned).

When implementing integrated security the credentials
Click to expand...
are automatically transferred to each
component of the tier. There is no need for your code to
Click to expand...
handle user credentials. It's set up
 
It's running as a Remoted (singleton) object, which is
hosted inside an NT Service application.
 
¤ Have you actually tried this though? I do not believe
¤ that it is possible. What's stopping the middle-tier
¤ computer from using it's security info instead of the
¤ client, when connecting to the SQL Server computer using
¤ intergrated security?

No, our environment is not set up for integrated security. We authenticate via the web server and
implement a universal logon to the database server. In addition, we have a trust relationship
between the web and database server.

Personally I'm not a big fan of implementing integrated security with the database server. It's an
administrative headache.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top