December 20, 2007 at 8:21 am
Before installing SQL 2005 SP2 i managed to set up a linked server using the following
USE master
GO
/* Add new linked server */
EXEC sp_addlinkedserver
@server='LINKED_OLAP', -- local SQL name given to the linked server
@srvproduct='', -- not used
@provider='MSOLAP.3', -- OLE DB provider (the .2 means the SQL2K version)
@datasrc='stevemacsvr', -- analysis server name (machine name)
@catalog='DonaldDuck2' -- default catalog/database
I've gone to use it again after installing SP2 to fix another problem and i now get the following when trying to test the connection
TITLE: Microsoft SQL Server Management Studio
------------------------------
"The test connection to the linked server failed."
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot initialize the data source object of OLE DB provider "MSOLAP.3" for linked server "LINKED_OLAP".
OLE DB provider "MSOLAP.3" for linked server "LINKED_OLAP" returned message "An error was encountered in the transport layer.".
OLE DB provider "MSOLAP.3" for linked server "LINKED_OLAP" returned message "The peer prematurely closed the connection.". (Microsoft SQL Server, Error: 7303)
Any ideas why it would fail when it was working ?
December 21, 2007 at 12:22 pm
Do you see failed logons for "NT AUTHORITY\ANONYMOUS LOGON" on the SSAS server? Does the linked server happen to work in an RDP session on the SQL Server?
Tommy
Follow @sqlscribeDecember 24, 2007 at 2:04 am
Tommy Bollhofer (12/21/2007)
Do you see failed logons for "NT AUTHORITY\ANONYMOUS LOGON" on the SSAS server? Does the linked server happen to work in an RDP session on the SQL Server?
I can't see any failed logons for today and i've just tried to set it up again and test the connection. I have checked the surface area connections and all are set to local and remote so i dont think that is the problem.
Not sure what you mean by a RDP session ?
Thanks for your help
December 24, 2007 at 5:17 am
I think i've now fixed the issue. When I used SP_ADDLINKEDSERVER i was specifying the Provider string property as MSOLAP.3
EXEC sp_addlinkedserver
@server='LINKED_OLAP', -- local SQL name given to the linked server
@srvproduct='', -- not used
@provider='MSOLAP.3', -- OLE DB provider (the .2 means the SQL2K version)
@datasrc='stevemacsvr', -- analysis server name (machine name)
@catalog='DonaldDuck2' -- default catalog/database
If you use the wizard direct in Management Studio, it will ask for a product, i just typed 'x' and then left the provider string blank and it worked fine!
November 22, 2010 at 3:18 pm
Tommy Bollhofer (12/21/2007)
Do you see failed logons for "NT AUTHORITY\ANONYMOUS LOGON" on the SSAS server? Does the linked server happen to work in an RDP session on the SQL Server?
Hello Tommy,
I have the exact same issue. I am able to use it when i RDP but from local.. Any idea..Please help !!!
May 9, 2011 at 9:43 pm
I had same problem with Linked server having SQL Authentication on SQL instance and mapped user in Security Tab of the linked server.
Only an hour later I relized that the Domain Account I used in there for the remote login was LOCKED off the domain. Unfortunately when you save changes, the dialog does not tell you anything. In fact it does not seem to be checking the validity of the remote login.
Bloody stupid domain admins set the service accounts to lock after N failed attempts.
The only way to test was to either stick the same domain username password in the bottom of the security tab (Be Made using this security context), which failed with a dodgy message (Cannot initialize the data source object of OLE DB provider...) immediately upon OK button. And then I tried EXECUTE AS... any local program, such as Notepad, which then popped up with ACCOUNT LOCKED message.
So the bottomline -- check the domain user
M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply