Login failed for user (null). Not associated with a trusted SQLSe

[Guest]

I know this question has been posted in many places, but I have not been able
to solve our current problem. The developers in my office are all new to
C#.net. We have been developing an application using Visual Studio .NET 2003
and IIS 6 with SQL Server 2000. Up until this point each developer has used
a local copy of IIS and connected to sql server on a remote server without
any problems. Next week we have a demo, and I have tried to deploy our
solution to the IIS(6) on the remote server which also hosts the SQL server
we have been using. Now we all get the 'Login failed for user (null)......'
error message.
The web.config has the following:
<authentication mode="Windows"/>
<identity impersonate="true" />

Integrated Security = SSPI is a part of the connection string.

On the remote server, Sql server is set to use both windows and sql server
authentication
IIS is set for Integrated Windows authentication.
We cannot use active directory and therefore do not have Kerberos enabled.
Any help would be greatly appreciated.

Thanks
Sherri

 

[Mark Rae]

we have been using. Now we all get the 'Login failed for user
(null)......'
error message.
The web.config has the following:
<authentication mode="Windows"/>
<identity impersonate="true" />

Integrated Security = SSPI is a part of the connection string.

On the remote server, Sql server is set to use both windows and sql server
authentication
IIS is set for Integrated Windows authentication.
We cannot use active directory and therefore do not have Kerberos enabled.
Any help would be greatly appreciated.

By specifying Integrated Security, you've told ASP.NET to try to connect to
SQL Server with the account that it's currently running under.

You've also told ASP.NET not to run under its default account, but to
impersonate a different account instead.

So, have you set up the account that ASP.NET is impersonating as a valid
user in the SQL Server database that you're trying to connect to...?

 

[Kris Lankford]

Sherri,

If your developers have IIS and SQL installed on the same box the user token
for SSPI will be passed to SQL for the login. If your demo environment has
the IIS and SQL on different boxes this would explain your issue. IIS can
not pass the Windows user token to another machine on the network. The
simplest solution will be to add and SQL server username and password to the
connection string. If you want to use windows authentication and SSPI you
will have to configure Active Directory to pass the Kerberos ticket along to
the next machine for authentication.

Hope this helps.

Kris

 

[Mark Rae]

IIS can not pass the Windows user token to another machine on the network.

Are you sure about this...? I'm doing precisely this on my current
project...

1) A new user has been created which has slightly more privileges than the
default user which ASP.NET uses - principally to support writing to
ActiveDirectory...

2) The ASP.NET app impersonates this user - set up in web.config /
aspnet_setreg...

3) SQL Server 2005, configured for integrated security, is running on a
different machine...

4) The new user has been added as a valid login to SQL Server, and granted
sufficient permissions on the databases it needs

5) ASP.NET uses the "SSPI" connection string

Works perfectly...

 

[Guest]

I'm new to this so I'll answer the best I can. Its my understanding that SQL
Server should be using the user account of the person logged in. There is an
account set up in sql server for this user with permissions to the database.

 

[Guest]

Sql Server and IIS are installed on the same box for the demo environment.
Is there anyway to do this without Active Directory?

 

[Mark Rae]

I'm new to this so I'll answer the best I can. Its my understanding that
SQL
Server should be using the user account of the person logged in. There is
an
account set up in sql server for this user with permissions to the
database.

1) Interrogate the servervariables collection on any of the aspx pages in
your site, specifically the AUTH_USER or LOGON_USER variables - that will
tell you which user your site is running under. If it's not what you think
it should be, then there's your problem...

2) From SQL Server Management Studio, try to connect to the server in
question by logging in as the user you think your ASP.NET app should be
using... Can you get in? Can you access the database(s) your ASP.NET app
needs to access...? If not, then there's your problem...