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;