Re: Distributed Query - Linked Servers?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hello Uri,

Hmm, do you try to connect as a 'sa'?

The account I'm using is Windows Domain Account that is a member of the sysadmin server role on both servers. I can run queries against both servers from within MSSMS, but I can't run a distributed query. The text in BOL suggests it's not possible to use a Windows Account to run a distributed query unless you have enabled delegation?

Perhaps after restore the test database user are not maped to login ?

Yes, but when you create a linked server in MSSMS, my understanding is that the "Test Connection" facility is not db specific. I believe it's "per server". The "Test Connection" facility is failing. I don't even get to try a distributed query.

It is pretty easy to create a linked server and tranfer the data, also make sure that the server has access to set true

See if that works
EXEC sp_serveroption TestServer, 'data access' , 'true'

I just looked at the script dump of the linked server and it says the 'data access' option is set to 'true'. It does raise an interesting point though; am I supposed to create the link on BOTH servers, or only Server1? I did try linking each server to the other but it still failed.

Here's a summary and error message I get.

1. Log into client PC with domain admin account and start MSSMS
2. Connect to Server1 and Server2 - they appear in left pane
3. On Server1, "Linked Servers", "New Linked Server"
4. Choose server name "Server2" and radio button "SQL Server"
5. Click OK

I get an error

The OLE DB provider "SQLNCLI" for linked server "Server2" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "Server2".
OLE DB provider "SQLNCLI" for linked server "Server2" returned message "Invalid authorization specification". (.Net SqlClient Data Provider)

--
Gerry Hickman
London (UK)

.

Relevant Pages

  • Re: Perflib errors in event logs after SP2 installation
    ... the server does seem to be working fine. ... System account is a potential security risk. ... Policy provider runs under the Local System account, ... Microsoft CSS Online Newsgroup Support ...
    (microsoft.public.windows.server.sbs)
  • Re: Cannot start services
    ... The solution in my case....once I got the server back in the domain I copied ... see Help and Support Center at ... | A provider, PerfProv, has been registered in the WMI namespace, ... | will be run using the LocalSystem account. ...
    (microsoft.public.windows.server.general)
  • RE: Event 63 Warning
    ... A provider, OffProv12, has been registered in the WMI namespace, ... to use the LocalSystem account. ... From what little I've found refers to a Server. ... This happen if a security updates been applied and the WMI is up and running ...
    (microsoft.public.windowsxp.general)
  • repeated failure of store - securty hack?
    ... I have a problem with a sbs 2003 server runnig fine since commissioning ... This problem is likely due to faulty hardware. ... This provider will be run using the LocalSystem ... This account is privileged and the provider may cause a security ...
    (microsoft.public.windows.server.sbs)
  • RE: SBS Slows down and stops
    ... This provider will be run using the LocalSystem ... This account is privileged and the provider may cause a security ... SBS Server 2003 with all updates from the Service Pack 1 document, ...
    (microsoft.public.windows.server.sbs)