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
>

Issues setting up users to authenticate via NT group into MSSQL

I have a NT Group of users that need to connect to MSSQL db using integrated auth and run a stored proc. I have got this concept to work for individual windows users but not a group.

Does anyone have any ideas?

(Win XP, MSSQL 2000 SP3, Windows Group = "SQLUsers", member is "User2")

-- TSQL --

USE master
GO
sp_grantlogin 'pc\SQLUsers'
GO
sp_defaultdb 'pc\SQLUsers', 'theDB'
GO
USE theDB
GO
sp_grantdbaccess 'pc\SQLUsers', 'SQLUsers'
GO
GRANT EXECUTE ON [dbo].[uspThing] TO SQLUsers
GO

-- Trying to connect with OSQL.EXE:

> osql -d theDB -E
Login failed for user 'pc\User2'.

BUT....

runing it for the individual user:

--TSQL

USE master
GO
sp_grantlogin 'pc\User2'
GO
sp_defaultdb 'pc\User2', 'theDB'
GO
USE theDB
GO
sp_grantdbaccess 'pc\User2', 'SQLUsers'
GO
GRANT EXECUTE ON [dbo].[uspThing] TO SQLUsers
GO

-- now trying to connect with OSQL.EXE as User2:

>osql -d theDB -E
1> exec uspThing
2> go
3> data...

all is fine.....

I can't use the user method - to much admin... any ideas!?

PKOk, get this... because I was using "runas /user:User2 cmd.exe" sessions for testing the different users integrated security (osql -E -d dbName) etc the group changes I was making were not reflected until I logged out of the command session.

I have been officially driven mad by this one...

Issue using the JDBC driver to connect to a remote server

Hi All,

We have a customer in Japan who's using our tool to connect to their SQL Server. Our application runs on a Linux machine and uses the MSSQL JDBC driver for connectivity.

We're getting errors back that are not-decipherable because, I presume, they're in Japanese, and then our logs don't log them in Unicode, so they get flattened out as question marks. The errors come out looking something like this:

Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]? 'MSSQL'

?

SQL State: HY000

SQL Code: 4060

[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]? 'sa' ?

SQL State: 28000

SQL Code: 18456

[Microsoft][SQLServer 2000 Driver for JDBC]An error occured while attempting to

log onto the database.

SQL State: 08001

SQL Code: 0

I think we could troubleshoot the problem if we could only read the errors coming back. I know in the ODBC world there are options like "Change the language of SQL Server system messages" and "Perform transaltion for character data" in the ODBC control panel. I couldn't find any similar options available in the JDBC driver however. Do they exist?

Even if they don't, just scanning the messages makes it pretty clear that there's an authentication/permissions issue at work here. We have another app using the same credentials that works fine though - so if there are any known issues about using JDBC to authenticate in a multi-lingual environment, that would be helpful too. Our tcpdumps seem to suggest that the JDBC driver is using multi-byte strings when communicating with the server, however the "working" application is using single-byte encoding. Could that be why we're not authenticating? How is that controlled? Note that at this point I'm just talking about authentication, not the codepages/encodings of the data in the database - right now I'm just worried about logging in.

Thanks!
Tom

I do not think app using single byte encoding should make any difference when logging in. You really should get the error string in English to understand what is going on.

|||

Error 4060 is "Cannot open database "..." requested by the login. The login failed.", 18456 is "Login failed for user ...".

I am moving to the "SQL Server Security" forum for further follow-up.