Linked Server Connection is Failing (SSAS, Azure Analysis Services (AAS) or SQL)

Linked Server Connection is Failing: I have seen many couple of cases/time where the linked server will not work. I will show all the steps were and what is the process to do, also the precautions and required things must be checked to trouble this issue.

Go through the below document to understand about the linked server configuration and still if you have doubts/concerns you can feel free to comment down below: https://learn.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine?view=sql-server-ver16

Linked Server Connection Steps:

Step 1: Connect SQL server with Windows Authentication.

Step 2: Server objects—linked server—New linked server

Step 3: Click the new linked server- In this example, connecting SSAS multidimensional cube.

Step 4: In the Security, from the below image use the 3 option, if you are using a windows authentication and it has permission to connect to the SQL server as sys admin and connect to SQL. We can check that in the SQL server permission for the user.

Step 5: Enable RPC and RPC Out to true.

Step 6: Do the Test connection for the successful.

Step 7: Able to query against the linked server. Use the below query to test that.

Execute ('select * from $system.discover_sessions') at <Linked server name>

Linked Server Connection is Failing

Possible issues while connecting linked server as follows below:

Steps to resolve this issue/incorrect configuration:

Step 1: In this scenario I will use a SQL authentication server to connect in the linked server.

This error comes when the user doesn’t have permission to connect to the SQL server and whenever we use SQL authentication to connect to the Analysis server

But if you still want to use SQL authentication for the SQL server but we have to use a windows authentication in the fourth option,

An account which has access in both the SQL server as SYSadmin and Connect SQL server in the permission option as the below image and Also admin permission in the Analysis server.

Step 2: If they allow in process is not enabled in the provider than you will get this error mentioned in the next screenshot.

Below error:

Make sure you enable it. Allow in process.

SQL Bi Links:

1. Port Number For Analysis Services – Find Ports For SSAS? (kingfishertechtips.in)
2. SMTP Server In SSRS/PBIRS |Subscribe To Email Problem/Issue (kingfishertechtips.in)
3. SQL Server 2022 Download, Install, Connect Using SSMS Tool (kingfishertechtips.in)
4. Creating Tabular Project & Deploy Model To Analysis Service (kingfishertechtips.in)
5. Deploy Tabular Project To Azure Analysis Service – SSDT Tool (kingfishertechtips.in)
6. SSRS/PBIRS – Install And Configure The Report Server On-Prem (kingfishertechtips.in)
7. Could Not Load Folder Contents In Portal URLs – SSRSPBIRS (kingfishertechtips.in)
8. SSRS/PBIRS – Install And Configure The Report Server On-Prem (kingfishertechtips.in)
9. SQL Server 2022 Download, Install, Connect Using SSMS Tool (kingfishertechtips.in)
10. Capture Fiddler Trace for Debugging the Network Traffic (kingfishertechtips.in)
11. On-Premises Gateway: Configure/Map SQL DB To Power Bi (kingfishertechtips.in)
12. Fixed Column/Row Header Make Visible In Any Report (kingfishertechtips.in)
13. Self-Signed Certificate: Configure SSRS/PBIRS With SSL (kingfishertechtips.in)
14. Create Local User Account/Local Admin User In Windows 10/11 (kingfishertechtips.in)
15. Login With AAD/Domain/Microsoft Account To Azure VM’s/RDP (kingfishertechtips.in)