SQL Server 2005 Logon Issues

[Lee T. Hawkins]

I am having a number of problems over the last two full days trying to get
an ASP.NET 2.0 application to connect to a SQL Server 2005 database...

First off, I built this application w/ Visual Studio 2005 SP1 and had it
working perfectly on my Windows XP machine on SQL Server 2005 Express. I
decided to publish this application to a test development server (with both
the SQL and IIS servers on the same box) running on:

Windows Server 2003 R2 SP2 (Running on top of Virtual Server 2005 R2)
Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.086.3959.00
(srv03_sp2_rtm.070216-1710)
Microsoft MSXML 2.6 3.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790

The application worked fine on XP, but I have had to make a TON of
configuration changes to get this thing to work (and it took me forever to
figure out how to set up, since no one seems to have a very clear how-to
guide that's been able to solve this problem).

This IIS web is set to use its own application pool which is set up to work
as its own local service account. The connection string in my web.config is
set as:

Data Source=127.0.0.1; Initial Catalog=<databasename>; Integrated
Security=SSPI;

SQL Server has an account that maps using Windows Integrated Security to an
account that has been assigned the dbowner role.

The pages that require no data access all work as they are expected to, and
my identity is working as well. Once I try to use a page that goes to the
database, I get this error from ASP.NET:

Login failed for user '<SERVER>\<ServiceAccount>'.
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
'<SERVER>\<ServiceAccount>'.

Here is the error log I get from SQL Server 2005:
2007-05-30 13:55:30.87 Server Microsoft SQL Server 2005 - 9.00.3054.00
(Intel X86)
Mar 23 2007 16:28:52
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

2007-05-30 13:55:30.88 Server (c) 2005 Microsoft Corporation.
2007-05-30 13:55:30.88 Server All rights reserved.
2007-05-30 13:55:30.88 Server Server process ID is 3128.
2007-05-30 13:55:30.88 Server Authentication mode is WINDOWS-ONLY.
2007-05-30 13:55:30.88 Server Logging SQL Server messages in file
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2007-05-30 13:55:30.88 Server This instance of SQL Server last reported
using a process ID of 2428 at 5/30/2007 1:55:29 PM (local) 5/30/2007 5:55:29
PM (UTC). This is an informational message only; no user action is required.
2007-05-30 13:55:30.88 Server Registry startup parameters:
2007-05-30 13:55:30.88 Server -d C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\master.mdf
2007-05-30 13:55:30.88 Server -e C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2007-05-30 13:55:30.88 Server -l C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2007-05-30 13:55:30.94 Server SQL Server is starting at normal priority
base (=7). This is an informational message only. No user action is
required.
2007-05-30 13:55:30.98 Server Detected 1 CPUs. This is an informational
message; no user action is required.
2007-05-30 13:55:31.19 Server Using dynamic lock allocation. Initial
allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is
an informational message only. No user action is required.
2007-05-30 13:55:31.25 Server Attempting to initialize Microsoft
Distributed Transaction Coordinator (MS DTC). This is an informational
message only. No user action is required.
2007-05-30 13:55:32.32 Server Attempting to recover in-doubt
distributed transactions involving Microsoft Distributed Transaction
Coordinator (MS DTC). This is an informational message only. No user action
is required.
2007-05-30 13:55:32.33 Server Database mirroring has been enabled on
this instance of SQL Server.
2007-05-30 13:55:32.35 spid5s Starting up database 'master'.
2007-05-30 13:55:32.84 spid5s Recovery is writing a checkpoint in
database 'master' (1). This is an informational message only. No user action
is required.
2007-05-30 13:55:33.04 spid5s SQL Trace ID 1 was started by login "sa".
2007-05-30 13:55:33.09 spid5s Starting up database
'mssqlsystemresource'.
2007-05-30 13:55:33.13 spid5s The resource database build version is
9.00.3042. This is an informational message only. No user action is
required.
2007-05-30 13:55:33.83 Server A self-generated certificate was
successfully loaded for encryption.
2007-05-30 13:55:33.85 spid5s Server name is '<SERVERNAME>. This is an
informational message only. No user action is required.
2007-05-30 13:55:33.85 Server Server is listening on [ 'any' <ipv4>
1433].
2007-05-30 13:55:33.86 Server Server named pipe provider is ready to
accept connection on [ \\.\pipe\sql\query ].
2007-05-30 13:55:33.88 spid9s Starting up database 'model'.
2007-05-30 13:55:33.90 Server Server is listening on [ 127.0.0.1 <ipv4>
1434].
2007-05-30 13:55:33.90 Server Dedicated admin connection support was
established for listening locally on port 1434.
2007-05-30 13:55:33.97 Server SQL Server is now ready for client
connections. This is an informational message; no user action is required.
2007-05-30 13:55:34.09 spid12s Starting up database 'msdb'.
2007-05-30 13:55:34.09 spid13s Starting up database '...'.
2007-05-30 13:55:34.09 spid14s Starting up database '...'.
2007-05-30 13:55:34.12 spid15s Starting up database '...'.
2007-05-30 13:55:35.06 spid9s Clearing tempdb database.
2007-05-30 13:55:36.80 spid5s Recovery of any in-doubt distributed
transactions involving Microsoft Distributed Transaction Coordinator (MS
DTC) has completed. This is an informational message only. No user action is
required.
2007-05-30 13:55:36.80 spid9s Starting up database 'tempdb'.
2007-05-30 13:55:37.41 Logon Error: 18456, Severity: 14, State: 11.
2007-05-30 13:55:37.41 Logon Login failed for user 'GOOFY\PETEDBUser'.
[CLIENT: 127.0.0.1]
2007-05-30 13:55:37.50 spid5s Recovery is complete. This is an
informational message only. No user action is required.
2007-05-30 13:55:37.50 spid12s The Service Broker protocol transport is
disabled or not configured.
2007-05-30 13:55:37.51 spid12s The Database Mirroring protocol transport
is disabled or not configured.
2007-05-30 13:55:37.56 spid12s Service Broker manager has started.

I have tried almost everything in the book, enabling/disabling SQL
protocols, changing the connection string, and whatever else, and no matter
what, I still cannot shake this error! PLEASE HELP ME! I've spent 2 days
almost 100% of the time trying to get this to work with pretty much ZERO
progress. Hopefully I've put in enough detail to give someone an idea of
what's wrong. The server is almost brand-spankin' new, and it's got nothing
out of the ordinary installed on it. I did also run the SQL Server Surface
Area Configuration Tool and allowed both local and remote connections via
named pipes and TCP/IP, and I'm just totally at my wits end. I'd appreciate
it if someone could at least stop the bleeding, if not end the nightmare!

Thank you!

 

[Lee T. Hawkins]

Okay, now I feel like a newb

I just started rummaging through SQL Server Mgmt Studio (now that help is on
the way) and figured out that I was adding my user into SQL Server through
the Databases\<databasename>\Security\Users as opposed to adding it down in
Databases\Security\Logins. This fixed my problem.

Sorry for the trouble! Hopefully this post will at least spare someone else
from wasting 2 days on a knucklehead problem!!!

 

[Lee T. Hawkins]

It's also worth mentioning that it actually needs to be added in both
places!

 

[Steven Cheng[MSFT]]

Hi Lee,

For accessing SQL Server (through windows authentication) from ASP.NET web
application, you need to take care of the following things:

1. Verify what's your ASP.NET application's current running identity. It
could be your ASP.NET process's process identity or a impersonated user's
identity(if you've used impersonate in your application). Also, process
identity may vary between IIS6 and IIS5(IIS 6 use application pool)

http://msdn2.microsoft.com/en-us/library/dwc1xthy.aspx

2. After you verify the application's running identity(which will be used
when access secured resource such as SQL Server), you can make sure whether
that certain identity has sufficient permission on the target resource.

Hope this also helps some. If you have any further question, please feel
free to post here.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead



==================================================

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.



Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.

==================================================



This posting is provided "AS IS" with no warranties, and confers no rights.