Re: Help: Login failed for User ???

microsoft.public.dotnet.framework.adonet

4 Apr 2006 7:23 PM

Jim Hughes

SQL Server operates in WIndows Auth or Mixed, sounds like yours is Windows
Auth only.

Mixed allows both trusted and userid specific.

You don't specify the password for Windows users, the domain credentials
that are obtained when the user logs in with their domain username and
password are used.

Did you try the UDL test? I would also try connecting with Query Analyzer in
Windows Auth mode as the client on your dev machine to eliminate
connectionstring issues as being the problem.

Windows Auth is generally considered more secure, especially if you are
embedding the username/password combo in the app.config. It is also usually
easier to manage because the user only needs to remember one password.

You may also want to look at the sp_change_users_login procedure to ensure
the Windows accounts are really linked. Check the docs and Google for more
info.

The following procs may also help track down the problem.

exec sp_helpuser
exec sp_helpgroup
exec sp_helplogins
exec sp_table_privileges 'Customers
exec sp_helpdb
exec sp_helprotect

Don't give up on Windows Auth, I think it's worth it!

Show quote

"Michael" <Mich***@discussions.microsoft.com> wrote in message
news:C3A0B0F5-50E8-4ECD-98B1-142F1105F1D6@microsoft.com...
> Hi Jim,
> Thanks again for the reply.
>
> "Jim Hughes" wrote:
>
>> The users desktop permssions are not relevant to their ability to access
>> the
>> SQL Server. They will not need to be admins on their desktop or the
>> server.
>>
> I was sure that the case, but wanted to make sure there was not some wierd
> setting that would make a difference.
>
>> UserID and Passwd are not used in the connection string when
>> trusted/integrated security is used and should not be present.
>>
> Understood. Have removed all that.
>
>> You can test database access by creating an empty file with as UDL
>> extension
>> and then opening it and setting the parameters and using the Test
>> Connection
>> button.
>>
>> The SQL Server will need to be a member server of the domain as will the
>> users workstations.
>>
>> Internal DNS will need to be properly configured with appropriate records
>> to
>> access the domain controllers.
>>
>> Firewall settings on the SQL Server will need to be set to allow access
>> for
>> Named Pipes.
>>
>> The SQL Server Network Configuration network utility will need to be set
>> to
>> allow Network connections via Named Pipes and perhaps TCP/IP.
>>
> On my development machine (logged in as myself) I can use the application
> without any login errors, but as soon as I Login into windows(again on my
> dev
> machine) using the clients(employees here) userid and password, my app
> will
> not work and gives the lonin error as noted before.
>
> I have a question about something I read in the online help.
> <Quote
> When a network user tries to connect, SQL Server uses Windows-based
> facilities to determine the validated network user name. SQL Server then
> verifies that the person is who they say they are, and then permits or
> denies
> login access based on that network user name alone, without requiring a
> separate login name and password.
> />
> Does this mean that the password needs to be set in SQL Server. I ask,
> because, since I have Windows Auth set for the database, I can't change
> any
> of the users passwords. If I have to include the passwords for the users,
> how do I do this in Windows Auth mode?
> I was trying to do a test and just setup the db so that it uses only SQL
> Auth, but when I try to change it to SQL Auth in the Edit database
> registration, I get an error:
> SQL Server registration failed because of the connection failure.
> Login failed for user 'sa'. Reason: Not associated with a trusted SQL
> Server
> connection.
>
> I was thinking of changing over to SQL Auth to make it easier (If I've got
> to go through this each time I add a new user). What do you all think?
> I've
> heard that Windows Auth is better than plain SQL Auth. Why? How about
> creating a single userid and password on the server and user that combo in
> the connect string to connect to the db. I hope I can get Windows Auth to
> work, so I won't have to go in this direction.
> Thanks for hanging in there.
> Michael
>

4 Apr 2006 7:48 PM

Michael

Hi Jim,
Thanks SO MUCH for you help

> SQL Server operates in WIndows Auth or Mixed, sounds like yours is Windows
> Auth only.
>

Yes, Windows Only

> Did you try the UDL test? I would also try connecting with Query Analyzer in
> Windows Auth mode as the client on your dev machine to eliminate
> connectionstring issues as being the problem.
>

I didn't do this one yet, I can use Query Analyzer from my dev machine. It
seems like the Protocals are working right, since I can use the app without
any problems(under My Windows Userid).
This came up yesterday, because we had a new install of the app and the
admin guys built a pc for them and after installing the app, the program
would not login using the users id. Then I went to my spare machine and
logged in as the user and got eh Login errors again, then I reLogged in as me
and it worked fine.

> Windows Auth is generally considered more secure, especially if you are
> embedding the username/password combo in the app.config. It is also usually
> easier to manage because the user only needs to remember one password.
>

Ok, I'll leave it this way, as LONG AS I CAN FIND THE DARN Solution, lol.

> The following procs may also help track down the problem.
>
> exec sp_helpuser
> exec sp_helpgroup
> exec sp_helplogins
> exec sp_table_privileges 'Customers
> exec sp_helpdb
> exec sp_helprotect
>
> Don't give up on Windows Auth, I think it's worth it!
>

I will look into these proces and see what I can find. Thanks agaon so much.
Michael