Fabian Williams SharePoint Blog

Solving problems with SharePoint day and night

Why it pays to have Smart Friends to Encourage you

Why write this blog post?

Sure its late and YES I could be rotting my brain away on the Idiot Box, but what started off as a Taunt by Ram turned out to consume my life for 2 days straight but with useful results, challenges that were overcome, and a better appreciation for not just Cloud, but the integration of Cloud and On-Prem using Windows Azure.  Let me tell you what I did, and how easy it was… in the end, plus the real implications of it.

First a little history – Pre Taunt

So, as I prepare for my sessions at #SPLive in Orlando in 3 weeks, I wanted to knock out a few Demos and make sure that I could showcase just about anything anyone asked of me based on the Session Abstract/Topic. In furtherance of that, I decided to do the following, build out solutions employing

SharePoint Designer 2013

  • External Content Type using a Native SQL Data Connection
  • External Content Type using an On-Prem (i.e. Hosted on one of my Dev Rig VMs) Windows Communications Foundation [WCF] Data Connection – JSON enabled
  • External Content Type using a .NET Assembly (built on my Visual Studio 2012, Deployed as a Farm Solution to my On-Prem SP2013) Data Connection
  • Workflow using the same On-Prem WCF above in my On-Prem Farm

Visual Studio 2012

  • A Locally IIS hosted WCF Service Library that exposes Northwind data from a Local SQL Server 2012 Box
  • A Locally IIS hosted OData Service Endpoint that exposes Northwind data from a Local SQL Server 2012 Box
  • External Content Type built as a .NET Assembly to be deployed to my ON-Prem SharePoint 2013 Farm
  • External Content Type AND External List SharePoint 2013 App built as an OData Sourced Application deployed to my ON-Prem SharePoint 2013 Farm

For every Instance of the WCF and the App Model External Content Type, I also targeted my Office 365 Tennant to surface the Data as well. I was pretty satisfied with myself until my good friend sent this tweet, and I know it was in jest…

image

but… I took it as a challenge and, I rarely back down from a challenge 🙂 so, since I have a MSDN subscription, there was “really” nothing holding me back besides my inexperience creating Azure Web Roles, which is what you need to do in order to have the requisite Data Access Points (URI) to create External Content Types and potentially Workflows. 

What Next you say…

So, with a little research on Google scratch that, I mean Bing, I found a few MSDN, and TechNet blogs on how to create Web Roles, although not many of them “accurately” showed you how to use Visual Studio to DEPLOY your solution back to Azure. Anyway, it was surprisingly easy, although initially it was somewhat intimidating and i did get snagged on a GOTCHA which i will blog about later when doing a ASP.NET Web Role where it requires you to lower your version of the System.Data.Client assembly from 5.0 to 4.something in order for it work in Azure, i say that because it worked perfectly when I debugged it locally on my Visual Studio IIS. I also had to create an Azure SQL Instance and used a script I downloaded from CodePlex to restore a Northwind Database to my Azure SQL Instance and then I set up firewall rules to allow me to access it over the internet.

But with quick turn around and because i could just Refactor my original On-Prem code logic, I was able to create the following

Visual Studio 2012

  • Azure ASP.NET Web Role that published as a Cloud Service which used OData via ADO.NET Entity Framework to get a Cloud based URI that i can do CRUD operations on
  • Azure WCF Web Role that published as a Cloud Service which used WCF via ADO.NET Entity Framework to get a Cloud based URI that i can do CRUD operations on as well

    image

Now I can really Mix and Match, I can

  1. Use Azure (Cloud) Hosted Data via an ON-Prem WCF or OData End Point and surface that information both on my ON-Prem SharePoint or my Office 365 Instance
  2. Use Azure (Cloud) Hosted Data via an Azure Cloud Service WCF or OData End Point and surface that information both on my ON-Prem SharePoint or my Office 365 Instance
  3. Plus everything I had when I started off

The takeaway here is that with very little investments in time and effort I extended my solution to a “MODERN” approach. I had Cloud in the sentence 🙂

 

Other Positive Implications

So, what made this so easy for me was I signed up for TFS Online here http://tfs.visualstudio.com/en-us/tfs-welcome.aspx and EVEN WITHOUT a MSDN Subscription you can sign up for FREE and get 5 accounts in one instance.

image

What I think is GOLD is…

  • You have a place to store all your CODE/ Work and before you ask, it supports different formats (see image below)
    image
  • Your Code/Work is accessible from Anywhere you have an Internet Connection now
  • You can reliably share your Code/Work with anyone now, rather than Zipping it up and Email it
  • Need Help with some of you work… Invite people as smart or smarter than you to review our code, many hands make light load 🙂
  • Most beneficial of all is that you can configure Continual Integration. I know my good friend Jeremy Thake always talk about Application Lifecycle Management (ALM) and you can employ some of those techniques from Azure through TFS Online and your Visual Studio with multiple Team Member… and guess what… FOR FREE
    image

So that’s all I have to say, I guess we really Paid It Forward RAM, you encouraged me to do this, I in turn, documented my efforts and hope that someone else can take it from here.

 

Cheers all, have a great night. Oh yeah, VS 2013 is out now. GO get it.

October 17, 2013 Posted by | Azure, JSON, OData, REST, SharePoint 2013, SharePoint 2013 Workflows, SharePoint Designer 2013, SharePoint Development, SQL Server, Visual Studio 2012, WCF | , , | Leave a comment

HELP: Unable to Create List using SharePoint 2013 REST API in SPD2013

Précis

So, I am took up @Office365 on their offer of help, but then they directed me to the forums because they said the limitation of twitter made it difficult to respond to my tweet for help

image

So i set about doing that this morning, however even the forums have limits when you try to paint a thorough picture of your problem using screen shots due to image sizes. see here

image

So, i will just blog it and point back to this blog post using the Forum as the initial pointer for anyone who wants to help, fortunately WordPress give me more freedom in images and i can be verbose. So here goes…

Problem Stated

The objective is to Create a new List in SharePoint Online Wave 15 via REST https://site/_api/web/lists and then passing the necessary headers. Also, if I do a GET using the same headers, IT WORKS, this only happens on POST with the addition payload of Request Content(Body) that I am sending, but in Fiddler "using" the same payload, I can create my list. I’ve blogged about this topic and how I go about doing REST via SPD and Fiddler here and here and here, the first and last are probably most appropriate for this line of question. Below are some screen shots of what I am experiencing now. I thank you in advance for any assistance you can provide.

Below are the Request Headers

1

Below is me constructing what will be the Request Content (Body) but to get the desired feel, I had to put the __metadata property inside its own dictionary object to get the curly braces and key value pair, you will see below

2

Below you will see me wrapping this variable inside another to get {‘__metadata’:{‘type’:’SP.List’} to come out properly

3

Next you will see my call to HTTP web Service properties

 4

Next you will see my results. this is my parsing the JSON returned in the Response Code and Response Body

 5

Proof that the same thing works in Fiddler

Here is me doing the same thing in Fiddler. and i also updated my Digest so that it would be current because i know it expires after a while

 

image 

and

image

and the list in O365 is created here

image

 

Update Section

This section will be filled with things I have tried either on my own or on suggestions.

The first thing i will do is take Hugh @hughajwood Wood suggestions and use a simple example on a post I actually praised earlier and do just a simple Add of a List Item in Office 365, i will dubb it TryAlpha

Try Alpha – 09/06/2013 1212 HRS

So the first thing I did was to make the headers as the post describe, to keep things simple i used the exact nomenclature

image

now wrap that header into a Dictionary header named __metadata and add a Title field

image

next use those headers to make my REST call

image

and we can safely say that using the REST API for List Items work. Let me also make some qualifiers

  1. Before doing this, I updated my X-RequestDigest to get a new value and I updated my requestHeaders variable, that was the only thing i did

image

End TestAlpha

Begin Test Bravo

So, I got some information from Paul Schaeflein @paulschaeflein He suggested that rather than hardcoding the Digest Information [which I know also expires after a while, and that is why whenever I run the WF, I redo the contextinfo URI and get a new one] I should do a POST insider my Workflow to get the Digest Information and THEN extract the X-RequestHeader value inside a variable and use it. The thought being, it will be more current than hard coding it, and Pauls Point was that it is safer from a security standpoint if SPD was preventing ‘malicious’ usages or hardcoded values.

Well In the end, it didnt work either. here is the story

Big Picture

image

In more detail I created one header to get the ContextInfo Information i.e. No Digest information was used in that one, and then I set the value coming from the JSON WebContextInformation object mapping to the Digest into a Variable called str_FormDigest.  I then used that Variable in another Header that I have specifically for the POST that I will use to create the List Item. See below

image

And for good measure I logged the output of the Digest so i know it works. here is the Digest Info and another failed attempt

image

End TestBravo

 

 

 

 

 

 

Summary

Any help is appreciated, I will ask that you post your feedback in the comments on this blog and i will update the Ticket in the Forum pointing back to this blog post.

 

Here is the Forum Thread: http://social.msdn.microsoft.com/Forums/en-US/dd9932f5-1e3f-416f-8f0b-e8ab2b792ba1/unable-to-use-spd-2013-and-call-http-service-to-create-a-new-list-in-spo-via-rest

 

 

del.icio.us Tags:

Thanks in advance

September 6, 2013 Posted by | JSON, Office 365, REST, SharePoint 2013, SharePoint Designer 2013, SQL Server | 8 Comments

How To: Configure and Consume Kerberos for use in SQL Server 2008 R2 and SharePoint 2010 Part1

NB. In an effort to make this page load better I am breaking this Blog up into Parts, this is Part 1

Part 2: – Configuring Service Applications, Sites, and Verifying our Work

Part 3: – Configuring and Executing Search, Using Web Parts and Communicating Securely across Web Applications with Kerberos

Synopsis:

I felt a compelling need to write this blog post because with the advent of SharePoint 2010, a whole new world opens up for access to information. With that comes the challenge of managing access and security.  An area that I spend most of my time is in Business Connectivity Services (BCS) and one of the challenges that I personally face is with the “Double Hop”; whereby, I have a desktop client that is using a Web Front End (WFE) via SharePoint to connect to an SharePoint Server in an Application Server Role to in-turn retrieve and consume information from a Line of Business (LOB) back-end database, Kerberos mitigates that problem through its protocol handling and service delegation. Secondly, I have clients who’s desktop environment is heterogeneous in that they have a mixture of Windows XP [yes still XP], Macs running Safari, with a need to avoid login prompts for authentication.

My colleagues out there constantly here me asking for an ‘Authoritative’ document/guidance when it comes to standing up SharePoint 2010 Features.  After looking out there for something like that as it relates to getting Kerberos, i found quite a bit of information but nothing really end to end. I think that is because depending on your need, you may start in one area and end up in the next.  In this document my approach is:

  • A Brand New SharePoint 2010 Installation that will use Kerberos for the Authentication everything being x64
    • Active Directory built on Windows Server 2008 R2
    • SQL Server is 2008 R2

In my quest I pulled information from the following sites and fellow SharePoint mates

  1. There is a really good article that Kathryn Birstein turned me on to when I met her at SharePoint Saturday in New York a few weeks ago. It is truly the Holy Grail for Kerberos – found here — Configuring Kerberos Authentication for Microsoft SharePoint 2010 Products and Technologies (http://go.microsoft.com/fwlink/?LinkId=196600) (7.3 MB)
  2. For Registering a Service Principal Name for SQL Server I got a great piece of article here — http://msdn.microsoft.com/en-us/library/ms191153.aspx
  3. In terms of How to Implement Kerberos Constrained Delegation with SQL Server 2008 see this — http://technet.microsoft.com/en-us/library/ee191523(SQL.100).aspx
  4. Now, if you are going to be creating sites and you want them to be crawled, it suits you best to have your Search Service Application already configured before you create that Web Application; for that I checked out Bill Baer’s Twitter Handle: williamsbaer blog — http://blogs.technet.com/b/wbaer/archive/2009/11/23/step-by-step-provisioning-the-search-service-application.aspx
  5. Another good article that covers a good portion of SharePoint 2010 using Kerberos is here http://technet.microsoft.com/en-us/library/ee806870.aspx and it gives a good Scenario based example
  6. I also have a few Blog Post at https://fabiangwilliams.wordpress.com and my new SharePoint 2010 FPWeb Hosted Site http://www.sharepointfabian.com/blog which outlines how to Install and Configure SharePoint 2010, in light of that I take leaps over those items already covered and hope that you will use those blogs as a source of reference.The one thing I want to emphasize and it is a good segway into item number 6 is that I now DO NOT use the Farm Configuration Wizard (FCW) to create my service applications after a SharePoint 2010 Install; Spencer Harbar Twitter handle @harbars constantly preaches that unless you are doing a POC or a Demo Environment.. DO NOT USE IT… so I now dont. 
  7. The last two articles are from Spencer Harbar, in my opinion, one of the most Solid SharePoint gurus out there, I used his blogs for guidance not only in this example but for setting up User Profile Service which I also bring into this blog post — SharePoint 2010 and Kerberos and Rational Guide to implementing SharePoint Server 2010 User Profile Synchronization

So how are we going to work this and make it flow?

  • Create the Service Accounts we need for SQL and SharePoint
  • I used a GPO that my work colleague and MVP Aaron Tiensivu twitter handle @atiensivu to restrict NTLM traffic to servers so that if Kerberos wasnt successful then audits and errors would be thrown, I will call this out later on
  • Tackle Installing SQL Server 2008 R2 and getting Kerberos Working there first—why
    • we need Service Principal Names (SPN) set for the MSSQLSvc under the Service Account SQL Server is running under if we intend to secure our communications with SQL Server with Kerberos
    • I went further by limiting the Network Transport (Protocol Name) to TCP and Named Pipes because I know in in SQL Server 2008 / Windows Server 2008 enhancements have been made for Named Pipes and typically I use TCP for communications anyway
    • I tested this by logging onto the SharePoint box and using the SQL Management Studio to connect back to the SQL Box, run a query to see what the Network Transport is and also the Authentication Scheme
  • Install SharePoint 2010 bits and set the Authentication to Negotiate(Kerberos) – Configure for Kerberos thereafter
    • Create the Managed Service Accounts for the Web Applications and other Service Applications
    • Set the SPN’s for the Web Application Service Accounts for the Portal, Team, and MySites
    • Create the Web Applications, Site Collections, and validate that Kerberos is the method used for connectivity
  • More Test and Verifications

My Envrionment (Lab) – VMWare  on my Lenovo T61p [dual core single proc with 8 GB RAM]

  • DC Box
    • Windows 2008 r2 x64 Ent
    • Active Directory in 2008 mode
    • SQL 2008 r2
    • ArgoSoft Mail Server
    • 1 GB Ram Allocated
  • SharePoint Box (Application Role)
    • Windows 2008 r2 x64 Ent
    • SharePoint with all Service Enables
    • 3 GB Ram Allocated
  • SharePoint Box (Web Server Role)
    • Windows 2008 r2 x64 Standard
    • WFE Role Only (so I can really test kerberos from another box other than the app box)
    • 1GB Ram Allocated
  • Guest System
    • Windows 7 x86
    • 1 GB Ram Allocated

Yes… this is pushing it to the limit, in fact my CPU is pegged constantly and my memory is tapped. But I dont run with the big dogs out there with SSD’s and 16 GB RAM, not yet.. 🙂

Part 1: SQL Server Squaring Away

After Installing SQL Server 2008 R2, the fist step I do is manage the Protocols under which SQL Server will run, this time because I am focusing on Kerberos I am only enabling TCP and Named Pipes for the reason I mentioned above.  I also enabled a GPO to restrict and/or audit NTLM traffic between the servers as seen below

image

As seen below the service that SQL Service runs under is ADOTOBLAB\SqlSvc and this is what we will create a SPN for

clip_image001

Screen clipping taken: 8/14/2010 3:27 AM

clip_image002

Screen clipping taken: 8/14/2010 3:26 AM

Below is where we create the SPN for the MSSQLSvc under the service account; it is also best practice to do both the FQDN and the NetBIOS names when doing SPN. I used the command line tool for one and the ADSI Edit tool for the second.

clip_image003

Screen clipping taken: 8/14/2010 12:06 AM

Below we also use the tool to validate the entries made.

clip_image004

Screen clipping taken: 8/14/2010 3:25 AM

Part 2: Installation and Configuration of SharePoint 2010 (Accelerated)

Installation of the bits for SharePoint 2010 comes next, I just want to call out the differnces that you will do if you DO NOT use NTLM, see my other blogs for details on installation of SharePoint

clip_image001[6]

Screen clipping taken: 8/14/2010 2:27 AM

Here is your last chance to back out 🙂 — nah — so all this dialog box is telling you is that you need to make arrangements with the Domain Admins if you do not have the access to set up SPN’s for your Service Accounts that you will be using in the creation of Service Applications and Web Applications.

clip_image002[6]

Screen clipping taken: 8/14/2010 2:30 AM

clip_image003[6]

Screen clipping taken: 8/14/2010 2:30 AM

clip_image004[6]

Screen clipping taken: 8/14/2010 2:30 AM

clip_image005

Screen clipping taken: 8/14/2010 2:39 AM

Above is verification that you are using Negotiate(Kerberos) as the Authentication Provider in this Installation of SharePoint 2010

Part 3: Validate that Kerberos is working

FROM THE SHAREPOINT MACHINE AND USING SQL MANAGEMENT STUDIO I RUN THE BELOW TO TEST. THE KEY HERE IS THAT YOU ARE USING WINDOWS AUTH AND LOGGED IN AS THE SHAREPOINT INSTALL ACCOUNT

clip_image001[8]

FROM THE SQL BOX I CHECK THE EVENT LOG AND VERIFY

clip_image002[8]

 

Part 4 – Configure Accounts (Service Accounts, Managed Service Accounts) and Service Principal Names (SPN)

First Register Managed Service Accounts

clip_image001[10]

Screen clipping taken: 8/14/2010 10:15 AM

For User Profile Service and Search and just to Kick off Provisioning of UPS you will need to have Local Admin Righs set on a few accoutns

clip_image002[10]

Screen clipping taken: 8/14/2010 10:17 AM

Make sure that the UPS account also has Replicate Changes and Create Child Objects in AD

Next I am going to create A Records for my Sites (Team, Intranet and MySite )

clip_image003[8]

Screen clipping taken: 8/14/2010 10:30 AM

At this time I set SPN’ for the Service Accounts to be used for the Portal Site, Team Site and MySite. Again ensure that you do both NetBIOS and FQDN for SPNs

clip_image004[8]

Screen clipping taken: 8/14/2010 10:40 AM

clip_image005[6]

Screen clipping taken: 8/14/2010 10:40 AM

When Setting SPN’s ensure that you do both NetBIOS and FQDN

clip_image006

Screen clipping taken: 8/14/2010 10:42 AM

clip_image007

Screen clipping taken: 8/14/2010 10:43 AM

We do the same for :

  1. Teams
  2. My

However Teams and MY will be on port 4444 and 5555 respectively so we will do two entries because of a known issue with setting SPNS for SharePoint

clip_image008

Screen clipping taken: 8/14/2010 10:50 AM

clip_image009

Screen clipping taken: 8/14/2010 10:51 AM

clip_image012

Screen clipping taken: 8/14/2010 11:12 AM

Do the same thing for Service Account for the Team Site (svcAppPoolSites)

NEXT – Part 2

August 15, 2010 Posted by | Kerberos, SharePoint 2010, SharePoint 2010 RTM, SharePoint General, SharePoint How-To, SQL Server | 3 Comments

How To: Use SharePoint 2010 WebParts with a GridView Control to get SQL Image Data type and other Values

Recently I was faced with a challenge

  • Render a LOB System Data in SharePoint. Datatypes include an Image (BLOB) , char and int
  • Provide a means to filter by Last Name

Initial Approach

So as all professionals we should ‘Strive For’ the solution that can be done Out-Of-Box before trying to code a solution.  That said, I came up with a few ideas

  1. Use Business Connectivity Services (BCS)[yeah my one an only true SharePoint Love] to get this information, why, because it is in a LOB system and its SQL, and… well you get the drift: failing that
  2. Use a Visual WebPart in Microsoft Visual Studio 2010 arsenal of weapons, why, because I can just have the gridview auto create the columnd and be on my way: failing that
  3. Use a gridview control as part of a WebPart in Visual Studio 2010.. this is what won otherwise there would be a 4,5,etc….

The Gotchas

Well with the BCS approach; using SharePoint Designer 2010, we are not able to get the Image data type to come accross, much less map to an office property (a nice to have)

clip_image001

clip_image001[5]

Screen clipping taken: 6/29/2010 6:09 PM

clip_image002[6]

Screen clipping taken: 6/29/2010 6:20 PM

Now that we have put that to bed. Lets try some of our coded approaches.

    One approach that we could take would be to do a visual webpart with a gridview control on the page with "Auto-Generate Field" turned on; yeah, that was my first instinct too. However that will not work

    clip_image001[7]

    What will happen here is that the fields will in fact auto-generate however the only fields that will come across will be textual fields and of course our SQL Data Type is of type "Image". So in the end if you use exactly what we saw in our Business Connectivity Services example with SharePoint Designer 2010 visualized in an External List.

    Not to be dissuaded; my next thought was NOT to abandon the gridview control but to add a ‘Template Field’ and include an ‘Image Control’ inside with a reference to a HTTP Web Event Handler… sure this has to work, because I will explicitly stream the bytes in the Handler.ashx file and call it in the ImageUrl Property of the control….

    clip_image002[8]

    Yeah that wont work either because by the time the page [a User Control page ".ascx"], it is too far gone…. But a Visual Web Part would have made it sooooo eassyyyyy. But alas we have to look for another way.

    So your results will look like this….

    clip_image003

     

    The Solution

    Interestingly enough, the solution lies in all of the above with the exception that we cannot use a "User Control Page", we have to use an ".aspx" page. We will still have to use a HTTP Web Event Handler because we need to deal with the fact that we are pulling an Image directly from a SQL Database as a BLOB. So here are the steps I took

    Steps:

    1. Create an Empty SharePoint 2010 Visual Studio Project
    2. Add your Mapped SharePoint Folders for the _Layouts Directory as we are creating an Application Page. If you plan to add images too especially for your Feature, then go ahead and map the Images Folder also

    clip_image004

    1. Next in the same manner add an Elements Section
    2. When you are done, right click on your Folder within your mapped folder [coincidentally it will be the same name as your Project] and ‘Add a new Item’ which will be an application page.

    clip_image005

    1. I call mine "DisplayEmployeesWithPic.aspx" and this will create your page and your code-behind .cs file

    clip_image006

    1. So as it is an Application Page in our Visual Studio Template, it ready to go with the ContentPlaceHolders; we will be targetting the PlaceHolder for "Main" and wire-up our GridView and I am using a Button to add a filter.

    clip_image007

    1. We code against that in our code behind page to set our Datasource [which because I am a lazy developer, we will use the LINQ to SQL Data Connection], perform our query, data-bind and apply filter. You will notice below that I moved away from "Integrated Security" to "Standard Security" in my SQL Connection string because I met into two (2) specific problems (a) ‘Double Hop Problem’ when I ran the code from my Virtual Workstation and (b) Unless I had the End User to the LOB System, they didn’t have access to the dataset. I could have used Kerberos to solve my Double Hop issue, but I still would have the data access to deal with and I use that database to showcase my Business Connectivity Services / Secure Store [Single Sign-On] demos also, and how could I reasonably say that I am doing single sign on when I have folks there all willy nilly in the DB.

    clip_image008

    Screen clipping taken: 6/30/2010 7:42 PM

    1. The next thing we have to do because we are going after a non character data type in the SQL Database and trying to display it on a Grid View control is we need an HTTP Web Handler (a .ashx) file. This little code here is what intercepts traffic and steams the image to the image control previously seen in Step 5

    clip_image009

    1. Next we will add a Feature and ensure that the files are copied into the "Items in the Feature" section. The Elements .xml should auto configure it self also. You will notice I set the Scope to Site not the Default "Web".

    clip_image010

    Below are the options now in Visual Studio 2010 Teamplates

    clip_image011

    1. You Debug or Build and Deploy your Solution to the Farm. Yes, Farm, this cannot be done as a Sandbox Solution because it is touching the file system.

Trust but Verify Section

So that you can see that I have nothing up my sleeves [if you have ever sit in one of my Confrences or Speaking engagements, you know I always say and do this part] I will show you the results as is, then I will walk you through adding a recoredset and see the live results in SharePoint

  1. This is what we expect to see based on what is in the LOB System Now

clip_image001[9]

With a Filter

clip_image002[10]

Screen clipping taken: 6/30/2010 7:57 PM

  1. Next and in the spirit of the World Cup where Spain WILL / MUST WIN!!! (I now digress), we will add David Villa and the guy I so love to pick on; and yeah maybe it’s a little haterism, cry baby Ronaldo. Lets go get some stats…

clip_image003[5]

Screen clipping taken: 6/30/2010 8:00 PM (curtosy of Fifa.com) so I don’t get sued…

clip_image004[5]

Screen clipping taken: 6/30/2010 8:04 PM

clip_image005[5]

FIFA.com – 2010 FIFA World Cup™ – CRISTIANO RONALDO

http://www.fifa.com/worldcup/players/player=201200/index.html

Screen clipping taken: 6/30/2010 8:07 PM

clip_image006[5]

The Proof is in the pudding

clip_image007[5]

Hope this helps everyone who comes across it, as usual, your comments, critiques, and questions are welcomed.

June 30, 2010 Posted by | Business Connectivity Services, SharePoint 2010, SharePoint 2010 RTM, SharePoint Designer 2010, SharePoint How-To, SQL Server, Strange Stuff, Visual Studio 2010 | 5 Comments

How To: So you have a GUID in your External List huh, Yes you can Update the List… if you set it up right

Synopsis:  I fielded a question on MSDN again and it peaked my interest because it had to do with of course BCS. The stated question was an issue when trying to update an External List when a GUID was present.  So I setup a new table, created a… well you will see it below.

image

 

Part 1: Set the Environment and Duplicate the Issue

clip_image001

Above: So I created the Table above and as you can see I used a GUID with (newid()) property set

clip_image002

Above: Added two people to the list and as you can see the GUID is present.

Part 2: Test it out

clip_image003

Above: Created an External List from the ECT, External List is called “GUID in People List” and what I did was also a “Create All Operation” however you will notice that you DO NOT SEE that the GUID Field is not present in the List View

clip_image004

Above:  I created a New Item and populated it with values. Notice here again that the GUID is not present and my Required or “NOT NULL” values from SQL is also denoted

clip_image005

Above: The New User is persisted

clip_image006

Above:  The External Content Type with CRUD capabilities

Part 3: Explaining why my example presumably worked “once” as  yours probably did too and not afterwards

clip_image007

Above: As you would expect the IDGUID field as a primary key / Identity field is protected.  So, it is set to Read Only in the Return Parameter Configuration Wizard.

clip_image008

Above: Just to show you, the second field i am putting in the Picker is NOT read only but it is a Required Field in the LOB

Part 4:  Why Subsequent try’s FAIL and how to make it work… yes Visual Studio

 

clip_image006[5]

Above:  So this is the entry that is made from the last example. BUT because the GUID is set to all Zeros as the question indicated in the Forum and it HAS TO BE a primary key, any subsequent addition in SharePoint will fail because of the duplicate entry.

clip_image001[5]

Above:  Here is our Test Case, let us add a new user called “User 95”

clip_image002[5]

Above:  And of course we error out because of the Primary Key Constraint in the LOB system

clip_image003[5]

Above:  So just to take this all the way to the end, lets do something that we SHOULD NOT DO. We will remove the Read-Only property of the Primary Key in SharePoint; in doing so you will find out exactly WHY it fails and how to fix it in the end. Notice it says that it needs the “PreUpdaterField” this field is in Visual Studio NOT SharePoint Designer.  So this is yet another answer that I give to people that constantly ask me “Hey Fabian, why do i need to use Visual Studio”

clip_image004[5]

Above: We try to put another record there..

clip_image005[5]

Above: Yes Same Error because the LOB system WILL NOT permit it to do so.

Part 5: How to Make it Work

See my BLOG on how to do CRUD ECT and External Lists using Visual Studio. See https://fabiangwilliams.wordpress.com/2009/12/03/creating-a-sharepoint-2010-external-content-type-with-crud-methods-using-linq-and-a-sql-lob-system/

I will try to do one specific for this example over the weekend time permitting.

April 17, 2010 Posted by | Business Connectivity Services, SharePoint 2010, SharePoint Designer 2010, SharePoint Development, SharePoint Error/Resolution, SharePoint How-To, SQL Server, Visual Studio 2010 | 3 Comments

How To: Create, Configure, Consume SharePoint 2010 Secure Store in Business Connectivity Services

Synopsis: I have seen quite a bit of confusion out there regarding how to use Secure Store Service for SharePoint 2010.  While MSDN does have interesting articles, there has been no Alpha to Omega process that shows the relationship to the LOB System, Security Groups representive of the BCS Consumers, BCS Access Account representive of the Credential Owner [Impersonated User], and how to wire it up in SharePoint Designer 2010. This blog hopefully will dispel all fears about Secure Store and answer a MSDN Forum question while at it.

UPDATE: – On 10/14/2012 I have added another blog post Series that will extend this post for SharePoint 2013 employing WCF, .Net Assembly and OData with SharePoint Apps see it HERE

 

The Blog is broken up into sections

  • Prep Work
    • Active Directory Users in Play
      • The Service Account I am selecting as the Impersonated User (Credential Owner)
      • The Security Group where all the people that will consume BCS Data will reside
    • SQL Server Security
      • Who has Access to What
  • Setup
    • Creating & Configuring the Secure Store Object
    • Creating & Configuring the External Content Type in SharePoint Designer 2010
      • Creating External Connection with Secure Store
      • Creating the External Content Type
    • Reviewing the External Content Type (ECT)
    • Reviewing the Security on the ECT
  • Test & Validation
    • Creating an External List derived from the ECT
    • Logging on as a User from the Security Group AND Secured in the permission setting of the ECT
    • Logging on as a User from the Security Group NOT Secured in the permission setting of the ECT

Part 1: Setup

clip_image001

Above:  This represents the AD Account [appBCSUser] which I will use as the Impersonated User i.e. the Broker if you will that will connect to the LOB system on behalf of the Group of people who should have access to the data but DOES NOT have access to the database. This is something your DBA will love because he doesn’t have a flurry of people having accounts on his/her DB.

clip_image002

Above: This represents the AD Security Group [SecureStoreBCSUsers] that have access or should have access to LOB Systems. You can of-course have multiple of these for any number of LOB Systems. Note here that Fabian and Hardeep are in this list, we will be the test users later on.

clip_image003

Above:  Lets look into CA now and set up our environment

clip_image004

Above:  Click Applications Management then Manage Service Applications

clip_image005

Above: We are interested in the Secure Store Service so we click it

clip_image006

Above:  We already have some there from previous Labs, but we will create a new one… click New

clip_image007

Above:  We create a Target Application ID [note this cant be changed once committed], Display Name which can be the Same App ID, and so on.

clip_image008

Above:  I populate the fields and choose “Group” as my Target Application Type. MSDN has a good explanation as to why you want to do that over other options. the Long and Short is that it allows me in this example to tie an AD Group FabianLab\SecureStoreBCSUsers to a single set of credentials i.e. the FabianLab\appBCSUser account. Ill show a few other options below

clip_image009

Above:  By default it wants to know how you will collect the credential of the Impersonated User in my case it is a Windows Account so this works.

clip_image010

Above: I change it around a bit for kicks by adding the word Testing infront of the default text

clip_image011

Above:  Here are a few other options that you can use. SSS is a Claims Aware SSO solution and can take in just about any Authentication Mechanism

clip_image012

Above:  So here because I only log on to CA with the Farm Admin Account, I set that as the target App Admin, however here is where we start to make the App Work for our design. In Members, you can see that i have my AD Group Account earlier. This means that I dont have to meddle with the SSS App anymore, just add and subtract from the AD Security Group.

clip_image013

Above:  It processes once i click OK

clip_image014

Above: Now i have a NEW SSS App, but wait you may ask… what about the Impersonated User.. we are coming to that…

clip_image015

Above:  We click on the custom actions available and select SET CREDENTIALS to set the Mapping for the Impersonated Users to the Group that we will Manage of “Allowed Users”…

clip_image016

Above:  Our trusty Silverlight App shows the progress of us opening a Dialog Pane

clip_image017

Above: The default look of the Credential Mapping

clip_image018

Above: I populated the values with my User Account previously mentioned in the AD Step

Part 2: Validation and Testing

 

clip_image001[4]

Above:  So in SQL Sever you can clearly see that the only account that has Access to the Database “FabianPlayPen” is the AD User mentioned above right…

clip_image002[4]

Above:  We create a new External Content Type by defining the name and Selecting External system to define our Connectivity

clip_image003[4]

Above:  We choose SQL from the list of choices

clip_image004[4]

Above: We define our SSO connection. One note here though in full disclosure, I had tried a few times to make this work and did a typo, so I re-did my SSS App and called it FabianLABSSSMSDNForumQ from what i had it last but the steps are the same.

clip_image005[4]

Above:  Here you may or may not get challenged for credentials when you click OK. The credentials you put here are or should be your own; assuming that you are in that Security Group that will be mapped to the Impersonated User. If not, then you need an account in that Security Group List.

clip_image006[4]

Above:  Once completed you will be able to connect to your LOB System, expand it and perform any operation allowable to you

clip_image007[4]

Above: In our instance lets just create a FULL CRUD operation

clip_image008[4]

Above: Validation that it is complete

clip_image009[4]

Above: Click the “Save” button to push the ECT up to the BDC Metadata Store.

clip_image010[4]

Above:  Now we can check a place where alot of Gotchas happen. Now one may assume that because they have access to the LOB system via the impersonated user and Group Mapping you are done… You’d be wrong, now you NEED to have permission to use the ECT and I already have mine set up by default under “Set Store Permission” to add myself, the search account, and my service account by default. You may need to put your security group here to make it seamless, but because i am doing demos and want it to break depending on my use case, i leave it fluid.

clip_image011[4]

Above:  to do that, click the custom actions and select “Set Permissions”

clip_image012[4]

Above: Do your business here by adding the users you want to have access. Here note that Hardeep doesnt have access while he IS a member of the Security Group.

clip_image013[4]

Above:  Once done, now we can create our External List by choosing our ETC recently created.

clip_image014[4]

Above:  Commit to the System and cross your fingers…. Voilla!

 

Part 3: UAT

clip_image015[4]

Above: Logged on as Me…

clip_image016[4]

Above: Logged on as Hardeep

 

Conclusion

Hopefully this helps you understand the mechanism of SSS, alot more can be done in Code using Visual Studio, have full all. Your comments and reposts are welcomed.

April 16, 2010 Posted by | Business Connectivity Services, Secure Store, SharePoint 2010, SharePoint Administration, SharePoint Designer 2010, SharePoint Development, SharePoint How-To, SQL Server | | 68 Comments

How to Backup Solutions, Sites in SharePoint 2010 Beta using Central Admin and STSADM

Synopsis:

So the idea here is to prepare to move my work I had in SharePoint 2010 Beta to Release Candidate.  I spent quite a bit of time in SharePoint Saturdays, Conferences, and just figuring stuff out just to ditch it as I upgrade my environment. That said, I wanted to backup as much as i can from my work especially my solutions i created for BCS and the LOB System Databases I used in my Demos. What I will outline below is the methods Out of Box (OOB) that you can use in SharePoint 2010 Beta [hopefully noting much changes in RTM, unless they make it better] to backup your solutions and web apps. I can certainly do this while i build out my next environment which will be after much consideration:

  • A Portable Solution i.e. my IBM Lenovo T61P Dual Core 2.33 with 8 GB RAM
  • Windows 7 x64 Professional
    • Virtual Box
      • One Host as Windows Server 2008 R2 Core
        • Active Directory
        • SQL Server
        • Some kind of SMTP Server
      • One Host as SharePoint Server 2010 RC
        • Squeeze Every Bit of Service App as possible on it
  • Office 2010 RC
  • Visual Studio 2010 RC

Step 1 – Identify the items you want to Backup/Preserve

I identified the following items to keep as i move forward with the some prioritization

  • Must Have
    • Backups of My Visual Studio Solutions as a part of a SharePoint Solution
    • Backups of my Content Database
    • Backups of my Other Databases for Demo Purposes
  • Nice to Have
    • Actual MDF and LDF from SQL because i am a meticulous person; some call it anal
    • The Solution Folder under My Documents for Visual Studio 2010, so I can have my code source files
  • Everything Else
    • All my files along the way worth saving especially drivers, pictures, sample docs to mess with

Step 2 – Perform the Backups in a few Flavors

Using Central Administration in SharePoint 2010 Beta

The first thin I did was to open Central Admin. For our purposes today we will be working with the “Backup and Restore” section; second column second row

clip_image001

After you click Backup and Restore you have a few options, we are actually going to use both of them so we can get the experience. Obviously that is overkill but this serves as a tutorial for us later on. First we will tackle the Farm Backup and Restore then the Granular Backup

clip_image002

Once you click “Perform a Backup” under “Farm Backup and Restore” you get the window below

clip_image003

clip_image004

First we will option to backup all the solutions we have created and at least have saved thus far in the Solution Gallery in SharePoint

clip_image005

Once you have finished selecting the files; in this case only solutions to backup. We also have a directory configured for the drop spot for for our backup files. We then will click next…

clip_image006

We can Monitor the process by clicking the Refresh Button/Link

clip_image007

We can see the processing is Preparing Below

clip_image008

Now the process is running

clip_image009

Finally, the process finishes…

clip_image010

Once we are done, we can inspect the results. Below you will see the status, elapsed time, and location.

clip_image011

Next we will backup using Full Backup process the Web App and all items for Port 80

clip_image012

See status below…

clip_image013

clip_image014

 

clip_image015

Below you will see the folders that are created by default when using the OOB tool and “Farm Backup and Restore”

clip_image022

clip_image023

The second option available to us in SharePoint 2010 Beta for backup is the Granular Backup which allows you to do a “Site Collection” backup. We have two SC’s under a managed path that we will backup.

clip_image016

I goofed with the nomenclature here below, but i wanted you to see the error handling now in SP 2010; very descriptive.

clip_image017

Once the SC is identified, you must provide a path and a File Name, unlike the previous method where you had to just determine the folder, here just as in STSADM commands, you must specify the file name.

clip_image018

Below you will see the Site Collections available; we did BCSAlpha first, now we do Charlie…

clip_image019

clip_image020

clip_image021

As you will note below; there is a backup file created for both BCSAlpha and Charlie

clip_image024

Next, just for show I will do the same backup by using STSADM commands. What i found interesting is that the file sizes were different using this method.  You should also notice the “SharePoint Root” folder is now “14”

clip_image025

And now for Charlie….

clip_image026

Next I opened up SQL Management Studio and made a backup of my Databases

clip_image027

 

clip_image028

There you have it….

March 1, 2010 Posted by | SharePoint Administration, SharePoint General, SharePoint How-To, SQL Server | 5 Comments

Using SharePoint Designer 2010 to surface data in External Lists through Business Connectivity Services SharePoint 2010

 

Part 1: Surfacing Data through SharePoint Designer – No CODE! yes, i said it, NO CODE!

Part 2: Surfacing the same Data through Visual Studio – Yes we will crack the seal and Code Away

Part 3: Consuming the Surfaced Data in Outlook Applications and other User Interfaces

 

In this blog I will demonstrate in my opinion “one of” if not “THE MOST” value added component in SharePoint 2010; accessing and surfacing data in Line of Business (LOB) systems with the ability to Read, Write, and Delete in Real-Time. 

So to accomplish this the first thing I will do is create a new Site using the Empty Site Template, Ill dub it BCS Example Alpha. Dont fault me for that name, fault the Military…

image

Once I have completed that process, which is almost the same as you do in MOSS 2007. See my previous post on how to create sites at Creating and Configuring your Sites in SharePoint 2010 – Service Applications and Web Applications Part 1 of 2 for a step by step process on how to accomplish that.  Anywho… once you have completed that process you will then use SharePoint Designer 2010 to open your new site. <aside> so I used to be the one ranting about how SPD 2007 was just FrontPage on steroids and I would only use it to design pages and nothing else, now I am eating crow </aside>

There is sooooo much you can do now in SPD 2010 especially extending solutions build in Visual Studio and writing back to the same .WSP file yep, same DLL. Isnt that freakn awesome. So back to where were were…

 

clip_image001

Above you will notice the Site “BCS Example Alpha” is now opened in SPD2010 and you have details on the site to include:

  • Basic Site Information
  • Site Permissions
  • Any Customizations
  • Any Sub-Sites
  • Basic Settings

Next, we need to create an External Content Type, we do this by clicking on the “Entities” link in the Navigation Pane which will give you an option in the Ribbon called “External Content Type” under the ‘New’ heading. Go ahead and click that bad boy…

clip_image002

Once you have clicked new “External Content Type”, you will be presented with the window below.  Basically, what you are doing here is defining the arms around what was previously your Application Definition File… thank GOD, no more XML [for now], but you are defining your:

  • Entity Nomenclature
  • Data Source
  • Version of your Content Type (app def file)
  • Permissions
  • etc…

 

clip_image003

Here is a good time for us to look a little closer to our Data Source, after all that is what we are going against right. So we are jumping to SQL Management Studio 2008 and looking at the AdventureWorks2008 Database for x64bit systems; you can get it from http://msftdbprodsamples.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=34032 and install it.  So… what I am showing you here is the Table that i am going after and also pulling some data so you can see it isnt SharePoint magic. NB. Pay attention to Item 3, we are gonna steal his identity in a bit.

clip_image004

I am also going to create a View on this guy also because more than likely you are not going to want to have direct access to your underlying table [right, right..] so I created a view aptly called “PersonViewforBDC’ and again you can see the output in my Query Window.

clip_image035

So now we are back to SharePoint Designer 2010 and we are going to name our Entity and create our External Content Type [previously our App Def File] with NO … say it.. No Code… No XML

clip_image005

Above we are calling naming the Display Name, Programmatic Name, and leaving the rest to the default; which in and of itself is no short order, we are saying here we want:

  1. Offline Sync for the List

Which is awesome! Take you data go off to the client, manipulate it and come back and sync.. hmmm Windows Mobile, Laptop, etc.. sky is the limit here

In the next few steps here is what we are doing

  1. Creating a new Data Connection
    1. click “Add Connection”

 

clip_image006

2. Define the type of Connection, in this instance I am electing to choose a “SQL Server” connection but the sky is the limit here also.

clip_image007

3. Provide the Server Name, Database Name, and an Optional Name, which is what shows up in the “Data Source Configuration”, “Entity Type” section when you are creating your External List in your Consuming Site.

clip_image009

4. After you click Ok, you go through a validation step as your Entity connects to the Database and Creates your Data Source for SPD 2010

clip_image010

5. Once that process has complete, you will see the window below which shows you your Database Connection, All Tables and Views that you have permission to see and manipulate.

clip_image011

6. As I am a Database person at heart also, and although it may seem weird, yes, I created a View for this demo, and it is that view that we are going to target or data to surface. So go ahead and Click on your Table or View. In my example I am using my view called “PersonViewforBDC”

clip_image030

7. Now here is where I earned new respect for SharePoint Designer 2010; you can just now “Right Click” on the Table or View and select from a myriad of options [Methods] to create. In this example I am going for the fences. So, I clicked on “Create All Basic Operations” and …

clip_image031

8.  Next you will follow a 3 dialog Wizard which allows you to again exercise some control over the data you are exposing. You may at this point

  • Define the Properties – Nomenclature
  • Apply Parameters – Define what will be the Picker i.e. the item in your SharePoint List that will trigger your custom actions or jump you to the details of the row
  • Apply Filter Parameters – Narrow your Result Set

clip_image032

clip_image033

9. Once you are finished, Click “Finish” and after a few seconds, you will see the below Status Message and Eventually your Entity Created. Make sure to Save your Content Type by Clicking the Save Icon at the top LH corner of SPD 2010, regardless if you don’t, you will be prompted to save your work anyway.

clip_image018

clip_image019

10. Yep, you are done… that easy..

Although I believe it worked, you may be a bit skeptical. So for this demo, I will go to Central Administration and investigate if my BDC/ BCS entity was created. So first thing you have to do is:

  • Under Application Management
    • Click “Manage Service Applications”

clip_image020

  • Because this is a Business Connectivity Services (still called BDC in this release but when RTM comes out I will bet you $1 it will change)
    • Click “Business Data Catalog” and click “Manage” in the Ribbon

clip_image021

clip_image022

Once you click on the Ribbon you will be taken to the browser below where you can see all the External Content Types that are hosted on that Server Farm. Below you have the hard work we completed in SharePoint Designer 2010 with … say it… No Code!

clip_image023

The Moment of truth, what you paid your Money For!

Now let us consume that bad boy; so now we go back to our Site we created and we are going to do some really simple steps. 

  1. Click Site Action

clip_image024

2. Click “More Create Options” because what we want to do here is create an “External List” which is under “Custom Lists”

clip_image025

3. Click External List

clip_image026

4. Define the Nomenclature for your list and here is where you will also select the External Content Type created earlier.  Easy Peasy huh..

clip_image027

To get the Window Below just Click the far right Icon under the “Data Source Configuration” section by Entity Type…

clip_image028

Select your Entity Here… and Click OK

clip_image029

Once you have Clicked “OK”, Go ahead and take a deeeeep breath, Click Create and Voila! there you have it.

clip_image034

Your External List is now created in SharePoint and it is a “Dynamic” representation of your LOB system Data in Real time… but wait… I did say we can perform Live Updates didn’t I?

Lets get our sanity check first….”Trust, but Verify”! So here is my SQL Query where I am looking at my data source… pay close attention to List Item 3 Mr. Roberto Tamburello

clip_image035

In SharePoint, in my newly created External List under my Picker I choose from when I defined my Content Type, I clicked on my down arrow and select “Edit”

clip_image036

Here is the Before below…

clip_image037

I am modding this guy to the following:

  • Making him a Knight
  • Giving him my name as his Middle name
  • and making him a Junior

Poor guy, just cant get a break.

clip_image038

Once I click “OK” notice that in the SharePoint External List, it is already represented as a changed item…. but what about good old LOB System SQL…

clip_image039

If i do my Select Statement… what do we have… YES! he is changed there also!

clip_image040

So in summary, without a stitch of code. We were able to surface data from a LOB System, bring it into SharePoint, manipulate it and Persist it back to the Original Source.

clip_image041

Hope you enjoyed! More to come.. feedback welcomed

November 9, 2009 Posted by | Office 2010 TP, SharePoint 2010, SharePoint Development, SharePoint How-To, SQL Server | , , , , | 4 Comments

SQL Server 2008 Installation Guide

SQL Server 2008 Installation Guide

My Environment

Server: Windows Server 2008 [Spun up on a VPC with 1.5 G RAM] on a Lenovo Thinkpad T61P with 3 G RAM

The Build
Pre-Requisites

· To see the Hardware and Software Requirement please visit http://technet.microsoft.com/en-us/library/ms143506.aspx#EE32

· In my environment I had already installed Windows Server 2008

o Applied the following Roles

§ Web Server

§ Application Server

Steps

1. Pre Setup and Configuration Steps

2. Setup and Configuration Steps

3. Post Setup and Configuration Steps

Pre Setup and Configuration Steps

1. Attach you CD ISO Image or Insert you CD/DVD Media into the Drive Bay. An auto-run will ensue and you will be presented with this screen
clip_image002

2. Click “Run SETUP.EXE”. Next the setup will inspect your system for the .NET Framework 3.5 with SP1. Failing that check, it will ‘attempt’ to install it for you. In my installation it failed as you will see below; however mitigation steps are included.
clip_image004

clip_image006
clip_image008

3.

Setup may fail here indicating that it cannot install .NET framework until .NET 2.0 SP1 is

installed. that is bogus. .NET 2.0 SP1 is installed with BASE windows 2008 and when

you install the roles of Web Server and Application Server .NET 3.0 is installed.

However, to overcome this, go the the directory below manually and install .NET 3.5 SP1

Indicate that you have read and Accepted the terms of the License Agreement and click “Install”
clip_image010

4. When you inspect the error you will see the below item.
clip_image012

5. Navigate to you Windows Explorer and double click the file “dotNetFx3Setup”
clip_image014

6. Microsoft .NET Framework 3.5 SP1 Setup will execute and upon a successful installation of .NET Framework you will be presented with the following screen
clip_image016

7. Then you will be prompted to Restart your Server
clip_image018

8. Once you have rebooted and you are on your desktop, restart Setup from your ISO or CD/DVD Media. You will now be prompted to install a Hotfix for Windows.
clip_image020

9. Click “OK”, once you have done that the hotfix will install automatically and the following two screens will appear during the process and if the result was successful.
clip_image022
clip_image024

10. You will be prompted to restart your Server again. Click “Restart Now” to continue

Setup and Configuration Steps

1. Once you have rebooted and you are on your desktop, restart Setup from your ISO or CD/DVD Media. You will be presented with a Wizard Popup as indicated below
clip_image026
clip_image028

2. Setup will perform an inspection of your Hardware, Operating System , and File System and Registry. If successful, you will see the screen below.
clip_image030

3. You may need to click "Show Detail" displayed as "Hide Detail" in my screen shot to verify what was checked, and if an error was thrown you would click the link under the status section. Furthermore, you may review a detailed report by clicking on the link labeled “View Detailed report”
clip_image032

4.

NB. If you remove the License Key from the box Ctrl-C or X you will not be able to paste it back.

Click “OK” to continue and the SQL Installation wizard will continue. Next a Key will be present based on your Licensing Model with Microsoft, I removed CDW’s in the event this document is used for external purposes.
clip_image033
clip_image035

5. Click “Next”. You will then be presented with the License Acceptance Dialog Box
clip_image037

6. Select the checkbox and click “Next”. The next process will begin the launch of SQL Server 2008 Setup Support Files.
clip_image039

7. Once the checks have completed click Install and if any errors or warning were experienced you will be presented with a report to remediate the issues. In my installation, I am on a Microsoft VPC Image, the NICS are not Public Facing and I hard coded my IP on an internal address. I received two warnings one of which is as a result of my own configuration; the other is based on Windows Default Configuration. Your own situation may be different.
clip_image041
clip_image043
clip_image045

8. Click “Next” after you have remediated your issues, or choose to ignore them as I did. You will then be presented with the “Feature Selection” dialog box. Select the Server options you desire for this installation of SQL Server 2008 and click “Next”. By Default it is Blank none is selected, but I will load mine to the hilt.
clip_image047

9.

This is not where your Databases will live you will configure those options later on.

The next window presented will permit you to define your “Instance of SQL Server”, you may elect the “Default” instance if this is you first server instance or create a “Named Instance” if your situation merits. You may also change the Instance ID or accept the default and you may also configure where your Installation and “Root” files will live. Click “Next”
clip_image049

10. SQL Server will inspect your options and validate the space requirements based on your selection and return any miss-configurations to you if any. Assuming none, Click “Next”.
clip_image051

11. You will now configure your Server “Administrative and Services” Accounts. It is at this time if you were following best practice you would just select the User Accounts already created; but if you are like me, you will need to create them now. You may, as I elected to create one service account for all services so I don’t have to remember as many username/password combinations. Click Next once you have provisioned the credentials.
clip_image053
clip_image055

12. I will present two options although I only performed the latter.

a. If you choose to select a separate credential for each service you will have to click nect to the account name dropdown and browse for your account.
clip_image057
clip_image059

b. If you elect to use the same account for all your services then once you select that button, then you will see a this dialog box below
clip_image061

13. Select or Browse for your credentials and supply the password, Click “OK” when finished.

14. Once completed you will be presented with a window similar to the one below. Confirm and then you may select the “Collation” tab if you are concerned with the case sensitivity of your Database entries or Language concerns. If not, leave the defaults and Click “Next”.
clip_image063
clip_image065

15. The “Database Engine Configuration” dialog box is presented next. Here you will determine if you will use “Windows Authentication” or “Mixed Mode” for your End User and code interaction with this SQL Instance. Furthermore, with SQL Server 2008 you can now include additional SQL Server Administrators right from this menu. Very Cool! Click the “Database Directories” Tab and here is where you will…
clip_image067

16.

This article does not discuss the provisioning of File Groups, RAID, SAN, or other disk provisioning methods. I just selected the default, not recommended for Production

Configure the locations of the System Databases and mentioned earlier in step 9.
clip_image069

17. You may also select the “FILESTREAM” Tab, but I don’t know what that does yet.
clip_image071

18. If you selected “SQL Server Analysis Service” [SSAS] then here is where you would need to configure Account and Directory Information as we did for the Server Engine above. Click the “Data Directories” Tab to determine the location of SSAS files.
clip_image073
clip_image075

19. After you click “Next”, you will get to the screen I have been waiting for, the NEW Reporting Services of SQL Server specifically configured for Microsoft Office SharePoint Server…and other options. Make your selection for Default, SharePoint or no configuration of the Reporting Service. Click “Next”
clip_image077

20. The “Error and Usage Reporting” Dialog box is presented next. Select your options and click “Next”.
clip_image079

21.

To get a detailed report go here: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20080821_205927\SystemConfigurationCheck_Report.htm

Once you have completed that step, you are almost there and now the SQL Installer will verify and validate your selections and options as denoted below. As usual you may show or hide the report and/or view a detailed Web report. Click “Next” when you are or have satisfied the selections.
clip_image081

22. The final screen before SQL Server takes over and commits the install is another verification screen or a “Last Chance” if you will, Click “Install” and you are on you are ‘Bolt-ing’ on your way.
clip_image083

23. This part is the longest, for my install it took over 35 minutes to complete, but if you are successful, you will be presented with a continual progress screen and eventually a success screen.
clip_image085

24. As the process winds down to a close, another progress wizard dialog box will appear and then…
clip_image086
clip_image088
clip_image090

25. You have just Successfully Installed SQL Server 2008. Next the Post Setup and Config Steps.

Post Setup and Configuration Steps

1. Verify and Validate your Services by selecting the “SQL Server Configuration Manager” in Program Files à SQL Server 2008. Make sure the service that should be running are running.
clip_image092

2. Verify and Validate that the Protocols that should be operational are operational
clip_image094

3. Log on and Verify that you can use the new Management Studio
clip_image096

4. Verify and Validate your System and User Databases
clip_image098

5. Check your SQL Instance Properties. Right Click on the Server Instance and Click Properties
clip_image100

6. Good Luck Hope this Helps! SQL 2008 ROCKS! For comments or questions please feel free to contact me. Fabian Williams – Fabian.Williams@CDW.com

April 28, 2009 Posted by | SQL Server | Leave a comment