none
Windows Authentication - Cannot generate SSPI context - Login failed for user ''. The user is not associated with a trusted SQL

    Question

  •  

    We are looking at developing an SQL Server 2005 Database and I would like to use Windows Authentication rather than SQL Server Authentication to connect our client app.

     

    In our development environment, we have two Servers, one being used as a file server and the other as an SQL Server. We have now set up a domain using the file server as the domain controller. (We had previously been set up to use a workgroup).

     

    I have set up an active directory group called SqlDevelopers and added an active directory user called Jonathan to it.

     

    On the SQL Sever, in management studio, I have set up a new server login which uses windows authentication called Domain\SqlDevelopers. I used the GUI to verify I could see the domain and the group.

     

    The default database is set to a test database on the server. A user in the test database is mapped to the Domain\SqlDevelopers and given the Roles dbo, db_datareader, db_datawriter.

     

    To test the log in, on the server, I logged out as administrator and in as Jonathan. I could successfully access the server through management studio using windows authentication.

     

    However, if I log in as Jonathan on my client PC and try to access the SQL Server using management studio and windows authentication, I have problems.

     

    The first time I try I will get a timeout error. If I try again will get either:

     

    Login failed for user ''. The user is not associated with a trusted SQL Server connection

     

    Or

     

    Cannot generate SSPI context

     

    I can’t determine any pattern to which of the above errors I get.

     

    However, if I log in as administrator on my client PC, I can connect to the server using management studio and windows authentication.

     

    Sounds like Active Directory/Domain or other Network issue (Not really my area). I would be grateful for any help.

     

    Thanks,

     

    Jon

     

    Tuesday, May 29, 2007 3:23 PM

Answers

  • Go to the BOL search pane and search for constrained delegation, it will take you to a technet page that includes all you need and the restrictions. What you are getting is called double hop error.  If an admin can create a pass through account it wiil do the same thing.  Hope this helps.

     

    Wednesday, May 30, 2007 5:16 PM

All replies

  • You skipped adding the user to SQL Server on the server level so SQL Server does not know the person exist, what I mean is in Windows authentication you have to add the user's domain account to SQL Server and then the database or you will get the above error.  So go to the security section in Management Studio and add user's Windows account as a SQL Server login.  Hope this helps.
    Tuesday, May 29, 2007 3:43 PM
  •  

    Hi, thanks for the reply.

     

    Does this mean i can't have one server login that is mapped to a group,

    i have to have a server log in for every single user?

     

    Cheers,

     

    Jon

     

    Tuesday, May 29, 2007 4:02 PM
  •  

    I added a server log in for domain\user rather than domain\group and mapped it to a database user.

     

    I still get Cannot Generate SSPI context.

     

    Jon

    Tuesday, May 29, 2007 4:10 PM
  • What you are talking about will break DCL(data control language) rules, the only way you can map many users to one account in SQL Server is if all use that account to login to SQL Server because in DCL access to server is not access to database access to database is not access to tables and objects.  Now if you want to generate context you need a system admin who can create pass through authentication for you. What I am talking about is tedious but simple because there is no group concept in RDBMS.  Hope this helps.
    Tuesday, May 29, 2007 4:24 PM
  •   I would recommend trying the recommendations located on the following KB:

    * How to troubleshoot the "Cannot generate SSPI context" error message: http://support.microsoft.com/kb/811889

     

      Let us know if you have any additional questions.

     

       Thanks,

     -Raul Garcia

      SDE/T

      SQL Server Engine

    Tuesday, May 29, 2007 4:52 PM
  •  

    I typed in the UNC path to the sql server in explorer, this prompted me for a password, so I used the server's administrator password.

     

    I then went into management studio and i was able to connect using windows authentication.

     

    Then about an hour or so later I tried to save a database diagram, which resulted in 'cannot generate SSPI context'.

     

    This has got to be something (probably basic setup) to do with the domain, server, active directory permissions etc.

     

    Sadly we have no in house networking people as we are all developers, we guessed our way though setting up the domain.

    Wednesday, May 30, 2007 3:30 PM
  •  

    Ps. If i log off and log back on, i can get back into management studio using windows authentication but its only a matter of time until i get the SSPI error again. 

    Wednesday, May 30, 2007 3:39 PM
  • Go to the BOL search pane and search for constrained delegation, it will take you to a technet page that includes all you need and the restrictions. What you are getting is called double hop error.  If an admin can create a pass through account it wiil do the same thing.  Hope this helps.

     

    Wednesday, May 30, 2007 5:16 PM