Project Server 2007, Analysis Services2005 and Building a Cube

November 12, 2008 Leave a comment

By far and away the most irritating long term issue I have had is in regards to building a cube with Project 2007 and Analysis Services 2005.  I was unable to find any documentation on what I had to do to get this working and had a client who really needed it working yesterday.  After weeks of puzzling over it I have finally worked through some problems and gotten it going!In retrospect it was really simple, but some basic knowledge was holding me back.  Essentially the problem was that AS2005 did not have the correct repository setup, and the entire answer was in this link : http://support.microsoft.com/default.aspx?scid=kb;EN-US;921116In particular the following step : On the computer that is running SQL Server 2005 Analysis Services, follow these steps:

Note This includes SQL Server 2005 Analysis Services that are running remotely in a 3-tier configuration.

  1. Create a subfolder that is named DSO9 in the following folder: C:Program FilesMicrosoft Sql ServerMssql.NumberOLAPNote Mssql.Number is the folder in which the SQL Server 2005 Analysis Services files are stored.
  2. Copy the Msmdrep.mdb file from a computer that is running a fully patched SQL Server 2000 Analysis Services to the DSO9 folder on the computer that is running SQL Server 2005 Analysis Services.
  3. Share the DSO9 folder, and then specify MSOLAPRepository$ as the share name for the shared folder.
  4. Assign Read access and Write access for the shared folder. Do this for the Local Administrators group or for any account that Project Server 2003 will use to connect to Analysis Services for processing.
  5. The account that accesses Analysis Services from Project Server 2003 must be granted the appropriate permissions on the Analysis Services instance.
    6. Open the Msmdsrv.ini file, and then replace the contents of the <DSO> tag with the following code:
<DSO>


    <RemoteRepositoryConnectionString>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\ComputerNameMSOLAPRepository$msmdrep.mdb;Persist Security Info=False</RemoteRepositoryConnectionString>
    

    <RepositoryConnectionString>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesMicrosoft SQL ServerMSSQL.NumberOLAPDSO9msmdrep.mdb;Persist Security Info=False</RepositoryConnectionString>
<RemoteLocksDirectory>\ComputerNameMSOLAPRepositorymce_markerlt;/RemoteLocksDirectory>
<LocksDirectory>C:Program FilesMicrosoft SQL ServerMSSQL.NumberOLAPDSO9</LocksDirectory>
    
</DSO>
  6. Note The Msmdsrv.ini file is located in the PathConfig folder. Make sure that you replace ComputerName in the code with the name of the computer that is running SQL Server 2005 Analysis Services.
  7. The first time that you save the Msmdsrv.ini file, SQL Server 2005 Analysis Services encrypts the “ConnectionString” information. After this occurs, you cannot read the information when you open the file again. Therefore, you should keep a copy of the code in case you need it in the future.
  8. Stop and then restart the SQL Server 2005 Analysis Services service.
  9. Create a subfolder that is named DSO9 in the following folder: C:Program FilesMicrosoft Sql ServerMssql.NumberOLAPNote Mssql.Number is the folder in which the SQL Server 2005 Analysis Services files are stored.
  10. Copy the Msmdrep.mdb file from a computer that is running a fully patched SQL Server 2000 Analysis Services to the DSO9 folder on the computer that is running SQL Server 2005 Analysis Services.
  11. Share the DSO9 folder, and then specify MSOLAPRepository$ as the share name for the shared folder.
  12. Assign Read access and Write access for the shared folder. Do this for the Local Administrators group or for any account that Project Server 2003 will use to connect to Analysis Services for processing.The account that accesses Analysis Services from Project Server 2003 must be granted the appropriate permissions on the Analysis Services instance.
    13. Open the Msmdsrv.ini file, and then replace the contents of the <DSO> tag with the following code:
<DSO>


    <RemoteRepositoryConnectionString>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\ComputerNameMSOLAPRepository$msmdrep.mdb;Persist Security Info=False</RemoteRepositoryConnectionString>
<RepositoryConnectionString>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesMicrosoft SQL ServerMSSQL.NumberOLAPDSO9msmdrep.mdb;Persist Security Info=False</RepositoryConnectionString>
<RemoteLocksDirectory>\ComputerNameMSOLAPRepositorymce_markerlt;/RemoteLocksDirectory>
<LocksDirectory>C:Program FilesMicrosoft SQL ServerMSSQL.NumberOLAPDSO9</LocksDirectory>
    
</DSO>
Note The Msmdsrv.ini file is located in the PathConfig folder. Make sure that you replace ComputerName in the code with the name of the computer that is running SQL Server 2005 Analysis Services.The first time that you save the Msmdsrv.ini file, SQL Server 2005 Analysis Services encrypts the "ConnectionString" information. After this occurs, you cannot read the information when you open the file again. Therefore, you should keep a copy of the code in case you need it in the future.
  13. Stop and then restart the SQL Server 2005 Analysis Services service.


So why did I ignore this for so long?  Purely because the file mentioned there does not exist in AS2005!  It is a legacy file from AS2000.  I do not know why it was not included, and I?m sure there are better ways to create a repository, but this method definitely works.

You can download a blank fresh copy of mdmdrep.mdb from http://www.msexperts.org/downloads/msmdrep.zip, follow these steps, and make sure that you give the SSP users and the SQL database appropriate permission to be able to access C:Program FilesMicrosoft SQL ServerMSSQL.NumberOLAPDSO9. Please also ensure that you replace the sections in bold in the config file with the appropriate values, or you will continue to get errors.

I really wish I had found and tried this earlier.  Don?t ignore this because msmdrep.dll doesn’t exist, or it looks like it is unrelated due to the AS2000 references, it will definitely help.

Filed under Project Server Tagged with , , ,

Content Deployment error? How to find those checked out items within Sharepoint

October 30, 2008 Leave a comment

If you have content deployment running you have probably seen this error before :

10/30/2008 1:01 AM You cannot perform this action on a checked out document. at Microsoft.SharePoint.SPListItem.SetRequiredInfoForUpdateItem(Boolean bDocLib, Boolean bAdd, Boolean bMigrate) at Microsoft.SharePoint.SPListItem.PrepareItemForUpdate(Guid newGuidOnAdd, Boolean bMigration, Boolean& bAdd, Boolean& bPublish, Object& objAttachmentNames, Object& objAttachmentContents, Int32& parentFolderId) at Microsoft.SharePoint.Deployment.ListItemSerializer.AddOrUpdateDoclibItemVersion(SerializationInfoHelper infoHelper, SPListItem& listItem, SPWeb web, Guid newId, String& listItemServerRelativeUrl, Boolean bIsPublish, Boolean exists, String version, Boolean isFirstVersion, Boolean isLastVersion, StreamingContext context, ISurrogateSelector selector, ImportObjectManager objectManager) at Microsoft.SharePoint.Deployment.ListItemVersionSerializer.AddListItemVersion(SPWeb web, SPListItem listItem, Guid newId, Boolean editHistory, Boolean existsInDb, Boolean isFirst, Boolean isLast, Boolean isDocLib, StreamingContext context, XmlElement listItemData, SPImportSettings settings, ImportObjectManager objectManager, SerializationInfoHelper listItemInfoHelper, String& listItemServerRelativeUrl, ISurrogateSelector selector) at Microsoft.SharePoint.Deployment.ListItemSerializer.UpdateListItemVersionData(SerializationInfoHelper infoHelper, SPWeb web, SPListItem& listItem, Guid newId, Boolean existsInDb, Boolean isDocLib, String& listItemServerRelativeUrl, StreamingContext context, SPImportSettings settings, ISurrogateSelector selector) at Microsoft.SharePoint.Deployment.ListItemSerializer.SetObjectData(Object obj, SerializationInfo info, StreamingContext context, ISurrogateSelector selector) at Microsoft.SharePoint.Deployment.XmlFormatter.ParseObject(Type objectType, Boolean isChildObject) at Microsoft.SharePoint.Deployment.XmlFormatter.DeserializeObject(Type objectType, Boolean isChildObject, DeploymentObject envelope) at Microsoft.SharePoint.Deployment.XmlFormatter.Deserialize(Stream serializationStream) at Microsoft.SharePoint.Deployment.ObjectSerializer.Deserialize(Stream serializationStream) at Microsoft.SharePoint.Deployment.ImportObjectManager.ProcessObject(XmlReader xmlReader) at Microsoft.SharePoint.Deployment.SPImport.DeserializeObjects() at Microsoft.SharePoint.Deployment.SPImport.Run()
10/30/2008 1:01 AM Content deployment job 'Remote import job for job with sourceID = bc232239-4545-4050-a8fe-85a5e4b8b112' failed.The exception thrown was 'Microsoft.SharePoint.SPException' : 'You cannot perform this action on a checked out document.'

 Pretty obviously this error is caused by a checked out document, which can be a real pain to find if you have a big site with multiple authors.  The following query when run on your content database (run this on your authoring database, not your production one!) will find all checked out items and tell you roughly where to look, which filename, and the user responsible.

SELECT tp_dirname, tp_leafName, userinfo.tp_title, userinfo.tp_email
FROM AllUserData
INNER JOIN AllLists on AllUserData.tp_listid = AllLists.tp_id
INNER JOIN userinfo on alluserdata.tp_checkoutuserid = userinfo.tp_id
WHERE tp_checkoutUserId != ''
order by tp_dirname, tp_leafname

Filed under SharePoint Tagged with ,

Increasing the maximum upload limit on a Sharepoint Site

October 23, 2008 Leave a comment

A new colleague here asked me today how to configure WSS v3 for files larger than the default limit of 50mb today.  I asked him to quickly tap it into google as I figured there would be hundreds of posts out there on how to do it, but sadly almost all of them seem to be for WSS v2.

Fortunately most of the principles are still the same, except there are far less steps to do :

1.  Increase the Web Applications Upload limit.
2.  Increase the HTTP Timeout.
3.  Increase the Execution Timeout and MaxRequestLength in web.config

Typically step 1 should only be necessary on an Intranet environment, but steps 2 and 3 should only be necessary if you are on a slow connection, or getting timeout errors when attempting to upload large files.

The instructions for each step are below :

1.  Open Central Administration. Go to "Application Management", then "Web Application General Settings".  Ensure you are on the correct web application and modify the "Maximum Upload Size" field.

2.  Open IIS, find the appropriate website and open its properties.  The timeout setting is on the Website tab and in the connections setting under "Http Connection Timeout".  I'd probably consider adding 120 seconds per extra 50 mb, more or less depending on your connection.

3.  Edit C:Program FilesCommon FilesMicrosoft SharedWeb server extensions12TEMPLATELAYOUTSweb.config

From : 

  <location path="upload.aspx">
    <system.web>
      <httpRuntime maxRequestLength="2097151" />
    </system.web>
  </location>

To : 

  <location path="upload.aspx">
    <system.web>
      <httpRuntime maxRequestLength="2097151" executionTimeout="999999"/>
    </system.web>
  </location>

Edit the web.config in the home directory of the IIS Site of your web application.

From :

<httpRuntime maxRequestLength="51200" />

To :

<httpRuntime maxRequestLength="51200" executionTimeout="999999" />

Filed under SharePoint Tagged with ,

Slipstreaming your Sharepoint installation, easy!

October 8, 2008 Leave a comment

If you have been regularly installing Sharepoint farms you might notice that in the last 12 months there has been a large amount of time consuming hotfixes to deploy with each server installation of both WSS and MOSS.  With MOSS you've got all the WSS ones as well as the copious amount of MOSS hotfixes.

Here is the current patch path as recommended by the Microsoft Sharepoint Team :

1. Windows SharePoint Services 3.0 Service Pack 1
2. The 2007 Microsoft Office Servers Service Pack 1
3. The Windows SharePoint Services Infrastructure Update x86 x64
4. The Microsoft Office Servers Infrastructure Update x86 x64
5. KB 953397: Excel Server Security Update x86 x64
6. KB 955586: Document Lifecycle Workflow Update
7. August Cumulative Update for Windows SharePoint Services 3.0 (Global)
8. August Cumulative Update for Windows SharePoint Services 3.0 (Local)
9. August Cumulative Update for Microsoft Office Servers

Wow thats a few patches needed!  I started to get fairly jack of installing these before I was even installing 3 – 9, so heres a really brief and easy way to slipstream the install.

1. Download all of the above patches
2. Take a copy of the MOSS dvd.  Keep note of the path you copied this to.
3. One by one use the following syntax to pull out all of the information from the hotfixes :

hotfixname.exe /extract:<path to moss folder>x86updates /passive

This will extract all the files in the hotfix to your copy of the MOSS DVD x86update folder. 

Now when you run setup, you wont need to spend half an hour patching afterwards.  For WSS the same principle applies, except use the WSS patches only, and probably start with the WSS 3.0 SP1 installer.

Filed under SharePoint Tagged with ,

Force Deletion of an SSP

September 20, 2008 Leave a comment

There is a lot of pain involved with removing an SSP thats having errors due to the database no longer existing.  Of course trying to remove the database via Central Administration and even STSADM, I got alot of errors such as "Cannot open database." and "Failed to delete SSP".

Fortunately there is an undocumented switch for the "STSADM -o DeleteSSP" command that will help in this situation. 

"Stsadm -o deletessp -title SSPNAME -force".

 The -force will delete the entry even if there are errors occuring. 

There are less aggressive methods, such as using stsadm -o deleteconfigurationobject, but if you are trying to connect to a non-existant database, that one probably wont work.  To do this one, query the objects table in the config database for your server, looking for the SSP name in the "name" column, Then take the ID of that object and use it against the stsadm -o deleteconfigurationobject command.

Filed under SharePoint Tagged with ,

.NET Loading extremely slowly

September 14, 2008 Leave a comment

One of my Sharepoint servers has been painfully slow after installation, even running stsadm would take about 10 seconds.  Whats really infuriating about it is that during the time frame where STSADM is typed into the command prompt, and its response, the server is doing next to nothing.

I've seen this behaviour before, however it was on an extremely overloaded ESX platform, so I initially thought it was due to that and didn't bother to look closely into it.  This time is on a glorious HyperV system that has resources out the wazoo, so should have absolutely no reason to run anything slow.  My guess from the outset was that something was timing out, and eventually erroring and after spending some time with FileMon trying to find something, I eventually turned to NetMon and ran a couple of captures.

The first time I found nothing interesting, but the second time I ran it I found a frame referring to an outbound HTTP connection attempt to 131.107.115.28.  A few frames later I saw the same request again, but never saw a response.   I did a quick tracert to that IP address and immediately noticed this :

Tracing route to crl.microsoft.com [131.107.115.28]

CRL is short for "Certificate Revocation List", for more information click on the link.  It turns out for every single signed assembly being loaded, .Net is checking the CRL to ensure that the key used to sign the assembly is legitimate. 

To confirm that it was this problem contacting the CRL server that was the problem, I decided to try a quick fix and added an entry for CRL.microsoft.com into my hosts file, pointing to localhost.  After doing this the problems I'd had were almost instantly resolved.  This however is only useful as a diagnosis, stopping the server from accessing crl.microsoft.com has some long term security risks, and is not advisable. 

Methods to fix the error :

  • Fix the problem with the internet access

If you can't do the above solution :

  • Point CRL.microsoft.com to 127.0.0.1 in a hosts file
  • Untick the "Check for Publishers Certificate Revocation" under security in the advanced Internet Explorer options.
  • Put <generatePublisherEvidence enabled="false" /> into your application's .config file.

I'd really recommend just going with the first one however.

Filed under SharePoint Tagged with , ,

Locking down the people picker to an OU

September 13, 2008 Leave a comment

I thought I'd already covered this, but a quick look through my history shows I didn't.

Locking down a Web Application's people picker to a specific OU is very easy, but not well known.

stsadm -o setsiteuseraccountdirectorypath -url http://WEBAPPLICATIONURL -path "OU=OU,DC=DOMAIN,DC=COM"

Its really that simple, just point the path to the distinguished name of your OU, and you're set!

 

Filed under SharePoint Tagged with ,

Accidental cross domain results in People Picker

September 13, 2008 Leave a comment

For a while I have had an open ticket with a client in regards to odd behavior while trying to create a new site collection.  The odd behaviour follows : "In Central Administration – when trying to create a new site collection and using the people picker to select the Site Collection Administrator, users from other domains are appearing."  Obviously for a hosting provider this is pretty bad behavior to have happen. 

It turns out that there are a very large amount of posts out there for allowing a people picker to search cross forest or cross domain, and very few about how to lock them down or limit them, so here we are.

Like most organisations, we try to lock our active directory environments down as much as possible, and this means removing list view for all authenticated users, except to their own OU.  Having a client be able to view entire organisations that were in different domains or forests is fairly counter-productive and needs immediate resolution.

I had initially thought that this might be fixed with AD permissions, but thats a bridge I'd rather not cross right now, if possible I'd rather fix this up at the people picker level.

There are quite a few commands that might help out here :

  • We could limit the people picker to a specific OU.  That would fix the problem, unless the same OU exists on one of the other domains.
  • We could limit the people picker to only show people within the site collection.  That would work ordinarily, but when creating a new site collection, you may want users that dont exist in a site collection yet.  And besides, which site collection would this pick to find the users in?  Thats one for another time.
  • We could limit the people picker to only one domain.  Bingo.

The peoplepicker-searchadforests command is perfect for this.   So I tap in the following command :

stsadm -o setproperty -pn peoplepicker-searchadforests -pv "domain:domaintosearch.com,USERNAME,PASSWORD" -url http://centraladminurl

Sidenote : The first time I ran this I got an error

Cannot retrieve the information for application credential key.

This was as I had not set an apppassword.  A quick run of "stsadm -o setapppassword -password PASSWORD" fixed this very quickly.  Make sure you keep that password written down, just in case.

Figuring that this is happening at the Central Administration level, I use the Central Admin web application as my target.  This returns a successful result, and so I go off to Central Administration again to test this.  It didn't work, I could still see multiple other domains. 

After a bit of musing, I decided to this feature against the Web Application I was trying to create the site collection into.  Bingo this worked.   Obviously when creating a new site collection,  the Central Administration people picker must inherit the settings from the selected Web Application, this would probably explain that long wait when you select a different Web Application.

Additional Note :

Make sure if you run this, you add the username and password to the command.  If you don't you'll lock your people picker down so well you wont find anyone.

Filed under SharePoint Tagged with ,

Find your 10 top largest mailboxes with Powershell

September 12, 2008 Leave a comment

Heres a quick powershell script to find your ten biggest mailboxes via powershell :

 

Get-MailboxStatistics -server SERVERNAME |sort-object -Property totalitemsize -des |select-object Displayname, ItemCount, totalItemSize -first 10

 

Just replace SERVERNAME with your server, and away you go.  

 

Filed under Exchange Tagged with ,

Anonymous access to Lists in MOSS

August 8, 2008 1 Comment

I spent a little time today trying to figure out why I could not grant anonymous access to a list in Sharepoint.  I tried just about everything I could trying to track down why I was continually being prompted for authentication each time I connected.

My first guess would have been an IIS issue, however I was not getting an IIS 401 error, I was getting a Sharepoint 401 error.  So that ruled out IIS pretty quickly, also the fact that the rest of the site was working anonymously was indicating obviously that it was working.

I really couldn’t understand why when I clearly had anonymous access enabled I was being continually prompted for authentication.  It turns out this is a sharepoint feature (literally here, its a feature, as in XML and in the features folder) called “Lockdown”.

What it appears to do is severely reduce the granularity that permissions can be assigned to objects, such as lists.

To turn it off just run this command :

stsadm -o deactivatefeature -url http://www.website.com -filename ViewFormPagesLockDown\feature.xml

Then go back to your list, and remove the permissions for anonymous access, then readd them.  This will force a reset of the granular permissions that have been defined.

You should now be able to acccess the list with anonymous auth.

Naturally, after finding the problem and the solution, I was able to find a technet article on it.  Tap ViewFormPagesLockDown\feature.xml into goolge and a technet KB comes up immediately.  http://support.microsoft.com/kb/927082

Filed under Uncategorized

← Older posts

Newer posts →