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.
-------------------------- ---------- ---------- ---------- ---------- ---------- ------
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.
--------------------------
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.
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.
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!
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!
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.
Your named Pipes connections are disabled
Enable it and hope that will work for you.
This Link will show you how to enable it.
ASKER
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,
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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..
ASKER
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
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.
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.
ASKER
Thanks a lot, I manually re-configure the impersonate account and it now works!
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.