SQL Server 2000 + ASP.NET: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

1
votes

I just migrated a development workstation

  • FROM: Windows XP Pro SP3 with IIS 6
  • TO: Vista Enterprise 64bit with IIS 7

Since the move, one of my pages that accesses an SQL Server 2000 database is receiving the following error from my ASP.NET 2.0 web page: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'."

I have:

  • enabled Windows Authentication in IIS and web.config
  • disabled Anonymous Authentication in IIS
  • set up Impersonation to run as the authenticated user
  • verified that the logged in user (in this case, me) has access to the appropriate database on the SQL Server
  • verified that my login and impersonation information is correct in the ASP.NET page by checking User.Identity.Name and System.Security.Principal.WindowsIdentity.GetCurrent().Name (both display my username)

My connection string using SqlConnection is "Server={SERVER_NAME};Database={DB_NAME};Integrated Security=SSPI;Trusted_Connection=True;"

Why is it trying to login with NT AUTHORITY\ANONYMOUS LOGIN? I have to assume it's some setting or web.config entry specific to IIS7 since it worked fine before the migration.

NOTE: The SQL Server is Windows authentication only - no mixed mode or SQL only.

3
sql-serversecurityiis
SQL is installed on a dfferent box? – gbn

3 Answers

2
votes

you'll need to set up an SPN in active directory. Assuming you're running SQL server under a domain service account on the intermediary server, you'll need to run this link on the domain controller:

setspn -A MSSQLSvc/SERVERNAME.domain.name:1433 domainname\SQLServiceAccount

Switch out SERVERNAME for the intermediary server name (the one that's accessing the SQL 2000 box on your behalf), make sure you use the fully-qualified domain name (IE. Myserver.mycompany.local), and then domainname\SQLServerAccount is the account you're using to run the SQL services (IE the account that will be accessing the SQL 2000 box on your behalf).

If you want more details, here's an MSDN article about it - http://technet.microsoft.com/en-us/library/bb735885.aspx. Post back if you run into any problems.

1
votes

Assuming SQL Server is installed on a separate box and is in a domain

Your workstation needs enabled for delegation in Active Directory to pass through credentials ("login token") and to allow impersonation to work. It looks like this is what you are trying to do based on your other steps,

0
votes

Run in SSMS

EXEC master..sp_addsrvrolemember @loginame = N'NT SERVICE\MSSQLSERVER', @rolename = N'sysadmin'