Getting COM components running in ASP.NET to connect to SQL Server databases

[Oenone]

Sorry for the cross-post but I've really no idea where this is best suited.

I've an ADO.NET application which connects to a SQL Server database. I have
spent the entire morning trying to get it to connect, but eventually got it
working by setting (in the web.config file) the authentication mode to
"Windows" and the identity impersonate flag to "True". I have left the
identity username blank so that it picks up the username and password from
the IIS configuration windows.

This is working fine. However, at one stage in my application I create a COM
object and call a function within it. The COM object creates an ADO-classic
connection to the database. This connection is failing every time with the
following error:

Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.

The connection string it is using is as follows:

Provider=SQLOLEDB;SERVER=(servername);Trusted_Connection=yes;Initial
Catalog=(dbname)

The IIS web site is configured to allow Anonymous access, and it has been
provided with the username and password of a domain user account. This user
is successfully being used by the ASP.NET and ADO.NET portion of my code.
But the COM and ADO-classic seems to be ignoring these settings.

My SQL Server only has Windows Authentication enabled (I am unable to use
SQL Server authentication due to company network policies).

What can I try doing in order to get this to work? I'm tearing my hair out
with this at the moment as I just can't find a strategy to move this
forward.

My thanks in advance,

 

[William \(Bill\) Vaughn]

This is a classic issue. Consider that in an ASP application, it's IIS that
"owns" the connection and the ASP pages that get launched. As such it has
its own identity "ASPNET". This means you need to create a Login Account in
SQL Server to connect to the Windows account "ASPNET" and grant it rights to
the appropriate objects (and just those objects).

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Oenone]

This is a classic issue. Consider that in an ASP application, it's
IIS that "owns" the connection and the ASP pages that get launched.
As such it has its own identity "ASPNET". This means you need to
create a Login Account in SQL Server to connect to the Windows
account "ASPNET" and grant it rights to the appropriate objects (and
just those objects).

Hi Bill,

Thanks for the reply. I'm not sure this is actually the problem though...

With a combination of Windows authentication and impersonation, I've already
configured the IIS session to use a different user identity to ASPNET (I'm
using a domain account which we have created called IUSR_IIS). I can see
that the ASP.NET connection is successfully logging into the database using
this username (and not ASPNET).

However the ASP-classic connection (created within the COM object) appears
not to be using the IUSR_IIS credentials that the main process is using.

Could it be that the COM object is somehow falling back to the
non-impersonated identity?

Thanks,

 

[William \(Bill\) Vaughn]

It could be. I usually recommend use of application-specific logons. I use
other techniques to validate the user using my own "valid user" tables. This
lets me tune what the user sees and can do based on their "rights" specified
by this server-based table. It's a lot easier than trying to figure out how
to get Kerberos to work.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Willy Denoyette [MVP]]

COM uses the process identity to call-out, while ADO.NET uses the
impersonated thread context.
So in your case COM uses "aspnet" to call out, while ado.net uses the domain
account.
One option to solve this issue is to changed the identity of the worker
process from aspnet into the same domain account, but I would never suggest
to run asp.net using a domain identity. I suggest you don't use integrated
security to concet to SQL server, but supply explicit SQL credentials in
your conncetion string(s). These credentials can better be encrypted and
stored in your config file.
Willy.


Willy.

 

[Oenone]

So in your case COM uses "aspnet" to call out, while ado.net uses the
domain account.

Many thanks for that -- I can see a way to progress in this case. And I
still have a little bit of hair left too.

I suggest you
don't use integrated security to concet to SQL server, but supply
explicit SQL credentials in your conncetion string(s). These
credentials can better be encrypted and stored in your config file.

By this do you mean to use SQL Server authentication instead of Windows
authentication? If so then whilst I don't disagree with what you're saying,
I'm not sure it is an option for me (we don't permit SQL Server
authentication on any of our servers as part of our company security
policy). It may be better for me to just permit the ASPNET user to access
the databases on the SQL Server and allow COM to access them like that.

Thanks for your help,

 

[Willy Denoyette [MVP]]

Many thanks for that -- I can see a way to progress in this case. And I
still have a little bit of hair left too.


By this do you mean to use SQL Server authentication instead of Windows
authentication? If so then whilst I don't disagree with what you're
saying,
I'm not sure it is an option for me (we don't permit SQL Server
authentication on any of our servers as part of our company security
policy). It may be better for me to just permit the ASPNET user to access
the databases on the SQL Server and allow COM to access them like that.

Thanks for your help,

You won't be able to access SQLServer on another server using the "aspnet"
account. ASPNET is a local (machine) account (auto-generated) with
restricted privileges (doesn't have network privileges at all).
So, your options are rather limitted:
Run the asp.net worker process using domain credentials (not sure the the
company security policy allows this), or,
drop your DAL in a COM+ (System.EnterpriseServices classes) server type
application that runs with fixed windows credentials.

Willy.

 

[Oenone]

So, your options are rather limitted:
Run the asp.net worker process using domain credentials

Could you by any chance point me to a resource that explains how to do this?

Many thanks,