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

[Brian Fulford]

I am losing lots of hair over this issue. We are trying to implement windows
authentication from a web server to a database server.
The web server is on a domain; the db server is not... does this matter?
the IUSR_machinename user from the web server has been added as an account
on the db server and also been to added to db logins and given access to the
databases that it will need access to.

My web.config has the following:
<authentication mode="Windows"/>

<sessionState mode="SQLServer" sqlConnectionString="Network
Library=DBMSSOCN; data source=192.168.30.95; Integrated Security=SSPI;"
cookieless="false" timeout="20"/>


Anyone run into this?


Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean&
isInTransaction)
at
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec
tionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
at System.Web.SessionState.SqlStateConnection..ctor(String
sqlconnectionstring)

 

[Ken Schaefer]

Hi,

a) the session state service would be trying to connect to the SQL Server
using ASP.NET's process identity. If this is IIS5, then the default process
identity is MachineName\ASPNET. This is a *local* account, and can't be
assigned permissions to network resources. You will need to use some other
account (eg a domain account). If this is IIS 6.0 then ASP.NET uses the web
application pool's process identity, which is NT Authority\Network Service
by default.

b) the above has nothing to do with the *user's* credentials. You are using
Windows Authentication, but I don't know whether you are using
*impersonation* (which is where ASP.NET impersonates the authenticated
Windows user). If ASP.NET is using impersonation, then ASPNET (IIS5) or
Network Service (IIS6) will not be used - instead, whatever user IIS is
logging on will be used. This would be IUSR_<machinename> if IIS is allowing
anonymous authentication, or the specific user if you are forcing the remote
user to authenticate.

BUT

In most cases (Basic Authentication excepted), IIS does not have the user's
username/password, so it can't directly impersonate the user when accessing
remote resources. Instead, all IIS has is an access token, which does not
have privileges to network resources (well it does, but only as
"anonymous"). To get around this, you can use Kerberos Authentication, and
enable Delegation, or you can use Basic Authentication.

HOWEVER

If you do this, you will defeat connection pooling, since a separate pool
will be created for each authenticated user.

Here are some useful links:

Read chapter 12 from the Building Secure ASP.Net Application Book - it has
very good information about building scalable, secure ASP.Net applications
(eg using a trusted subsystem model):
http://msdn.microsoft.com/library/d...-us/dnnetsec/html/secnetlpMSDN.asp?frame=true

The following document explains various authentication mechanisms in IIS
(like Basic, Digest, IWA), and how they work:
http://www.adopenstatic.com/resources/books/293_CYA_IIS6_05.pdf

The following document explains Kerberos Delegation (this is *very*
detailed - well worth reading):
http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerbdel.mspx

Some other articles:

http://support.microsoft.com/?id=319723
INF: SQL Server 2000 Kerberos support including SQL Server virtual servers
on server clusters

http://support.microsoft.com/default.aspx?scid=kb;en-us;810572
HOW TO: Configure an ASP.NET Application for a Delegation Scenario

http://support.microsoft.com/?id=294382
Authentication May Fail with "401.3" Error If Web Site's "Host Header"
Differs from Server's NetBIOS Name

http://support.microsoft.com/default.aspx?kbid=325894
HOW TO: Configure Computer Accounts and User Accounts So That They Are
Trusted for Delegation in Windows Server 2003 Enterprise Edition (also
includes Windows 2000 instructions)

http://www.microsoft.com/resources/...andard/proddocs/en-us/se_con_del_computer.asp
Configuring Users and Computers for delegation (there's a couple of pages -
use the links in the nav bar to get to them)

Windows 2003 Protocol Transition
http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/constdel.mspx

Cheers
Ken

 

[Brian Fulford]

Could I sync up the ASPNET user accounts on the web server and the database
server?
since anonymous requests are allowed and we have our own login system, basic
authentication is out the window.

Inside the asp.net page when it first starts up, here is the windows
identity ---
? System.Security.Principal.WindowsIdentity.GetCurrent

{System.Security.Principal.WindowsIdentity}

AuthenticationType: "NTLM"

IsAnonymous: False

IsAuthenticated: True

IsGuest: False

IsSystem: False

Name: "CBF1\IUSR_FULFOB"

Token: 868

Is this misleading or is the IUSR account actually the identity the asp page
is running under? I did also neglect to say that I have impersonate = true
in the web config.
<identity impersonate="true"></identity>

SO my line of thinking leads me to believe that the web page is using the
IUSR account... and should pass the ISUR_FULFOB name on to the database
server, which has this user account created on the machine. Or am I way off
base here?

Brian

 

[Ken Schaefer]

Because you have <identity impersonate="true" /> ASP.NET is impersonating
the user that IIS is logging on as, which is IUSR_<machinename> by default.

Now, if you do not want the user's Windows credentials to flow back to SQL
Server, then just change the anonymous user account configured in IIS to be
a domain account, and grant that domain account sufficient privileges in SQL
Server.

If you want the user's Windows credentials to flow back to SQL Server, then
you have a whole different set of issues (which I alluded to in my previous
post).

Cheers
Ken

 

[Paul]

Following info might help:

ms-help://MS.MSDNQTR.2003FEB.1033/vbcon/html/vbtskAccessingSQLServerUsingWindowsIntegratedSecurity.htm



Paul