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.
----------------------------------------------------------------------------------
xuewei shiDBAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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.
0
Jerry_JusticeCommented:
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.
0
xuewei shiDBAAuthor Commented:
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!
MSAS-DB-Connection.pngerror-when-process-cube-using-host-n.png
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vikas GargBusiness Intelligence DeveloperCommented:
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.
0
xuewei shiDBAAuthor Commented:
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,
0
Vikas GargBusiness Intelligence DeveloperCommented:
HI,

Which account you have configured for impersonation information ?

In Cube datasource we have to set the impersonation account.

https://msdn.microsoft.com/en-IN/library/ms187597.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
xuewei shiDBAAuthor Commented:
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..
0
xuewei shiDBAAuthor Commented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
xuewei shiDBAAuthor Commented:
Thanks a lot, I manually re-configure the impersonate account and it now works!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.