Link to home
Start Free TrialLog in
Avatar of cip
cip

asked on

SQL Server 2005: Login failed for user ''

We are really getting crazy for this:

Suddenly when trying to connect to Management Studio with trusted connection, our db server is returning this error :

Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: X.X.X.X]

In the sql server log and in the event viewer this error is always associated with this one:

SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: X.X.X.X]

We don't own the sa password (the guy who installed the server says he didn't input any password for sa, is that possible?), blank password doesn't work.

We have access to the phisical computer with an administrator account. The very same administrator account has been used to access SQL using trusted connection until friday and now suddenly is returning this error, apparently with no logical reason.

I have been trying in any possible way using a remote connection with terminal service:

1. tried connecting with oSql -E -S <servername> (same result)
2. tried to add the administrator account to every possible SQL SERVER group on the machine
3. tried connecting using SQL server authentication using the administrator username e password

The computer is not even in a domain, it's the only machine in a workgroup.

Any suggestion to find a way out other than spending at least 99 euros to open a microsoft incident with technical support is welcome...
Avatar of cip
cip

ASKER

Ok news are that I was able to enter using this connection string:

oSql -S lpc:<SERVERNAME> -E

That allowed me to connect to sql server bypassing the TCP/IP protocol, I used shared memory instead.

Once I could connect I was able to change the sa password with:

1>SP_PASSWORD NULL , 'newpassword','sa'
2>GO

and then, since I found out the sa account was disabled:

1>ALTER LOGIN sa ENABLE
2>GO

This allow me now to connect to management studio with the sa account, but still I don't know how to the trusted connection back to work. At least I'm out of emergency.

Check the following

1) Are Remote connections not enabled on the SQL server?
2) Is the machine a member of the domain
3) Check the syntax in connection string. A space after server name in the connection string can cause this error.

Create a Named Pipe Alias for the SQL Server and see if it connects. If it does then you have issues with service principal name.
Go to Services and check the if start up for SQL Server has been changed. If it has been changed, You need to stop and restart SQL Server.
If you still get the same error, then you need to query the AD for any duplicate SPN's.

Open SQL Server 2000 Books Online and look for a topic that says "Security Account Delegation". This explains how to add and delete SPN's.


Also open AD User & Computers on Trusting domain, go to the security properties of the resource in question - Computer account. Add the user/group from the Trusted domain, and in addition to Read, check the box to Allow: "Allowed to Authenticate". This will give that user account the ability to access the shared resources across the trust.


Lastly go to start ->Run- Type regedit

Navigate to the following key

HKLM->Software->Microsoft->Microsoft SQL Server->MSSQL(your SQL instance)->MSSQLSERVER

On the right pane of the registry you will find a value called "LOGINMODE". If it has a value data of '1' then you have windows authentication.
If you have the value data "2", you have mixed mode authentication in which case you may be typing the Wrong "SA" Password. You can reset the SA password without having to know the current password.
You can also change the Login Mode data to '1' and this will revert the SQL back to windows authentication only.
Avatar of cip

ASKER

MohanKNair, thanks for you help:

- Remote connections are enabled
- The machine is not member of any domain, as I previously said it's the only machine in its workgroup
- With trusted connection I don't have to type neither the username nor the password, where do I check syntax, in control panel's computer management, users and groups?

lokeshgm7:

- How do I create a named pipe? Anyway, I think I was already able to reproduce your test by connecting to sql server through shared memory using trusted connection, and that worked. I think that this means the problem is somehow related to TCP/IP.

- The computer is NOT on a domain, it's in a workgroup, I don't even have AD enabled, the AD Users & Computers on Trusting domain option doesn't even show up in Control Panel -> Administrative tools.

- As I have written I was also able to restore the sa account by connecting through shared memory so now I have administrator access.

- This also means that I can check the loginmode without using regedit, I simply open the management studio and open the sql instance properties window, which shows a mixed mode authentication.

lokeshgm7, I'm not a dba, I mainly take care of development processes so please be patient when explaining.
Thanks for your help until now tho.
ASKER CERTIFIED SOLUTION
Avatar of lokeshgm7
lokeshgm7

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of cip

ASKER

Ok, I think I'm getting closer to it:

I created a named pipe, and successfully connected. Then I created a test user, and added it to the sql server security context, tried to connect with that user and it worked.

As I was getting curious I checked if the administrator account was listed into the sql server security context accounts but it wasn't, so I added it and I now have the administrator login with trusted connection working again.

This puzzles me a little, how can it be possible I was able to connect with trusted connection until friday then suddenly nothing?

Looking under the security tabs I have the accounts my applications use to connect to the db and then:

<computername>\SQLServer2005SQLAgentUser$<computername>$MSSQLSERVER
<computername>\SQLServer2005MSSQLUser$<computername>$MSSQLSERVER
<computername>\SQLServer2005SQLAgentUser$<computername>$MSSQLSERVER
BUILTIN\Administrators

NT AUTHORITY\SYSTEM

The first group of four accounts is shown with a group graphical icon, the other is shown with a user graphical icon. Those accounts do not match with any account or group in windows.

Thanks to lokeshgm7 I was able to bring back administrator to trusted connection, now the main issue is to understand what has happened.

For example, on my local sql server 2005 installation, I can login with a windows administrator account and with that account do a sql server trusted connection, but I definitely DON'T have to add that account under the sql server security context to have it working.







Avatar of cip

ASKER

Correction, these groups:

<computername>\SQLServer2005SQLAgentUser$<computername>$MSSQLSERVER
<computername>\SQLServer2005MSSQLUser$<computername>$MSSQLSERVER
<computername>\SQLServer2005SQLAgentUser$<computername>$MSSQLSERVER

do match with groups defined in windows, and the administrator account is member of all of them, has always been, I remember I checked this issue too.

Administrator however is also a member of the local administrators windows group.


If there is a windows user account (including administrator by default) that is listed in the Local Admins group, we need to ensure that the builtin\administratos is a part of the SQL Security Logins. if they are not, you need to ensure all windows users are listed individually in the SQL Security.
Avatar of cip

ASKER

lokeshgm7, how do I make sure the buildin\administrators is part of the SQL Security Logins?

I see there's a local windows "administrators" group (o.s.), and the "administrator" account is member of it. As far as I know, the builtin/administrators account should map directly to that group, is that true?

But if I remove the individual administrator account from the SQL Server "Security/Logins" folder, I cannot login to sql anymore. So it seems that the local windows administrators group doesn't map to the BUILTIN/administrators account in sql server anymore. Can it be possible?

The buildin/administrators account is listed under the "Security\Logins" folder as well. In the "properties" dialog, I see the account has permission granted to connect to the database engine e login enabled.

What other tests should I perform?
Avatar of cip

ASKER

I have awarded the points to lokeshgm7 since he was the one who best helped me get me out of emergency. However I gave a C because I'm far away from understanding what happened, nor I have any idea of the tests needed to find out, and the situation on my db server is not perfectly clear, I had to manually add the administrator account to the sql server security context, which is not a clean solution (with trusted connection it should work without the need to add that account). I didn't want to leave the points unawarded though, since the comments did help me. Hope this is fine for all.