problem with trusted connection (asp.net)

[Mad Scientist Jr]

I am getting the following error when trying to access a database with
a trusted connection:

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

My connection string is:

"Server=MyServer; Database=MyCatalog; Trusted_Connection=True;"

Note that Anonymous Access is off in IIS, and I am able to read my
Windows login with:

Textbox1.text =
System.Web.HttpContext.Current.User.Identity.Name.Substring(System.Web.HttpContext.Current.User.Identity.Name.IndexOf("\")
+ 1).ToLower()

Can anyone explain how to query the db using a trusted connection?

PS Is there a way to dynamically retrieve a list of databases the
current user has SQL rights on, by specifying only the server, again
using trusted connection?

Thanks...

 

[Hari Prasad]

Hello,

Have you added the OS user into the SQL Server? If not add the particular
user into SQL Server Logins and try connecting.

Thanks
Hari

 

[Damien]

I am getting the following error when trying to access a database with
a trusted connection:

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

My connection string is:

"Server=MyServer; Database=MyCatalog; Trusted_Connection=True;"

Note that Anonymous Access is off in IIS, and I am able to read my
Windows login with:

Textbox1.text =
System.Web.HttpContext.Current.User.Identity.Name.Substring(System.Web.HttpContext.Current.User.Identity.Name.IndexOf("\")
+ 1).ToLower()

Can anyone explain how to query the db using a trusted connection?

PS Is there a way to dynamically retrieve a list of databases the
current user has SQL rights on, by specifying only the server, again
using trusted connection?

Thanks...

You're almost certainly encountering the "double hop" issue. This
arises when IIS and SQL Server are on separate servers. If that isn't
true in your circumstance, then ignore the rest of this post.

The problem arises because your client machine authenticates you to the
IIS server, but the IIS server has no means of authenticating you to
the SQL Server box (It can't make the second "hop" of authentication).
There was a superb MSDN Magazine article some time back that describes
this, but I can't seem to find it right now. I have found this
knowledge base article:

http://support.microsoft.com/kb/810572

which seems to cover the same areas.

Damien

 

[Mad Scientist Jr]

Thanks for your reply...

I read up on it and fixed the problem on my local machine by adding the
following to my web.config file:

<identity impersonate="true"/>

However when I try running this on the live web server I get this
error:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Any ideas?

 

[Joe Kaplan]

Like Damien said, this is a double hop issue and is solved by implementing
Kerberos delegation. I'd suggest reading the various technet docs on
Kerberos delegation, as they are quite good, as well as reading Keith
Brown's MSDN magazine articles that discuss this.

You will not get this to work using IWA auth, impersonation and SQL on a
different machine unless you do this.

Joe K.

 

[Mad Scientist Jr]

I read the articles below and it makes sense. I am working with my
system admin to set this up. Thanks to everyone for your replies.

Once I get this working, I have a second question - from ASP.NET is
there a way to dynamically retrieve a list of databases on a given SQL
server that the current user has SQL rights on?

Thanks again

How to configure an ASP.NET application for a delegation scenario
http://support.microsoft.com/kb/810572

Allow a computer to be trusted for delegation
http://technet2.microsoft.com/Windo...a055-43f7-b9be-20599b694a311033.mspx?mfr=true

How to Open Active Directory Users and Computers
http://www.microsoft.com/technet/pr...ce2-5557-4a3e-b2f7-df3f65640671.mspx?mfr=true

 

[Joe Kaplan]

You may also need to read the "troubleshooting Kerberos delegation" paper
from TechNet as well if this doesn't work right. Unfortunately, it can be a
bit painful when it doesn't just work and there are a bunch of options which
make things complicated in Windows Server 2003 (constrained delegation and
protocol transition).

I can't answer the second question. I'm a security guy.

Joe K.

 

[Jason]

We ran into the same problem a couple of weeks ago. Turn off the
impersonation and just grant db access to what ever account the appPool is
using, default is NETWORKSERVICE. You'll have to script the user into the db
since you can't browse for MACHINENAME$ (the server's NETWORKSERVICE account).

this article explains it al
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/PAGHT000008.asp?_r=1