IWAM_ and login failure

I've been scratching me head for 3 weeks so far on this one.
I'm trying to teach myself how to do databases, etc., for MSSQL 2000. A
friend made a database for me to play around with. He created this on a
Windows 2003, IIS 6 machine. I'm running Win2K AS, IIS 5 and AD installed.
This machine is an internal LAN.
Now, when I try to access this page, I repeatedly get a
"System.Data.SqlClient.SqlException: Login failed for user
'MAINSERVER1\IWAM_MAINSERVER'."
I've been googling the hell out of this, and tried various NG's. Nothing has
worked as of yet.
Things I've tried:
I've added the user to the database permissions (as dbo).
I've made sure that the login is listed under 'Security/Logins'.
I added the user to the NTFS permissions for the folder, both where the DB
is stored plus the intranet folder where the pages are housed.
I changed the logon for 'ASP.NET State Service' from the IWAM account, to
Local System Account. I have noticed, that I can not manually start the
service when the logon is set to IWAM (I think this is part of the problem).
I've uninstalled/reinstalled IIS and .Net Framework 1.1 already.
I added IUSR_ and NT Authority\System to the 'Security/Logins' and verified
permissions for the DB for the same.
I'm at a loss as to what to try next. Any ideas?
TIA
CharlesCharles
Are you using windows only or mixed security? (If you don't know, open Enter
prise Manage, right click on your server and choose properties. The security
tab will show you)
For windows only
Right click My computer on your desktop and choose manage
Open local users and groups
Add user to appropriate group (usually Administrator or Users depending on w
hat you are going to use account for)
For Windows and mixed
In EM open Security tab and click on Logins
Right click and add new login
Give account details including domain
If you want full access to everything go to Server Roles tab and tick system
s administrator, else tick any other role that is suitable
If you want to contol access at individual level or you have not given sa ac
cess go to database access tab and assign database access
If not sa (systems administrator)
Go to individual databases and choose users
Right click and choose new user
Account should be available from drop down menu
Choose any roles in addition to public that is required
If dbo (db_owner) nothing else required
Else click on properties
Choose role just assigned and click permissions
Assign per,issions at an individual level
All should be done now. Full database access.
Hope this helps
John|||IF the error occurs because the web page is accessing sql for user data ( as
opposed to using SQL for IIS State), then you should ONLY have to
1. Add the IWAM login as a valid login to SQL Server
2. Add the login as a valid user in the database you are using
3. Give the user in the database the appropriate permissions...
A quick simple check would be to simply add the login and make it a system
administrator In SQL Server. then try the app again..
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Charles" <cfciii@.adelphia.NOSPAM.net> wrote in message
news:eaI4OgCDEHA.3784@.TK2MSFTNGP10.phx.gbl...
> I've been scratching me head for 3 weeks so far on this one.
> I'm trying to teach myself how to do databases, etc., for MSSQL 2000. A
> friend made a database for me to play around with. He created this on a
> Windows 2003, IIS 6 machine. I'm running Win2K AS, IIS 5 and AD installed.
> This machine is an internal LAN.
> Now, when I try to access this page, I repeatedly get a
> "System.Data.SqlClient.SqlException: Login failed for user
> 'MAINSERVER1\IWAM_MAINSERVER'."
>
> I've been googling the hell out of this, and tried various NG's. Nothing
has
> worked as of yet.
> Things I've tried:
> I've added the user to the database permissions (as dbo).
> I've made sure that the login is listed under 'Security/Logins'.
> I added the user to the NTFS permissions for the folder, both where the DB
> is stored plus the intranet folder where the pages are housed.
> I changed the logon for 'ASP.NET State Service' from the IWAM account, to
> Local System Account. I have noticed, that I can not manually start the
> service when the logon is set to IWAM (I think this is part of the
problem).
> I've uninstalled/reinstalled IIS and .Net Framework 1.1 already.
> I added IUSR_ and NT Authority\System to the 'Security/Logins' and
verified
> permissions for the DB for the same.
> I'm at a loss as to what to try next. Any ideas?
> TIA
> Charles
>|||I am using Mixed Security. The login ID is already there, and I included
IUSR_. I went through, and added the server role of SA for both logins. I
performed a net stop/start for mssql, still the same problem.
Again, I rechecked my permission settings in EM, on the DB file itself, on
the 'Data' folder for MSSQL, and the folder/files inside IIS. I have the
permissions set to 'Full Control' on the files/folders.
It's confusing as hell to me what the problem could be.
Charles
"John Bandettini" <anonymous@.discussions.microsoft.com> wrote in message
news:95E7DA3A-A561-488B-A0F9-A5FBB25DA2AF@.microsoft.com...
> Charles
> Are you using windows only or mixed security? (If you don't know, open
Enterprise Manage, right click on your server and choose properties. The
security tab will show you)
> For windows only
> Right click My computer on your desktop and choose manage
> Open local users and groups
> Add user to appropriate group (usually Administrator or Users depending on
what you are going to use account for)
> For Windows and mixed
> In EM open Security tab and click on Logins
> Right click and add new login
> Give account details including domain
> If you want full access to everything go to Server Roles tab and tick
systems administrator, else tick any other role that is suitable
> If you want to contol access at individual level or you have not given sa
access go to database access tab and assign database access
> If not sa (systems administrator)
> Go to individual databases and choose users
> Right click and choose new user
> Account should be available from drop down menu
> Choose any roles in addition to public that is required
> If dbo (db_owner) nothing else required
> Else click on properties
> Choose role just assigned and click permissions
> Assign per,issions at an individual level
> All should be done now. Full database access.
> Hope this helps
> John
>|||Response inline:
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:%23Fq5FUDDEHA.3344@.tk2msftngp13.phx.gbl...
> IF the error occurs because the web page is accessing sql for user data
( as
> opposed to using SQL for IIS State), then you should ONLY have to
> 1. Add the IWAM login as a valid login to SQL Server
--Are you referring to the 'Start and Run SQL Server in the following
account'? That uses the Administrator login.

> 2. Add the login as a valid user in the database you are using
--The ID is already added to the DB as a user and SA.

> 3. Give the user in the database the appropriate permissions...
--Already done.

> A quick simple check would be to simply add the login and make it a system
> administrator In SQL Server. then try the app again..
--Still have the same error.
Charles

> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Charles" <cfciii@.adelphia.NOSPAM.net> wrote in message
> news:eaI4OgCDEHA.3784@.TK2MSFTNGP10.phx.gbl...
installed.
> has
DB
to
> problem).
> verified
>

It seems a bug of SQL Server 2005.

I do the following steps:

1:Use Manage Studio login the server with Integrated security.

2:Create a dabase named testdb;

3:Create a SQL Server login named amber ,and set it to be dbowner of testdb;

4:Create a SQL Server login named guxiaobo ,set it's default databse to be testbd,and in the testdb databse map login guxiaobo to user guxiaobo;

5:Close Manage Studio and reopen it ,this time use login amber log to the server.

6:In database testbd create a databse role role1 owned by dbo;

7:In database testdb create a schema schema1 owned by dbo;

8:Set user guxiaobo to be member of role1,and set guxiaobo's default schema to be schema1;

9:In the schema properties-schema1 dialog choose permissions,in the users or roles listview I add role1 to it.in the explicit permissions for role1 listbox,I choose select /update/insert/delete/view definition grant checkboxes.and apply the selects.

10:In the schema1 schema I create a table:create table a(a int ,b varchar(10));

11:In the dabase role properties-role1 dialog choose securables panel ,int securable listview I add the schema1.a table,and in explicit permissions for schema1.a I choose all for grant ,then apply the selects.

12:Now I use login guxiaobo to log into dabase and issue "select * from a",but got a error msg saying guxiaobo has not enough permission to select form table a.

Does anyone has found anything I missed?

And in step 11 ,I choose all the columns of table a for all permissions.|||Anybody helps ?

ISSUE: After the SQL Server restart, Login failed for user 'XXX\Administrator'

Hello,
I've a problem, When I Execute the following COMMAND in a batch
command(.bat)
NET STOP MSSQLSERVER
NET START MSSQLSERVER
MY_DATABASE APPLICATION
I GOT Login failed for user 'XXX\Administrator' Exception, but when
after the SERVER started long time, MY_DATABASE APPLICATION will not
get the exception
My COnnection string is "Trusted_Connection=Yes;Integrated
security=SSPI;data source=(local);initial catalog=xxx"
Is there any help that can help me to get rid of it?
MSSQL SERVER 2005I've noticed that SQL Server 2005 takes longer to start. I suggest adding a
delay after starting SQL
Server and before starting your application. Or, modify your application cod
e so it has some
re-tries with some wait in between.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"zzppallas" <zzppallas@.gmail.com> wrote in message
news:1140253035.885476.318630@.g43g2000cwa.googlegroups.com...
> Hello,
> I've a problem, When I Execute the following COMMAND in a batch
> command(.bat)
> NET STOP MSSQLSERVER
> NET START MSSQLSERVER
> MY_DATABASE APPLICATION
> I GOT Login failed for user 'XXX\Administrator' Exception, but when
> after the SERVER started long time, MY_DATABASE APPLICATION will not
> get the exception
> My COnnection string is "Trusted_Connection=Yes;Integrated
> security=SSPI;data source=(local);initial catalog=xxx"
> Is there any help that can help me to get rid of it?
> MSSQL SERVER 2005
>|||Actually this is a weird explanation. If you start the server, are you
getting at this point the exception or at connection time with your
client application. If you are getting the exception at service start,
it seems that your account is a) not prvililedged any more or b) the
password you enetered in the service manager has changed.
If you are getting the exception in the connection via a application
you have to make sure that the user you are connecting with has the
appropiate right on the database. per default administrator are
priviledged on the server, but they can be removed from the autorized
list.
HTH, jens Suessmeyer.

ISSUE: After the SQL Server restart, Login failed for user 'XXX\Administrator'

Hello,
I've a problem, When I Execute the following COMMAND in a batch
command(.bat)
NET STOP MSSQLSERVER
NET START MSSQLSERVER
MY_DATABASE APPLICATION
I GOT Login failed for user 'XXX\Administrator' Exception, but when
after the SERVER started long time, MY_DATABASE APPLICATION will not
get the exception
My COnnection string is "Trusted_Connection=Yes;Integrated
security=SSPI;data source=(local);initial catalog=xxx"
Is there any help that can help me to get rid of it?
MSSQL SERVER 2005I've noticed that SQL Server 2005 takes longer to start. I suggest adding a delay after starting SQL
Server and before starting your application. Or, modify your application code so it has some
re-tries with some wait in between.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"zzppallas" <zzppallas@.gmail.com> wrote in message
news:1140253035.885476.318630@.g43g2000cwa.googlegroups.com...
> Hello,
> I've a problem, When I Execute the following COMMAND in a batch
> command(.bat)
> NET STOP MSSQLSERVER
> NET START MSSQLSERVER
> MY_DATABASE APPLICATION
> I GOT Login failed for user 'XXX\Administrator' Exception, but when
> after the SERVER started long time, MY_DATABASE APPLICATION will not
> get the exception
> My COnnection string is "Trusted_Connection=Yes;Integrated
> security=SSPI;data source=(local);initial catalog=xxx"
> Is there any help that can help me to get rid of it?
> MSSQL SERVER 2005
>|||Actually this is a weird explanation. If you start the server, are you
getting at this point the exception or at connection time with your
client application. If you are getting the exception at service start,
it seems that your account is a) not prvililedged any more or b) the
password you enetered in the service manager has changed.
If you are getting the exception in the connection via a application
you have to make sure that the user you are connecting with has the
appropiate right on the database. per default administrator are
priviledged on the server, but they can be removed from the autorized
list.
HTH, jens Suessmeyer.

issue with a login name

Hi All,
Our network admin logged on to a machine with a Windows
Advanced Server OS as an administrator and installed SQL
Server 2000.
In the dialog box for the Account Services he selected
the system account option (not the Local). He entered my
login name and my password for the user login and the
password (mitra/mitra)
I logged on to the machine with an administrator login
and password (not my user login and password that we
entered for the services). I created a database using
MSSQL Enterprise Manager. I clicked "Login" in
the "Security" folder, there I saw three login names:
<Builtin\administrator>
<domain_name\mitra>
<sa>
I created a database. I clicked "Users" in my new
database. I did not see the <domain_name\mitra> being
listed as a user. Then I right-clicked on the "User" and
added this <domain_name\mitra> as a user to my new
database (the login name was already listed in the Login
drop-list).
I launched our application and tried to create the
database schema using <domain_name\mitra> for the login
name. I kept getting a sql message that the login is
invalid. I tried the <sa> login, no problem, i was able
to create my database schema.
Can someone please tell me why i could not create the
database schema using <domain_name\mitra> login name.
Thanks,
--Mitra> I launched our application and tried to create the
> database schema using <domain_name\mitra> for the login
> name. I kept getting a sql message that the login is
> invalid. I tried the <sa> login, no problem, i was able
> to create my database schema.
> Can someone please tell me why i could not create the
> database schema using <domain_name\mitra> login name.
I would expect a different error message (e.g. 'CREATE TABLE permission
denied'), given the steps you described. This is because a user needs to be
granted CREATE permissions in order to create a database object. If the
objects are to be owned by that user, you can grant the user CREATE
permission for the desired object types. For example:
GRANT CREATE TABLE TO [domain_name\mitra]
In order to create objects in a different schema (e.g. 'dbo'), the user
needs to be a member of one of the roles below. These roles have
permissions to create objects in any schema:
1) sysadmin server role
2) db_owner fixed database role
3) db_ddladmin fixed database role
Hope this helps.
Dan Guzman
SQL Server MVP
"mitra fatolahi" <mitra928@.hotmail.com> wrote in message
news:11af101c410a3$d08e31f0$a301280a@.phx
.gbl...
> Hi All,
> Our network admin logged on to a machine with a Windows
> Advanced Server OS as an administrator and installed SQL
> Server 2000.
> In the dialog box for the Account Services he selected
> the system account option (not the Local). He entered my
> login name and my password for the user login and the
> password (mitra/mitra)
> I logged on to the machine with an administrator login
> and password (not my user login and password that we
> entered for the services). I created a database using
> MSSQL Enterprise Manager. I clicked "Login" in
> the "Security" folder, there I saw three login names:
> <Builtin\administrator>
> <domain_name\mitra>
> <sa>
> I created a database. I clicked "Users" in my new
> database. I did not see the <domain_name\mitra> being
> listed as a user. Then I right-clicked on the "User" and
> added this <domain_name\mitra> as a user to my new
> database (the login name was already listed in the Login
> drop-list).
> I launched our application and tried to create the
> database schema using <domain_name\mitra> for the login
> name. I kept getting a sql message that the login is
> invalid. I tried the <sa> login, no problem, i was able
> to create my database schema.
> Can someone please tell me why i could not create the
> database schema using <domain_name\mitra> login name.
> Thanks,
> --Mitra
>