Import ULS log files to SQL Server database for Sharepoint 2010

Sometime you may need to import ULS log files to SQL server . It can be done with several ways by using SQL Server import tools or import from an Excel file to SQL  server. Or you can use fallowing free open source windows application tool. With this tool it can be done with more quickly . By multy-threading and bulk insert architecture feature provides you that ULS logs contains total 1.500.000 row  can be upload in  apprx. 2 minutes. (as you know it depends on your configuration and hardware)

Than you can query SQL tables as you wish : Fallowing query will return execution time over 1 sec for SPMonitoring event records.

select * from WFE1
where [ExecutionTime] > 1000 and [Eventid] =’b4ly’
order by [ExecutionTime] desc

“All These source codes are freeware and is provided on an “as is” basis without warranties of any kind, whether express or implied, including without limitation warranties that the code is free of defect, fit for a particular purpose or non-infringing. The entire risk as to the quality and performance of the code is with the end user

For Download  ULStoSQLImporter v1.0  for Sharepoint 2010:


Unable to create DataSource with using Excel Service in DashBoard Designer

The symptoms are when you lunch PerformancePoint DashBoard Designer and try to create a new Data Connection like
Right Click Data Connections and select “New DataSource” -> Excel Services and Click Ok. you should have get an error like
“An unexpected system error has occured.Additional details have been logged for your administrator.”

In ULS Logs you can see the fallowing error:

An unexpected error occurred.  Error 8205.  Exception details: System.Web.Services.Protocols.SoapException: You do not have permissions to open this file.
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at Microsoft.PerformancePoint.Scorecards.Client.ExcelService.OpenWorkbook(String workbookPath, String uiCultureName, String dataCultureName, Status[]& status)
at Microsoft.PerformancePoint.Scorecards.DataSourceProviders.ExcelServicesDataSourceProvider.GetCubeMetaData(Boolean extendedMetadata)
at Microsoft.PerformancePoint.Scorecards.Server.PmServer.GetCubeMetaDataForDataSourceHelper(DataSource dataSource, Boolean extendedMetadata)
at Microsoft.PerformancePoint.Scorecards.Server.PmServer.GetCubeMetaDataForDataSource(DataSource dataSource)

The cause is Anonymous Authentication is not supported if you need to connect Excel Services by DashBoard Designer.

“You cannot connect to an Excel Services data source when the site or library containing the workbook you are trying to connect to is set to Anonymous Access.”

For solution
1) Disable “Anonymous Authentication” from IIS Management Console -> Authentication -> Anonymous Authentication

2) If you still need anonymous authentication than extend the site without Anonymous Authentication.

Using Distribution Groups in SharePoint for securing SharePoint securables is not possible.

Using Distribution Groups in SharePoint for securing SharePoint securables is not supported. So if you checked fallowing article on SharePoint side,

“In Active Directory Domain Services (ADDS), the following groups are commonly used to organize users:

  • Distribution group A group that is used only for  e-mail distribution and that is not security-enabled. Distribution groups cannot be listed in discretionary access control lists (DACLs), which are used to define permissions on resources and objects.
  • Security group A group that can be listed in DACLs. A security      group can also be used as an e-mail entity.

You can use security groups to control permissions for your site by adding security groups to SharePoint groups and granting permissions to the SharePoint groups. You cannot add distribution groups to SharePoint groups, but you can expand a distribution group and add the individual members to a SharePoint group. If you use this method, you must manually keep the SharePoint group synchronized with the distribution group. If you use security groups, you do not need to manage the individual users in the SharePoint application. Because you included the security group instead of the individual members of the group, ADDS manages the users for you.”

You can not use any Distribution Group for providing  permission on SharePoint securables. Because distribution groups cannot be listed in discretionary access control lists (DACLs), which are used to define permissions on resources and objects.SharePoint is using this ACL objects to make security operations. There is no way or workaround for using Distribution Groups in SharePoint for securing SharePoint objects.

So we have some other options.

1)      As mentioned in above article :  you can expand a distribution group and add the individual members to a SharePoint group and using this SharePoint groups for securing objects.

2)      Changing Distribution Groups as Security Group in AD and use it in SharePoint. (I am suggesting this step because AD Groups will provide more gain on performance issues)