Severn River Cruise on the Mobjack Bay

The weather around Veterans Day 2010 was absolutely stunning; Crystal clear blue skies, 65F, and a fresh 15-20Kts out of the NE.  I got permission to take the Alberg 22 docked near Yorktown, VA for a two-day solo cruise.  With the wind creating Small Craft Advisory conditions, I was thinking of heading NW up the York see West Point from the water and then find a little creek to tuck into.  As I headed out into the York, I was surprised to find ideal flat water conditions.  I realized there was quite a bit of protection from the land, but it was so tempting to head east…so that’s what I did.  As I got nearer to the Guinea flats, the wind picked up.  As I cleared the land and the full fetch of the Mobjack was felt, the waves were a short steep 3-5’ seas.  The Alberg could really have used a reef at this point, but easing the sheet just a little helped with the weather helm.  The boat kept a stead 4.5-5 kts with a nice motion.

 

Being a mid-November Thursday, there weren’t a lot of boats on the wather, so I was relieved to see a 50’ M/V and a little later, I saw what looked like two boats sailing really close together.  Later this turned out to be a gaff-rigged schooner that I followed into the Severn River.  I headed up the North branch and noticed what looked like a large sailboat behind a spit of land.  The tide was about 3’ above normal so it seemed like my 3’ draft should have no trouble getting back there.  I was rewarded with a beautiful little anchorage that was surrounded by some gorgeous Matthew’s County properties.

 

I didn’t have too many chances to snap pictures while sailing (solo SCA kept my attention), but the anchorage afforded an amazing sunset:

http://picasaweb.google.com/snyd1437/ChesapeakBay#

IIS 404 error!!! It's now 2am...thank you very much Microsoft security team

http://support.microsoft.com/kb/315122

 

By default, when IIS is installed on any version of the Windows Server 2003 family, IIS only serves static content (HTML).

Creating a WebReference to a specific CRM tenant

Sometimes it’s the little things that can take forever to figure out.  I inherited a web application that connected to a CRM tenant and after adding a few new attributes to CRM, I needed to update my WebReference.  Unfortunately the last developer had been working in a VPC so my WebReference that I had set to the CRM server resulted in the proxy classes that were “generic” and did not contain my customizations.  I remembered doing this before, but it had been awhile so I had to search for the answer (again):

 

<add key="crmservice.CrmServiceWsdl" value="http://<CRM Server>/mscrmservices/2007/crmservice.asmx?uniquename=<tenant>"/>

Setup a domain user account as the CrmAppPool identity

It was difficult to find the documentation on exactly which groups / policies the CrmAppPool identity needed when changing from Network Service to a domain user account.  Fortunately I found this Microsoft KB which gives you step-by-step instructions (note, I removed the CRM 3.0 portions) and it worked on the first try:

 

1.     Include the domain account user in the following groups in Active Directory:

o    The Domain Users Active Directory group

o    The PrivUserGroup

o    The SQLAccessGroup

To do this, follow these steps:

d.             Log on to a server as a user who has the Domain Admin rights or the rights to update these groups.

e.     Right-click the Domain Users group in Active Directory, and then click Properties.

f.      In the Group name box, type the name of the user who is running the CRM Application Pool, and then click OK two times.

g.     Repeat steps b and c for the PrivUserGroup group and for the SQLAccessGroup group.

If you have more than one Microsoft Dynamics CRM deployment installed, multiple groups exist in Active Directory. To determine the groups that you want to update, follow these steps.

For Microsoft Dynamics CRM 4.0

h.             Run the following SQL statement against the MSCRM_config database:

select id, friendlyname from organization

i.              Note the GUID. For example, the GUID may be C8AB1D52-9383-4164-B571-4C80D46674E3 Org Name.

j.      Find the PrivUserGroup group and the SQLAccessGroup group in Active Directory. The group name contains the GUID that you noted in step b.

2.     Include the domain account user in the following groups in the Microsoft Dynamics CRM server:

o    The local IIS_WPG group

o    The local CRM_WPG group

Note The domain account user must have the following local user rights:

o    The Impersonate a client after authentication right

o    The Log on as a service right

To do this, follow these steps:

d.     In the Microsoft Dynamics CRM server, click Start, point to Administrative Tools, and then click Local Security Policy.

e.     Expand Local Policies, and then click User Rights Assignment.

f.      Right-click Impersonate a client after authentication, and then click Properties.

g.     Click Add User or Group.

Note You may have to click Location to select the domain instead of the local computer.

h.     In the Group name box, type the name of the user who is running the CRM Application Pool, and then click OK two times.

i.      Repeat steps 2c through 2e for the Log on as a service right.

3.     Restart Internet Information Services (IIS). To do this, click Start, click Run, type IISRESET, and then click OK.

 

Connect to CRM Web Service using impersonation

My Dev and Test servers live in two different domains and I am developing an external “portal” for public users to interface with CRM using web services. I am developing locally, but my CRM server is on a different domain, so I need to use a different domain user to connect to the web service. I am using the new SDK and the connection string was easy to change from “Integrated” to “AD”:

<add name="Crm"

connectionString="Authentication Type=AD;Server=http://CRM/Org1;User ID=USA\user1;Password=pass1"

/>

But I also need to use the MetadataService to lookup some picklist values and that requires connecting the “old school” way. We were already using the Crm connection string to configure the MetadataService, so I just needed to check the Auth type and grab the User ID and Password:

public MetadataService GetMetadataServiceForConnString()
{
    string serverPath = GetConfigAttribute("server=http://");
    string[] serverPathParts = serverPath.Split('/');
    string connServer = serverPathParts[0];
    string connOrgName = serverPathParts[1];
    string username = null;
    string password = null;
    string domain = null;
    if (GetConfigAttribute("authentication type=").ToLower() == "ad")
    {
        // need to use active directory credentials
        string[] usernameParts = GetConfigAttribute("user id=").Split('\\');
        domain = usernameParts[0];
        username = usernameParts[1];
        password = GetConfigAttribute("password=");

    }
    return GetMetadataService(connServer, connOrgName, username, password, domain);
}

private string GetConfigAttribute(string searchString)
{
    string connCrm = System.Configuration.ConfigurationManager.ConnectionStrings["Crm"].ConnectionString;
    int pathPadding = searchString.Length;
    int pathStart = connCrm.ToLower().IndexOf(searchString.ToLower()) + pathPadding;
    int pathLength = connCrm.IndexOf(";", pathStart);
    if (pathLength == -1)
        pathLength = connCrm.Length;
    pathLength -= pathStart;
    return connCrm.Substring(pathStart, pathLength);
}

public MetadataService GetMetadataService(string server, string organizationName, string username, string password, string domain)
{
    CrmAuthenticationToken token = new CrmAuthenticationToken();
    token.AuthenticationType = AuthenticationType.AD; //Active Directory
    token.OrganizationName = organizationName;
    MetadataService service = new MetadataService();
    service.Url = string.Format("http://{0}/mscrmservices/2007/MetadataService.asmx", server);
    if (username == null)
    {
        service.UseDefaultCredentials = true;
    }
    else
    {
        service.UseDefaultCredentials = false;
        System.Net.ICredentials WebServiceCredentials = 
            new System.Net.NetworkCredential(username,password,domain);
        service.Credentials = WebServiceCredentials;
    }
    service.PreAuthenticate = true;
    service.CrmAuthenticationTokenValue = token;
    return service;
}

Use xp_cmdshell and BCP to Export SQL Server data to CSV

This article describes how to use xp_cmdshell and BCP to export data to CSV.  One problem I ran into was the error “Unable to open BCP host data-file”.  This turned out to be a file permission issue.  I had to give NETWORK SERVICE (the local account that runs my SQL Server service) Full Access rights to the queryout directory.  I also had to use the –S flag to specify which SQL Instance (my server has two) to connect to:

 

EXEC xp_cmdshell 'bcp "SELECT * FROM <my database>.dbo.account" queryout "D:\queryout\bcptest.txt" -T -S "<my server>\<my instance>" -c -t,'

 

 

Get a list of all the tables in a SQL 2005/2008 database

I thought this SQL 2005/2008 tip was very cool.  This gets a list of all the tables in my database:

 

SELECT [name] FROM <my database>.sys.tables;