Guest user account

The Guest user is an account which is automatically created when a database is created. This account is used when a login connects to a database without having a user account created in it. By default, guest is disabled, to enable and disable, use the codes below;

-- to enable

GRANT CONNECT TO GUEST

-- to disable

REVOKE CONNECT FROM GUEST

The recommended way of managing guest account is, have it in disabled status. If you have enabled it for some reason, permission can be granted either using graphical interface or through T-SQL.

-- Give data read permission to guest on Customer table

GRANT SELECT ON Sales.Customer TO GUEST

If guest is enabled in AdventureWorks2008 database and above permission is granted, when a login (eg. Jane) connects to AdventureWorks2008, she will be able to read data from Sales.Customer though she has no user account created in AdventureWorks2008.

Template Explorer in SQL Server

Although this is not much popular among DBEs, it is one of the facilities should be used. The Template Explorer gives us various type of templates for constructing objects. For an example, if you need to create a database using T-SQL, the template of the CREATE DATABASE can be taken from the Template Explorer and altered as you need. This definitely speeds up coding though it discourages remembering syntax.

The Template Explorer can be opened using View –> Template Explorer (or Ctrl+Alt+T) menu. Templates are grouped into three: SQL Server, Analysis Server, SQL Server Compact. Templates in a group are categorized too. For an example, if you want to create a database with multiple file groups, CREATE DATABASE ON MULTIPLE FILEGROUPS in DATABASE  category under SQL SERVER group can be used.

Template can be brought into query window by dragging and dropping or double-clicking. Once placed, need to replace parameter values with your values. Although you can manually do it, easiest way is, using Specify Values for Template Parameters window. It can be accessed using Query –> Specify Values for Template Parameters menu.

You can create your own templates and have them in Template Explorer. Start it with creating a folder or add new ones in given folders (categories). This facility is open through right-clicking on the folder in Template Explorer and selecting New. One thing you need to remember is the way of adding parameters. Parameters should be enclosed with “<” and “>” and should contain three parts: Parameter name, type, value. You can have the same for both Parameter name and value, allowing user to replace the “value” with their value. For an example, parameter for database name would be: <Database_Name, sysname, Database_Name>.

sys.database_files: size column and why 128?

It came up while my class was being conducted. We were discussing of finding the size of files attached to a database and available free space of files. As usual I used my sample codes to show few things, this is one of them:

SELECT name, size/128 size FROM sys.database_files

Now, why 128? It did not come into my mind immediately . Result of being a designer.

Size column shows current size of the file in 8KB pages. It is the number of pages that have been allocated. So, why 128? It is for converting to MB. There are 128 8KB pages in 1MB. Here is my altered code;

SELECT name, size/128 SizeInMB

    , FILEPROPERTY(name, 'SpaceUsed')/128 SpaceUsedInMB 

FROM sys.database_files

-- or

SELECT name, size*8 SizeInKB

    , FILEPROPERTY(name, 'SpaceUsed')*8 SpaceUsedInKB 

FROM sys.database_files

Installing and Configuring PowerPivot for SharePoint 2010

Microsoft PowerPivot is slowly becoming an essential technology for Business Users addressing Self-Service Business Intelligence. It is innovated by Microsoft SQL Server team as a part of Microsoft SQL Server 2008 R2 release and comes in two flavors; PowerPivot for Excel and PowerPivot for SharePoint. I have already written a post on Excel add-in (Creating Self-Service Business Intelligence dashboard with PowerPivot – Part I) which discusses dashboard creation with Excel and PowerPivot. This post discusses about Installing PowerPivot for SharePoint 2010 with following sections;

• Excel and SharePoint
• Installing PowerPivot for SharePoint 2010
• Error: Could not load assembly
  ‘Microsoft.AnalysisServices.SharePoint.Integration.dll’
• SQL Server Analysis Services Installation – VertiPaq
• Deploying PowerPivot solution packages to Web Application
• Other Services required
• Creating PowerPivot Service Application
• PowerPivot Feature Integration for Site Collections
• Configuring PowerPivotUnAttendedAccount for Data Refresh

Excel and SharePoint
Why two technologies? We can consider that Excel is for authoring PowerPivot applications. It allows to create an application with one or many data sources and then facilitates data analysis in a multi-dimensional structure. It does not need a constant connection to used data sources, everything will be stored inside Excel Workbook and if want, data can be refreshed. SharePoint can be considered as a hosting environment for PowerPivot applications. We usually go for SharePoint if collaboration, sharing and reporting are required.

Installing PowerPivot for SharePoint 2010
There are two components need to be installed. PowerPivot System Service and Analysis Services in VertiPaq mode which is a local, in-process Analysis Services engine. Let’s start with Analysis Services.

Installing Analysis Services for PowerPivot is straight forward. I have already set up my SharePoint 2010 farm. It uses SQL Server 2008 R2 that exist in same box. Note that my installation contains full SQL Server product suite, including SQL Server Analysis Services, hence new instance of Analysis Services for PowerPivot will be a named instance. There is a known issue you might face if SQL Server installation is already exist. It is;

Could not load assembly ‘Microsoft.AnalysisServices.SharePoint.Integration.dll’
You get this error if you have a prior installation of SQL Server 2008 R2. Unfortunately it comes at the completion of the installation.

Solution for this is simple. Create a text file as below and save it as Setup100.exe.config.

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

  <runtime>

    <disableCachingBindingFailures enabled="1" />

  </runtime>

</configuration>

Then move the file into %ProgramFIles%\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\x64. Once the file is placed, you need to start the installation again. For more information, see this: http://support.microsoft.com/kb/2261507 .

SQL Server Analysis Services Installation – VertiPaq
Before starting the installation, make sure that a domain account is created for new Analysis Services instance for running the service. In my case, newly created account for this is PowerPivotAnalysisServices.

Now run the SQL Server 2008 R2 setup. It would be better to run the setup as Administrator (Right click on Setup.exe and select “Run as Administrator”). If you have configured more than one server for the farm as Application Servers, the installation should be done for all servers (Generally we set up two servers one for WFE and another for applications. This installation should be done only on Application Servers).

Once the installation is started, select New installation or add shared features as per the screen below.

Continue with screens until you get a screen which allows you to select SQL Server PowerPivot for SharePoint. Select Existing Farm for Add PowerPivot for SharePoint to (Note that assumption here is Farm is already configured).

Next screen is Feature Selection. It is a read-only screen, you will not be able to change anything on it. Continue with the wizard until you get Instance Configuration screen. You will notice that the instance has already been named ad POWERPIVOT. Do not change it, continue the wizard.

At the Server Configuration screen, set newly created domain account and its password for SQL Server Analysis Services. When you have Analysis Services Configuration screen, make sure that accounts that need to be administrators for Analysis Services are added.

Everything is set now, continue the wizard and complete the installation. Once it is done, go to SharePoint Central Administration –> System Settings –> Manage Servers in this Farm. It shows all the servers in the farm and services running. If you see SQL Server Analysis Services and SQL Server PowerPivot System Service, your installation is successful.

Deploying PowerPivot solution packages to Web Application
Next part is deploying PowerPivot solution packages to Web Applications (to WFE Servers). There are two solution packages. Installation we performed earlier deploys solution packages for Central Administration but not for our web application. Go to SharePoint Central Administration –> System Settings –> Farm Management –> Manage Farm Solutions. You should see two solutions;

• powerpivotfarm.wsp is deployed globally, no need to deploy again.
• powerpivotwebapp.wsp, you can see that it has been deployed to my default web application (http://w2008r2-sp:10000). This is the solution we need to deploy manually to Web Application which suppose to hold PowerPivot data.

Let’s deploy powerpivotwebapp.wsp to a web application. Click on powerpivotwebapp.wsp. It opens Solution Properties. Click on Deploy Solution. Select the web application from the drop-down you need to use under Deploy to? and click OK. Once the Solution Properties page is open, make sure that added web application is listed as below.

Other Services required
Next step is starting all other necessary services required by PowerPivot. Make sure following services are running;

• Excel Calculation Service
• Secure Store Service
• Claims to Windows token Service
• SQL Server Analysis Services
• SQL Server PowerPivot System Service

These services are listed under SharePoint Central Administration –> System Settings –> Manage Services on Servers.

Creating PowerPivot Service Application
This helps to establish the communication between WFE and PowerPivot service via PowerPivot Service Application Proxy. Let’s create it. Go to SharePoint Central Administration –> Application Management –> Manage Service Applications. Click on New and select SQL Server PowerPivot Service Application.

With Create New PowerPivot Service Application window, set the name as PowerPivotServiceApplication. Do not use any existing pool, create a new one and assign a domain account for it. Set the database server and database name then. Finally select the checkbox Add the proxy for this PowerPivot service application to the default proxy group. Click OK to create it.

PowerPivot Feature Integration for Site Collections
This is appeared under Site Collection Features and must be activated for each of the site collections. Go to your site collection and click on Site Actions –> Site Settings –> Site Collection Administration –> Site Collection Features. When the page open, find PowerPivot Services feature integration for Site Collections and activate it.

Configuring PowerPivotUnAttendedAccount for Data Refresh
PowerPivot unattended account is used for refreshing data in the workbook if user credentials are not exist with the workbook. This account is stored in SharePoint Secure Store Service. In order to continue, you need to make sure that Secure Store Service is up and running, and master key is created. I have already configured Secure Store Service for PerformancePoint. If you have not, see this post for configuring it: http://dinesql.blogspot.com/2010/07/configuring-performancepoint-2010.html.

Let’s open the Secure Store Service Application. Go to SharePoint Central Administration –> Application Management –> Manage Service Applications. Click on Secure Store Service (or select it and click on Manage) link;

Secure Store Service uses Target Applications for holding accounts. In my case, I have an application set up for PerformancePoint and that is what has been listed. If you have note, nothing will be listed. Once a Target Application is created for PowerPivot, it has to be associated with PowerPivot Service Application. Let’s start creating the Target Application. Click on New for opening Create New Secure Store Target Application. Set ID and name for the application. Appropriate ID and name would be PowerPivotDataRefresh and Power Pivot Data Refresh. Enter a valid email for Contact Email and select Individual for Target Application Type.

Next screen for specifying fields (columns) for the application. Accept the default as we need only user name and password and continue.

Next screen for setting admins for the application. Make sure that you have added the account which is used for PowerPivot Service Application pool. In addition to that, any account need to be an admin can be added. Continue the wizard and complete. Finally you are back on Secure Store Service. You should see the newly created application listed now.

Next is setting credentials for PowerPivotDataRefresh application (or PowerPivot unattended account). Select the check box next to newly created application and click on Set Credentials button. It opens the Set Credentials for Secure Target Application (Individual). Settings for this screen will be;

• Credential Owner: This is the owner of the credential. It will be used by PowerPivot unattended account but this is not the account used as the unattended account.
• Windows user name and password: This is the actual account used as the unattended account.

Click OK. Now we need to associate the application with PowerPivot Service Application. Go to SharePoint Central Administration –> Application Management –> Manage Service Applications. Find PowerPivot Service Application and click the link (or select it and click on Manage button). This opens PowerPivot Management Dashboard. Click on Configure Service Application Settings in Actions frame. It opens PowerPivot Settings. Find PowerPivot Unattended Data Refresh Account input box under Data Refresh. Type the ID of the application created in Secure Store Service, in my case, it is PowerPivotDataRefresh.

Done. To complete the process, few setting have to be done in Excel Services.

Settings in Excel Services
Go to SharePoint Central Administration –> Application Management –> Manage Service Applications. Find Excel Services Application and open it. It opens Manage Excel Services Application. Click on Trusted File Locations.

You will see that SharePoint has been added as a Trusted Location (Address is http://). In order to access a workbook by both Excel and PowerPivot services, this is required.

Get the menu of http:// and select Edit. Change following items in Edit Trusted File Location.

• Workbook Properties: Maximum Workbook Size. Set as you want. Note that maximum file size accepted by SharePoint for both Excel and PowerPivot is 2GB.
• External Data: Select Trusted data connection libraries and embedded under Allow External Data.

Click OK to save settings. Now, You should be able to upload an Excel file that contains PowerPivot data into the web application and see.

Will discuss more on this in another post.