n_srikanth4
asked on
No Catalogs are created in Linked Server using SQL Authentication
Hi Experts ,
Issue : Linked Server is being created but no catalogs are found when accessing the SSMS (Sql Server Management Studio) with SQL Authentication but with Windows Authentication , I can access the catalogs.
Please Note : WH DB Server /Cube Server / Linked Server are on the same machine.
Here is the code , how I am trying to create a linked server and catalogs .
Please help me with the solution , will appreciate your help in this regard.
if NOT exists (select srvname from master.dbo.sysservers where srvname = 'RPM_Cubes')
BEGIN
Exec master.dbo.sp_addlinkedser ver
@server = N'RPM_Cubes',
@srvproduct ='',
@PROVIDER = N'MSOLAP',
@datasrc=N'localhost\sqlse rver2016',
@catalog ='RPM_Database'
EXEC master.dbo.sp_serveroption @server=N'RPM_Cubes', @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'RPM_Cubes', @optname=N'rpc out', @optvalue=N'true'
END
ELSE
PRINT 'Link Server already exists'
Thanks,
SRK.
Issue : Linked Server is being created but no catalogs are found when accessing the SSMS (Sql Server Management Studio) with SQL Authentication but with Windows Authentication , I can access the catalogs.
Please Note : WH DB Server /Cube Server / Linked Server are on the same machine.
Here is the code , how I am trying to create a linked server and catalogs .
Please help me with the solution , will appreciate your help in this regard.
if NOT exists (select srvname from master.dbo.sysservers where srvname = 'RPM_Cubes')
BEGIN
Exec master.dbo.sp_addlinkedser
@server = N'RPM_Cubes',
@srvproduct ='',
@PROVIDER = N'MSOLAP',
@datasrc=N'localhost\sqlse
@catalog ='RPM_Database'
EXEC master.dbo.sp_serveroption
EXEC master.dbo.sp_serveroption
END
ELSE
PRINT 'Link Server already exists'
Thanks,
SRK.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
change: @datasrc=N'localhost\sqlse rver2016',
to: @datasrc=N'<MyHOSTNAME>\sq lserver201 6',
to: @datasrc=N'<MyHOSTNAME>\sq
ASKER
good
or using sp_addlinkedsrvlogin set login to your SSAS using account that has SSAS permissions
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addlinkedsrvlogin-transact-sql
and just in case
Issues with using msolap.x provider name in linked server definition
https://blogs.msdn.microsoft.com/as_emea/2012/09/27/issues-with-using-msolap-x-provider-name-in-linked-server-definition/