Re: Trusted SQL Server connection (SQL 2005)

idoek (idoek@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
At first I thought your answer is correct, but then I logged into a
Windows machine where I know the administrator cannot access the local
SQL Server 2005 using Windows Authentication.

(When I try to login to the SQL Server 2005 I get the following error:
"Login failed for user ''. The user is not associated with a trusted SQL
Server connection. (.Net SqlClient Data Provider)")

I don't think that message means that your Windows login does not have
access to SQL Server. In such case the error message should have listed
the name of the Windows login.

When the login name is blank, this indicates that SQL Server cannot even
get hold of the name of the Windows login.

To illustrate this, here is what I tried at work. First I connected to our
development server, and I got:

Msg 18456, Level 14, State 1, Server CRUSH, Line 1
Login failed for user 'EU\sommaerl'.

(Of old habits everyone uses sa for development). Then I tried to connect to
a production server at a customer site:

HResult 0x4814, Level 14, State 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
HResult 0x2746, Level 16, State 1
TCP Provider: An existing connection was forcibly closed by the remote
host.

This is a completely different domain, and there is no trust between our
domain and theirs.

Now, what is funny in your case is that this appear to happen with a
Windows login on the local machine. Why this happens I have very little
idea. All I can say that looking at users and permissions in SQL Server
is not going to help you. This is a problem with something outside SQL
Server, or SQL Server's interaction with the outside.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: login 101..
    ... On Windows 2003, SQL Server 2005 can enforce the Windows password complexity ... Windows authentication - SQL Server uses a special protocol to ask ... user is in the list of allowed logins, ...
    (microsoft.public.sqlserver.security)
  • RE: How to create a trusted connection
    ... You need to grant access for the Windows login by referring to the books ... is set to use Windows authentication to be able to do trusted connection. ... There are two modes of authentication in SQL Server: ...
    (microsoft.public.sqlserver.security)
  • RE: How to create a trusted connection
    ... What do you mean by "creating a Windows login"? ... To login SQL Server 2000 using trusted connection, grant the Windows ... Group" in SQL Server Books Online to get the concept, ...
    (microsoft.public.sqlserver.security)
  • Re: Connecting to SQL from a Windows Service
    ... There is probably some way to debug this, but that's deeper Windows knowledge than I can dredge up right now. ... I looked into the Event Log and found that when my service ... 1st Login succeeds. ... You can try turning on login auditing in SQL Server and then check the ...
    (microsoft.public.sqlserver.connect)
  • Re: Trusted SQL Server connection (SQL 2005)
    ... To be able to login to SQL Server using a Windows account, then a login must be created in SQL Server Security\Login for that account or for a Windows group which consists that Windows account. ... Create login for the Windows group who you want them to be able to login to your SQL Server and assign them necessary permissions for your database objects or whatever. ...
    (microsoft.public.sqlserver.security)