February 13, 2008 at 3:05 pm
I am trying to set up a liked server using the UI in Management studio. In the Object Explorer I can see the server i want to link to MyServer2 and I have connected to it. I goto to the server in which i want to create the link MyServer1 and click on Server Objects then Linked Servers, New Linked Server.
In the server name I enter the server I want to link to, MyServer1. I don't know what to enter or where after that? What else do i need to set up?
When i try this select i get the following error.
select * from MyServer1.MyDB.dbo.MyTable
Error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "MyServer1" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "MyServer1".
I have created the linked server under the server objects in the object explorer. In the security tab of the linked server properties if you don't select a value from the Local Login drop down you can not enter the user or password. So I selected SA and entered my user and password. Also I use Windows authentication on both servers.
February 14, 2008 at 4:42 pm
Your sql server will be running in local system account. change that to domain account so that the server will be able to mutally authenticate itself.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 15, 2008 at 10:48 am
I am new to SQL... How do i do that?
February 18, 2008 at 11:32 am
Kirk, try this.
Find (or ask your system admin to create one) a domain account that has very strong permissions. I use an account here named DomainName\sqlexec.
You will NOT need to add this account to the Windows users or as a SQL Server login.
Stop SQL Server/MSSQL or go into Windows services and stop all the SQL Server services. (Admin Tools --> Servcies)
Change these services to Log on as your new domain account:
SQL Server
....Agent
....Browser
....Full Text
....Reporting Services
....VSS Writer
I left the other services set to Network Service in my installation.
Now, start SQL Server and Agent and others.
This works well for me.
Hope it helps.
John
February 18, 2008 at 2:27 pm
I will give that a try thanks.
February 22, 2008 at 12:30 pm
I have this script saved that fixed the same issue I had described above. The trick was the integrated security setting, of course the options may be different for your situation. Hope this helps
----
declare @ServerName varchar(50)
Set @ServerName = 'ServerName'
exec sp_addlinkedserver @server=@ServerName, @srvproduct='', @provider='SQLOLEDB', @provstr='Integrated Security=SSPI;'
-- Set options
exec sp_serveroption @ServerName, 'data access', 'true'
exec sp_serveroption @ServerName, 'rpc', 'true'
exec sp_serveroption @ServerName, 'rpc out', 'true'
exec sp_serveroption @ServerName, 'use remote collation', 'true'
-- test retreival
select count (*) from CHIMSSQLDEV.master.DBO.sysdatabases
¤ §unshine ¤
February 22, 2008 at 2:00 pm
Are there any special tricks if you're trying to link a SQL2000 server into a SQL2005 server? I feel like I've tried every possible combination without success. :crazy:
I'm using this syntax:
EXEC sp_addlinkedserver
@server='TheNameIWantToCallMySQL2000LinkedServer',
@srvproduct='',
@provider='SQLNCLI',
@datasrc='MySQL2000Server',
@catalog='MySQL2000?DatabaseName'
My error comes back as:
OLE DB provider "SQLNCLI" for linked server "MySQL2000Server" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18456, Level 14, State 1, Line 0
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
February 22, 2008 at 2:24 pm
I just tried your code on my server and did not receive any errors. Let me do some more research.
¤ §unshine ¤
February 22, 2008 at 2:53 pm
Are both servers on the same security? Windows Only or Mixed mode?
¤ §unshine ¤
February 22, 2008 at 4:43 pm
I believe they are both on the same security.
February 25, 2008 at 8:23 am
I'm not sure what else it could be, you might want to double check all the above mentioned... making sure both servers are on the same security, both servers have services starting under domain account, and both servers have the user id you are trying to log in as.
¤ §unshine ¤
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply