Link to home
Start Free TrialLog in
Avatar of xuewei shi
xuewei shi

asked on

SQL analysis database connection issue.

Hello, I am seeing a wierd issue in MSAS database.

The MSAS database data source is a SQL database on a remote SQL Server, called RemoteSQL1.
If I input host name "RemoteSQL1" as server name in Data source, I am not able to process cubes. The error is as below. However if I use server's IP address in Data Source, then I am able to process cubes without any error.
I tried ping the remote SQL server name, it works. Also telnet RemoteSQL1 port works too.

The error message says about "Named pipes provider", does it mean it tried to connect to remote SQL using Name pipes? How can I force it use TCP/IP?

Do anyone has idea how to trouble shoot this? Thank you so much!
 
---------------------------------------------
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 [5]. ; 08001.
----------------------------------------------------------------------------------
Avatar of ste5an
ste5an
Flag of Germany image

Errors marked as HYTxx are normally errors on the network (physical) layer.

The named pipe provider is used in fallback cases.

Check for active AV and firewalls. Check your network, NICs and switches. Check whether you can ping your server hosting SSAS. Check whether you can access your cube locally on the server using TCP/IP. Well, check the instance name. Check the client configuration.
Avatar of Jerry_Justice
Jerry_Justice

SQL Server Browser service MUST be running on the remote server if you want to connect to it by instance name.

On the host machine, make sure "SQL Server Browser" is running.  If it is, make sure Firewall (if it is running) has an incoming exception for UDP Port 1434.
Avatar of xuewei shi

ASKER

Hi Ste5an, many thanks for your kindly reply and help.
I can ping the remote SQL Server host name from MSAS server, it works. Also telnet port works.
When I input host name and click "test", it is successful. However when I start process cube, it failed.
From another MSAS Server, we can process same cube connecting to same remote SQL Server, so I guess it's not issue with the remote server... very weird.. any additional comment is appreciated a lot!
User generated imageUser generated image
Hello,

Your named Pipes connections are disabled

Enable it and hope that will work for you.

This Link will show you how to enable it.
Hello Vikas,
Thanks for your reply.. I enabled named pipes on both server and client, and services have been restarted. However still cannot process the cube..

I use "Microsoft network monitor" to capture the network trace, however I cannot see any connections make from MSAS client to remote SQL Server when processing cubes..
Anyone know how can find how to find from "Microsoft network monitor"   what has been failed between the connection from MSAS client to remote SQL Server? Can I search for some key word? Thanks,
ASKER CERTIFIED SOLUTION
Avatar of Vikas Garg
Vikas Garg
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you for the reply! I am using service account, which is a domain account, it has all admin permissions on the server, SQL and MSAS..
Thanks a lot for your kindly reply!
SQL browser on remote server is not running, since remote server only has one default SQL instance and our security policy requires disable SQL Browser for server which has only one default instance.
We have another MSAS server which connecting to the remote SQL too, and it works fine. So I guess should be not Browser problem.
I have not checked firewall yet, need to ask network guys check that. I will feedback when I have updates
Hi xuewei,

Did you set SPN for MSAS account?
If not you'll need to do it so you can connect through the name. Here's the MSDN article about it.
Thanks a lot, I manually re-configure the impersonate account and it now works!