Connecting to Sql Server using Impersonation from Asp.Net

June 16th, 2009

So you have an Asp.Net application that needs to authenticate its users to Active Directory, and you also want to use their credentials for connecting to a database server. It’s pretty logical thing to do in an enterprise environment where you would normally control all your user privileges using Active Directory. This is especially nice since you also don’t have to put sensitive credentials in your web.config file.

Before I begin, here are a few links worth mentioning:

How To: Use Impersonation and Delegation in ASP.NET 2.0
http://msdn.microsoft.com/en-us/library/ms998351.aspx

How To: Use Protocol Transition and Constrained Delegation in ASP.NET 2.0
http://msdn.microsoft.com/en-us/library/ms998355.aspx

How To: Use Windows Authentication in ASP.NET 2.0
http://msdn.microsoft.com/en-us/library/ms998358.aspx

This turns out to be a very easy thing to do; it’s just hard to find any simple information about it. Authenticating to Asp.Net using Windows and using impersonation gives us the ability to let the worker process inherit privileges of the authenticated user. All it takes is a few web.config changes.

First, you need to change authentication mode to Windows. under <system.web> set <authentication mode=”Windows”/>

It’s also a good idea to force anonymous users to authentication. Do that by changing the <authorization> config.

Now we have users authenticating to the Windows network. Now lets enable impersonation so the worker process will adopt privileges for their requests. Set <identity impersonation=”true”/>

When you’re done, your web.config file should resemble this.

<system.web>

    <authentication mode="Windows"/>

    <identity impersonate="true"/>

    <authorization>
        <deny users="?"/>
    </authorization>

</system.web>

All that’s left is putting the proper delegation rules in place for your web server to hand out Windows tokens (at least that’s my understanding). In Active Directory Users and Computers from a domain controller or accessible machine, right click and go to the properties of the web server that will be using impersonation.  Click the Delegation tab and select the third option. Trust this computer for delegation to specified services only.  Select Use any authentication protocol.

imageThen browse for a machine account for the database server you want to access with the impersonated accounts. Choose the MSSQLSvc with the port number next to it. (Ours had two services).  Then click OK to apply the changes.

You may need to reset IIS or wait a few minutes for the AD changes to propagate. We reset ours from a command line with “iisreset”.

That should be it!  You should now be able to browse your web app and access the database using your AD Windows Account.

nathan .NET, ASP.Net, Sql Server , , , , ,

Natal, Wow!

June 5th, 2009

This is probably the thousandth repost of this; but go take a look at Microsoft’s new controller Natal.  Simply amazing!

nathan Generic

WCF Data Contracts and “k__BackingField” Property Naming

May 28th, 2009

So recently, I was playing around with WCF, and I created a few classes that I wanted to expose in operations. Usually when I play with plain old objects, I tend to type them up pretty lazily and use any code shortcut I can. In this case I defined my properties using the 3.0 auto-generated field method, which looks like an interface property. The C# compiler automatically generates the backing field on the fly for me at compile time.  Here is a sample class I’m using in the silverlight music project.

[Serializable]
public class FileBrowser
{
    public string LocalMusicRoot { get; set; }
    public string WebMusicRoot { get; set; }
    public List<string> Files { get; set; }
}

imageThe catch comes in when you expose it to WCF. When serializing these objects, the serializer will look at all FIELDS of the class no matter their scope:  public, private, etc.  This is because it uses System.Runtime.Serialization, not System.Xml.Serialization. It does not serialize properties since properties really are just accessor functions.  So what you get is a funky looking proxy class with some properties you didn’t want and some cryptic naming. I.E. k__BackingField appended to your field names. The serializer simply takes what it has which is the auto-generated field name the C# compiler made for us.

So how do I clean this up?!?!   Easy!  Use a data contract.   By defining your classes using the DataContract attribute from System.Runtime.Serialization, the serializer will interpret your DataMembers as fields on the class and use your naming. It will also ignore anything that’s not defined as a DataMember; so that hidden stuff will remain hidden and not be included in the proxy class. Take a look at this.  We modified our original class with these attributes and the generated proxy class on the right now uses the names and fields we want!  Easy peasy!

[DataContract]
public class FileBrowser
{
    [DataMember]
    public string LocalMusicRoot { get; set; }

    [DataMember]
    public string WebMusicRoot { get; set; }

    [DataMember]
    public List<string> Files { get; set; }
}

imageSo now…  When you’re defining your entity classes that you want to pass around WCF; just be sure to take advantage of DataContract attributes to clean up the client proxy classes.

By the way… If you’re playing around with SubSonic 3 Alpha, this is a really sweet tweak you can make to your classes.tt T4 template.  This will make them all Data Contracts and put DataMember attributes on each of the data fields. This will hide the foreign key properties from the WCF generated proxies.  Good stuff! :)

nathan Serialization, WCF , , , ,

Regex Groups and Find/Replace Tagged Expressions

May 13th, 2009

Every once in awhile I have a need to use regular expressions in code. I love using them in Visual Studio find/replace. And one of my favorite features in the find/replace dialog is tagged expressions. This allows me to make a regular expression match and at the same time extract pieces of it for use in my replace with expression.

So today, I’m toying around with Regex and find a need to have the same behavior in code. The difference is, tagged expressions in code are referred to as matching groups. Normally you would use braces for your tagged expressions in a find/replace dialog; but in code, you would use parentheses instead.

(BTW, These are very simplified and by no means an end-all match expression for this scenario).

For today’s search, I need an expression that would give me a table name from an insert or update t-sql statement. Simple enough right?  With this expression: “insert into [a-zA-Z0-9_]+|update [a-zA-Z0-9_]+ ” I can validate either of the two statements. But I really want the table name used in this match.  In a tagged expression, I would’ve used “insert into {[a-zA-Z0-9_]+}|update {[a-zA-Z0-9_]+} ” so I could use \1 and \2 for my table name in the replace expression. But in code, we’ll need to use groups. So the regex will look like this, “insert into ([a-zA-Z0-9_]+)|update ([a-zA-Z0-9_]+) “.

Fortunately in our search text, it can be only insert or update, not both. So in code, I could match a statement like this:

Regex regex = new Regex("insert into ([a-zA-Z0-9_]+)|update ([a-zA-Z0-9_]+) ", RegexOptions.IgnoreCase);

if (regex.IsMatch(sql_command))
{
    string table_name = regex.Match(sql_command).Groups[1].Value;
}

The Groups collection contains the sub matches starting with the second element.  This is handy; so you don’t have to re-search your match with a second expression. In our scenario, only one group match will ever exist, so using Groups[1] gets me the table name.

You can get more info from the MSDN docs.

nathan .NET , , , , ,

Unable to Find Valid Certificate Path for Google Checkout Notification on IIS6

May 4th, 2009

Well this was a fun one. For onelittlebow.com, we’re using Google Checkout as one of our checkout methods. Recently we just switched to using a virtual dedicated server rather than the normal shared hosting environment at GoDaddy. Their shared hosting never really supported Google Checkout notifications because their shared IIS servers were configured to use Basic Authentication with Windows and always pre-authenticated the request. Since the merchantID and key wasn’t a valid NT account on the server IIS would always kick back a 401 response and ASP.NET wouldn’t even touch the request.

Now with the virtual dedicated environment, we are finally able to use these callbacks and handle the authentication header ourselves in code (or by setting up an account on the server).  But in production, we must use fully trusted SSL. That’s not a huge deal; we just purchased one of their SSL certs and installed in on our server. Easy peasy right?  Well if you’re like me and you’re not super familiar with the nitty gritty details of SSL and certificate validation, then you might be getting this error in the Integration Console on Google Checkout’s admin page.

We encountered an error trying to access your server at https://somewhere.com/callback_endpoint — the error we got is javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

When you pull it up in a browser, everything ’seems’ fine, but I did notice Google Chrome didn’t like it too much with the big red warning page about its invalid certificate. Not really understanding why, I started asking questions on the forums and to Google Checkout’s support. They came back instructing me to fully install the certificate path. Soon after, I found this useful article on GoDaddy’s support website.  You can find links for IIS 5 and IIS 7 here.

Basically, this article walks through the process of setting it up on your IIS6 server. The issue I had was related to not installing the intermediate certificates along with my IIS certificate. I also had to disable a previously installed root certificate.

After that, all was well. Our Google notifications started working the second we re-enabled them, and my browsers fully trusted our certificate.

nathan .NET, ASP.Net, Google Checkout , , , ,

Silverlight Deployment and WCF Endpoints

April 27th, 2009

I’m sure this has been written about a ton, but why not repost some useful information.

When using Silverlight with WCF, you quickly learn about the cross-domain restrictions. It becomes a huge pain when deploying from dev to production because you have to deploy and configure your Silverlight using the prod endpoint address, which for me at least didn’t always work. So what I found was that quite a few people are setting their WCF client end points on the fly using the current host during runtime. It’s relatively easy to do, and since they both are typically deployed at the same time, it works out pretty well.

This snippet strips away the ClientBin/xxx.xap from the application source and retains the application root URL.

string full = Application.Current.Host.Source.AbsoluteUri;
return full.Substring(0, full.LastIndexOf('/')) + "/../";

You can then put this functionality into a utility class for your application and make it very easy to consume when using WCF services. Using the music manager service from a previous post, we have:

MusicManagerService.MusicManagerServiceClient client;
client = new MusicPlayer.MusicManagerService.MusicManagerServiceClient();

//use dynamic endpoint
client.Endpoint.Address = new System.ServiceModel.EndpointAddress(this.RootUrl + "MusicManager.svc"); 

client.GetFilesCompleted +=new EventHandler<MusicPlayer.MusicManagerService.GetFilesCompletedEventArgs>(client_GetFilesCompleted);
client.GetFilesAsync(uxSearchString.Text.Trim());

For deployment, you just xcopy your published web app to the deployment directory and you’re done. No reconfiguring/recompiling necessary.

Enjoy!

nathan .NET, Silverlight, WCF , , ,

Fixing a Corrupt Sql Server 2005 Install

April 13th, 2009

Okay, so I’ve been fighting with this workstation that has a corrupt Sql Server 2005 install, and I’m unable to re-install or remove the old install. I’ve tried manually removing registry keys and the whole nine. I even found a few references to articles posted about the topic.

I finally figured it out, so I thought I’d share since I’ve had to do this more than once. My situation is that I have a machine with Sql Management Studio Express installed and I needed the full Management Studio installed. So while uninstalling a few components, I must’ve done it in the wrong order, my Sql Server Add/Remove just disappeared.  So then I was unable to cleanly uninstall the software.

When I tried to re-install the client components, I kept getting this error:

A component that you have specified in the ADD_LOCAL property is already installed. To upgrade the existing component, refer to the template.ini and set the UPGRADE property to the name of the component.

sqlerr

Sounds a big greek to me.  But after a few minutes of reviewing google results for the subject, I found this article.

http://www.techtalkz.com/microsoft-sql-server/163115-uninstall-sql-2005-studio-express-so-i-can-install-sql-2005-t.html

In the last post, he mentions that he downloaded the Windows Installer Cleanup Utility and it worked.  So I thought I’d give it a whirl since my local install was pretty much a handful of nastiness.  So I downloaded the utility from Microsoft’s web site.

After installing, I ran the utility, found the missing add/remove items and removed them.  Easy peasy…

Finally I restarted the Sql 2005 installer as normal, and the Client Components install went through without a problem.

Now I can get some work done…

nathan 2005, Sql Server , ,

Silverlight, WCF, and Streaming My Personal Music Repository from IIS

April 8th, 2009

imageA buddy of mine recently had mentioned he wanted a web based program he could use to listen to his music from home.  As a contractor, we move around a lot. At some locations, we can’t install any software; others, we have firewall issues. Our mobile devices couldn’t hold every song we stored at home, and copying our music around everywhere just didn’t seem like a great idea.  So we thought… wouldn’t it be cool if we could have just a simple website that could stream our music from anywhere? It’s an easy install…

So I started digesting the idea and thought yeah, we could do that.  At first we’ll have to just use something simple like streaming the files directly from IIS in a silverlight music player of sorts.  But eventually, we can really take advantage of some cool WCF hosting features in windows and not require IIS at all, which would be great for most of us since everyone doesn’t have a windows server sitting around at home. After a tiny bit of research, I realize we could also take advantage of the Media Services component built into windows server and IIS, which from the sound of it are pretty comprehensive. But with our end goal in mind, we’ll want to find a way to stream it right out of a simple windows app available to any edition of Windows.

Having said all that, I decided to start writing about this little toy project.  To begin, we have a simple Silverlight application with a music player, list manager, and an IIS server hosting a virtual directly to our mp3 repository.

Getting Started

Of course you’ll need all the links and references to some handy Silverlight information.

http://silverlight.net/GetStarted/ is an excellent starting point.

You may need the Silverlight Tools for Visual Studio 2008 SP1.

I’m using Expression Blend 2, and for my project templates to be correct, I needed Expression Blend 2 SP1.

Download the Sample

So here we have our simple player. It has a stop and play button and a list box.  It’s very, very, very simple.  The idea is to load the music list on startup, then as we choose an item in the list, it plays.  It also will auto-play the next song in the list when the current song ends, and it will recycle the whole list when the last song in the list ends. There are a TON of cool things we can do with this, but for now, we’re starting simple.

I started out by creating a new Silverlight Application in Expression Blend.  If you don’t have Expression Blend, you can create a new Silverlight Application using Visual Studio with Silverlight Tools. This creates a class library containing the Silverlight controls along with a website project containing sample run pages to load and test your Silverlight application. In this case, our project name is MusicPlayer and the auto-generated web project name is MusicPlayer.Web.

The Player

Add a new Silveright User Control to the MusicPlayer project called Player. Here’s the XAML for our new control.

<UserControl x:Class="MusicPlayer.Player"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    Width="513" Height="613" xmlns:d="http://schemas.microsoft.com/expression/blend/2008" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="d" x:Name="ContentPanel">
    <Grid x:Name="LayoutRoot" Background="#343434">
        <MediaElement x:Name="MediaElement1" Margin="0,0,103,0" Height="26" VerticalAlignment="Top" Width="109" HorizontalAlignment="Right" AutoPlay="True" />
        <Button HorizontalAlignment="Left" Width="95" Content="Stop" x:Name="uxStop" VerticalAlignment="Top" Height="26" />
        <Button Content="Play" x:Name="uxPlay"  Height="26" VerticalAlignment="Top" Width="99" HorizontalAlignment="Right" MinHeight="0" UseLayoutRounding="True"/>
        <ListBox Margin="0,30,0,0" x:Name="uxPlayList" Background="#FF000000" BorderBrush="#FF345CA4" Foreground="#FF8FC0FF" Opacity="0.41"/>
        <TextBlock Margin="99,0,103,0" VerticalAlignment="Top" Text="" TextWrapping="Wrap" Foreground="#FFAEAEAE" x:Name="uxStatus" Height="30"/>
    </Grid>
</UserControl>

This should give you something similar to the control shown above. Start wiring up events for the simple things like the buttons and such. For their click event, you can control the MediaElement by asking it to Play(), Stop(), Pause(), etc.    I included a text block that just shows the current state of the media element.  Use the CurrentStateChanged event on the MediaElement to update its value.

image.png

Getting the Playlist

Before we get into loading the list control, we’ll need to define its WCF services. I designed a very simple structure to handle the information we need for our Music in this exercise. For now, it contains only a list of filenames and their relative path to the root of the mp3 repository. It also contains the public web path configured on the server.

MusicManagerService will have one function for now: GetFiles(), which will return a FileBrowser filled with the information from the MP3 repository. Simple enough…

Within the FileBrowser we have our implementation of the LoadFiles() function where it actually seeks the local repository and collects its information.

//FileBrowser.cs

public void LoadFiles()
{
    if (!string.IsNullOrEmpty(LocalMusicRoot) && Directory.Exists(LocalMusicRoot))
    {
        if (this.Files == null)
            this.Files = new List<string>();

        this.Files.Clear();

        this.Files.AddRange(Directory.GetFiles(this.LocalMusicRoot, "*.mp3", SearchOption.AllDirectories));
        this.Files.AddRange(Directory.GetFiles(this.LocalMusicRoot, "*.wma", SearchOption.AllDirectories));

        //trim off the full path; leave only the relative one.
        List<string> newFiles = new List<string>();
        foreach (string file in this.Files)
        {
            newFiles.Add(file.Replace(LocalMusicRoot, string.Empty).Replace('\\','/'));
        }
        this.Files = newFiles;
    }
    else
        throw new ApplicationException("Cannot continue. No directory specified or the directory does not exist.");
}

WCF Setup

The easiest way to do this is to add a new Silverlight-enabled WCF Service to your web project.  Change its service contract to resemble the following snippet.  This will also automatically configure your web.config as well. The funny thing about the Silverlight enabled web service is that it mixes the contract interface with the class implementation. You can optionally separate this out into a WCF library in a more traditional approach using the interface and class implementation separated. We’ve done this in our downloadable sample code.

[ServiceContract()]
[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
public class MusicManager
{
    [OperationContract]
    public FileBrowser GetFiles()
    {
        FileBrowser fb = new FileBrowser();
        fb.LocalMusicRoot = Properties.Settings.Default.LocalRootPath;
        fb.WebMusicRoot = Properties.Settings.Default.WebRootUrl;
        fb.LoadFiles();

        fb.LocalMusicRoot = null; //clearing unnecessary local path from return value
        return fb;
    }
}

You should be able to browse MusicManager.svc and see the service page. You can also reference the service in the MusicPlayer silverlight project. In the ContentPanel Loaded event, we will call out to the service.

private void ContentPanel_Loaded(object sender, RoutedEventArgs e)
{
    //load up the music files.
    MusicManagerService.MusicManagerServiceClient client = new MusicManagerService.MusicManagerServiceClient();
    client.GetFilesCompleted += new EventHandler<MusicPlayer.MusicManagerService.GetFilesCompletedEventArgs>(client_GetFilesCompleted);
    client.GetFilesAsync();
}

Our async callback function for the service call will populate the list control.

void client_GetFilesCompleted(object sender, MusicPlayer.MusicManagerService.GetFilesCompletedEventArgs e)
{
    this.uxPlayList.Items.Clear();
    _Browser = e.Result;

    foreach (string file in _Browser.Filesk__BackingField)
    {
        this.uxPlayList.Items.Add(file);
    }
}

To finish up, add implementation for the selected index changed on the list control to change the MediaElement’s Source to the selected file with its web path. For convenience, we AutoPlay to true on our MediaElement to autoplay its media. Anytime the Source property changes values, it will begin streaming and playing the audio.

private void uxPlayList_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
    //start streaming the file.
    MediaElement1.Source = new Uri(_Browser.WebMusicRootk__BackingField + uxPlayList.SelectedItem.ToString());
}

When the media ends, we need to increment the playlist selection.

private void MediaElement1_MediaEnded(object sender, RoutedEventArgs e)
{
    //move to the next item
    int ix = uxPlayList.SelectedIndex;
    ix++;

    if (ix >= uxPlayList.Items.Count)
        ix = 0;

    uxPlayList.SelectedIndex = ix;
}

Wrap Up

So the end result will be our player initializing by pre-loading a playlist. Once the first song is played, the rest will auto-play as they increment through the list. With my MP3’s on a cable connection I’ve noticed it’s uploading around around 60KB/s.  So as long as the location you’re at can tolerate a little bit of bandwidth, it’s a pretty good start to a little remote music player. You might even be able to throttle IIS down so it doesn’t saturate the wire.

Next Time

The next round will be using a restful http request via WCF 3.5 to stream audio files rather than straight up IIS.  Stay tuned!

nathan .NET, Silverlight, WCF , , , ,

iSeries ADO.Net Provider and Common FAQ Links

March 19th, 2009

These links might come in handy.  It’s the ADO.NET provider for iseries dB2 and has links to the VS Add Ins.   I think you need a login to download some of them.  And it says that they’re looking at a LINQ to iSeries provider via Entity Framework.

IBM .Net downloads

IBM .NET FAQ

To get a description of files, schemas, columns, you can use the views under QSYS2. I noticed some nice views that bring together the physical files and logical files.

nathan ADO.Net , , ,

Updated root website

March 14th, 2009

Well, I’ve posted this blog as my primary website. This will probably last a little while until I can get some updated layouts going for the new biz. At least this way, I can more easily keep it updated with fresh new content.

nathan Generic