Category Archives: SQL Server

Install SharePoint 2013 Public Beta on Windows Server 2012 RC – Part III – Installation of SQL Server 2012 on Windows Server 2012 RC

With the new version of SharePoint almost in Public Beta (I have hopes it will be this month) it is time to show how you can install it on the Release Candidate of Windows Server 2012.

Since the new version of SharePoint isn’t there yet this series of blog post will continue with the Installation of SQL Server 2012 on Windows Serve 2012 RC. After this post the following post will follow:

Part IV – Installation of the Prerequisites on Windows Server 2012 RC
Part V – Installation of SharePoint 2013 on Windows Server 2012 RC
Part VI – Configuring SharePoint 2013 on Windows Server 2012 RC

So let us begin with booting up the Virtual Machine we got this far and add the ISO of SQL Server 2012 to the machine.

When continuing from the previous blog post the “Server Manager” will be opened. Close the Server Manager and open “Active Directory Users and Computers” to create the service accounts we need for the installation of SQL Server:

To open Active Directory Users or Computer do the following:

Go to the Start Menu by using the Windows button or by going to the low left corner with your mouse.
With the Start Menu open select “Active Directory Users and Computers”.

Open the Users OU and use your other mouse button to add users:

When you have the “New Object – User” window open create the following users:

First name	Last name	User Logon name	User logon name
SQL	DB Engine	svc_sqldbengine	svc_sqldbengine
SQL	Reporting	svc_sqlreporting	svc_sqlreporting
SQL	Agent	svc_sqlagent	svc_sqlagent

Make sure you select the options:

User cannot change password.
Password never expires.

This will make sure the password of the accounts will not be reset or be disabled.

When the accounts are created it is time to start the installation of SQL server. Run the Setup wizard that’s on the SQL Server 2012 CD / DVD.

When you run the setup the SQL Server Installation Center will open. Press the Installation option on the left and then select “New SQL Server stand-alone installation or add feature to an existing installation”.

The installation setup will start with running a few startup rules. When you have passed all of the rules click Ok to continue with the installation.

A new window will open and ask for your product key. If your product key hasn’t been filled in yet fill it in and continue by clicking next. On the next step accept the license terms and click next again.

The wizard will now start installing the required files for the installation and will start up another wizard. This wizard will automatically check the conditions of the server.

Most of the the the wizard will give two warnings:

Computer domain controller: You can ignore this warning because we will use this pc as a development pc. For production servers never install SQL on a domain controller this is for security reasons.
Windows Firewall: This warning states that the Firewall is blocking SQL server. Because the machine will be a one machine farm we can ignore this warning. When you are creating a farm with multiple pc’s you will have to open the SQL server port. By default this is: 1433.

Because we can ignore the warnings we will click next and proceed to the next step. The next step will give us the option to select the installation type. You will have three options:

SQL Server Feature Installation: This option will give you the possibility to install specific features of SQL Server.
SQL Server PowerPivot for SharePoint: This option will give you the possibility to install PowerPivot for SharePoint.
All Features with Defaults.

We will select the first option: “SQL Server Feature Installation” because we want to install SQL server and select the features we want. We do not want to install everything because this can downgrade the performance of our virtual machine.

In the next step we can select the features we want to install. We will select the following features:

Database Engine Services.
- SQL Server Replication
- Full-Text and Semantic Extractions for Search
- Data Quality Services
Management Tools – Basic
- Management Tools – Complete

At the moment we will not install “Reporting Services Add-in for SharePoint Products” because we do not have a instance running of SharePoint server. Click next to continue with the installation.

The wizard will perform a couple of checks based on the features you have selected. If you pass all of the Rules click next to continue to the Instance Configuration. On the instance configuration screen you have the option to change the name of the SQL Server Instance. We will keep the default settings and click “next” to continue.

The Disk Space Requirements Step will show you if you have enough hard drive space to install SQL server if you pass this step click next to continue. This will bring us to the “Server Configuration” step. In this step we will have to fill in the accounts and passwords for the services.

Fill in the accounts that we have created in the first step:

Service	Account Name
SQL Server Agent	svc_sqlagent
SQL Server Database Engine	svc_sqldbengine

When you proceed you will have the option to select the authentication mode off SQL Server and were the data files are saved. For now we will leave it to the default settings and we will only fill in the SQL Server Administrators. I always fill in the BUILTIN\Administrators group but because were are installing it on a domain controller it cannot find this group that’s why we will just add the current user.

Now that we have configured these last steps we are almost finished with the installation. The next step can be ignored and you can simple click next. The wizard will again check a few rules, when you pass these rules click next to go to the confirmation screen.

In the confirmation window check every setting and click “Install” to start the installation.

When the installation is complete you can close the wizard. You can then op SQL Server management studio by following these steps:

Go to the Start Menu by using the Windows button or by going to the low left corner with your mouse.
With the Start Menu open select “SQL Server Management Studio”.

With SQL Server installed we can start the Installation of SharePoint 2013. We will start this fourth part of the blog series when the SharePoint 2013 Public Beta is released.

Configure Kerberos authentication

Leave a reply

A few weeks ago I tried to set up a SharePoint farm that uses Kerberos authentication. I always thought it was just a setting in SharePoint but infect it is much more than that.

What is Kerberos

Kerberos is a computer network authentication protocol, which allows nodes to communicate over a non secure network to prove their identity to one another.

Kerberos makes use of a trusted third party for the authentication, termed a Key Distribution Center (KDC) which consists of two parts: an Authentication Server (AS) and a Ticket Granting Server (TGS). Kerberos works on the basis of tickets which serve to prove the identity.

For communication between two entities, the KDC generates a session key which they can use to secure their interaction. A KDC runs on every domain controller as a function of the Active Directory Domain Services (AD KS).

Why use Kerberos

There are many reasons why u should use Kerberos authentication rather than the default NTLM. The main reason is because it is more secure than NTML. Besides this reason you should also use it to get around the “double hop” (http://support.microsoft.com/kb/329986) authentication issue. The most common scenarios for the double hop issue with SharePoint are around Excel services and Data Connection.

Setting up Kerberos authentication

To make use of Kerberos nothing needs to be changed if the domain controller is set-up correctly. Kerberos clients are configured to request ticket-granting tickets (TGT’s) of the Kerberos Key Distribution Center automatically. If the ticket is received successfully the Kerberos client saves the ticket on the locale machine.

One of the first things you should consider when you want to use Kerberos authentication is which accounts you will be using for your services and web applications. Those accounts will have to get a Service Principal Name. The servers you will be using will have to allow delegation.

To trust a computer for delegation

Click Start, point to Program Files, point to Administrative Tools, and then click Active Directory Users and Computers.
Navigate to the computer account that needs to be set, for example, Active Directory Users and Computers/<domain name>/Computers/<computer name>.
Double-click the computer name in the list on the right pane. On the Account tab, select the Account is trusted for delegation check box and then click OK.

If every server in the farm is trusted for delegation you can start creating SPN’s (Service Principal Names) for the accounts you will be going to use for SQL Server and the SharePoint farm.

Service Principal Names

Service principal names are associated with the security principal (user or groups) in whose security context the service executes. Service Principal Names are created with a specific Syntax.

Service Principal Name syntax:

The basic syntax of service principal name is as follows:

[service type]/[instance name]:[port number]/[service name]

The elements of the syntax have the following meaning:

service type: Type of service, such as “http” for the http protocol.
instance name: Name of the instance of the service. Depending upon the service type, it is either the name or IP address of the host running the service.
port number: Number of the port used by the service on the host, if it is different from the default for the service type.
service name: Name of the service. The name can be the DNS name of a host, of a replicated service, or of a domain; or it can be the distinguished name of a service connection point object or of an RPC service object.

Note: When you use Kerberos authentication with SharePoint you have to know that Internet Explorer does not send the port number with the service principal name. For example for this address https://intra.motion10.com:443 the SPN will be http/intra.motion10.com.

How to create a SPN in Active Directory

To create a SPN for an account you have to follow the following steps.

Create the SPNs for your SQL Server service account

Log on to your Active Directory domain controller using the credentials of a user that has domain administrative permissions.
In the Run dialog box, type ADSIEDIT.MSC.
In the management console dialog box, expand the domain container folder.
Expand the container folder containing user accounts, for example CN=Users.
Locate the container for the SQL Server Service account, for example CN=mosssqlsvc.
Right-click this account, and then click Properties.
Scroll down the list of properties in the SQL Server Service account dialog box until you find servicePrincipalName.
Select the servicePrincipalName property and click Edit.
In the Value to Add field, in the Multi-Valued String Editor dialog box, type the SPN for example MSSQLSvc/sqlserver:1433 and click Add.
Click OK on the Multi-Valued String Editor dialog box, and then click OK on the properties dialog box for the SQL Server service account.

Important: You cannot give two accounts the same Service Principal Name. This will cause the authentication to fail or fall back on NTLM if it is possible.

After you created the Service Principal Names you have to trust the accounts for delegation:

Trust accounts for Delegation

Windows server 2003 Active Directory

On the Domain controller, click Start, point to Programs, point to Administrative Tools, and click Active Directory Users and Computers.
Under your domain, click the Users folder.
Under your user account, click Properties.
Navigate to the “Delegation” tab.
Choose “Trust this user/computer for delegation to any service (Kerberos)“.

Windows server 2000 Active Directory

On the Domain controller, click Start, point to Programs, point to Administrative Tools, and click Active Directory Users and Computers.
Under your domain, click the Users folder.
Under your user account, click Properties.
On the Account tab, select the Trusted for delegation check box.
Under the account that you are trying to delegate, clear the Account is sensitive cannot be delegated check box.

Note: If you do not have the delegation tab in the user properties you will have to use the steps of the Windows server 2000 Active Directory. This is because Windows 2003 Active Directory can run a in a 2000 mode.

Configure Kerberos for SQL server

Kerberos for SQL server has to be configured before you can install SharePoint Server 2007. This means we have to create a SPN for the SQL service account:

Service type: MSSQLSvc
Instance name: SQLSERVER / SQLSERVER.mydomain.com
Port number: 1433 (default SQL communication port)

The SPN’s will be:

MSSQLSvc\SQLServer:1433
MSSQLSvc\SQLServer.mydomain.com:1433

Testing the Kerberos authentication for SQL Server

If you have configured the SPN’s for the SQL service account you can test if it works by following the following steps.

Run SQL Server Management Studio in another server in the domain.
Connect to you SQL Server.
Check if Kerberos authentication is used by running the event viewer on your SQL host server and examine the Security log. In this log you should have a Success Audit that has used the Kerberos protocol.

If there isn’t a Success Audit that uses the Kerberos protocol you can check the following:

Is the SPN in active directory correct?
Do I have two accounts in Active Directory that have the same SPN?

Configure Kerberos for your Web Applications

If you want your web applications to make use of Kerberos you have to create Service Principal Names for the accounts that will used for running the web applications.

If have written down a few examples of SPN’s of several addresses of web applications.

Internet address	Service Principal Name
http://intra.motion10.com	http/intra.motion10.com
https://intra.motion10.com	http/intra.motion10.com
http://mossadmin:43433	http/mossadmin
http://mysites.intranet.com	http/mysites.intranet.com

Testing the Kerberos authentication for the web application authentications

If you have created SharePoint web applications that use Kerberos authentication, you are ready to test your configuration by following the following steps:

Start internet explorer and navigate to the web application that has Kerberos authentication enables and login.
On the SharePoint server open the event viewer and examine the Security log. In this log you should have a Success Audit that has used the Kerberos protocol.

Configure the SSP Infrastructure for Kerberos

For the SSP infrastructure to use Kerberos you have to follow the following steps:

Register a new SPN for the SSP Service.
Configure the SSP infrastructure to use Kerberos

Register a new SPN for the SSP Service

For the SSP infrastructure to use Kerberos a few new SPN’s need to be created. This is because the SSP infrastructure runs on every server in the SharePoint farm. It is also bound to the following ports TCP 56737 en TCP 56738 and the SPN also needs to include the name of the Shared Service Provider.

So you have to create a separate SPN for each server in the farm. In the following table you will see a few examples.

Computer Name:

MOSSQUERY

Name Shared Service Provider

SSP

MSSP/MOSSQUERY:56737/SSP

MSSP/MOSSQUERY:56738/SSP

Computer Name:

MOSSQUERY

Name Shared Service Provider

SharedServices4

MSSP/MOSSQUERY:56737/SharedServices4

MSSP/MOSSQUERY:56738/SharedServices4

Computer Name:

MOSSCRAWL

Name Shared Service Provider

SharedServices1

MSSP/ MOSSCRAWL:56737/ SharedServices1

MSSP/ MOSSCRAWL:56738/SharedServices1

Configure the SSP infrastructure to use Kerberos

After you created the Service Principal Names for the SSP Infrastructure you have to inform the SSP infrastructure to use Kerberos. You can inform the SSP infrastructure by using the stsadm tool.

To configure your SSP infrastructure to use Kerberos authentication, perform the following procedure:

Log on to your Active Directory domain controller using the credentials of a user that has domain administrative permissions.
On one of your servers running Office SharePoint Server 2007, open a command prompt.
Change to the following directory: %COMMONPROGRAMFILES%\microsoft shared\web server extensions\12\bin.
Type the following command: stsadm –o setsharedwebserviceauthn –negotiate, and then press ENTER.

After following all the steps you successfully configured your SharePoint farm to use Kerberos authentication.

Access SharePoint Data from the Database

Leave a reply

When you are developing web parts or other kind of solutions for SharePoint it is possible you work yourself in a situation where you use the SharePoint API for your solutions but you do not want to use it because it is insufficient.

When you are in a situation like this you can choose to connect to the content database of your site collection. When you choose to connect to the database you have to remember you should not alter the database directly but use the API for changing properties.

A few days ago I was in a situation like this. One of my colleges asked me if there was a web part in SharePoint were in he could see all the alerts you are subscripted to. The answer was no so I started looking if I could develop a web part.

I started with retrieving the alerts for the current user like this:

SPAlertCollection alertCollection = SPContext.Current.Web.CurrentUser.Alerts;

I thought I would get all off the alerts off the current user for the entire farm but that wasn’t it. The CurrentUser.Alerts only retrieves the alerts for the user within the context you are in, so I received all off the user alerts that were made in that particular SPWeb.

So I thought lets iterate trough all the site collections and then retrieve the alert like this:

 List<SPAlert> Alerts = new List<SPAlert>();

 SPWebApplication webApp = SPContext.Current.Site.WebApplication;

 foreach (SPSite site in webApp.Sites)
 {
    using (site)
    {
       foreach (SPWeb web in site.AllWebs)
       {
          using (web)
          {
             SPAlertCollection alerts = web.CurrentUser.Alerts;

             foreach (SPAlert alert in alerts)
             {
                Alerts.Add(alert);
             }
          }
       }
    }
 }

The solution works but the problem with this solution is when you get to a site you don’t have access to you will get a access denied and besides that if you have a large collection of SPWebs or SPSites it will take forever to perform this piece of code.

So my next solution was to retrieve the alerts from the database. You can do this by retrieving the content databases from your web application and connect to them. You can use a query like below to retrieve all the alerts for the user you want.

SELECT SchedSubscriptions.Id, SchedSubscriptions.NotifyFreq, SchedSubscriptions.WebId, SchedSubscriptions.SiteId, (SchedSubscriptions.SiteUrl+SchedSubscriptions.WebUrl) AS URL, SchedSubscriptions.ListTitle, SchedSubscriptions.AlertTitle, SchedSubscriptions.AlertType, SchedSubscriptions.Status
 FROM  SchedSubscriptions
 WITH  (NOLOCK)
 Where  SchedSubscriptions.UserId = 1 AND  SchedSubscriptions.Deleted = 0
 UNION 
 SELECT  ImmedSubscriptions.Id, NotifyFreq = 0, ImmedSubscriptions.WebId, ImmedSubscriptions.SiteId, (ImmedSubscriptions.SiteUrl+ImmedSubscriptions.WebUrl) AS  URL, ImmedSubscriptions.ListTitle, ImmedSubscriptions.AlertTitle, ImmedSubscriptions.AlertType, ImmedSubscriptions.Status
 FROM  ImmedSubscriptions
 WITH  (NOLOCK)
 Where  ImmedSubscriptions.UserId = 1 AND  ImmedSubscriptions.Deleted = 0

This is a simple query that queries the content database for all the alerts. The alerts are saved in two tables:

ImmedSubscriptions
SchedSubscriptions

The WITH (NOLOCK) we use in the query is for making sure the tables will not get locked will retrieving the values.

My code for retrieving all off the alerts for a user within a web application looked something like this:

 private const string getAlertCommand = "SELECT SchedSubscriptions.Id, SchedSubscriptions.NotifyFreq, SchedSubscriptions.WebId, SchedSubscriptions.SiteId, (SchedSubscriptions.SiteUrl+SchedSubscriptions.WebUrl) AS URL, SchedSubscriptions.ListTitle, SchedSubscriptions.AlertTitle, SchedSubscriptions.AlertType, SchedSubscriptions.Status FROM SchedSubscriptions WITH (NOLOCK) Where SchedSubscriptions.UserId = {0} AND SchedSubscriptions.Deleted = 0 UNION SELECT ImmedSubscriptions.Id, NotifyFreq = 0, ImmedSubscriptions.WebId, ImmedSubscriptions.SiteId, (ImmedSubscriptions.SiteUrl+ImmedSubscriptions.WebUrl) AS URL, ImmedSubscriptions.ListTitle, ImmedSubscriptions.AlertTitle, ImmedSubscriptions.AlertType, ImmedSubscriptions.Status FROM ImmedSubscriptions WITH (NOLOCK) Where ImmedSubscriptions.UserId = {0} AND ImmedSubscriptions.Deleted = 0";

 protected override void CreateChildControls()
 {
    base.CreateChildControls();

    List<SPContentDatabase> contentdatabases = new List<SPContentDatabase>();

    Guid siteID = SPContext.Current.Site.ID;
    int UserID = SPContext.Current.Web.CurrentUser.ID;

    SPSecurity.RunWithElevatedPrivileges(delegate()
    {
       SPWebApplication webAPP = null;

       using (SPSite site = new SPSite(siteID))
       {
          webAPP = site.WebApplication;

          if (webAPP != null)
          {
             contentdatabases = RetrieveContentDatabases(webAPP);

             foreach (SPContentDatabase database in contentdatabases)
             {

                //use the sql connection so that it will be disposed when we are done
                using (SqlConnection con = new SqlConnection(database.DatabaseConnectionString))
                {
                   //open the database connection
                   con.Open();

                   //Use the command. It will be disposed when we are done using it
                   using (SqlCommand com = con.CreateCommand())
                   {
                      com.CommandText = string.Format(getAlertCommand, UserID.ToString());

                      //read the informtion from the from the database                
                      using (SqlDataReader reader = com.ExecuteReader())
                      {
                          while (reader.Read())
                          {
                             //do what you like to do with the information you retrieved
                          }
                       }
                    }
                 }
              }
           }
        }
     });
 }

 private List<SPContentDatabase> RetrieveContentDatabases(SPWebApplication webAPP)
 {
    //create a generic list for containing the databases
    List<SPContentDatabase> databases = new List<SPContentDatabase>();

    //read out all the databases for the webapplication
    foreach (SPContentDatabase database in webAPP.ContentDatabases)
    {
       databases.Add(database);
    }

    //return the list with databases
    return databases;
 }

Integration Reporting Services with SharePoint 2007 in Native Mode

Leave a reply

It is possible to integrate SQL Reporting Services with SharePoint 2007, but know the question is how you integrate it with SharePoint. The easiest why is to install the SharePoint Reporting Web Parts on your server. This is also called the Integration in Native Mode.

It isn’t more than installing the webpart and connect those webpart to an existing report that resist on you reporting server. But how do you install these webparts.
In the installation folder off SQL server there is a cab file with the two webparts. You can find this file (RSWebParts.cab) in the following folder:

C:\Program Files\Microsoft SQL Server\80\Tools\Reporting Services\SharePoint\RSWebParts.cab

After you found this file you can install it on your SharePoint Farm. To do this you have to navigate to the Application server of your SharePoint farm. Open a Command Prompt window and navigate to the folder that has the Stsadm.exe tool for SharePoint. Normally this path is C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN.

Run the Stsadm.exe tool and run it with the following syntax:

stsadm -o addwppack -filename “(Folder of the cab file)\RSWebParts.cab” -globalinstall

Adding web part and configuring the webpart

Open a Web Part page in your SharePoint Web Application
Set your page in edit mode: Site Action -> Edit Page, and click ‘Add a Web Part’ in the zone where you would like to add the Web Part.
In the Add Web Parts dialog box, scroll down to Miscellaneous. Select Report Explorer or Report Viewer if you want to add both Web Parts, and then click Add.
Know modify the settings of the Report Web Part. In Report Manager URL, type your URL to a Report Manager instance. By default, a Report Manager URL has the following syntax: http:///reports. For this setting you can also set the Start Path.

The Web Parts also allows you to connect the Report Explore to the Report Viewer. This connection can be set in the edit mode of the page:

Click on the edit button of the title bar of the Report Explorer Web Part
Point to Connections and follow that up by pointing to ‘Show report in’, and then click ‘Report Viewer’

Free e-book offer SQL Server 2008 from Microsoft

Leave a reply

Today I received a Microsoft Newsletter to inform me that there is a new free eBook offer for SQL Server 2008.

I tough that more people can take advantage from this. So here is the link to the free e-book.

http://www.microsoft.com/learning/sql/2008/default.mspx#EBOOK

Enjoy your reading.

Microsoft Playground

debug.write("Microsoft Playground");

Category Archives: SQL Server

Install SharePoint 2013 Public Beta on Windows Server 2012 RC – Part III – Installation of SQL Server 2012 on Windows Server 2012 RC

Configure Kerberos authentication

Create the SPNs for your SQL Server service account

Access SharePoint Data from the Database

Integration Reporting Services with SharePoint 2007 in Native Mode

Free e-book offer SQL Server 2008 from Microsoft