AWS Linked Sever from SQL to SSAS Tabular Model

Question

Hello Experts,

We are using SQL Server enterprise edition where both SQL and SSAS(Tabular) are deployed on same RDS. I have to make Linked Server from SQL to SSAS Tabular database so that TMSL data processing/partitions query can be called from stored procedure. followed the AWS post by connecting to SQL through Admin.

https://aws.amazon.com/blogs/database/implement-linked-servers-with-amazon-rds-for-microsoft-sql-server/

EXEC sp_addlinkedserver @server = N'SSAS_DE2',

@srvproduct=N'', @provider=N'SQLNCLI',

@datasrc=N'audex-analyse-nprd-sql-ee-ssasenabled-database-1.c6lalk0xulcn.eu-west-2.rds.amazonaws.com',

@catalog=N'audex-analyse-de2-ssas'

have created a new domain user "ssas_service_account", trying to link above linked server with this login "ssas_service_account".

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SSAS_DE2',

@useself = N'false',

@locallogin = NULL,

@rmtuser = N'ssas_service_account',

@rmtpassword = N'**********'

GO

and also granted access to user "ssas_service_account" on "audex-analyse-de2-ssas" tabular database

--2 Grant access to SSAS Service Account exec msdb.dbo.rds_msbi_task @task_type='SSAS_ADD_DB_ADMIN_MEMBER',

@database_name='audex-analyse-de2-ssas',

@ssas_role_name='db_owner',

@ssas_role_member='audexanalyse\ssas_service_account'

I can verify the access has been granted to above service account successfully by running the query against the table msdb.dbo.rds_fn_task_status.

926 SSAS_ADD_DB_ADMIN_MEMBER Not Applicable 100 2 SUCCESS [2025-01-22 06:26:32.073] Task execution has started. [2025-01-22 06:27:32.027] Task has been done successfully.

There are no errors till here, for testing the linked server, opening the SQL database from EC2 machine after login with "ssas_service_account", and when testing the Linked Server, getting error "Login failed for user 'ssas_service_account'. (Framework Microsoft SqlClient Data Provider)". Server Name: audex-analyse-nprd-sql-ee-ssasenabled-database-1.c6lalk0xulcn.eu-west-2.rds.amazonaws.com Error Number: 18456 Severity: 14 State: 1 Line Number: 1

2nd Attampt: Recreating linked server again and changed the parameter "@datasrc as

EXEC sp_addlinkedserver @server = N'SSAS_DE2',

@srvproduct=N'',

@provider=N'SQLNCLI',

@datasrc=N'localhost:2382',

@catalog=N'audex-analyse-de2-ssas'

This time the test connection taking time before throwing error which is Named Pipes Provider: Could not open a connection to SQL Server [53]. OLE DB provider "MSOLEDBSQL" for linked server "SSAS_DE2" returned message "Login timeout expired". OLE DB provider "MSOLEDBSQL" for linked server "SSAS_DE2" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". (Framework Microsoft SqlClient Data Provider)

Server Name: audex-analyse-nprd-sql-ee-ssasenabled-database-1.c6lalk0xulcn.eu-west-2.rds.amazonaws.com Error Number: 53 Severity: 16 State: 1

Can any one please suggest what mistake I am doing.

Thanks

Prashant Pandey

Answer 1

SQLNCLI is an OLE DB provider for talking to SQL Server (and a very old and outdated one). I believe that for Analysis Services, you should use the MSOLAP provider.