Login Failed

Question

Login Failed

Roy-262336

Grasshopper

Points: 20
More actions
December 14, 2005 at 6:14 am

#96072

When I run this query from Query Analyzer when pointing to one server
SELECT TOP 10 *
FROM [OtherServer].[MyDataBase].[dbo].[MyTable]
I get the following error message
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
On the target SQL Server, Authentication is set to SQL Server and Windows.
I can use Query Analyzer to access the other server directly. The reason I am doing this is to test a query which I would like to include in a Stored Procedure that will access data from the other server.
Searching the net has not produced any real help.
Any help would be appreciated.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

David le Quesne SSCertifiable Points: 5229 More actions · Answer 1

When you access the other server directly from QA, are you using Windows authentication or a SQL Server login and password?

If you use a login and password to access the original server through QA, then when you try and access the other server using the query, I think it will use Windows authentication by default.

David

If it ain't broke, don't fix it...

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 2

You're running into a double-hop issue. You're doing Windows authentication to connect to the first SQL Server. You're also got the linked server connection to do the same to the second, right? Double-hop, without additional configuration, is prohibited.

If you're on an NT 4.0 domain, there aren't any other options. One of the two connections will have to be SQL Server login based. However, if you're on an Active Directory domain, delegation can be set up. Get with your Windows administrators because to do so involves some security questions as delegation has to be set up on the first server and that SQL Server's service account has to be configured to be allowed to delegate.

K. Brian Kelley
@kbriankelley

Roy-262336 Grasshopper Points: 20 More actions · Answer 3

Would you be able to give some pointers as to how to set up the delegation? Our production environment is active directory, and we also have a separate test/development environment that I can use to play with. I don't think our windows administrators here (in the sticks) have done anything like this before.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 4

In SQL Server 2000's Books Online there is a section entitled "Security Account Delegation." It tells you all you need to know.

You can find it at Administering SQL Server >> Managing Security >> Security Levels >> Security Account Delegation.

K. Brian Kelley
@kbriankelley

Roy-262336 Grasshopper Points: 20 More actions · Answer 5

Brian, thanks for the pointers! I will read up on it.

I have in the mean time I tried a different tack using linked servers.

Executing the following

EXEC sp_addlinkedserver

@server=LINKEDSERVER,

@srvproduct = 'SQLServer OLEDB Provider',

@provider = 'SQLOLEDB',

@datasrc = 'REMOTE_SERVER_NAME'

EXEC sp_addlinkedsrvlogin

@rmtsrvname='LINKEDSERVER',

@useself='false',

@rmtuser='UserName',

@rmtpassword='UsersPassword'

I now get the error "Login failed for user 'UserName'."

I have checked that this user is set up in the "Security" of the remote server and has access to the given database.

Any clues as what to look for here?

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 6

That should be sufficient. Using QA, are you able to connect to the remote server? It may fail if the remote server is set up for Windows authentication only.

K. Brian Kelley
@kbriankelley

Roy-262336 Grasshopper Points: 20 More actions · Answer 7

Using QA I can run queries to the remote server, The remote server is set for both types of authentication.