The purpose of the article is to provide technical means by which a programmer can combine executing a stored procedure from within a SELECT statement providing the stored procedure returns a result set and usually the execution of a stored procedure is done via an exec statement. In the article I'll show method of running a stored procedure implicitly by executing a SELECT statement.

We are working with some developers from India.  We have been forced to share some of our machines with the off shore developers while we figure out our distant relationship and connectivity issues when working on applications that have many end points within our network.  So the remote team will remote into certain desktops.  And yes we have enough licenses for the software.

Recently we have started to work with TFS source control.  We moved away from Source Safe and CVS.  Well TFS has the concept of workspaces.  Workspaces map locations in the TFS source control to local folders.  As a user you may log into multiple machines and create workspaces on those machines.  If you look at the works spaces on a machine via the Workspaces drop down in Visual Studio you will see all the workspaces that you have created across the multiple machines. 

So now say another user logs into your machine on the evening shift.  Why not safe a machine? Maybe not the best idea maybe so, but who cares.  Because this scenario can be just as valid if I decide to run-as another user in my Visual Studio environment to debug a security context specific bug.  Well if I need to get the latest code from TFS I will have to create a workspace and map to the same local file system as the workspace created under my everyday Windows account.  Well TFS will not allow this.  Two workspaces cannot be mapped to the same local path.

You will have to delete the other workspace so you can map a new workspace for the currently logged in user.  I guess you could say that why are you mapping to the same place.  Why not isolate the code from each developer in there own profiles?  Yes we could, but up to this point we have just wanted consistency in directory structure origin.  I tried this it just moved our problem to the IIS Virtual directory mappings.  Yep, we are using IIS rather than the built in web server, reality first I say. 

So assuming we are not going to change our ways, we will have to find the workspace that is using our local mapping and delete it.  To do this we need to use the tf.exe command line tool. 

 

Here is the work around I have come up with while sharing a machine between users: (substituted users and machine names)

The bolded characters are my command line text.

First list the workspaces for a machine, in this case it is JoesComputer for all owners.  Notice the /owner:* wildcard search.

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE>tf workspaces /computer:JoesComputer /owner:* /server:http://TFSServer:8080

Server: TFSServer

Workspace               Owner                Computer     Comment

----------------------- -------------------- ------------ -----------------------------------------------------------------------

HOBO_MyAccount          ShookJ               JoesComputer Temporary CruiseControl.NET Workspace

HOBO_NotificationServer ShookJ               JoesComputer Temporary CruiseControl.NET Workspace

HOBO_OnlineBillPayCsr   ShookJ               JoesComputer Temporary CruiseControl.NET Workspace

HOBO_ProfileManager     ShookJ               JoesComputer Temporary CruiseControl.NET Workspace

JoesComputer            ccnetServiceAccount  JoesComputer

JoesComputer            BullPenUser             JoesComputer

Then delete each of the Workspaces that are on the machine mapped to you machine name. 

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE>tf workspace /delete JoesComputer;ServDevelopmentBuild /server:http://TFSServer:8080

A deleted workspace cannot be recovered.

Workspace 'JoesComputer;ServDevelopmentBuild' on server 'http://TFSServer:8080' has 0 pending change(s).

Are you sure you want to delete the workspace? (Yes/No) y

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE>tf workspace /delete JoesComputer;BullPenUser /server:http://TFSServer:8080

A deleted workspace cannot be recovered.

Workspace 'JoesComputer;BullPenUser' on server 'http://TFSServer:8080' has 3 pending change(s).

Are you sure you want to delete the workspace? (Yes/No) y

This worked for me because we happened to have a workspace name that was the same as the machine name.  Technically that is not the reason for the conflict mentioned in the email below.  No two workspaces mapped to the same computer can point to the same folder.  Great.  But we cannot share workspaces either across user profiles.  Seems pretty crummy. 

There Is a more advanced search that will show you the mappings also so you know which workspaces to delete.

tf workspaces /computer:JoesComputer /owner:* /server:http://TFSServer:8080 /format:detailed

Then just manually recreate your workspace.  We will just have to live with this while sharing machines.

Has anyone our their created a script to make this more streamline?  Because I have admin rights on TFS this may not work for all users.  I will have to drill down on this.  But another path would be to isolate by user profile and thus have your own workspace.  Then create a script that will re-map your IIS virtual directories.  I have wanted to work with PowerShell and maybe this would be a good piece of automation to get me started.

BTW  I have also faced issues with the new VSTS DB Pro tool in a shared workstation environment.  The temporary validation database is only owned by the person that created it.  But when another developer logs onto a colleagues machine and opens a solution with a database project in it you may receive errors.  If you try to build the project you will receive errors.  I found that the temporary database from the primary user is trying to be accessed by the visiting user.  The database file is secured to a single owner.  More on this later. 

Find anyone's passport online:

http://www.scrolllock.nl/passport/

I was just going through my bills.  I have a hospital bill here for $778.28.  That is after the insurance payment.  Gotta pay those yearly deductibles.  I am thinking if I am going to pay a bill that big I should be getting some air miles racked up on the Visa.  So I visit the URL on the bill, follow the instructions.  I put in the account number and the total bill.  I get back an option to pay off in full for, $700.45.  That is a 10% discount.  Typically I just pay online through my checking account.  I will have to remember this and so will you.

Every thing is cool here.  Just taking a picture before we let this nice fish go.  The fish just keeps moving.

The the steelhead twists and arches back into my face.  It felt like I was hit with a hammer.  Notice the red spot.  Now hours later it realy hurts.  Thanks to my brother for getting the picture and laughing the whole time.

Hey Mr Schwary, is this one big enough for you.

Here are some nice arials.

Nothing technical here to see this week.  :) not that I have posted much in the recent past.  My brother is a river guide on the Situk river in Yakutat Alaska.  This weekend I am visiting him while he has some time off. 

Here are some pictures of me with some real nice chromies.   This is with 3 hours of sleep and 2 hours after getting off the plane.

Tomorrow I will have a picture from a magazine article my brother wrote for Salmon Trout Stealheader magazine.  The Nick Ammato, the editor says it is one of the best fish jumping ever caught on film.  It also is presented on the wall just as you enter the side door to the Yakutat Lodge.

Oh and here is a bear we saw.  He was pretty far away.

Please go and read this:

http://www.interact-sw.co.uk/iangblog/2007/05/09/uacproblem

I have been spelling Identity wrong for a long time in the title of this Blog.  Not one person mentioned it.  Maybe everyone thought I meant it that way?

I just installed the updated TestDriven.Net and the Ncover 1.5.2 Beta.  In seconds I was using a explorer interface that just walked me right to the code that was not covered.  I did not need to read instructions, nothing.  It just made sense.  This is zero friction for sure.

This is the best place to go to link you to the piece needed:

http://weblogs.asp.net/nunitaddin/archive/2006/01/30/436896.aspx

You need:

• TestDriven.Net
• NCover 1.5.1 Beta (or later)
• .NET 2.0 framework.

It runs on any version of Visual Studio, any!

I have installed an interesting application - BlogJet. It's a cool Windows client for my blog tool (as well as for other tools). Get your copy here: http://blogjet.com

"Computers are incredibly fast, accurate and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination." -- Albert Einstein

Recently, ok more than 7 or 8 months ago.  It has been a while since I last blogged.  I was working on a intranet web application and faced with testing role based business rules.  I needed to impersonate users temporarily.  I needed both positive and negative tests.  I needed to package the impersonation code so all developer on our team could easily reuse.  I needed to do it over night so team members that needed it could start right away.  I already implemented some code in our app for temporary impersonation but it wasn't abstracted to a reusable piece of code.  This is how I envisioned the code would look when developers used it. (simplified case)
 
[Test]
publicvoid Test_A() {
    ILocation location = LocationServices.GetLocation("LOCATION_TYPE_A");
    Assert.IsNotNull(location);
    //Impersonate a non supervisor.
    using (IdentityBlock idBloc = newIdentityBlock("HoboJoe")) {
        idBloc.ToString(); //just to get rid of warnings.
        Assert.IsFalse(location.CanViewPersonnel());
    }
    //Impersonate an actual Supervisor.
    using (IdentityBlock idBloc = newIdentityBlock("JoeCool")) {
        idBloc.ToString(); //just to get rid of warnings.
        Assert.IsTrue(location.CanViewPersonnel());
    }
}
 
Now I was off to create the IdendityBlock class to abstract the impersonation code away.  So I headed home for the night.  After all this is when the real creative work starts.  If you are going to write Impersonation code then keep Keith Browns book/wiki close by. 
This is what I came up with:

public class IdentityBlock : IDisposable {
    WindowsImpersonationContext wic;
   [DllImport("advapi32.dll", SetLastError=true)]
   static extern bool LogonUser(
                                                string principal,
                                                string authority,
                                                string password,
                                                LogonTypes logonType,
                                                LogonProviders logonProvider,
                                                out IntPtr token);
   
    [DllImport("kernel32.dll", SetLastError=true)]
    static extern bool CloseHandle(IntPtr handle);
   
    enum LogonTypes : uint {
        Interactive = 2,
        Network,
        Batch,
        Service,
        NetworkCleartext = 8,
        NewCredentials
    }
   
    enum LogonProviders : uint {
        Default = 0, // default for platform (use this!)
        WinNT35, // sends smoke signals to authority
        WinNT40, // uses NTLM
        WinNT50 // negotiates Kerb or NTLM
    }
 
    public IdentityBlock(string user) {
        SetThreadIdentity(user);
    }
 
    public IdentityBlock(string user, bool impersonateMe) {
        if(impersonateMe) {
            wic = ImpersonateUser(user);
        }
        else {
            SetThreadIdentity(user);
        }
    }
    ~IdentityBlock() {
            this.Dispose(false);
    }
 
    ///<summary>
    /// Set the thread identity
    ///</summary>
    ///<param name="user"></param>
    privatevoid SetThreadIdentity(string user) {
        WindowsIdentity id;

        string pwd = GetPassword(user); // psuedo code
        IntPtr token;
        bool result = LogonUser(
                                              user, "[Put_Your_Domain_Here]", 
                                              pwd,
                                              LogonTypes.Interactive,
                                              LogonProviders.Default,
                                              out token);
       
        if (result) {
            id = newWindowsIdentity(token);
            CloseHandle(token);
            WindowsPrincipal p = newWindowsPrincipal(id);
            System.Threading.Thread.CurrentPrincipal = p;
        }
        else {
            Console.WriteLine("LogonUser failed: {0}",
                Marshal.GetLastWin32Error());
            thrownewException(string.Format("LogonUser failed: {0}. User: {1}",      
                Marshal.GetLastWin32Error(), user));
        }
    }
 
    ///<summary>
    /// Set the thread identity and impersonate.
    ///</summary>

    privateWindowsImpersonationContext ImpersonateUser(string user) {

        WindowsIdentity id;

        string pwd = GetPassword(user); // psuedo code
        IntPtr token;
        bool result = LogonUser(
                                              user,
                                              "[Put_Your_Domain_Here]", 
                                              pwd,
                                              LogonTypes.Interactive,
                                              LogonProviders.Default,
                                              out token);
        if (result) {
            id = newWindowsIdentity(token);
            CloseHandle(token);
            WindowsPrincipal p = newWindowsPrincipal(id);
            System.Threading.Thread.CurrentPrincipal = p;
            return id.Impersonate();
        }
        else {
            Console.WriteLine("LogonUser failed: {0}",
                Marshal.GetLastWin32Error());
            thrownewException(string.Format("LogonUser failed: {0}. User: {1}",            
                Marshal.GetLastWin32Error(), user));
        }
    }



 
    #region IDisposable Members
    ///<summary>
    /// Dispose of this object's resources.
    ///</summary>
    publicvoid Dispose() {
        Dispose(true);
        GC.SuppressFinalize(true); // as a service to those who might inherit from us
    }
 
    ///<summary>
    /// Free the instance variables of this object.
    ///</summary>
    protectedvirtualvoid Dispose(bool disposing) {
        if (! disposing)
            return; // we're being collected, so let the GC take care of this object
        else {
            if(wic != null) wic.Undo();
                WindowsPrincipal p = newWindowsPrincipal(WindowsIdentity.GetCurrent());
                System.Threading.Thread.CurrentPrincipal = p;
            }
    }
    #endregion
}
 
Note:  I supply the user name to the IdentiyBlock constructor.  And then in the guts of the the IdentityBlock I am tied to a GetPassword implementation.  Rather it be hard coded or a Static helper class.  But even in a UnitTest harness you need to figure out a secure method to protecting these Prinicpals.  If you are lucky enough to be on a native Windows 2003 AD then you could use Protocal Transition.  Maybe I will come back to this some day polish that last piece.
 
Comments and ideas are welcome.
 
And I was working with NUnit.  I believe that tools like MBUnit may have impersonation abstraction features build in.  Don't quote me on that.

I like the part about "hard work that very few people have the courage and patience to undertake".  I have worked with a lot of bright people.  Most if not all avoid playing out the full implementation of a secure deployment.  Do they lack courage or patience?  Why?  I still don't know many developers that know much about their platform.  My context is Windows.  I always feel like I have more to learn about platform but I have yet to work with a dev that gets it more than I do.  Am I alone? 

Quotes from Interview with Marcus Ranum

It's not a technology problem, it's a management problem. There are plenty of tools that can be used to control inter-host trust, but they are generally not used because they're "too hard" or "inconvenient" or whatever.

In order to build really secure systems you need to understand the trust relationships between your systems and then build your systems to enhance and support your mission based on those trust relationships. But that's hard work that very few people have the courage and patience to undertake. So instead, they want to just throw technology at the problem - which won't work - because there is no amount of technology that can effectively build your trust relationships for you if you don't understand them yourself. d

Via Exploration Through Example, a story from Jonathan Kohl.

There are a lot of changes here.  Looks like I will be able to use Triggers instead of custom shedulers I created a year back.  I am also going to rethink my custom labeler and see if I can use what is already available in ccnet and/or NAnt.  I was trying to get the sourcecontrol block for source safe to work with both filtered and multi so I could always pull from the tools directory in source control and only look for changes in the rest of the source safe project directory.  Why?  I am storing a project specific version of NAnt in the tools directory and I need to pull it down regardless the first time.  I may be thinking this whole problem the wrong way right now.  But I did want to mention that the new ccnet has some nice items in the log. 

[CruiseControl Server:Warning]: Unused node detected: autoGetSource="true"
[CruiseControl Server:Warning]: Unused node detected: <projectUrl>http://e-swis-6/SwisCIDev</projectUrl>
[CruiseControl Server:Warning]: Unused node detected: <intervalTrigger seconds="300" />

Notice the the logs are indicating problems with my ccnet.config file.  It isn't crashing and it isn't ignoring problems.  I really like this feature.  ProjectUrl is obsolete if you read the docs for the email task.  intervalTrigger needs to be in a <triggers> element and I am yet to figure out why autoGetSource is giving me problems but at least I know there is a problem and where it is at.  So far so good. 

Part of the reason I started upgrading is because I want to redesign my directory tree.  I want to have a tools directory for each build project.  Now that we are up to about 10 project building on the same server we have had issues with different projects running various versions on NUnit.  Also every project is sharing the same NAnt.exe thus the same NAnt.exe.config resulting in all projects writting to the same nant.log files.  That is a nightmare when you want to read them.  Anyways it seems downloading tools specifically for each project will help isolate and allow for different projects to use third party tools of varying versions.