sql server connect: Hello,

I have been attempting to configure an intregrated (AD and Windows)
authentication between two servers running an ASP.NET/SQL Server 2000
environment for months. I have not been able to make this work as
documented, no matter what KB articles I follow or how much I upgrade
software.

Right now, I have upgraded the IIS server to Windows 2003 Server with
FPSE, Service Pack 2 of .NET Framework 1.0, and set authentication to
Windows Integrated authentication only. We are using Visual
Studio.NET 2002, and upgrading to 2003 is not currently an option.

The SQL Server 2000 box has also been upgraded to Windows 2003, uses
mixed authentication, and has the AD group in the database.

The user authenticates properly to the main page, but when he attempts
to authenticate to the database (on a separate Windows 2003 Server)
via a Web Form, the following error message occurs (yes I know it's a
common one):

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

I've tried a variety of connection strings, even played with OLE DB,
but nothing works. The string I am using right now is:

"Data Source=MYSERVER;Initial Catalog=MYDB;Integrated Security=SSPI;"

In my web config fille, I also have the following settings.

<authorization>
<allow roles="mydomain\mygroup" />
<deny users="*" />
</authorization>

<identity impersonate="true" />

The AD group has been granted public, datareader, and datawriter
permission in the SQL Server database, and basic permissions on the
web site. Standard database password and userid connection strings
work, but that's not acceptable for this application.

I do not have DBA or Administrator access to the SQL server box, so if
the problem is on that end, I need an explanation in very plain terms
to convey to the DBA as to what needs reconfiguring.

Please email me as well as post to the groups, as I am desparate for
an answer, and the next step is a support ticket with Microsoft that
will have to be coordinated amongst organziations and vendors. Need I
say more?

Thanks in advance,
Julie Cooper
julie.cooper@cmsis.com;
julie.cooper@fairfaxcounty.gov

[quoted text, click to view]

[snip]

[quoted text, click to view]

[snip]

Julie,

The scenario that you have detailed appears to me that you are trying to use
your web application in a delegation scenario, however you have not
configured your servers to be enable delegation of credentials. Have you
checked out this article ?

http://support.microsoft.com/default.aspx?scid=kb;en-us;810572

hth
andrew

Andrew,

Thanks for your reply. I had seen the KB article to which you refer,
but it is not applicable in my situation. After a 2 hour Microsoft
support call with 4 of their engineers, myself, and a Windows
administrator who manages a 30,000 user MS network, the problem was
solved with one command:

setspn -A MSSQLSvc/myserver.microsoft.com:1433 MYDOMAIN\sqlsvc

See:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_2gmm.asp

http://support.microsoft.com/?kbid=319723

This was not pretty. I hope some poor soul with the same problem
finds this post and saves themselves the trouble.

Regards,
Julie

[quoted text, click to view]