Install guide for using Team Foundation Server 2010 Source Control with BIDS / SSMS 2008

At one of my customers is decided to start using Team Foundation Server 2010 for source control and release management for SQL Server database and BI development.

To make it possible for Visual Studio 2008 (VS2008), Business Intelligence Development Studio 2008 (BIDS2008) and SQL Server Management Studio 2008 (SSMS2008) to work with TFS2010 additional software must be installed on the developer station.

This blog describes the installation for the source control components on the developer station.

The following components need to be installed:

1 – VS2008 Team Explorer

2 – VS2008 SP1

3 – VS2008 Team Explorer KB974558

4 – VS2010 Team Explorer

5 – TFS2010 MSSCCI Provider

6 – TFS2010 PowerTools

The numbering determines the installation order.

 

1 – Visual Studio 2008 Team Explorer

Download latest version of TE2008 from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=0ed12659-3d41-4420-bbb0-a46e51bfca86. The downloaded file is an ISO file. During writing of this document the (29/12/2010) the available version is extracted from the ISO and placed in a folder 01_VS2008TeamExplorer.

1. logon to the Developer station with a local administrator account
2. Right click the setup.exe in the 01_VS2008TeamExplorer folder and select Run as administrator
3. Click Yes on the User Account Control window
4. De-select Yes, send information… and click Next when the Welcome to Setup window appears
    
5. Select I have read and accept… and click Next when the License Terms window appears
    
6. Click Next when the Destination Folder window appears
    
7. Click Install when the Ready to Install window appears
    
8. Installation will be started, have some patients
    
9. Click Finish to complete the installation.
   

2 – Visual Studio 2008 Service Pack 1

Download latest version of VS2008SP1 from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=27673c47-b3b5-4c67-bd99-84e525b5ce61. The downloaded file is an ISO file. During writing of this document the (29/12/2010) the available version is extracted from the ISO and placed in a folder 02_VS2008SP1.

1. logon to the Developer station with a local administrator account
2. Right click the SPInstaller.exe in the 02_VS2008SP1 folder and select Run as administrator
3. Click Yes on the User Account Control window
4. Click Next when the Welcome window appears
    
5. Select I have read and accept…. De-select I would like to improve…. And click Next when the Microsoft Software License Terms window appears.
    
6. This installation will start, have some patients.
    
7. Click Finish to complete the installation.
   

3 – Visual Studio 2008 Team Explorer KB974558

Download latest version of VS2008TEKB974558 from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=cf13ea45-d17b-4edc-8e6c-6c5b208ec54d&displaylang=en. During writing of this document the (29/12/2010) the available version is placed in folder 03_VS2008TeamExplorer_KB974558

This KB makes sure you can connect to Team Foundation Server 2010 with Team Explorer 2008. See previous blog post: http://blog.arjanfraaij.com/2010/12/visual-studio-2008-and-team-foundation.html

1. logon to the Developer station with a local administrator account
2. Right click the .exe file in the 03_VS2008TeamExplorer_KB974558 folder and select Run as administrator
3. Click Yes on the User Account Control window
4. Click Next when the Welcom to …. Window appears
    
5. Select I have read… and click Next when the Microsoft Software License Terms window appears
    
6. The installation will start, have some patients
    
7. Click Finish to complete the installation
   

4 – Visual Studio 2010 Team Explorer

Download latest version of VS2010TE from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=fe4f9904-0480-4c9d-a264-02fedd78ab38&displaylang=en. The downloaded file is an ISO file. During writing of this document the (29/12/2010) the available version is extracted from the ISO and placed in folder 04_VS2010TeamExplorer

When using Team Foundation Server with Team Explorer 2008 not all functions are available, like creating a new project. Therefore Team Explorer 2010 needs to be installed.

1. logon to the Developer station with a local administrator account
2. Right click the setup.exe file in the 04_VS2010TeamExplorer folder and select Run as administrator
3. Click Yes on the User Account Control window
4. De-Select Yes send information…. And click Next when the Welcome window appears
    
5. Select I have read and accept…. And click Next when the license term window appears
    
6. Click Install when the Install window appears
    
7. Installation will start, have some patients.
    
8. Click finish to complete the installation
   

5 – Team Foundation Server 2010 MSSCCI Provider

Download latest version of TFS2010MSSCCI from http://visualstudiogallery.msdn.microsoft.com/en-us/bce06506-be38-47a1-9f29-d3937d3d88d6. During writing of this document the (29/12/2010) the available version is placed in folder 05_TFS2010_MSSCCI_Provider.

This provider makes it possible to use TFS2010 source control in SSMS 2008.

1. logon to the Developer station with a local administrator account
2. Right click the .msi file in the 05_TFS2010_MSSCCI_Provider folder and select Install
3. Click Run on the Open File – Security Warning window
4. Click Next when the Welcome window appears
    
5. Select I Agree and click Next when the License Agreement window appears
    
6. Select Everyone and click Next when the Select installation Folder window appears
    
7. Click Next to start installation when the Confirm installation window appears
   
8. Select Yes on the User Account Control window
9. Click Close to finish the installation.
   
   

6 – Team Foundation Server 2010 Power Tools

Download latest version of Team Foundation Server 2010 Power Tools from http://visualstudiogallery.msdn.microsoft.com/en-us/c255a1e4-04ba-4f68-8f4e-cd473d6b971f. During writing of this document the (29/12/2010) the available version is placed in folder 06_TFS2010_PowerTools_September2010.

1. logon to the Developer station with a local administrator account
2. Right click the .msi file in the 06_TFS2010_PowerTools_September2010 folder and select Install
3. Click Run on the Open File – Security Warning window
4. Click Next when the Welcome to setup… window appears
    
5. Select I accept… and click Next when the license agreement window appears
    
6. Select Custom and click Next when the Setup Type window appears
    
7. Select the needed features and click Next when the Custom Setup window appears
    
8. Click Install when the Ready to Install the Program window appears
    
9. Select Yes on the User Account Control window
10. The installation will start have some patients
     
11. Click finish to finish the installation
    

This completed the installation of the developers station to make it possible to use BIDS and SSMS 2008 with Team Foundation Server 2010. See other blog posts on how to use it.

BULK Admin (Operating system error code 5 (Access is denied))

Today I was supporting a 2 node SQL 2008 Cluster with 3 instances:

SQL01\Test, SQL01\Acceptance and SQL01\Production

The customer wanted to use BULKINSERTS and was granted Server Role BULKADMIN on all instances. The below SQL Statement was executed on the SQL01\Test Instance.

The result was as expected, data was inserted in tblSomeTable.

Then they tried to execute the same statement on the SQL01\Acceptance and Production instance what resulted in the error:
msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file “\\SomeShare\SomeFolder\SomeFile.txt” could not be opened. Operating system error code 5(Access is denied).

The first thing I checked has the user all needed permissions on the share, it was working on Test using the same SQL Active Directory based principal and share so that could not be the issue.

The second check if principal has the bulkadmin server role granted on all instances:

I hope you trust me when I say that this is correctly granted on all instances.

So I needed to go a bit deeper. To understand what was needed first I need to show you a little image of how the environment is working:

On the client they started SSMS 2008, and executed the given BULK INSERT command. The image explains that there are 2 hops or so cold “Double Hop” needed to get to the files. This means that the SQL01 instances need to be able to forward the user security token to the FILE1 server file share. This requires KERBEROS authentication and delegation!

So the first thing I checked was what protocol is used for each connection.

This resulted in:
- SQL01\Test TCP KERBEROS
- SQL01\Acceptance TCP NTLM
- SQL01\Production TCP NTLM

So there is the reason why it is not working, NLM does not support the authentication pass through. But what is causing this? You can not configure this as a setting direct in SQL Server. Lucky I have some experience with Kerberos configuring for SharePoint environments.

The next thing I did was looking for the SPN (Server Principal Names) that are created for the SQL Server Service accounts for each Instance. SPN’s needed to be configured for each Service Account to make sure Kerberos delegation is supported.

You need the Windows resource kit to be able to check SPN’s with the setspn tool.

I executed setspn –L <service account> for each instance. The result was displaying the following:

SQL01\Test
- MSSQLSvc/SQL01.corp.local:Test
- MSSQLSvc/SQL01.corp.local:2612

For the other to instances there were no SPN’s configured!!! I created the below SPN’s with the setspn tool:
setspn –A MSSQLSvc/SQL01.corp.local:<port> <service account>
setspn –A MSSQLSvc/SQL01.corp.local:<port> <instancename> <service account>

SQL01\Acceptance
- MSSQLSvc/SQL01.corp.local:Acceptance
- MSSQLSvc/SQL01.corp.local:2611

SQL01\Production
- MSSQLSvc/SQL01.corp.local:Production
- MSSQLSvc/SQL01.corp.local:2610

When I now execute the query

All instance connections are configured for using TCP KERBEROS

Testing the BULK INSERT again and all work fine on each instance!

When in your situation it is still not working don’t forget to check or the SQL Services domain accounts <service account> are configured for delegation. In AD Users & Groups look for the Service Accounts, select the Delegation Tab is should be configured as “Trust this user for delegation to any service (Kerberos only)”

SharePoint 2010 Installation

This blogs describe the installation of the SharePoint 2010 server in the below environment.

The basis of all installations is Microsoft Windows Server 2008 R2, 64 Bits with all available updates installed until the 1 November 2010.
No rights can be taken from this documentation, you are still responsible for the correct configuration of your own environment. I only hope this document will help you with your implementation.
 

Configure IIS 7.0 for installation of SharePoint 2010

Before you can install SharePoint on the server you first need to configure IIS 7 on Windows and install additional features.
 

Installation IIS 7.0

1. Login with a local administrator on the SHP01 server
2. Start the Windows Server Manager
3. Click Add Roles
4. Select the Web Server (IIS) Role
5. Click Add required features when the pop-up Add features required for Web Servers (IIS) does appear
6. Click Next
7. Click Next
8. Select the below services to be installed
1. Web Server
1. Common HTTP Features
2. Static Content
3. Default Document
4. Directory Browsing
5. HTTP Errors
2. Application Development
1. ASP.NET
2. NET Extensibility
3. ISAPI Extensions
4. ISAPI Filters
3. Health and Diagnostics
1. HTTP Logging
2. Logging Tools
3. Request Monitor
4. Tracing
4. Security
1. Basic Authentication
2. Windows Authentication
3. Digest Authentication
4. Request Filtering
5. Performance
1. Static Content Compression
2. Dynamic Content Compression
6. Management Tools
1. IIS Management Console
2. IIS 6 Management Compatibility
3. IIS 6 Metabase Compatibility
9. Click Next
10. Click Install
11. Follow the default left steps to finish the installation of IIS and components.

 

Installation .NET FrameWork 3.x feature

1. Login with a local administrator on the SHP01 server
2. Start the Windows Server Manager
3. Click Add Features
4. Select the .NET Framework 3.x feature
5. Click Add Required Role Services when the Add role services and features required for .NET Framework 3.x features appears
6. Click Next
7. Click Next
8. Click Next
9. Klik Install
10. Follow the default steps to finish the installation.

Create SharePoint basic installation accounts

Two accounts are required for the basic installation of SharePoint.

SharePoint Farm Setup account

The farm setup account is a dedicated account that will be used for installation of the SharePoint binaries on each server. The account is also used during basic configuration of SharePoint.
Do not use an existing domain administrator account or your own domain account as a local administrator on the server during the installation. The account is granted specific rights during the installation of SharePoint on the servers in the farm. It is not desired to grant those rights to your own account or a standard domain administrator account. It’s best to keep a dedicated account during installation or when you would like to add a new server to the farm in a later stadium.
For example it might be used to run the Windows SharePoint Services Timer

Account name	Rights
corp\shpSetup	Member of the local administrator group of each SharePoint server in the farm Login account on the SQL Server with the server roles dbcreator security administrator

Make corp\shpSetup Local admin on SHP01

1. Login to the SHP01 server with an local administrator account
2. Start the Windows Server Manager
3. Go to Configuration > Local Users and Groups > Groups
4. Double click on the Administrators Group
5. Click Add
6. Add the domain account corp\shpSetup
7. Follow the default steps to add the account and then logoff from the server.

Make corp\shpSetup a Login on SQL01

1. Login to the SQL01 server with an local administrator account that is a member of the SQL Server role sysadmin
2. Start SQL Server Management studio
3. Create a connection to the SQL instance where you will create SharePoint databases. (In my environment I use a named instance: SQL01\MSSQL01)
4. Click New Query
5. Copy below SQL Script to create the Login corp\shpSetup with the needed rights for the SharePoint installation.
   

   CREATE LOGIN [CORP\shpSetup] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english] GO EXEC sp_addsrvrolemember @loginame = N'CORP\shpSetup', @rolename = N'dbcreator' GO EXEC sp_addsrvrolemember @loginame = N'CORP\shpSetup', @rolename = N'securityadmin' GO

6. Close SQL Server Management studio logoff from the server.

SharePoint Farm Administrator account

The Farm administrator account is also mentioned as the default content access account. Het The account is the primary administrator account of the SharePoint farm and is granted db_owner to all content databases in the farm.

Account name	Right
corp\shpFarm	Account is granted rights during installation. No rights are needed to be granted before installation.

Installation SharePoint binaries

The installation of the binaries is done with the account corp\shpSetup. Installation is done with the installation DVD of SharePoint.

1. Login to SHP01 server with the CORP\shpSetup account
2. Start windows explorer
3. Browse to cd-drive
4. Double click the splash.hta to start the installation
5. Click “Install software prerequisites” and follow the basic steps to finish
6. Click “Install SharePoint Server”
1. Click yes on the User Account Control dialog box
2. Insert your product key on the Product Key dialog box
3. Select I accept terms of agreement on the License agreement dialog box and click continue
4. Select Server Farm on the Choose the installation you want dialog box
5. Select Complete – Install all components. on the server type dialog box
6. Click the File Location, leave all default and click Install Now
7. Install Progress is shown have some patients.
7. Unselect Run the SharePoint configuration wizard now and click Close
8. Do NOT run the technology configuration wizard.

SharePoint Products and technology configuration wizard

During this step the Central Administration (CA) web application is created. During the step 2 databases are created:

• shpFarm1_Config
• shpFarm1_AdminContent_{GUID}

1. Login to the SHP01 server with the CORP\shpSetup account
2. Go to Start > Microsoft Office Server
3. Click the SharePoint Products and Technologies Configuration Wizard
1. The window named “Welcome to SharePoint Products” appears, click Next
2. A window appears to warn you about services that may be started or reset during the installation process, click Yes
3. The window named “Connect to a server farm” appears, select “Create a new server farm” and click next
4. The window named “Specify Configuration database settings” Appears, give the below values and click Next:
1. Database server: SQL01\MSSQL01
2. Database name: shpFarm1_Config
3. Username: CORP\shpFarm
4. Password: xxxxxxxxxxxxxxxxx
5. The window named “Specify farm security settings” appears, give the below values and click Next:
1. Passphrase: xxxxxxxxxxxxxxxxxxxxxxxxx
2. Confirm passphrase: xxxxxxxxxxxxxxxxxxxxxxxxx
6. The window named “Configure SharePoint Central Administration Web Application” appears, give the below values and click Next:
1. Specify port number, check the box:12000
2. Select Negotiate (Kerberos)
7. A window Appears to warn you about the additional steps for Kerberos authentication, click Yes
8. The window named “Completing the SharePoint Products Configuration Wizard” appears, click Next
9. The windows named “Configuring SharePoint Products” appears, just wait until it has finished all 10 configuration steps.
10. The windows named “Configuring Successful” appears, click finish
4. The Central Administration starts to open the “Initial Farm Configuration Wizard” window.
1. The window named “Help Make SharePoint Better” appears, select below value and click Ok:
1. No, I don’t wish to participate
2. The window named “Central Administration, Configure Your SharePoint Farm” appears, Click Cancel
3. The basic Central Administration window appears, just close it!

This is it for now, we are no ready for starting the configuration of SharePoint 2010. There is a lot to configure. To make this not one very big blog I will create separate blogs for configuration. Starting with “Configure SharePoint Outgoing E-Mail”
I hope you are not disappointed, just wait for the next blog and watch me on twitter @arjanfraaij
See you!

T-SQL Checking or column exist in specific table

There are several ways to check or a column exists based on SQL 2008 Server, but I like to be sure that I can use the method on older and newer versions. Now I always use the INFORMATION_SCHEMA for checking existence of columns.

Is this a best practice, most compatible with older and coming version of SQL Server?

Should you use syscolumns or is it sys.columns but the you need to combine to get the result with other system views:

Also this could or has been changed in SQL server edition from the past or in the future.

When you have a better method please let me now!

Visual Studio 2008 and Team Foundation Server 2010 Errors TF31002 & TF30335

At my current customer I’m looking for a tool to use with Visual Studio 2008 (VS2008 or BIDS 2008) and source control.

I have been trying TortoiseSVN with VisualSVN Server (free edition). I do like the combination but there is no real checkout structure that directly locks sources for other developers. Also TortoiseSVN does not interact with VS2008. I did take a look at the VisualSVN client that there for free, but did not like the interaction with VS2008. (this might be personal). Also I like the Server side of the source control to be able to interact with Active Directory, this is possible with VisualSVN when upgrading to the paid edition. Also remote management is available in VisualSVN only for the paid edition.

So let’s take a look at Team Foundation Server 2010 (TFS2010) and the use with BIDS 2008.

My starting point was that I had VS2008 already installed and added Service Pack1 (SP1). Installed the trial edition of TFS2010 as a single server deployment.

The TFS2010 (ISO) installation media only contains Team Explorer 2010, first installed that on my client. This is not compatible with VS2008, it does not show up in the tools or add-in sections in VS2008.

The next thing to do is to download Team Explorer 2008 (TE2008): http://www.microsoft.com/downloads/en/details.aspx?FamilyID=0ed12659-3d41-4420-bbb0-a46e51bfca86&displaylang=en

After installation of the TE2008 client I started VS2008 again, I now have available “Connect to Team Foundation Server” in the VS2008 Tools menu.

Let’s click, the below dialog box appears:

Click Servers, the below dialog box appears:

Click Add, the below dialog box appears. I inserted the desired values already.

After clicking OK I receive a error message TF31002

I’m sure that this is not the case, so I tried to use the URL to add the server.

Again after clicking OK I receive a different error message TF30335

Most solution on the Google friend point out to install Visual Studio SP1, Other mention to add the URL direct in the registry or install the hotfix “Visual Studio Team System 2008 Service Pack 1 Forward Compatibility Update for Team Foundation Server 2010”

http://www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=cf13ea45-d17b-4edc-8e6c-6c5b208ec54d

I Already have VS2010 SP1 installed so started to install the hotfix. Finding that there was nothing to install?

First thing I had to do was reinstall Visual Studio 2008 SP1, it was installed prior installing Team Explorer 2008. Tried above steps again to see if it maybe already solved my problem. This was not the case. I tried the installation of the hotfix again, this time it did want to update VS2008 SP1.

After installing I started VS2008 again and first tried to connect to TFS2010 by only typing the server name in the Add server dialog box, again below error did appear.

The last try for now I typed the full URL in the Add Server Dialog Box hit OK and..

We have succeeded!! But are not there yet, when trying to add a new project from the Team Explorer TF20172 error is appearing, but that’s is for an other blob post.

------------------------------
Twitter: @arjanfraaij

SharePoint 2010 Installation Configure Outgoing E-Mail

This blogs describe the installation of the SharePoint 2010 server in the below environment.

The basis of all installations is Microsoft Windows Server 2008 R2, 64 Bits with all available updates installed until the 1 November 2010.

No rights can be taken from this documentation, you are still responsible for the correct configuration of your own environment. I only hope this document will help you with your implementation.

See previous blog: http://blog.arjanfraaij.com/2010/12/sharepoint-2010-installation.html

Configure SharePoint Outgoing E-Mail

All activities are done on the SHP01 Server logged in with the corp\shpsetup account and on central administration website http://shp01:12000 .

Outgoing E-Mail is used by SharePoint to send mail to end users on different occasions. Samples are Alerts, Request access, etc.

Configuring Exchange 2010

Create a Receive Connector on Exchange 2010 to handle the SharePoint 2010 outgoing Mail.

1. Exchange activities are done on EX01 as domain administrator
2. Launch the Exchange Management Console and navigate to Server Configuration / Hub Transport / New Receive Connector
3. In the Action Pane click New Receive Connector
4. The window named “New Receive Connector” appears
   1. Sub screen Introduction
      1. Name: SharePoint Outgoing E-Mail
      2. Select the intended use…: Custom
   2. Sub screen Local Network Settings
      1. Leave all Default and click Next
   3. Sub screen Remote Network Settings
      1. Click Edit
      2. Start Address: <SharePoint Servers IP>
      3. End Address: <SharePoint Servers IP>
      4. Click OK and Next
   4. Sub screen New Connector
      1. Click New
   5. Sub screen Completion
      1. Click Finish
5. Select the just created Connector SharePoint Outgoing E-Mail
6. In the Action pane click Properties
7. Select Permission Groups tab
8. Select Anonymous Users and click OK

Configure SharePoint 2010 Outgoing E-Mail

1. Start Central Administration
2. Select System Settings
3. Select configure outgoing e-mail settings
4. Outbound SMTP Server: EX01.CORP.LOCAL
5. From address: sharepointmail.corp.local
6. Reply-to address: sharepointmail.corp.local
7. Click OK

Test SharePoint Outgoing E-Mail

As soon I have the entire environment up-and-running I will edit this blog post to show how you can test or the outgoing e-mail function is working.

Blogging from Word changed to Essentials Log Writer

This is a test; from word I found you could blog direct to different blog sites. I myself use blogger.com one down fall you can not configure word to upload images with blogger.com. Behind this picasa is used.

So I changed to Microsoft Essentials 2011 Log Writer; and see I can start uploading images now direct from writing this blog.

You can download Microsoft Essentials 2011 Log Writer from:

http://explore.live.com/windows-live-essentials?os=other