SQL Server / Server 2003 "Login failed for user "(null)". Not ass

[Denda]

Hi all -

I put this on the Server 2003 forum & haven't had any responses, anyone here able to help me?

got a goofy issue here, that I'm pretty sure the resolution is staring me in the face, but I'm coming up blank....

I have a SQL database on Server 2003. I can log into the database from the client as anyone who is part of our domain admins group. I'm wanting windows authentication only, if possible.

If I try to log into the database from an ordinary user, I get the following error from the server "Login failed for user "(null)". Not associated with a trusted SQL Server connection."

I have added the domain user to SQL database security as well as the shared security on the server. I have even given them db_owner rights to see if I'm not giving enough rights for connection. Still the same result.

Again, if I log onto the pc as myself (equal to domain admin) everything works fine, if I log out & back in as a normal user, I get the error.

Any ideas? Thanks in advance

 

[Denda]

Ok - I've got a little more information. I started searching on the ODBC error & got a little further, I hope. Unless it's false hope.

I get the following error after trying to connect via just the ODBC: SQL Server Error: 18452

When I searched on that, I found that the server itself is set as mixed authentication & not just Windows authentication. I'm trying to change the SQL Server configuration security setting to windows only (as the client config on the ODBC is), but eventhough I restart the database, it keeps going back to SQL Server & Windows..

Any ideas on why it's doing this? Thanks

 

[Denda]

just had to bounce my server to get the SQL server auth mode to change. Finally is showing windows only. I'm still getting the same error.

I have the ODBC set to windows only, I have the sql server set to windows only. What else can I check?

 

[Denda]

OK - I think I know what's going on now... When the user tries to connect remotely (same domain) to the SQL server, the SQL server gives this error:
Event Type: Failure Audit
Event Source: Security
Event Category: Logon/Logoff
Event ID: 529
Date: 9/30/2004
Time: 11:39:07 AM
User: NT AUTHORITY\SYSTEM
Computer: TWB-APPS
Description:
Logon Failure:
Reason: Unknown user name or bad password
User Name: USER1
Domain: TWB-PCA
Logon Type: 3
Logon Process: NtLmSsp
Authentication Package: NTLM
Workstation Name: TWB-PCA
Caller User Name: -
Caller Domain: -
Caller Logon ID: -
Caller Process ID: -
Transited Services: -
Source Network Address: -

OK - here's the issue... The workstation is coming across as the domain. The user is a domain user & has no issues logging in at all.

Any ideas?

 

[SQLBill]

Here is how Windows Authenticatio works....

1. The user logs into their Windows computer. That login is authenticated on the domain (usually at a Domain Controller).

2. The user attempts to access SQL Server.
-the user MUST have a login on SQL Server and the login MUST be set to Windows Authentication.
-the SQL Server login for the user MUST be the same login they use to access Windows.

3. SQL Server attempts to check the login via the Domain Controller it uses.
-SQL Server must use the same Domain Controller OR a domain controller that has the same logins (two different DCs would have to replicate their logins between each other).

If the user's computer and SQL Server use two different Domain Controllers and the DCs don't replicate logins between them (in other words they have to be able to act as backup DCs for each other), then they are not TRUSTED DOMAINS.

Without Trusted Domains, Windows Authentication will not work.

-SQLBill

 

[Denda]

OK - I'm posting this for anyone else that may have this issue. I got this working properly by promoting my W3K server to a domain controller. Once I did that, all authentication started jiving via AD. Word to the Wise.. If you do not have any w3k servers that are domain controllers already, you MUST prepare AD for the W3K.

 

[Denda]

Thanks Bill -
I noticed your post after I posted my last post. ) thanks for the explanation. The domain controller for both was the same, but it just wasn't jiving w/o making the server a dc. We've got some issues with our AD & W3K is bringing everything to a head. Thanks again.

 

[ehx6]

Hi
I hope you can help.
I am getting similar issue . here are my sql server configuration
1. I logged to window server 2003 as administrator
2. Registered sql server as window account( window authonication)
3. Under sql server property security, I got mix authonication( window , sql server)

so, I created DTS to import data into a table. so when I use window authonication, it works, however, when I use sql authonication, I get error, Login Failed, for user sa: Reason: Not associated with a trusted sql server connection.

Note. I am confused, because I am using mix mode. and I am using sa account. Why would am I getting login faile error. Any ideas?
thanks for help
Ehx

 

[miq]

Hi,
make sure sqlserver and sqlagent service has same domain login account with same permissions as the windows login account has which you used sucessfully with DTS package. or simply assign doman administrator pemissions.

This may help. I think!

B.R,
miq

 

[Sweaver72023]

Set your SQL Agent to start with the Windows Domain account. In the SQL Enterprise Manager under Management right click the SQL Server Agent and select properties. Goto the Connections tab and set the SQL Server connection to Use SQL Server Authentication and provide the credentials.

 

[SQLBill]

The SA account CAN NEVER BE USED FOR WINDOWS AUTHENTICATION. PERIOD.

If you are going to use the SA account (always a bad thing) you must use SQL Server authentication.

-SQLBill

Posting advice: FAQ481-4875