When you programmatically connect to the SQL Server, the first factor to consider is what connection string to use to connect to the Microsoft Dynamics CRM database. Previous versions of Microsoft Dynamics CRM stored the database connection string in the registry, but Microsoft Dynamics CRM 4.0 stores the connection string in the MSCRM_CONFIG database. This allows Microsoft Dynamics CRM to store multiple connection strings because each organization uses its own database. The connection strings stored in the MSCRM_CONFIG database use Windows Authentication to connect to SQL Server. This is very important to understand if you want to query against the filtered views and use the native security model, which we will discuss further in the next section. The following code shows a method named GetConnectionString that demonstrates how to retrieve the connection string by organization name.

Note

The connection string is returned in the following format: Provider=SQLOLEDB;Data Source=[SQLSERVER];Initial Catalog=[CRMDATABASE];Integrated Security=SSPI.

public string GetConnectionString(string organizationName)
{
   string configurationDatabaseConnectionString;

   // grab the ms crm config database connection string from the crm server's registry
   using (RegistryKey mscrmKey = Registry.LocalMachine.OpenSubKey(@"SOFTWAREMicrosoft
       MSCRM"))
   {
      if (mscrmKey == null)
      {
         throw new InvalidOperationException(
             @"Registry key 'HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSCRM' does not exist or
             is inaccessible.");
      }

      configurationDatabaseConnectionString = (string)mscrmKey.GetValue("configdb", String.
          Empty);
      if (String.IsNullOrEmpty(configurationDatabaseConnectionString))
      {
         throw new InvalidOperationException(
            @"Registry value 'HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSCRMconfigdb' does not
            exist or is inaccessible.");
      }
   }

   // make a call to config database to get the connection string to the current
   // organization's database
   using (SqlConnection conn = new SqlConnection(configurationDatabaseConnectionString))
   {
      using (SqlCommand cmd = new SqlCommand(
                                  "SELECT ConnectionString FROM Organization WHERE
UniqueName=@orgName", conn))
      {
         cmd.Parameters.Add(new SqlParameter("orgName", organizationName));
         using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
         {
            DataTable organization = new DataTable();
            adapter.Fill(organization);

            if (organization.Rows.Count == 0)
            {
               throw new InvalidOperationException(String.Format("No organization with the
                   name '{0}' exists.", organizationName));
            }

            if (organization.Rows.Count > 1)
            {
               throw new InvalidOperationException(
                                        String.Format("More than one organization with the
                                            name '{0}' exists.", organizationName));
            }

            return (string)organization.Rows[0]["ConnectionString"];
         }
      }
    }

 }

Note

We discuss using the SQL authentication approach later in this chapter.

The Microsoft Dynamics CRM database contains a list of SQL views referred to as filtered views. Each entity offers a corresponding filtered view, and they are convenient because they simplify the underlying table structure into a single entity view. Further, Microsoft Dynamics CRM automatically updates these views each time you make schema changes. The filtered views use the native security model to determine what data Microsoft Dynamics CRM should display to the user .

Note

Filtered views are not available for Microsoft Dynamics CRM Online.

Built-in security is the biggest advantage of using filtered views, but they also provide some extra functionality. Querying against the base tables in the Microsoft Dynamics CRM database can lead to some pretty complex SQL statements. Using the filtered views will save you from having to add extra joins to retrieve the text values for some attribute types, such as PickList, Lookup, or Customer. Filtered views contain columns for the name field of certain attribute types. This saves you from having to do an extra join in your query. For example, if you need to retrieve your Account’s primary contact’s full name, you simply select the prima-rycontactidname field in your query, as shown here:

SELECT
    primarycontactidname
FROM
    FilteredAccount
WHERE
    accountid = '<insert Account Guid>'

To do this using the base tables requires a more complex query that joins on the ContactBase table:

SELECT
    c.fullname
FROM
    AccountBase a
INNER JOIN
    ContactBase c ON a.primarycontactid = c.contactid
WHERE
    accountid = '<insert Account Guid>'

The following attribute types will have a corresponding name field in the filtered views:

To see a list of all available columns in the filtered views, access them using Microsoft SQL Server Management Studio.

Accessing filtered views in Management Studio

Microsoft Dynamics CRM uses a custom SQL function to determine the proper system user with which to enforce the proper security settings. This function requires authentication to SQL Server by using Windows authentication or it needs to be passed a valid user context for your queries to return data.

In most cases, you can simply connect to the database by using Windows authentication instead of SQL Server authentication.

More Info

Remember that Microsoft doesn’t support changes to the Microsoft Dynamics CRM databases, including adding your own routines or stored procedures. The preferred recommendation is to create your own database to store your custom routines.

This SSPI authentication approach presents numerous challenges in environments with multiple servers because it relies on Kerberos and delegation. What we have seen in practice is that these networking issues can be very problematic to maintain and troubleshoot.

An alternative to the Kerberos authentication approach is to authenticate to SQL Server using SQL authentication and then switch the user context programmatically in your query.

You can use the content_info() function to impersonate a user. First, create SQL Server authentication with an account that has access to any custom and Microsoft Dynamics CRM databases. Then, use this account when connecting to the database with a connection string such as the following:

server=databaseserver;database=yourcustomdatabase;uid=sqluser;pwd=sqlpwd

With SQL Server 2005, you can add synonyms to the Microsoft Dynamics CRM filtered views to query from your custom database. Synonyms provide a pointer (or alias) to its target, in this case the filtered views, so that you don’t have to refresh the synonym when Microsoft Dynamics CRM updates the filtered views. Then, you can use the command in a routine such as the following:

create procedure MyStoredProcedure
(
  @userid uniqueidentifier
)
as

declare @original uniqueidentifier
set @original = context_info() -- store original value

set context_info @userid

/* Execute code with this new context value */
-- Example: This will pull only the accounts that the @userid has read access to
-- select name from filteredaccount

-- Set context back to original value
if @original is null
      set context_info 0x
else
      set context_info @original
end

Because context_info() persists for the entire session, you simply capture what it was before you changed it and then set it back after the query logic is complete.

Tip

You won’t be able to set the context_info() directly to null. If you wish to null out the context, use the following command: set context_info 0x.

Microsoft Dynamics CRM filtered views do have a potential drawback when it comes to performance. Because the views were designed to denormalize picklist and lookup relationships, the number of joins can be quite large. If your tables contain a large volume of data, you might find that these views seriously impact performance. If you find the performance of the filtered view lacking, you may need to query the base tables and provide your own security mechanism to filter the data.