VSTFS error when running the Analysis Services processing job “There are currently no logon servers available”
Today we got a fun one… In Visual Studio Team Foundation Server 2010, the server has a SQL Server database(s), an SQL Server Analysis Services database (cube), and several reports on top of the cube.
There were two symptoms:
1. Data was not refreshed when using the Reports in VSTFS Admin. Data would show up in the reports, but the data was “stale”. No real reporting errors however.
2. We kept getting this funny error when trying to run the “Process Analysis Database” job in the VSTFS admin. After staring the job with either Full or Incremental as the parameter, we checked the job status, and saw the errors. Here’s the Shortened version of the Visual Studio TFS error XML.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Test Plan', Name of 'Test Plan' was being processed. Errors in the OLAP storage engine: An error occurred while the 'Test Plan ID' attribute of the 'Test Plan' dimension from the 'Tfs_Warehouse' database was being processed. Internal error: The operation terminated unsuccessfully. Internal error: The operation terminated unsuccessfully. Server: The operation has been cancelled. The following system error occurred: There are currently no logon servers available to service the logon request.
Please note, in the job status page where the XML results are shown, there is a separate XML tag for each job. If one of them had an error, you may still see a copy of the error until all the jobs succeed. Make sure you look at the current job during your testing to fix these errors. Use the time stamps (especially the minutes since UTC is likely not your local timezone) to help you keep the results straight when comparing to your testing.
+ Full Analysis Database Sync
<Job Name="Full Analysis Database Sync" JobProcessingStatus="Idle">
- last run (may contain error)
<LastRun QueueTimeUtc="2010-08-09T06:00:00Z" ExecutionStartTimeUtc="2010-08-09T06:00:01.02Z" EndTimeUtc="2010-08-09T06:00:33.517Z" Result="Failed">
- current run
+ Incremental Analysis Database Sync
- last job
- current job
So let's tackle the error- There are currently no logon servers available to service the logon request.
1. At first, I thought the domain controller is inaccessible.
From the command prompt, you can check that.
Set LogonServer
The results will tell you the server name. Make sure you can ping the server.
ping <mylogonserver>
We tested logging in with domain accounts and they all worked fine, so we figured the domain controller was working OK and doing its job.
No such luck!
2. Then we figured out it was something really simple. In SQL Analysis Services, you can choose which account and give a password for the account to be used when the Analysis Services processing connects to the SQL Server relational database to pull in the fact data during processing.
Connect to your Analysis Server from Management Studio on the actual Server machine itself. Expand the database, Expand the Data Sources. I am showing Adventureworks, but obviously your TFS database will have a different name. Right click > Properties on the Data source highlighted.
Two things to do with this data source.
A> Test 1: Validate the connection string.
Highlight the cell right of the label “Connection String” in the properties grid. You will notice there is an Ellipsis button that becomes available. […]
Then when you get the Connection Manager, make sure it points to the right server name, and right database. To test click the [Test Connection] button.
When you click the button you should get confirmation that it works.
The test validates the following parameters
a> Your own logged in account (whomever you may be)
b> 32-bit connectivity only.
c> The client machine (where Management studio runs) can connect. Hope that’s the same as the server machine for this test to be “true”
Therefore this test is not very realistic- it does not test the
d> Correct security context and
e> Doesn’t test 64-bit providers that the server probably uses (ie. AS uses 64-bit providers if the install is 64-bit)
f> Doesn’t necessarily test the server machine itself. Where did you run Management Studio from – a client machine, or from the server itself? Therefore if you run it on the server itself (and not a client machine) you may get close to a good test, but not an exact test. You need to manually test the proper security contexts if you want to simulate real life processing of cubes.
So, you have the power to test a,b,c, & f easily with the click of a button. The other two – factor d and e, are a little bit more involved.
-----------------------
An aside – testing factor d “the correct security context”
To test the security context as mentioned in factor “d”, you can use runas to launch management studio under the “right” security context.
start > run > cmd
For 2008:
runas /user:domain\username "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"
For 2005:
runas /user:domain\username "C:\Program Files (x86)\Microsoft SQL Server\90\Tools\binn\VSShell\Common7\IDE\SqlWb.exe"
You will be prompted for the password after each of the RunAs commands. Then you can be sure you are testing a realistic security context. You can test the domain\username of the client app which will be the one running the XMLA processing command to process the cube.
--------------------------------
B> Consider the Impersonation Info security context of how the client application (in this case VSTFS application server logic) connects to AS and runs the cube processing commands.
Highlight the cell right of the label “Impersonation Info” in the properties grid. You will notice there is an Ellipsis button that becomes available. […]
Now you get the Impersonation Information page with 4 settings.
We tried the gamut of settings above. All seem like viable settings.
A> Use a specific Windows user name and password. This means you type in the account and password that is supposed to authenticate properly to query the SQL Server relational database. DOMAIN\user with the Password. Sounds like its very flexible, but you have to remember to come in for maintaining the password.
B> Use the service account. This means which ever service account runs the SQL Server Analysis Services server will also be used to connect to the SQL Server database engine to run the relational queries to query the fact data and build the cubes. Check your start > run > services.msc. In the list of services, highlight the SQL Server Analysis Services. Double-click. On the Logon tab you can see the “account” used for the service to start with. This it the choice you are making above – use this account to connect to SQL Server when running the Cube processing, or any relational queries for that matter from Analysis Services.
C> Change the AS security setting to "Use the credentials of the current user" so that the same identity as the IIS application pool for TFS will be used to start the FULL processing. Who is the current user? Whomever makes the Analysis Services XMLA connection and does the request to process the cube. ie. “that guy” – impersonate that client user who logged in when talking to the SQL Server to run the queries and build the cube.
D> Inherit (means copy the setting from higher up in the cube/database design above the data sources)
For VSTFS as a client app, we found that the first option was the default. As long as the user name and password you type is correct, seems like that should work. However, that’s one more place to keep a password stored. Therefore, Opting for option B or C is less maintenance to remember to revisit this setting every time a password changes.
We chose “use the service account” option on the Impersonation Info on the Analysis Services Data Source and resolved the funny error There are currently no logon servers available to service the logon request.