April 6, 2004 at 2:17 pm
I setup a linked server on an instance with Windows Only authority and it works fine. When I change the authentication to SQL Server and Windows it fails with the error 18452: Login failed for user "(null)'. Reason: Not associated with a trusted SQL Server connection.
It seems to want me use a SQL Login. I want to use my windows id.
Any ideas on how to get this to work?
David Bird
April 7, 2004 at 8:32 am
Were you running the query from the source server (ie the server where linked server is setup) ? If you are running from a client then delegation needs to be setup for the windows authentication to be carried to the linked servers. Just to verify this run the query from the server (ie EM on the server ).
April 7, 2004 at 2:08 pm
I get the error when I run it from the local copy of developer edition and when I am connected to servers with linked servers.
David Bird
April 7, 2004 at 8:04 pm
Let me clarify:
If you have servers S1 and S2. Let us say S2 is defined as a linked server on S1. I am assuming you have selected the windows user ID with permissions on both the servers to be allowed to connect to server S2 in the linked server configuration. Now if you are on the server S1 itself and use any tools (SQL EM, QA) you should connect to S2 without any issues. How ever if you use a workstation W1, connect to Server S1, and then use linked server to S2, that would not work. Since the windows credentials from Workstation W1 cannotbe transferred to S1 nd then to S2. To configure this you must configure delegation using SPN (service principl names). Read onthe books online on how to set this up.
April 8, 2004 at 6:17 am
I agree it should work.
I am logged onto S1. When S1 is setup for Windows Only authority, I can access tables on S2. But when S1 is setup with SQL and Windows (Mix Mode), I cannot access S2. It seems like a bug.
David Bird
May 4, 2004 at 9:07 pm
In S1 I added s2 as linked server. There is no third machine involved.
I tried:
i) Mixed mode authentication
Error: sql doesn't exist or accessd denied.
ii) Windows authentication.
I checked (radio button) in Linked Server-S2 "Security" tab
a) "Be made without using security context"
Error: OLE DB Provider reported an error, Invalid authorization
b) "Be made using the login current security context"
Error: sql doesn't exist or accessd denied.
I couldn't do distributed quiery at all in any mode. Please help.
When I tried to call a SP in S2 from within S1 it gave me the following error.
Error: S2 is not configured for RPC
[ I did have RPC checked in Server Option tab in Linked Server-S2]
BTW: What is the difference between remote server and linked server?
May 5, 2004 at 9:51 am
Munzer:
AFAIK, Remote server is used to run remote stored procedures only and these are only for backward compatibility. Linked servers are a superset of remote servers and can do remote procedures and a "lot more", like direct data operations on remote servers, besides support for servers other than just SQL servers.
In your case, Are any of the servers involved here are clusters? If they are, there seems to be an issue with using named pipes between server s1 and s2 and only tcp/ip sockets seems to work. Now if they are not clusters, First make sure you can register server s2 on server s1. Assuming you can register, under mixed mode, you can create an account on both machines with same password and use the impersonate option on the security tab. Now if you login to the server s1 with the account you just created, you should be able to quest server s2. If you are using windows authentication, the same thing would work, with the exception that the account you created would be a windows account. Bear in mind that in this case (windows auth), if you were to use another workstation and connect to s1 (using QA or EM) and then run a remote query against s2 that would fail, by design. BTW, you are selecting "SQL Server" in the general tab, right?
May 5, 2004 at 10:04 am
The linked server is a cluster. I thought we where using TCP/IP but I will need to verify. I have no problem registering any of the servers.
We are using the linked server to retrieve data only.
David Bird
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply