Technical: Microsoft – SQL Server – Analysis Services (v2012) – Deployment – Error – 08001 – No Such host is known

Introduction

While deploying a SSAS Solution, I ran into a bit hard to identify\trace error.

Errors

SQL Server Aliased Connection

Error 4 OLE DB error: 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; TCP Provider: No such host is known. ; 08001. 
0 0 
Error	5	
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'IISLogDW', Name of 'IISLogDW'.
0	0

Resolution – Changed Solution Data Sources to use actual server name.

I had created an Alias Connection by running cliconfg.exe and entering DB Connection Details:

SQLServerClientNativeNetworkUtility-IISLogDB

It seems that SQL Server Analysis Service (SSAS) does not consider SQL Server Alias Configurations.

As so we changed Solution Data Sources from using SQL Server Aliased connection to use actual server name:

DataSourceDesigner

Login failed for user

The other error we got is pasted below:

Error 4 OLE DB error: OLE DB or ODBC error: Login failed for user 'NT SERVICE\MSOLAP$MSSQL2012'.; 28000; Cannot open database "IISLogDW" requested by the login. The login failed.; 42000. 
0 0


Error 5 
Errors in the high-level relational engine. A connection could not be made to the 
data source with the DataSourceID of 'IIS Log DW', Name of 'IIS Log DW'. 
0 0

Resolution – Grant SQL Server Permission

And, it was far easier to debug and address.

--create login 
create login [NT SERVICE\MSOLAP$MSSQL2012]
from windows;

--use [db-name]
use [IISLogDW]
go

--create db user
create user [NT SERVICE\MSOLAP$MSSQL2012]
	   from login [NT SERVICE\MSOLAP$MSSQL2012]
go

-grant object level read permission
grant select on [dbo].[DimTime] to [NT SERVICE\MSOLAP$MSSQL2012];
grant select on [dbo].[factSales] to [NT SERVICE\MSOLAP$MSSQL2012];

Same Error

Same Error  (in MS Event Viewer) – OLE DB error: OLE DB or ODBC error: Login timeout expired; HYT00; – Could not open a connection to SQL Server [53]. ; 08001.


OLE DB error: 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 SQLServerBooks Online.; 08001; Named Pipes Provider: Could not open a connection to SQL 
Server [53]. ; 08001.

Same Errors (in SQL Server Profiler \ Profiling SSAS) – OLE DB error: OLE DB or ODBC error: Login timeout expired; HYT00; – Could not open a connection to SQL Server [53]. ; 08001.



Internal error: The operation terminated unsuccessfully. Internal error: The operation terminated unsuccessfully. OLE DB error: 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. Checkif 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 [53]. ; 08001. Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'IIS Log DW', Name of 'IIS Log DW'. Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Dim Time', Name of 'Dim Time' was being processed. Errors in the OLAP storage engine: An error occurred while the 'Calendar Year' attribute of the 'Dim Time' dimension from the 'salesDimensionDW' database was being processed. Server: The current operation was cancelled because 
another operation in the transaction failed. Internal error: The operation terminated unsuccessfully. Internal error: The operation terminated unsuccessfully. OLE DB 
error: 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 [53]. ; 08001. Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'IIS Log DW', Name of 'IIS Log DW'. Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Dim Time', Name of 'Dim Time' was being processed. Errors in the OLAP storage engine: An error occurred while the 'Full Date Alternate Key' attribute of the 'Dim Time' 
dimension from the 'salesDetectionDW' database was being processed.

Summary

Though Business Intelligent Development (BIDS) and SQL Server Data Tools support SQL Server Named Aliases, it seems SQL Server Analysis Services does not support SQL Server Named Aliases.

 

 

References

2 thoughts on “Technical: Microsoft – SQL Server – Analysis Services (v2012) – Deployment – Error – 08001 – No Such host is known

    • Tiago:

      You are welcome.

      That particular post was brought out of frustration.

      Yes, I wished that the problem was solved, but yet I felt that as we are all within the same community, I owe each person in same tool shop.

      And, part of the owing is sharing the Good and Bad.

      Thanks for leaving a comment and I really do love and like Analysis Services.

      With grace,

      Daniel

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s