SQL Server Windows Authentication failing 1

[wastiehl]

Running MS SQL Server 2000 on Windows 2000.
Database Security Property set to "Windows Authentication"
Rebooted everything, but any attempt to connect from user application program fails because user is invalid. Acts just like it's doing SQL Server Authentication for a Standard User. Out of ideas and anxious. thanks.

 

[JayKusch]

Can you give us the associated error message your app is returning?

Thanks

J. Kusch

 

[younguser]

You may need set up permision for user.

 

[SQLBill]

Has Windows Authentication EVER worked for you?

If not, there are certain things that need to happen for it to work:

1. Each user MUST have a valid login and password (Enterprise Manager, drill down to Security and then Logins). This login AND password MUST be the same as they are using from the client application.

2. The login MUST have use Windows Authentication chosen

3. The login MUST be given access to the appropriate database(s).

-SQLBill

 

[wastiehl]

The error message is "10003 Login incorrect"

SQLBill writes that the users must be set up individually.
I am familiar with that procedure, and if I set up SQL Authentication and don't do it, that's the message I get here with the same error number. I am running SQL Server 7 and Windows NT in the back, my customer is running SQL Server 2000 on a Win 2000 box. She and I get the same error under these conditions: 10003.

When I set up my own database for Windows NT Authentication, there is no need to create users for the database. In fact when I started my development with NT Authentication, it puzzled me that I could not get attempts to login to fail at all. Any username and password got me into my application. When I learned more, I changed the server authentication. My user, however, does not want SQL Server Authentication. She has been unable to get Windows Authentication to work. So she and I are left to wonder if there is a critical difference between SQL Server 7 and SQL Server 2000. Does her engine require usernames even for network authentication?

 

[SQLBill]

I have SQL Server 2000.
I use Mixed authentication.
I have two different accounts (LOGINS) in SQL Server.
The first account is my regular account and uses Windows NT authentication.
The second account is for testing and uses SQL Server Authentication.

I HAD to create both security logins regardless of the method used for accessing the database. When I created the logins, I had the option to chose the method of authentication for that LOGIN.

But there has to be a login or ANYONE could access the database(s).

-SQLBill

 

[wastiehl]

SQLBill,

I have learned that one critical difference between my database (SQL Server 7) and my customer's (SQL Server 2000) is that for the latter I need the keyword "SECURE" in the command line to connect. I have made that change for her, and for Windows Authentication we do not pass the username and password values in the command.

She and I have both discovered that we are unable to connect to our respective servers from our XP clients. We suspect that there are client-side differences between 2000 and XP clients. Do you have any familiarity with this? She has now discovered the same problem (with the two clients) with another application she has that's not from me.

Your respnse was helpful, and I have sent it to her.

 

[SQLBill]

Sorry, we don't use XP at this time, so I can't compare them.

Good luck on fixing the problems.

-SQLBill

 

[Steveedwey]

To set up SQL Server 2000 for Windows authentication access (enables browser script access)
Assume the user logging into Windows is "Steve"


Open Computer Management:
---------------------------------
In Local Users and Groups: - Set up these accounts

IUSER_Steve - Internet Guest Account
Member of = Guests
IWAM_Steve - Launch IIS Process Account
Member of = Guests
TsInternetUser - TsInternetUser (if using Terminal Services)
Member of = Guests
---------------------------------
In IIS -> Web Sites->Properties->Directory Security -

TICK Anonymous access
in Edit->
Anon User=IUSR_Steve
TICK Allow IIS to control password

Clear Basic Authentication

Tick Integrated Windows Authentication
-------------------------------------------------------
a. In Microsoft SQLServers->(local)->Properties->Security
Set Authentication=SQL SERVER AND WINDOWS (That bit is important!)
Startup service account=SYSTEM ACCOUNT
--------
b. In Microsoft SQLServers>(local)>Edit SQL Server Registration Details
Set - Use Windows Authentication
----------------
Open (local) Server. Go to Security>Logins
You'll see logins here for"builtin\\administrators", and "sa" and for Steve_IUSR
(Steve_IUSR has already been authorised to enter SQL Server by Windows, so you can't change his password).
However, if you want your web page script to connect to SQL Server regardless of who's logged on to Windows,

you include in your script a connection string which specifies a username and password for SQL Server access.

For example:
sConnString = "Provider=SQLOLEDB.1;Data Source=(local);Initial Catalog=RosterPro_Live;uid=harry;pwd=edwey"

This username and password must correspond with a login account in SQL Server>Security>Logins. So . . .

In Security>Logins
Create a new login for the example user "harry" specified in your connection string
Settings = General > SQL Server Authentication - pwd="edwey"
>access - Tick the database(s) you want the script to be able to access. (That's important!)
------------------------------------------------------
In Services

MSSQLSERVER>Properties>Logon
Set - Use LOCAL SYSTEM ACCOUNT

SQLSERVERAGENT>Properties>Logon
Set - Use LOCAL SYSTEM ACCOUNT

Now stop and restart MSSQLSERVER

Now your web page script should be able to access the database you specified within SQL Server.

Query analyser, if you use it, will also connect via windows authentication or SQL Server authentication with the

right password.