By default the SqlConnection will use the credentials of the current
user (from the process token or if the thread is impersonating from the
thread token). This is the equivalent of
System.Net.CredentialCache.DefaultCredentials.
As the AdoNetAppender tries to open the SqlConnection during
ActivateOptions the credentials will depend on the thread used to
configure the appender.
If you are loading the configuration file in your ASP.NET
Application_Start method then you need to be aware of the security token
held by the thread running this method. This depends on how you are
hosting ASP.NET. If you are using IIS5 then this will be running as the
local user ASPNET. Under IIS6 the user depends on how you have setup the
Identity section of the relevant application pool.
Do your web users use integrated authentication to authenticate with
your web application? Do you have <identity impersonate="true" /> set in
your Web.config file? If so then that explains why your users are able
to open connections to the database.
If you want to use integrated authentication for your web application
(rather than a specific user that is using your application) to talk to
the database then you need to make the ASP.NET application run under a
windows account that has permission to connect to your database. There
are various ways of doing this depending on your environment, these are
well covered in the MS documentation.
The SecurityContext property on appenders allows the thread token to be
overridden for the appenders activities. If you cannot allow the Web
application to access the database by changing the identity it runs as,
and you cannot use sql user logins (sql server in mixed authentication
mode) then you can use a SecurityContext to do this.
To set the SecurityContext in the config file to use windows integrated
authentication use:
<appender name="AdoNetAppender" type="log4net.Appender.AdoNetAppender">
<securityContext type="log4net.Util.WindowsSecurityContext">
<userName value="test1" />
<password value="password" />
<domain value="domain" />
</securityContext>
... other properties here ...
</appender>
>From a security point of view this is no better or worse than specifying
the sql user id and password in the connection string.
Cheers,
Nicko
> -----Original Message-----
> From: Billy Barnum [mailto:[EMAIL PROTECTED]
> Sent: 22 September 2005 18:48
> To: [email protected]
> Subject: How to pass integrated security credentials to
> AdoNetAppender from ASP.NET?
>
> I'm successfully logging to a SQL Server table using stored
> procs and my own context information - the whole 9 yards.
> Works great with SQL Server security; chokes with 'integrated
> security=SSPI', etc. I get the following error msg:
>
> log4net:ERROR [AdoNetAppender] Could not open database
> connection [workstation id=BBARNUMXP;packet
> size=4096;integrated security=SSPI;data
> source=BMT00002;persist security info=False;initial catalog=WIAN]
> System.Data.SqlClient.SqlException: 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.GetPooledConnec
> tion(SqlConnec
> tionString options, Boolean& isInTransaction)
> at System.Data.SqlClient.SqlConnection.Open()
> at log4net.Appender.AdoNetAppender.InitializeDatabaseConnection()
>
> I know I'm otherwise setting up correctly, because I can
> successfully invoke the same stored proc using the same
> connection string through standard ADO.NET SqlDataAdapter,
> DataSet, and Connection objects. I connect, no prob.
>
> So I looked in the SDK docs and found WindowsSecurityContext
> and tried to set the AdoNetAppender.SecurityContext property,
> but no dice. My
> Application_Start() code is below, and I've tried varying the
> order of events as well, all to no avail; same error. Also 2
> other questions: (1) How would I pass in the equivalent of
> System.Net.CredentialCache.DefaultCredentials instead of
> hard-coding uid/pwd (especially password!) ? (2) Note I pass
> a null to Impersonate(). Couldn't figure out what I was
> supposed to do there. Could this be the problem?
>
> I'm not very good with security issues; anyone got any ideas?
> Thanks in advance.
>
> -BillyB
> William Barnum
> [EMAIL PROTECTED]
>
> P.S. I'm surprised no one has ever asked this question
> before. You'd think this would come up often; none of my
> clients have ever used anything but integrated security.
> Anyway, I searched everywhere, so if the answer is previously
> posted, my abject apologies.
>
> protected void Application_Start(Object sender, EventArgs e) {
> log4net.Util.WindowsSecurityContext log4NetSecurityContext =
> new log4net.Util.WindowsSecurityContext();
> log4NetSecurityContext.DomainName = "BMT";
> log4NetSecurityContext.UserName = "SQL_USER";
> log4NetSecurityContext.Password = "abcef";
> og4NetSecurityContext.Impersonate(null);
> log4NetSecurityContext.ActivateOptions();
>
> XmlConfigurator.Configure();
> Hierarchy hierarchy = (Hierarchy)LogManager.GetRepository();
> if (hierarchy != null)
> {
> AdoNetAppender adoAppender =
> (AdoNetAppender)hierarchy.Root.GetAppender("SqlServerAppender");
> if (adoAppender != null)
> {
> adoAppender.ConnectionString =
> ConfigurationSettings.AppSettings["DefaultConnectionString"];
> adoAppender.SecurityContext = log4NetSecurityContext;
> adoAppender.ActivateOptions();
> }
> }
> }
>
>
