Archive

Archive for the ‘.net 1.1’ Category

SQL Server 2005: Stored Procedures Executor Role

April 7, 2008 1 comment

Granting permissions to use stored procedures is something I rarely do… in most of my applications, I either use .NET SQL projects and compile my SQL code OR everything is handled in the data layer/LINQ code.

We’ve had a recent consult project and the coders use stored procedures heavily.  That’s cool—to each their own.  Performance seems good on the system and the procedures are pretty small.

But… remembering to readd the EXECUTE permission to the user account on each reload is REALLY annoying.  I’d read this somewhere (if I can find the original source, I’ll cite it) ages ago and dragged it out of the code library.  A quick bit of TSQL code to create a database role that SHOULD exist out of the box—one that can execute stored procedures.

/* Create a new role for executing stored

   procedures */

CREATE ROLE db_executor

 

/* Grant stored procedure execute rights

   to the role */

GRANT EXECUTE TO db_executor

 

/* Add a user to the db_executor role */

EXEC sp_addrolemember ‘db_executor’, ‘AccountName’

Quite easy and it makes it VERY easy to control EXECUTE permissions for your accounts.

Advertisements

.NET Tip of the Day

February 28, 2008 Comments off

I stumbled upon the site earlier today while watching my system Ghost—and it’s pretty cool.

If you haven’t checked it out, they provide a single “tip” or trick to using .NET (from using Visual Studio to performance considerations oc concatenation) and is available ala RSS (just the way I like it).  The site is clean, which is an added bonus.

Check it out.

 

Creating Single Sign-on Web Parts in SharePoint 2003

January 28, 2008 4 comments

Download Code (SharePoint 2003, .NET 1.1)

In our environment, we have several web applications that use forms authentication.  We wanted to, however, move past that on our SharePoint portal and pass that information directly if the user is already authenticated.  We assume that they’re authenticated if they’re on the Portal and authorized if they can see the link (controlled by audiences which are controlled by AD groups).

So, from there, we needed a general web part that we could supply an image (for the button), a bit of descriptor text, a link to the application, and a general format for passing the GUID (we use GUIDs rather than sAMAccountName, you could alter the code for either).

A SharePoint 2003 web part control is a composite control that contains labels, hyperlink controls, and image controls.  Most web parts are at a maximum 200px wide to accommodate modular design or have a height/width public property that can be set through the UI.  We’ll keep those properties in mind when creating our SSO web part.

NOTE: We’re not using the SSO functionalities of SharePoint.  Could this be done through that?  Maybe, but I’ve never tried.  If you have—I’d be interested in hearing how it compares.

Required Assemblies

The following assemblies should be used to generate the web part.

  • Microsoft.SharePoint, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c
  • All other .NET 1.1 references, such as System.DirectoryServices, which are needed for the operation of the web part.

In addition, I’m using the WebPart template for VS 2003 (I’m running VS2003 in a VM with the SharePoint libraries copied over to the machine).

Fetching the GUID

The following code copies the current user (in the format of “domain\userName”) into the name variable.  Inside the try/catch statement, we’re connecting to Active Directory (using a delegated read-only account—the ONLY way you should be connecting for web applications) and using System.DirectoryServices to filter about by the sAMAccountName.  If an entry is found, the GUID is returned.

private void GetUserADGuid()

{

string name = this.Page.User.Identity.Name;

       name = name.Substring(name.IndexOf(@”\”) + 1);

       try

       {

              string[] adCredentialsArray =

ConfigurationSettings.

AppSettings[“adCredentials”].Split(new char[] { ‘/’ });

              string adUserName = adCredentialsArray[0];

              string adPassword = adCredentialsArray[1];

              DirectoryEntry entry = new DirectoryEntry(“”,

adUserName, adPassword, AuthenticationTypes.Secure);

              DirectorySearcher searcher = new DirectorySearcher(entry);

              searcher.Filter =  string.Format(“(sAMAccountName={0})”, name);

              SearchResult result = searcher.FindOne();

              if (result != null)

              {

                      _userGuid = result.GetDirectoryEntry().Guid;

}

       }

       catch (Exception exception)

       {

              this.Context.Response.Write(exception.ToString());
              this._userGuid = Guid.Empty;

       }

}

Consuming the GUID and Rendering the Web Part

The next step is to use our GetUserADGuid method and render the redirection code for the web part (which will use JavaScript). 

private void BuildRedirect()

{

if (this._userGuid == Guid.Empty)

{

       this.GetUserADGuid();

}

       try

       {

              string guidString;

              if (_encryptGuid)

              {

                     guidString = this.EncryptString(

string.Format(“{0}{1}”,

userGuid,

DateTime.Now.ToUniversalTime()));

              }

              else

              {

                     guidString =

string.Format(“{0}”, userGuid);

              }

             

UriBuilder builder = new UriBuilder(this.LinkTarget);

 

string link =

this._linkTarget.Replace(“%GUID%”,

this.Context.Server.UrlEncode(guidString));

 

              this.RedirectToService(link);

       }

       catch (Exception exception)

       {

              this.Context.Response.Write(exception.ToString());

}

}

The Replace method on _linkTarget does the majority of the heavy lifting for this web part.  Part of the instructions to go along with this part is to include %GUID% on the SharePoint design side wherever you wish the GUID to be placed.

Such as:  http://myapplication.domain.local?uid=%GUID%

The web part would then replace %GUID% with the authenticated user’s GUID.

The EncryptString method is not included in this post (but in the code project attached) and is a simple method that encrypts (using the RijndaelManaged class in System.Security.Cryptography) the string information that is processed.

Finally, the web part overrides RenderWebPart and builds the control based on the properties specified at the web part’s design time (in SharePoint) and the results of the public properties.  When either the logo image or the link is clicked, the BuildRedirect method is executed that evaluates options, such as encryption and “new window”, and then redirects accordingly.  The method that builds the redirection link with the GUID is separated out from the actual redirection action for sake of testing.

SPS2003: Changing the Portal URL

December 10, 2007 Comments off

Fortunately, when you move your SharePoint Portal Server 2003 portal from one URL to another, the transition isn’t painful at all. The URLs are relative, so as long as /sites/mis, /sites/whatever continue to exist, the root URL doesn’t matter.  Good deal.

That is, of course, until alerts and confirmations get emailed out…

Over the weekend, thousands of our users were pummeled by emails (not uncommon) for the 90 day confirmation.  Unfortunately, all the URLs were wrong in these emails.  Bad deal for our Customer Service department—that sure didn’t make me any friends for a Monday morning.  I even brought homemade oatmeal raising cookies today.  LOL.

So, to change it, I have hunted all through the Central Administrator and other SPS tools, queried all through STSADM, and finally found the answer poking around with the API in .NET.  The Sites appear to have the URL hardcoded.  After a bit more looking, I found in the entries.

In the SPS2003 SITE database is a table called, very approprately, Sites.  One of the columns is called FullUrl.

http://oldportalservername/sites/mis

Well, that’s not good.

While the “don’t mess with the databases” mantra has beaten me down for years, this “fix” has worked for us and worked quite well…  if it can be done with CA, PLEASE someone let me know.

This, however, can be fixed by a quick line of SQL:

UPDATE Sites

SET FullUrl =

REPLACE(LTRIM(RTRIM(FullUrl)), ‘OldUrlHere’, ‘NewUrlHere’);

Restart the Portal services and you’re good to go.  The alerts generates reported the updated Url and things were nice.

Migrating SharePoint 2003 without SPSBackup

November 1, 2007 2 comments

The title of this post may not be totally accurate.  It could also be:

  • Migrating SharePoint 2003 from a Single to Farm environment.
  • Migrating SharePoint 2003 by Restoring the Portal
  • Migrating SharePoint 2003 through SQL Server.

or, my favorite:

  • Migrating SharePoint 2003 when the SharePoint Gods are angry with you.

There are numerous factors that come into play when trying to migrate SharePoint 2003.

  • Versions and subversions of SharePoint,
  • Versions and subversions of Windows Server,
  • Versions and subversions of SQL Server 2005,
  • Deployment hierarchy
  • and number of “Unexpected errors” that can occur for no real reason.

After a few days of battling topologies, obscure messages in SPSBackup, and service packs, I finally just installed clean copies of SharePoint 2003 on each of the servers; upgraded to the latest service packs, and tried a different approach—using SQL Server backups.

Backing Up

  1. Backup the “to be” migrated SharePoint databases (I had a SITE, PROF, and SERV database) to three separate .BAK files.
  2. Copy those files to your new SQL Server and restore them as SITE_Old, etc.
  3. The ConfigDb database is unnecessary.

Restoring the Portal

  1. When you’re first presented with the SharePoint Central Administrator, complete the service account information, server topology, etc.  This will recreate the ConfigDb for your new portal server.
  2. When creating a portal, change the radio button from ‘Create a portal’ to ‘Restore a portal’.
  3. You’ll be prompted for three database names: your site, user profile, and services.  Enter the names you gave them (SITE_Old, etc) from “Backing Up” Step #2.
  4. Specify your Portal’s URL and click OK.

That’s it; it’ll churn for a few minutes and recreate the portal.  If you are in a farm with multiple web servers, make sure you’ve recreated all necessary files, web parts, etc. on each server.

 

“Unsupported topology” in SharePoint 2003

October 2, 2007 1 comment

Okay, so we’re reupping our SharePoint 2003 environment to a better environment.  Why not MOSS 2007?  Ehh, quite honestly, because that’s politics and would make kittens cry.  But, that’s not the point of this post.

Our planned setup was:

  • Two web-front end servers, each running the web service.
  • One search server, running the search service.
  • One index server, running the indexing and job services.
  • Two SQL servers, clustered and attached to terabytes of space in the SAN.

Looked good, analyzer balked, but, according to KB887164 on Microsoft’s TechNet article, it’s a working, suggested scenario.

At the bottom of that article, it says:

Note After you follow these steps and then locate Configure Farm Topology (FarmTopologyView.aspx) on the Central Administration Web site, you may receive one or both of the following messages:

The current topology is not supported.
You selected an invalid server farm configuration.

These warnings may be safely ignored.

Yeah, well, that’s a lie. While running in an “unsupported topology,” you can’t add portal sites or do backup/restore operations.  That’s not unsupported, that’s unfunctioning.

I found an old blog post that has a thread regarding layout and that error.  The MVP helping the poster, Shane Young, responds with:

Technically it will let you setup this environment.  But then SharePoint will give you an error message telling you that you have an unsupported config.  Whenever SharePoint is displaying that error message it will not allow you to create new portals and it will not allow you to use its backup utility.  This is just how it works.

That’s just how it works, huh?  No recommendations or ideas?  Searching through Bill English’s SPS 2003 Resource Kit is of no use either.  In fact, the book has an architecutre of EXACTLY what I want in the book with no mention of issues or failures (pp. 116). 

If you keep digging (in the Resource Kit), page 282 lists the “supported” topologies.  For a large farm, it seems that there are three supported topologies:

  • small (1 all-in-one box + 1 sql source)
  • medium (2 all-in-one boxes + 1 sql source)
  • large (2–8 web boxes, 2–4 search boxes, 1–4 index boxes (plus 1 job) + 1+ sql source)

Notice that there are no in-betweens?  The medium can’t break apart the roles and the large requires a minimum of two search boxes. 

So, for now, my solution is to pull the Search box out and fall back to a hybrid of the medium configuration that seems to work.

  • 2 web + search boxes
  • 1 index + job box
  • 1 sql source

That configuration makes Backup and Restore and the portal creation tools happy, but seems like such a waste to put those search engines on the web drives when I have a server sitting here for it.

So, for those interested—unsupported messages may safely ignored as long as you’re not planning on USING your SharePoint Portal.

SPS2003 – Cannot Add To Site Directory? It’s a feature!

When trying to add a site to Site Directory (either manually or part of the Create Site routine), you click OK and it just does nothing.  By looking at Fiddler, it’s not so much it stops sending data, locks up, or anything—it just never TRIES.  After some digging, it’s a known “bug” with Microsoft: article #934229.  It’s caused by installing Windows Server 2003 SP2 to the box.  I wish someone would tell me when these things are applied… or, *gasp* use change controls.

The fix appears to be to bypass the InputButton control on the new site page and use simple JavaScript to submit the form.  The fix has been cataloged here from the newsgroups.

The summary of the proposed fix is to:

1. Open C:\Program Files\Common Files\Microsoft Shared\web server extensions\60\TEMPLATE\[LCID]\SPSSITES\LISTS\SITESLST\NewForm.aspx.  LCID == your locale identifier, it’s 1033 for US English.

2. Modify the file’s code to use a JavaScript submission rather than the standard web controls provided by SPS2003.  What we’re changing is bolded.

Original Code:

<SPSWC:InputFormButtonSection runat=”server”>
<SPSWC:InputFormButtonAtBottom ID=”ButtonOk” runat=”server”
TextLocId=”Page_OkButton_Text”/>
<SPSWC:InputFormButtonAtBottom ID=”ButtonCancel” runat=”server”
TextLocId=”Page_CancelButton_Text” visible=”false” />
</SPSWC:InputFormButtonSection>

Updated Code:

<SPSWC:InputFormButtonSection runat=”server”>
<input type=”button” value = “      OK       “ 
onClick=”document.forms[0].submit();” />

<!— Commented Out for Workaround
<SPSWC:InputFormButtonAtBottom ID=”ButtonOk” runat=”server”
TextLocId=”Page_OkButton_Text”/>
—>

<SPSWC:InputFormButtonAtBottom ID=”ButtonCancel” runat=”server”
TextLocId=”Page_CancelButton_Text” visible=”false” />
</SPSWC:InputFormButtonSection>

Unfortunately, that doesn’t work.  Yes, the form submits, but custom fields, such as Category (we call it Building), does not submit—thus you have to go back into the directory, find your site, and manually edit it.  What a pain.