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

[DC Gringo]

Using latest SP Win2k and .NET versions, I have a .NET application running
on server1 with a SQL Server database running on server2. I have the
Windows user account passwords sync'd for server1\aspnet (installed by .NET
Framework and server2\aspnet (created by me--this machine has no .NET
Framework on it). The aspnet user on the database server (server2) has
access to the database.

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

IIS is set to Windows Integrated Authentication...although same result with
Anonymous, with and without IIS controlling password that I enter.

-----------------------------------
Here's my connection string:

Me.SqlConnection1.ConnectionString = "workstation id=""CIL-094"";packet
size=4096;integrated security=SSPI;data source=""myserver"";persist security
info=False;initial catalog=mydb;"


-----------------------------------
Here's my web.config:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<system.web>

<compilation defaultLanguage="vb" debug="true" />
<customErrors mode="Off" >
<error statusCode="404" redirect="~/errors/missingPage.aspx" />
</customErrors>
<authentication mode="Windows" />

<authorization>
<allow users="*" /> </authorization>

<trace enabled="true" requestLimit="10" pageOutput="true"
traceMode="SortByTime" localOnly="true"/>

<sessionState
mode="InProc"
stateConnectionString="tcpip=127.0.0.1:42424"
sqlConnectionString="data source=127.0.0.1;"
cookieless="false"
timeout="20"
/>

<globalization requestEncoding="utf-8" responseEncoding="utf-8" />
</system.web>
</configuration>

 

[Ken Schaefer]

This is my uninformed understanding - I could be completely wrong about
this!

Seems to me that ASP.NET is running as Server1\ASPNET which is an account
local to the webserver. It's access token probably only has "anonymous"
rights to network resources, which isn't sufficient to login to SQL Server
on the remote box. The ASPNET account you have on server2, is
Server2\ASPNET, which isn't the same.

Cheers
Ken

 

[Jediah L.]

DC,

Are you in an AD realm? If not - you will not be able to hop your
identities across the machines, but still have a few other options.

(its been a while since i've tackled the problem - but i'll do my best from
memory).

1: You can enable Basic Authentication instead of NTLM - while this is not
very secure - it will allow your credentials you supplied to the web site to
be passed (clear text) to the server which contains the sql database - just
grant the authenticated account access to the DB.

2. Setup impersonation on your web site, using the Identity node in the
web.config - keep NTLM on your site, and grant your identity that you
impersonate access to the database (this also requires additional setup to
the asp.net processModel - or elevating the ASPNET account to be able to do
identity impersonation), if you are in an AD realm you can let the web
application impersonate the NTLM user - otherwise you can specificy a
specific proxy account in the identity node - and encrypt it using the
aspnet_setreg.exe.

3. I find it unlikely that syncing your passwords would work in any
scanrio - while this would have worked back in NT - I don't believe windows
is still dumb enough to fall for it.


This should give you enough information to poke around and get it right.

Good Luck!

 

[Guest]

The webserver ASPNET account may not be properly mapped with Server2(SQL).
Just delete the ASPNET acocunt in sql server, and create a Nt Account like
Server1\AspNet (Not a SQL serve account).

if nothing is working create a new Account in Webserver and assign to Worker
process account and refer the same account in Sql Server too.

Regards,
Govind.

 

[Nick Malik]

The most common answer is to run your web app under a domain account and
give that domain account access to SQL Server.

I have tried to use this "mapping of a local account" before. It only works
if both the account name and the password are the same on both machines.
Since the ASPNET account's password is managed by IIS, and changed on a
regular basis, it would be an unusual event to sync the ASPNET account.

Good Luck
--- Nick

 

[Jeff Cochran]

Using latest SP Win2k and .NET versions, I have a .NET application running
on server1 with a SQL Server database running on server2. I have the
Windows user account passwords sync'd for server1\aspnet (installed by .NET
Framework and server2\aspnet (created by me--this machine has no .NET
Framework on it). The aspnet user on the database server (server2) has
access to the database.

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

Might look at:

PRB: ASP/ODBC/SQL Server Error 0x80040E4D "Login Failed for User
'(Null)'":
http://support.microsoft.com/default.aspx?scid=kb;en-us;307002

Jeff

 

[Greg Burns]

Nick,

I have been using the "mapping of a local account" without a problem for
quite awhile now. Why do you say that they ASPNET account's password would
be changed on a regular basis?

I have manually set the ASPNET account's password on our web server and
hardcoded it (in clear text) in the machine.config (granted this may not be
the most secure thing), but why would IIS ever change it?

I have also created a local ASPNET user on our SQL box with matching
password. Works fine.

I chose not to go with a domain account simply because I did not fully
understand (and still don't) what all permissions I would need to apply to
that account on our webserver to mimick the default ASPNET user.

Greg

 

[Greg Burns]

"Synching passwords" (aka pass-through authentication) worked before we went
to AD and continues to work in mixed-mode AD for us.

Here is link on the subject, all this one doesn't mention pass-through.
Cannot find the msdn article that did.
http://msdn.microsoft.com/library/d...vbconAccessingSQLServerFromWebApplication.asp

I am not using a domain account simply because I wasn't sure what
permissions to assign to it on the web server to mimick the default ASPNET
user. I just found this link, with which I might revisit the problem:

http://msdn.microsoft.com/library/d...cpconASPNETRequiredAccessControlListsACLs.asp

Greg

 

[Nick Malik]

I misspoke and I offer my apologies. ASPNET password is not known to the
end user, but it is not managed by IIS either.
You do have to change it so that you will know what it is.

You do agree, though, that the username and password for the local accounts
must be the same on both machines, yes?

The correct steps for creating a local account to run ASP.NET are described
here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/secmod/html/secmod15.asp
Including all the details of what privileges are required at a minimum.

A good blog that provides a FAQ for ASP NET security is:
http://blogs.bartdesmet.net/bart/archive/2004/07/31.aspx

HTH,
--- Nick

 

[Greg Burns]

You do agree, though, that the username and password for the local

accounts
must be the same on both machines, yes?

Most definately.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/secmod/html/secmod15.asp

Great link!

Thanks,
Greg

 

[Guest]

If the AD group is not configured properly for access on the SQL server then
you could get this problem. For instance, if the ad group has system
administrator access at root level, but not for individual databases.