January 26, 2009 at 2:40 am
Hi,
I have a strange problem !!!
I have one user using SSMS 2005 express who cannot login to one of our SQL servers with his Windows credentials...
The scenerios -
1) login using windows authentications
2) Cross domains account (trust relationship setup correctly)
3) this user used to have account in the trusted domain but was deleted...have double check that this account does not exist in the domain that the SQL server resides in which he has problem login to.
4) He can logon to another SQL server using the same credentials in the same domain of the other SQL server.
5) from his workstation other users' credentials work
6) from other workstation, his credentials does not work..
The work around at the moment is to give him a specific ID and password (which is not elegant !!) in order for him to do his work...
Any help is appreciated...
January 26, 2009 at 2:49 am
Does the user has a login defined in the SQL Server? If he doesn't your have to create one using create login statement.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 26, 2009 at 2:53 am
Yes he does...
The thing is we have two servers setup, which are identical in SQL server terms...
One he can login, one he cannot...using the same credentials...
January 26, 2009 at 3:04 am
Check if the user's Windows account has administrator privilege in the server in which he is not able to login into SQL. He might have administrator privilege in the other server where he is able to login into SQL.
This is because, by default SQL allows members of the group BUILTIN\Administrators (all admins in the server) to access the databases.
January 26, 2009 at 3:10 am
Thanks for the replies 🙂
He has no "BUILTIN\Administrators" rights only DBO access to the database he is working on, also he is listed under "login security" of both SQL servers...
I tried to add him to local Administrators on the server that he cannot login...no luck...:-(
January 27, 2009 at 7:45 am
Three things to check that come immediately to mind:
1) Is his Windows login disabled?
2) Is his Windows login denied access to connect?
3) Are any of the Windows groups he is a member of denied access to connect?
These two queries will both the disabled and denied connect:
SELECT name
FROM sys.server_principals
WHERE is_disabled = 1
SELECT SUSER_NAME(grantee_principal_id) FROM sys.server_permissions
WHERE [type] = 'COSQ'
AND state = 'D'
K. Brian Kelley
@kbriankelley
January 28, 2009 at 6:06 am
The thing is we have two identical machines on one domain - one for testing and onw for production. He is using the same credentials (another domain) and he can log onto the testing machine and not able to logon to production machine.
His credentials is domainAAA\thomasa, accessing domainXXX where the two machines reside. There is two way trusts between the two domains. The security settings on the two SQL servers are equal in everyway....
January 29, 2009 at 10:25 am
Is the Windows firewall on from the other machine when he logs on?
K. Brian Kelley
@kbriankelley
January 30, 2009 at 6:20 am
No firewall problems...
Other users who have the same rights as him can login using his workstation. When he tries his credentials on the users' workstation who has access, he cannot login !!!
January 30, 2009 at 4:48 pm
When he attempts to login from the workstation where his account is failing, do you see a failed login attempt on the SQL Server corresponding to the attempt? If so, what error, if any, is given in the OS' security event log?
K. Brian Kelley
@kbriankelley
February 2, 2009 at 12:53 am
As said only him that cannot login via SSMS, he has tried 3 different workstations where other people can login to the same SQL server using the same SSMS....
Had Tried both Mixed mode and Windows only mode (due to company policies we use only Windows Authentications mode)
----------------------------------------------------------------------------------------------------------------
TITLE: Connect to Server
------------------------------
Cannot connect to 192.168.xxx.yyy.
------------------------------
ADDITIONAL INFORMATION:
Login failed for user ''. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18452&LinkId=20476
------------------------------
Event log
Log Name: Application
Source: MSSQLSERVER
Date: 10.12.2008 13:02:22
Event ID: 17806
Task Category: (4)
Level: Error
Keywords: Classic
User: N/A
Computer: XXX.YYY.ZZZ.net
Description:
SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 10.xx.yy.zz]
Event Xml:
February 2, 2009 at 6:59 am
This is the application event log, but the error given is for invalid credentials. Do you see an error in the Security event log? If so, what does it say?
K. Brian Kelley
@kbriankelley
February 3, 2009 at 7:23 am
An account failed to log on.
Subject:
Security ID:NULL SID
Account Name:-
Account Domain:-
Logon ID:0x0
Logon Type:3
Account For Which Logon Failed:
Security ID:NULL SID
Account Name:henningk
Account Domain:INT
Failure Information:
Failure Reason:Unknown user name or bad password.
Status:0xc000006d
Sub Status:0xc0000064
Process Information:
Caller Process ID:0x0
Caller Process Name:-
Network Information:
Workstation Name:HENNINGK
Source Network Address:-
Source Port:-
Detailed Authentication Information:
Logon Process:NtLmSsp
Authentication Package:NTLM
Transited Services:-
Package Name (NTLM only):-
Key Length:0
This event is generated when a logon request fails. It is generated on the computer where access was attempted.
The Subject fields indicate the account on the local system which requested the logon. This is most commonly a service such as the Server service, or a local process such as Winlogon.exe or Services.exe.
The Logon Type field indicates the kind of logon that was requested. The most common types are 2 (interactive) and 3 (network).
The Process Information fields indicate which account and process on the system requested the logon.
The Network Information fields indicate where a remote logon request originated. Workstation name is not always available and may be left blank in some cases.
The authentication information fields provide detailed information about this specific logon request.
- Transited services indicate which intermediate services have participated in this logon request.
- Package name indicates which sub-protocol was used among the NTLM protocols.
- Key length indicates the length of the generated session key. This will be 0 if no session key was requested.
September 16, 2009 at 9:42 am
the same problem my friend..
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply