G
Guest
Hello everybody....
I've got an interesting issue that is popping up... We require our SQL
server to be as secure as possible and therefore we have it set to mixed
security. We have 1 user that is a SQL user with one extremely complicated
password that only 1 person knows and is written down and locked in a vault.
Now, that being said, we had to find a way to do NT based security from our
..NET apps (specifically ASP.NET) to log into our applications. I did not
want to create an account for the ASPNET on SQL Server and we also wanted to
lock down certain areas of our database by USER. Here's what we did... for
different areas of our system (shipping, invoicing, purchasing) we set up
domain level accounts for each of those areas... i.e. (SQLUser_Shipping,
SQLUser_Invoicing, etc) and we gave those users RIGHTS to the tables, views,
stored procedures, and other database objects that they needed. The issue
was, how do we log in THOSE specific users from ASP.NET. We also wanted to
create a dataprovider that managed all of the connection/command stuff for
the developer so they weren't stuck writing the same data access code over
and over. What we did was create an encrypted file with usernames and
passwords for those users in the form of a persisted XML dataset. I created
an object that I set up for object pooling that had that dataset as a public
property (this is so we wouldn't hammer the file system every time we wanted
to connect to the database). What the dataprovider does is determines which
application is calling it (Purchasing, Shipping, etc.) and appropriately
calls LogonUser for that user and impersonates that user account. This
allows the thread to take the context of whatever user we want in a secure
manner and the token of that user be passed to SQL server where they have
access. Now, everything has been running great for a LONG time. However,
we've just pushed out some more new applications that are using the
dataprovider more than it has ever been used before. What I'm noticing is
that the LSASS.exe process (the one that actually does the logging on of the
user) begins to eat up more and more CPU. It also begins to eat HUGE amounts
of memory and it acts like it never gets garbage collected. Is this due to
the fact that we're just HAMMERING it with impersonation requests. What can
I do to get the thing to stop doing this. Eventually it gets so bad each day
that we have to re-boot the server because LSASS.EXE is taking up 30% CPU and
200 MB of memory. Any insight on how I could force it to Garbage Collect
would be hugely appreciated.
I've got an interesting issue that is popping up... We require our SQL
server to be as secure as possible and therefore we have it set to mixed
security. We have 1 user that is a SQL user with one extremely complicated
password that only 1 person knows and is written down and locked in a vault.
Now, that being said, we had to find a way to do NT based security from our
..NET apps (specifically ASP.NET) to log into our applications. I did not
want to create an account for the ASPNET on SQL Server and we also wanted to
lock down certain areas of our database by USER. Here's what we did... for
different areas of our system (shipping, invoicing, purchasing) we set up
domain level accounts for each of those areas... i.e. (SQLUser_Shipping,
SQLUser_Invoicing, etc) and we gave those users RIGHTS to the tables, views,
stored procedures, and other database objects that they needed. The issue
was, how do we log in THOSE specific users from ASP.NET. We also wanted to
create a dataprovider that managed all of the connection/command stuff for
the developer so they weren't stuck writing the same data access code over
and over. What we did was create an encrypted file with usernames and
passwords for those users in the form of a persisted XML dataset. I created
an object that I set up for object pooling that had that dataset as a public
property (this is so we wouldn't hammer the file system every time we wanted
to connect to the database). What the dataprovider does is determines which
application is calling it (Purchasing, Shipping, etc.) and appropriately
calls LogonUser for that user and impersonates that user account. This
allows the thread to take the context of whatever user we want in a secure
manner and the token of that user be passed to SQL server where they have
access. Now, everything has been running great for a LONG time. However,
we've just pushed out some more new applications that are using the
dataprovider more than it has ever been used before. What I'm noticing is
that the LSASS.exe process (the one that actually does the logging on of the
user) begins to eat up more and more CPU. It also begins to eat HUGE amounts
of memory and it acts like it never gets garbage collected. Is this due to
the fact that we're just HAMMERING it with impersonation requests. What can
I do to get the thing to stop doing this. Eventually it gets so bad each day
that we have to re-boot the server because LSASS.EXE is taking up 30% CPU and
200 MB of memory. Any insight on how I could force it to Garbage Collect
would be hugely appreciated.