Problem:
You have set up a tabular model or multidimensional cube on an Analysis Services instance. You attempt to connect to this Analysis Services instance in one of the following ways:
- In Excel, you choose to set up a data connection From Other Sources -> From Analysis Services. When you enter in the [ServerName\InstanceName], you get the following error:“Errors in the OLE DB provider. Could not connect to the redirector. Ensure that the SQLBrowser service is running on the ‘[ServerName]’ server.”
- In Excel, you choose to set up a data connection From Other Sources -> From Analysis Services. When you enter in the connection string [ServerName:SSASPort] (usually 2382/2383 by default), you get the following error:“The following system error occurred: The system detected a possible attempt to compromise security. Please ensure that you can contact the server that authenticated you.”
- In Excel, you choose to set up a data connection From Other Sources -> From Analysis Services. When you enter in the connection string [ServerName] (no Instance name or port), you get the following error:“The following system error occurred: The system detected a possible attempt to compromise security. Please ensure that you can contact the server that authenticated you.”
- In SSIS, you attempt to create a new connection to Analysis Services. In “Server or file name” textbox, you enter [ServerName\InstanceName] and get the following error:“Test connection failed because of an error in initializing provider. Errors in the OLE DB provider. Could not connect to the redirector. Ensure that the SQLBrowser service is running on the ‘[ServerName]’ server. Errors in the OLE DB provider. An error occurred while named instance information was being retrieved from the SQLBrowser service on the ‘[ServerName]’ server.”
- In SSIS, you attempt to create a new connection to Analysis Services. In “Server or file name” textbox, you enter [ServerName] (no Instance name) and get the following error:“Test connection failed because of an error in initializing provider. The following system error occurred: The system detected a possible attempt to compromise security. Please ensure that you can contact the server that authenticated you.”
Solution:
First things first, since the error message may have mentioned the SQLBrowser service is not running – log on to your server and ensure it is running. Mine was. On to the next solution:
Try simply putting in the IP ADDRESS of the server instead of the server name. No Instance name or port needed. In both Excel AND SSIS, this solution worked for me! What this most likely means is that there is a name (DNS) resolution issue on the server that needs to be resolved. I was not involved in setting up the server so I had no idea that there was a resolution issue.
After spending two days trying to get this to work in Excel, I finally stumbled upon this MSDN article that covers connection problems and found a paragraph about name resolution that I had completely skipped over upon first consulting it. It has a pretty good explanation of what ended up being my issue:
If the client application cannot connect to Analysis Services by using the server (or server\instance) name, there may be name resolution problems on the network. Try connecting to the Analysis Services instance by using the IP address of the computer that is hosting Analysis Services rather than using the server name itself (xxx.xxx.xxx.xxx or xxx.xxx.xxx.xxx\instance_name).
Tip If you are connecting to an Analysis Services instance on the same computer as the client application, try using the server name or IP address rather than using localhost or (local).
You can also use the Ping command-line utility to isolate the problem. Try to ping the computer that is hosting Analysis Services by using the computer’s hostname, fully qualified domain name, and IP address. You can use the Ping –a parameter to return the fully qualified domain name of a computer.
If you can ping the computer only by its IP address, you have isolated the source of the connectivity problem. Resolve the name resolution issue and the connectivity issue will go away.
Tip To begin resolving a name resolution issue, verify that there are no incorrect entries in the hosts or lmhosts files on the local computer. These files are located in the ..\system32\drivers\etc\ folder on the client computer.
Please let me know in the comments if this solution worked for you!
