locked
SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has be RRS feed

  • Question

  • Hello, I have a sql 2005 server, and I am a developer, with the database on my own machine.  It alwayws works for me but after some minutes the other developer cant work in the application

    He got this error

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

    and When I see the log event after that error, it comes with another error.

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

    He has IIS5 and me too.

    I created a user on the domain called ASPSYS with password, then in the IIS on anonymous authentication I put that user with that password, and it works, on both machines.

     

    and in the connection string I have.

    <add key="sqlconn" value="Data Source=ESTACION15;Initial Catalog=GescomDefinitiva;Integrated Security=SSPI; Trusted_Connection=true"/>

    I go to the profiler, and I see that when he browses a page, the database is accesed with user ASPSYS, but when I browse a page, the database is accesed with user SE\levalencia.

    Thats strange.

    The only way that the other developer can work again on the project is to restart the whole machine. He has windows xp profession, I have windows 2000.

    If you want me to send logs please tellme

     

     

    Tuesday, March 7, 2006 8:26 PM

Answers

All replies

  • Another strange thing is that after some minutes he gots another different error.

    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 192.168.1.140]

     

     

    Tuesday, March 7, 2006 8:27 PM
  • Luis,

     This is a IIS authentication configuration issue.

     Here is a KB about it.

    http://support.microsoft.com/kb/247931/en-us.

    You probably want to check IIS forum or Asp forum on this.

    Tuesday, March 7, 2006 11:02 PM
  • Tuesday, March 7, 2006 11:13 PM
  • Hi

    I'm experiencing the same problem you mentioned above with my Sharepoint site.  Did you ever find out what was causing the problem?

     

    Thanks

    Monday, November 5, 2007 10:56 AM
  • Use setspn to delete the following SQL servicePrincipalNames from the server and add to your SQL service account.

     

    MSSQLSvc/hostname:1433
    MSSQLSvc/FQDN hostname:1433

     

     

    Friday, July 18, 2008 7:53 PM
  • I had the same issue on my servers

    After a bit of investigation I found that we have multiple SPN account registered per computer.

    I used the SETSPN tool and found that the SSPN is registered on the Computer class. But I found the spn query script of the Microsoft website http://www.microsoft.com/technet/scriptcenter/solutions/spnquery.mspx.

    With this script we found that the SPN was registered to the user class as well as registered to different computer class accounts.

    See if you your SPN's are registered more than once and if so delete them and have them registered only to the computer class.

    are you running kerberos or NTLM authentication on your SQL servers?



    select auth_scheme from sys.dm_exec_connections where session_id=@@spid



    • Proposed as answer by cornasdf Tuesday, May 8, 2012 1:46 PM
    • Unproposed as answer by cornasdf Tuesday, May 8, 2012 1:46 PM
    Monday, July 28, 2008 2:28 PM
  • kevinda said:

    Use setspn to delete the following SQL servicePrincipalNames from the server and add to your SQL service account.

     

    MSSQLSvc/hostname:1433
    MSSQLSvc/FQDN hostname:1433

     

     



     I've used setspn to delete them from the server but how do I add them to the SQL service account?
    Wednesday, January 14, 2009 3:25 PM
  • Depends on whether your SQL Server service account has the priviledge to register SPN, SQL Server will automatically register the SPN when you restart SQL Server. Usually, Local System and Network System have the priviledge, and your own domain account does not.
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, January 14, 2009 7:45 PM
  • Well here's my problem, maybe you can help. Intermittenly I get a login failed when connecting to a db engine through Server Management Studio using Windows authentication. When this happens the following entries are generated on the server's application event log:

    Event Type:        Error

    Event Source:    MSSQLSERVER

    Event Category:                (4)

    Event ID:              17806

    Date:                     1/14/2009

    Time:                     10:41:31 AM

    User:                     N/A

    Computer:          <server name>

    Description:

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

     

    Event Type:        Failure Audit

    Event Source:    MSSQLSERVER

    Event Category:                (4)

    Event ID:              18452

    Date:                     1/14/2009

    Time:                     10:41:31 AM

    User:                     N/A

    Computer:          <server name>

    Description:

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

    I've already ensured that the server is set to mixed authentication mode. Oddly enough, the workaround that I've found is that if I remote desktop into the server, log in and then log back out, Management Studio is suddenly able to connect again. No idea why it works. 

    As I said before, it is intermitten. Some days it errors on login, other days it doesn't and there are no configuration changes between them. Also, both client and server are in the same domain and same site so there is no VPN or anything in between. I'm really quite stumped. Any help would be great, or if you can point me in the right direction of where to look. Thank you in advance!
    Thursday, January 15, 2009 8:06 PM
  • As I'm reading around, here's other information that may help: I haven't changed any of the hostnames recently. I switched the default network protocol from tcp/ip to named pipes and the login error still occurs (have since switched back to tcp/ip). I queried the sys.dm_exec_connections DMV and it returned "NTLM" instead of "KERBEROS". The Domain Controller with AD and KDC are in the same domain and site. The sql server instance service is running under the local system account. The server's FQDN resolves so there's no issues with DNS. Other forums say that the fact that its a null user error in the event log seems to indicate a SPN issue so I'm looking more into that.

    Thursday, January 15, 2009 9:51 PM
  • Hello,

    Sorry for delay in response, But I got the same issue and It seems you, this is not with SQL Server, It cause because the Windows AD group or your computer/Client password changed recently.

    I would suggest, Please restart your client machine and try it, It work for me Hopefully it should work for you also.

    AS per the windows Server policy user's account will expire and when we changed the password it takes affect in delays (might be domain is different or Windows replication delay).


    Manu
    Tuesday, June 30, 2009 9:08 PM
  • Wybenga,

    I have been having the same issue with a 2005 (SP3) instance running on a remote Win2k3 machine. I was actually giving your fix a try (i.e., logging in with terminal services) when I realized my account had locked out (not sure if it was from the login attempts thru SSMS). I had my domain account unlocked, and I was able to login again. Just like you stated it is intermitten, and I receive the same error in the event log.

    Frustrated,
    Adam
    Friday, July 24, 2009 5:39 PM
  • I had this error during setup of SQL2008 recently.  Had to set SQL account to Local System (that auto-registered the SPN's) and in addition had to map server name to FQDN in an Alias in SQLConfiguration manager as follows:

    Alias Name:TOM2, port 1433, prot:tcp/ip, Server: TOM2.mydomain.com

    Hope this helps!


    AS
    Friday, April 16, 2010 3:39 PM
  • I had the same error when trying to access a SQL 2005 server when using a CNAME.

    In the end I used the suggestion from "mrTwister" to fix my problem. Adding an Alias to both the "SQL Native Client Configuration" and the (32bit) one.

     

    Example Settings:

    Alias Name: CNAME being accessed

    Port: 1433

    Protocol: tcp

    Server: FQDN of the actual server running SQL.

     

    Wednesday, May 19, 2010 9:09 PM
  • Hi,

    Old thread, but I also had this issue recently.

    I was trying to run the SharePoint Configuration Wizard on my Project Server installation, but it couldn't display a list of configuration databases.  I was getting the SSPI Handshake errors in my SQL Server Application event log.  I then tried a lot of the suggestions here with no success.

    I managed to resolve the issue by removing the project server from the domain and then rejoining it again.

    Hope this helps someone.

    Monday, March 28, 2011 1:10 PM
  • Hello Pridmorej,

    I am having the same issue as all others explained above, but cannot fix it as of yet.

    Can you please explain in detail exactly how you "remove the project server from the domain then rejoin it again."  I have absolutely no idea what you mean by this, nor what a "project server" is.

     

    Thank you so much!

    Bjorgen T. Eatinger

    Monday, April 11, 2011 3:07 AM
  • Here is the IIS forum.

    http://forums.asp.net/169/ShowForum.aspx


    this site is not available, anyone can give advice.?
    Thursday, July 21, 2011 12:42 PM
  • Not helpful when the link has been taken down.

    thanks microsoft

    Wednesday, June 26, 2013 10:05 PM
  • Follow the steps in http://mssqlwiki.com/2013/12/09/sql-server-connectivity-kerberos-authentication-and-sql-server-spn-service-principal-name-for-sql-server/ to resolve this error.

    Thank you,

    Karthick P.K |My blogs|My Scribbles|Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Monday, January 13, 2014 3:09 PM