Reply

Francis Favorini Posted: Fri, Feb 24 2006 1:53 AM

rated by 0 users

Just installed CS 2.0, and I get the following error when visiting http://localhost/cs

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

Here's my setup:

• Web server: Win2003 x64, IIS 6.0, ASP.NET 1.1, ASP.NET 2.0 (CS is using 2.0)
• SQL server: Win2003 x64, SQL Server Enterprise 2005 x64, Default instance, Windows Auth Mode

My connection string from web.config:

<add key="SiteSqlServer" value="Data Source=sql1.xxx.xxx;Initial Catalog=CommunityServer;Integrated Security=SSPI" />

I want to use Windows Authentication to connect to the SQL Server.  Is this not supported if on a separate server?  Is this a problem with permissions on the SQL Server "CommunityServer" DB?  Do I want to have only Anonymous Authentication on /cs in IIS Manager?

-Francis

Server Error in '/cs' Application.

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

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Source Error:

[SqlException (0x80131904): Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.]
   System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +437
   System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
   System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
   System.Data.SqlClient.SqlConnection.Open() +111
   CommunityServer.Data.SqlCommonDataProvider.LoadSiteSettings(String application, Int32 settingsID, Boolean findFirst) +275

[CSException: Unable to open connection to data provider.]
   CommunityServer.Components.SiteUrls.Instance() +567
   CommunityServer.Components.CSUrlReWriter..cctor() +26

[TypeInitializationException: The type initializer for 'CommunityServer.Components.CSUrlReWriter' threw an exception.]

[TargetInvocationException: Exception has been thrown by the target of an invocation.]
   System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandle& ctor, Boolean& bNeedSecurityCheck) +0
   System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean fillCache) +103
   System.RuntimeType.CreateInstanceImpl(Boolean publicOnly, Boolean skipVisibilityChecks, Boolean fillCache) +261
   System.Activator.CreateInstance(Type type, Boolean nonPublic) +66
   CommunityServer.Components.SingletonProviderHelper.LoadInstance(String ProviderKey, Type defaultType) +141
   CommunityServer.Components.UrlReWriteProvider..cctor() +26

[TypeInitializationException: The type initializer for 'CommunityServer.Components.UrlReWriteProvider' threw an exception.]
   CommunityServer.Components.UrlReWriteProvider.Instance() +0
   CommunityServer.CSHttpModule.ReWriteUrl(HttpContext context) +23
   CommunityServer.Components.CSContext.Create(HttpContext context, UrlReWriterDelegate rewriter) +50
   CommunityServer.CSHttpModule.Application_BeginRequest(Object source, EventArgs e) +173
   System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +92
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42

Reply

KeyserSoze replied on Fri, Feb 24 2006 8:17 AM

rated by 0 users

Windows authentication isn't really recommended, but if you really want to there is a sql script in the SqlScripts directory named user.sql.  This will add the proper permissions to your database if you run it in Query Analyzer.

Reply

Francis Favorini replied on Fri, Feb 24 2006 1:17 PM

rated by 0 users

KeyserSoze:

Windows authentication isn't really recommended, [...]

According to the docs on the Installation Wizard, it is recommended (Step 13), and it's the default.  Why would it not be recommended?  Everything I ever see on SQL Server security recommends that you use Windows Authentication mode and not Mixed mode.

Is no one else running Windows Auth mode to connect to a separate SQL Server?

-FF

Reply

Francis Favorini replied on Fri, Feb 24 2006 1:58 PM

rated by 0 users

KeyserSoze:

[...] if you really want to there is a sql script in the SqlScripts directory named user.sql.  This will add the proper permissions to your database if you run it in Query Analyzer.

This doesn't work.  It assumes you are running the script on the web server (since is uses @@SERVERNAME).  This won't be the case if the SQL Server is a separate machine.  Also, using SQL Server Management Studio, I cannot add WEBSERVER\ASPNET as a login--my only choices for domain are the local machine (SQL) and the domain.

NT AUTHORITY\NETWORK SERVICE already has the db_owner role for the CommunityServer database.  Shouldn't this be all that's needed? Why does the error messsage say login failed for ANONYMOUS LOGON instead of NETWORK SERVICE?

-FF

Reply

Francis Favorini replied on Fri, Feb 24 2006 3:07 PM

rated by 0 users

favorini:

Also, using SQL Server Management Studio, I cannot add WEBSERVER\ASPNET as a login--my only choices for domain are the local machine (SQL) and the domain.

In IIS 6.0, ASP.NET processes run as NETWORK SERVICE anyway, so the ASPNET user is irrelevant (unless running in IIS 5.0 process isolation mode, which I am not).

Do I need to change the Windows account that IIS uses for anonymous access for the CS app to something other than IUSR_server, or is that irrelevant?  Or should I create a separate application pool for CS which runs with identity set to a domain user (DOMAIN\CSDB, say) that I then give permissions to access the CommunityServer database on the SQL Server?

-FF

Reply

Francis Favorini replied on Fri, Feb 24 2006 4:16 PM

rated by 0 users

favorini:

Or should I create a separate application pool for CS which runs with identity set to a domain user (DOMAIN\CSDB, say) that I then give permissions to access the CommunityServer database on the SQL Server?

I tried this and it worked.  However, I am still puzzled by the fact that CS is trying to connect to the DB as ANONYMOUS LOGON and not NETWORK SERVICE.  What could be causing this?  If I grant ANONYMOUS LOGON access to the CommunityServer database and go back to having CS run in an application pool as NETWORK SERVICE, it works also.  What are the security ramifications of allowing either NETWORK SERVICE or ANONYMOUS LOGON to be the owner of the CommunityServer database?  Does this mean that anyone can connect to the SQL Server anonymously and have complete control over the database (barring firewall protection)?

-FF

Reply

Francis Favorini replied on Mon, Feb 27 2006 11:23 AM

rated by 0 users

In case anyone else cares, in order to make this work, do the following:

1. Add the service account domain user to the web server's local IIS_WPG group.  
2. Add permissions for IIS_WPG to %WINDIR%\Temp to be the same as those for NETWORK SERVICE (namely, List Folder/Read Data and Delete).  (Or you could just change the account on the ACL from NETWORK SERVICE to IIS_WPG, since NETWORK SERVICE is a member of IIS_WPG, but I'm being slightly paranoid.)

I'm still interested as to why CS does not work out of the box with a separate SQL server in Windows authentication mode.

-Francis

Reply

J-O Eriksson replied on Mon, Mar 13 2006 4:21 PM

rated by 0 users

Isn't the NETWORK SERVICE account a local account for the machine? If so it cannot have rights on a different server.

You are running IIS 6.0 right? What you could have done was to change the identity of the application pool that your CS web runs in to a domain account, and then given that account the proper rights in SQL on the other machine.

J-O

Reply

Francis Favorini replied on Mon, Mar 13 2006 4:47 PM

rated by 0 users

jeriks:

You are running IIS 6.0 right? What you could have done was to change the identity of the application pool that your CS web runs in to a domain account, and then given that account the proper rights in SQL on the other machine.

Right. That's exactly what I did, as I say in post 517441 just above.

jeriks:

Isn't the NETWORK SERVICE account a local account for the machine? If so it cannot have rights on a different server.

Well, yes. However, in this MSDN article they say the following:

MSDN:

Granting Access to a Remote SQL Server

If you are accessing a database on another server in the same domain (or in a trusted domain), the Network Service account's network credentials are used to authenticate to the database. The Network Service account's credentials are of the form DomainName\AspNetServer$, where DomainName is the domain of the ASP.NET server and AspNetServer is your Web server name.

For example, if your ASP.NET application runs on a server named SVR1 in the domain CONTOSO, the SQL Server sees a database access request from CONTOSO\SVR1$.

To access a remote SQL Server using Network Service

To grant access to a remote database server in the same domain or a trusted domain, follow the steps described earlier for a local database, except in step 4, use the DomainName\AspNetServer$ account to create the database login.

Note   In production environments, you should place the network service account into a Windows group and create a SQL Server login for the Windows group.

This did not seem to work for me. SQL Server Mgmt Studio would not accept the machine account when creating a SQL Server login. I didn't try setting up the group, though I think now that it would work. (What I didn't notice at the time was that to create a SQL login from a group, you need to click Search, then Object Types, then select Groups.)

In the end, I think having a specific login for the CS app is more secure anyhow, since you can narrow down access to just a specific web app, instead of the whole machine.

BTW, nice avatar.

Reply

Francis Favorini replied on Mon, Mar 13 2006 5:04 PM

rated by 0 users

favorini:

However, I am still puzzled by the fact that CS is trying to connect to the DB as ANONYMOUS LOGON and not NETWORK SERVICE.  What could be causing this?

Upon further reflection, I suppose what's happening is that SQL Server is seeing an incoming connection from the NETWORK SERVICE on WEBSERVER, which actually comes in as the machine account DOMAIN\WEBSERVER$. This account doesn't match a SQL login, so it is mapped to ANONYMOUS LOGON. Someone correct me if this is wrong.

Reply

J-O Eriksson replied on Mon, Mar 13 2006 5:59 PM

rated by 0 users

favorini:

jeriks:

You are running IIS 6.0 right? What you could have done was to change the identity of the application pool that your CS web runs in to a domain account, and then given that account the proper rights in SQL on the other machine.

Right. That's exactly what I did, as I say in post 517441 just above.

Sorry didn't see that. Although I looked for it if you had used app pools.

J-O

Reply

mliedtke replied on Sat, Jan 13 2007 11:01 PM

rated by 0 users

I posted an answer to this problem in this thread:

http://communityserver.org/forums/thread/517260.aspx

Reply

mrtot replied on Wed, May 23 2007 11:32 AM

rated by 0 users

awesome! that did solve the problem.

I wouldn't come up with that solution in hundret years. Thank you! 

Reply

mrtot replied on Wed, May 23 2007 11:34 AM

rated by 0 users

 that answer was not sufficient! I wasn't even able to select "public" on the database-level plus the user allready existed for both, server-level and database-level