SQL Server 2005: Login failed for user ''

Posted on 2006-05-30
Last Modified: 2011-10-03
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...
Question by:cip
    LVL 1

    Author Comment

    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'

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


    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.

    LVL 16

    Expert Comment

    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.

    LVL 3

    Expert Comment

    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.
    LVL 1

    Author Comment

    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?


    - 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.
    LVL 3

    Accepted Solution

    Shared memory is local to the computer and it generally does work. To create a named pipe protocol, you need to do the following

    Go to SQL Server Configuration manager->SQL native client configuration and under client protocols make sure named pipe is enabled.
    Then go to Aliasses Tab->Right Click on it and select New Alias. In the Alias window, choose Named Pipes under the protocol option, provide the name of the machine(On which SQL is installed) on both "Alias and Server" box.

    Open the SQL mamagement studio, In the connection box under server name type "NP:Servername" and try to connect.
    To test TCP/IP connection you can do so by typing "TCP:Servername, Portnumber" (Do not include the double quotes)

    This is more of a login failure problem for the windows account and not a TCP/IP issue. 0x8009030c resolves to ( -2146893044) which means "The logon attempt failed".

    Create a new windows user and give it local admin rights.
    Login to the machine using that windows user account.
    Login using SA into the Query analyzer (You can use named pipes or LPC).
    Add a newly created Windows user under security context of the SQL.
    Then exit the query analyzer and use windows account to connect using TCP/IP. (technically it will attempt the login using the new windows account)
    Get back to us with the results.
    LVL 1

    Author Comment

    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:



    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.

    LVL 1

    Author Comment

    Correction, these groups:


    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.

    LVL 3

    Expert Comment

    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.
    LVL 1

    Author Comment

    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?
    LVL 1

    Author Comment

    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.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    In today’s complex data management environments, it is not unusual for UNIX servers to be dedicated to a particular department, purpose, or database.  As a result, a SAS® data analyst often works with multiple servers, each with its own data storage…
    I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now