Unable to connect to another connection in SSAS Tabular

0
votes

I have a very weird issue with SSAS Tabular connecting to a SQL Server source database. I've spent almost the entire day on this, and searched online in various ways to find a helpful answer, but none of the suggestions solved this mysterious problem for me.

It used to work fine, but I tried various connection settings (Windows vs SQL authentication, and different impersonation modes), but now I no longer can get it to work (even if I revert back to the previous bim file in source control).

When I process a table from the WORKSPACE database (through SSMS), it works. But when I process a table from the DEPLOYED database (still through SSMS), it does NOT work.

The connection I am using is: -SQL Server Native Client 11.0 -Windows Authentication -Impersonation mode of a Windows account (which has the appropriate permissions)

The source database is in a SQL Server database located on a different server (but in the same network).

I have searched everywhere online, but didn't find anything that could help me. I have tried changing the connection driver (OLE DB Provider for SQL Server), and I have restarted the SSAS Tabular service.

The full error message I get when trying to process a table on the deployed database is:

Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: Login timeout expired; HYT00; 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.; 08001; Named Pipes Provider: Could not open a connection to SQL Server [5]. ; 08001. A connection could not be made to the data source with the Name of 'DataWarehouse'. '.

In fact, after I tried a second time to restart the SSAS Tabular service, the error message I get after attempting to process a table is slightly different:

Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: 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.; 08001; SSL Provider: The requested security package does not exist ; 08001; Client unable to establish connection; 08001; Encryption not supported on the client.; 08001. A connection could not be made to the data source with the Name of 'DataWarehouse'. '.

1
sql-serverssasssas-tabular
Can you remote on to the computer running SSAS and confirm that you can connect to the SQL Server there? Maybe someone changed a firewall. You might also want to edit the connection info in the deployed model and add tcp: to the start of the host name, just to force TCP/IP instead of named pipes, just to narrow the issue down – Nick.McDermaid
The issues have mysteriously disappeared (no idea what has changed, which is scary). But that's good point, definitely something worth to try: I can currently remote connect to the server that has the source database, from the SSAS server, but I don't know if I was able to at the time the issues were happening. – Marc

1 Answers

0
votes

According to your description, it seems to be related to connection problem. You need to check whether corresponding SQL server service and SSAS Service is running(check server which contains "DataWarehouse" is running and can be connected). You could go to "SQL Server Configuration Manager " to restart them.

In addition, you also need to make sure your current windows credential can access this database(if you use window credential). And check whether this server enable remote connect.

Zoe