Custom SharePoint Reports using CAML

I've long wondered how the default SharePoint Site Collections reports are created, and finally I came across an article on how this is done. It seems the only thing required, is a CAML query.

The following article from the MS ECM blog explains how to create your own reports with a number of examples using CAML queries. Check it out.

http://blogs.msdn.com/ecm/archive/2006/11/15/report-rapport.aspx

A summary of how to do it:

Site Actions > Manage Content and Structure > Content and Structure Reports > New item and fill in the following information:

• Report Title
• Leave both Resource Ids empty, they're for internal use (?)
• CAML List Type - You can specify a list template here if you only want your report to search through a specific type of list. For example, the out of the box "My Tasks" report specifies in this field to ensure the report only queries the My Tasks list. This SDK article has a list of these ServerTemplate values for each SharePoint list type.
• CAML Query - The CAML query for the report
• Target Audience - if you want the report to be targeted to a particular audience
• Report Description
  

Setting up a SharePoint Development Environment

Farm (?) ish environment
http://weblogs.asp.net/erobillard/archive/2007/02/23/build-a-sharepoint-development-machine.aspx

Single Machine Environment
http://blah.winsmarts.com/2007-10-A_Single_Developers_SharePoint_2007_Development_Environment.aspx

Integrating ASP.NET applications and SharePoint 2007

Link Dump

SharePoint 2007 - Built on ASP.NET 2.0

MOSS 2007 - Creating an ASP.NET application in the _layouts directory using Visual Studio 2005

Application Development on MOSS 2007 and WSS 3.0

ASP.NET 2.0 Web Part Infrastructure and SharePoint 2007

Write Custom WebParts for SharePoint 2007

Using ASP.NET Web Part into MOSS 2007 Web Site

SharePoint databases file location

Typically, for performance, backup and various other reasons, you'll want that the SharePoint databases are not stored in the default locations. Unfortunately, the Central Administration does not allow you to specify the actual file locations to use when it creates the databases.

So to be able to specify different locations for the mdf / lsf files, you'll need to create the databases manually beforehand and then when the creating the Configuration database, creating web application (or other) databases, specify the location / name of the database created.

If you've already created your databases, a simple way to shift them to the correct locations is to stop the SharePoint services, detach the database, move the files to the correct locations, and then re-attach the databases. The restart the SharePoint services. As long as the name of the database doesn't change, SharePoint won't notice that the actual file locations have changed.

The only database for which I couldn't create the database beforehand is the AdminConfig database. Please comment if you know at which point during the setup this database is created, and whether one can create this database beforehand, and point to it.

SharePoint Performance Tuning

When you are thinking in terms of large server farms, you'll need to spend some good time tuning your SharePoint setup for performance.

Joel Oleson (who else?) has a great post on performance tuning of the application pool settings.

Although some people beg to differ on some of these settings. As always, you should always test and tune and see what works best for you.

SharePoint Look and Feel for ASP.NET applications

Ever wondered how to get an ASP.NET application to look like SharePoint? This blog shows you how to do this.

Approach 1

Approach 2

I would recommend Approach 2 rather than approach 1

User Accounts and Rights required for a MOSS Installation

Clayton has posted a great article about accounts required for MOSS installation:

"Installing MOSS 2007 in a farm environment requires a few dedicated accounts and can be quite a confusing process. I came across a couple of great resources so I thought I would save you the heart ache and post them here. "

http://claytonj.wordpress.com/2007/04/23/moss-2007-setup-accounts/

Scroll down for table (something is screwed up with this post :\ )

Account	Purpose	Scope	Used By	Needed	Requirements
Setup User	User account that is used to run setup on each server.	Farm	Person installing	Setup	Member of the administrator group on each Web front-end (WFE) server and application server computer in the farm. Member of the following SQL Server groups with SQL Security administrator and database creator rights on SQL servers.
SQL Server Service	This is the security context used By Central Administration for creating databases and other SQL configurations.	Farm	MSSQLSERVER, SQLSERVERAGENT	Setup	Member of the administrators group on each server on which setup runs, administrators group on each SQL Server computer, database system administrator, and member of the SQL security administrator and database creator SQL Server groups.
Server Farm	This account is also referred to as the database access account.	Farm	Central administration site application pool identity	Setup	Member of administrators group on each WFE server and application server computer in the farm with SQL security administrator and database creator rights on SQL Servers. Database Owner (DBO) for all databases and additional permissions on WFE server and application server computers are automatically configured for this account when SharePoint is installed.
SSP App Pool		App	SSP App Pool Identity	SSP Creation	No configuration is necessary. The following permissions are automatically configured for this account when SharePoint is installed: DBO for the Share Service Provider (SSP) content database, read/write permissions for the SSP content database, read/write permissions for content databases for Web applications that are associated with the SSP, read permissions for the configuration database, read permissions for the central administration content database, and additional permissions on WFE server and application server computers
SSP Service Account	Used to run timer jobs and for interserver communications.	Farm	SSP Timer service; SSP Web services	SSP Creation	Same as SSP App Pool Account
Windows SharePoint Services Search	Used as the service account for the Windows SharePoint Services Search service. There is only one instance of this service, and it is used by all SSPs.	Farm	Windows SharePoint Services 3.0 Search service	SSP Creation	Must be a domain account, but must not be a member of the farm administrators group. Permissions automatically configured for this account when SharePoint is installed include the following: read/write permissions for content databases for Web applications, read permissions for the configuration database, and read/write permissions for the Windows SharePoint Services Search database
Search Default Content Access Account	The default account used by a specific SSP to crawl content. It is used when an account is not specified for a content source.	App	Windows SharePoint Services 3.0 Search service	SSP Creation	Must be a domain account, but must not be a member of the farm administrators group. It requires read access to external or secure content sources that you want to crawl using this account. Additional permissions for this account are automatically configured when SharePoint is installed.
Search Specific Content Access Account	This is an optional account that is configured to replace the default content access account to crawl a specific content source.	Rule	Windows SharePoint Services 3.0 Search service	Create a new crawl rule	Read access to external or secure content sources that this account is configured to access.
User Profile and Properties Content Access Account	Account used to connect to a directory service, such as Active Directory, a Lightweight Directory Access Protocol (LDAP) directory, Business Data Catalog (BDC) application, or other directory source and used to import profile data from a directory service. Note: If no account is specified, the Search Default Content Access account is used. If the Search Default Content Access account does not have read access to the directory or directories that you want to import data from, you will need to specify a different account. You should plan for one account per directory connection.	App	Profile Import	SSP Creation	Read access to the directory service. For an Active Directory service connection that enables Server Side Incremental, the account must have the Replicate Changes permissions for Active Directory directory services provided by Windows 2000 Server. This permission is not required for Windows 2003 Active Directory. Manage user profiles right. View rights on entities used in Business Data Catalog import connections.
Excel Services Unattended Service Account	Excel Calculation Services uses this account to connect to data sources that require user name and password strings for authentication. The SSP App Pool account is used if none is specified. For security, plan to use a low-privileged account that does not have the database privileges of the SSP App Pool Account.	App	Excel Services Service	SSP Creation	Read/write access to the Excel data sources.
App Pool Identity	Used to access content databases associated with the Web application. Plan one for each application pool.	App	Web Applications	App Pool Creation	No configuration is necessary. SQL Server privileges that are automatically assigned to this account are member of Database Owners Group for content databases associated with the Web application, read/write access to the associated SSP database only, and read permission for the configuration database. Additional privileges for this account on WFE servers and application servers are automatically configured by SharePoint.

Uploading documents to SharePoint 2007 via a Webservice

Web Service for uploading documents into SharePoint

http://blogs.ittoolbox.com/km/sharepoint/archives/custom-web-service-for-uploading-files-to-sharepoint-13217

Securing SharePoint 2007

Very nice post on securing your SharePoint server, especially if it going to be exposed to the Internet. As taken from the Joel Oleson's blog.

More than 25 Tips to Lockdown Your SharePoint Environment

This is not fully comprehensive, but gets you started down the right track. Some of these may not apply.

1. Configure Firewall Rules lock down to most restrictive w/ acceptable level of usability (i.e. outbound HTTP)
2. Secure client communication with trusted SSL certificates (128bit HTTPS)
3. Use IPSEC Require mode between servers (Policy) Especially for secure communication between servers and DCs * Be careful with NLB. You can do also this on your Intranet with request mode, I recommend not using client require mode for non windows and legacy clients (MAC/Unix/Win 98)
4. Enable Kerberos Authentication (Intranet) *Careful with NLB
5. SQL SSL encrypted Traffic + Non Standard Port
6. Configure Central Admin on public internet facing servers on non routable IP (Index Server) Configure 2 factor and double hop access. i.e. 2 Factor auth VPN to TS to administration server to administer farm with specific IP rules to TS box.
7. Restrict IP Traffic on Central Admin and SSP App Pools (IIS)
8. Configure Deny Policies (Not Auth Users) on Content/Admin Web Apps for Applicable Groups/Domains, configure deny policy for Server Admins on all web apps (use Special non privileged accounts for administration of SharePoint farm)
9. Configure ISA Secure Publishing (or reverse hosting) better than Router ACLs (Rejects Invalid Requests and Verbs)
10. Configure at least 1 DMZ aka 2+ Firewalls/Interfaces between corp and publicly addressable Internet
11. Test/Run Windows R2 Server SCW (Security Configuration Wizard) (Custom Template)
12. Consider Basic over SSL alternatives… SSL with FBA with Expiring Cookies
13. Configure and enforce Auditing Policies on Site Collections (Solution Deployment & Timer job), Enable WSS & MOSS Usage Reporting
14. Remove unused server side extensions (i.e. ASP, HTA, IDX, etc..) and unused .NET extensions and verbs (Debug)
15. Disable the Web Services that are not used. i.e. SSP & Central Admin
16. Ensure that Any Auth traffic is secured between DC & Servers (IPSEC)
17. Ensure inbound email services are configured for auth users, and lock down SMTP/Outbound to allow only specific IPs
18. Stop unused services (this will require testing)
19. Configure Site Collection Quotas
20. Increase blocked file types to include non approved content
21. Install Antivirus Protection (Recommended FrontBridge with Inbound scanning and regular scan of all at a minimum, filter content as well)
22. Monitor for suspicious activity & Review #Failed Login Attempts Security Logs – Use Black Ice or other intrusion Detection software on all servers in the farm with reporting and alerting
23. Lock down SSC (Self Service Creation) to few trusted Support/Service groups
24. Run service accounts with domain accounts, run SSP and Central admin with different service accounts (ensure these accounts have no special rights)
25. Lock down SQL with relevant lockdown/hardening guides, remove server admin role and rights

TechNet: Plan Security , Plan Server Hardening (Lockdown) - More detail on locking down SQL ports, securing the web services (from the file system), RPC end point for DCOM communication (excellent recommendation), list of SharePoint NT services.

1. Configure and/or lock down Excel safe locations. This will give you more control over calc perf.
2. Consider Extranet Mode (limited UI mode/prevents SOAP interaction and depricates UI)
3. Remove people picker AD lookups on extranet (stsadm -o setproperty peoplepicker-nowindowsaccountsfornonwindowsauthenticationmode)
4. Secure LDAPs (636) over SSL
5. Lock down web services to the service accounts that need them (going to the files on the file system and changing file system security)
6. Ensure outbound is restricted to only what is needed. Need to consume (outbound) XML web services or RSS feeds?
7. Configure RPC DCOM server communication end points to static high ports
8. Ensure Web Front Ends can talk to all Query boxes (even if they are on other web front ends)
9. Disable Anonymous auth throughout the farm (off by default)
10. Ensure policy against using authenticated users is well communicated and policed
    Thinking about email archiving and email enabled lists... I'd recommend not using it when internet/extranet facing. Configuration allowing anonymous email is in the site collection/list level.
11. I'd also recommend against anonymous blog comments, there are way too many spammers out there. There isn't any approval mechanism without enabling workflows and there are a lot of potential opportunities for spammers. Overall anonymous contribution scenarios should be highly thought out.
12. Web Based Forms and Forms Server does have some great scenarios, but if you aren't using it, lock down the anonymous posting services (disabled by default)
13. Considering SQL auth to the SQL box in a separate locked down area? I'm a long time fan of getting the data/SQL in a separated isolated behind a firewall with no outbound holes and no service from the public DMZ accounts. I think I already mentioned it, but I'm a fan of non standard SQL ports even more than SQL traffic over SSL. If the traffic is bad who cares if it's encypted bad traffic. Maybe I'm not the only one who spent some time with slammer.
14. Your SharePoint farm should be on Non Routable IPs, which goes to say... not directly in DNS.

BI and KPIs in Analysis Services 2005

Nice introductory article to Business Intelligence and KPIs in Analysis Services 2005

http://www.databasejournal.com/features/mssql/article.php/10894_3604206_1