I was asked to create a web interface front-end with Microsoft Dynamics CRM as back-end. But I had some troubles setting up the connection, since it has to be done using a domain logon. This doesn't have to be a problem at all, unless your configuration is wrong! In this article I'll explain a few things and point you in the right direction when you have login problems.
As stated earlier, the server running the PHP installation is not Microsoft. In this case a AS400 installation, but it could've been a Linux installation also. I'm using PDO for this article and PHP version 5.2.11. Even if you don't want to use PDO, I recommend using it only for debugging (if possible) since that will give you *most likely* more debug information then the mssql_* family.
When using PDO with a MS-SQL database, you'll need to supply "dblib" as driver and DBLib uses FreeTDS as underlaying library. FreeTDS can be a source of troubles when you're trying to connect, if not configured properly. So I'll kick-off with a little information about it. Don't skip it if you have login problems!
FreeTDS is a library that allows Unix and Linux installations to talk with Microsoft SQL Server and Sybase databases. The default configuration is not set to properly handle domain logins. The minimum FreeTDS version you'll need is 0.6 and the minimum TDS protocol version you'll need is 7.0 (And FreeTDS uses 5.0 als default). You'll need that version to use the "SSPI" (Security Support Provider Interface, also referred too as: Microsoft Integrated Securtiy) feature. So locate your freetds.conf and make sure the TDS version meets the minimum required, I've put mine on: "tds version = 8.0" and I'm using FreeTDS version 1.1.
In the older version FreeTDS used the configuration property "try domain login". However that became deprecated and now it uses automatic detection based on the '\' character in your user name. So, basically just use your full domain user name, something like: "MY_DOMAIN\crmuser" and FreeTDS will try the domain login.
So, assuming FreeTDS now uses TDS protocol version 8.0 and you use your full domain + user name as connection login you should be on the right track and you should be able to login now.
If you are, just like me, connecting with a CRM database and are trying to query filtered views (since that is recommended). Then make sure the account you use is added in MS-CRM also. Your CRM admin should have no problems adding that, but make sure to give it sufficient rights! It's better to start with too many and narrow it down, then to give it insufficient rights to start with. If you are able to login, but get 0 results (while you expected more). Then you have a good chance that your user is not known in CRM or has insufficient rights.
So, now you should be able to login and query the MS-CRM filtered views. If you are still unable to, retrace your steps and check logs, logs are helpful in these times!
An example:
// Fetching some accountstry
{ $pdo =
new PDO
( 'dblib:host=crmdbserver;dbname=MSCRM_DEV',
'MY_DOMAIN\crmuser',
'password' );
} catch
(PDOException
$e) { echo 'Unable to connect, reason: '.
$e->getMessage
();
}$stmt =
$pdo->prepare
("SELECT TOP 10 * FROM FilteredAccount");
$stmt->execute
();
while (false !==
($row =
$stmt->fetch
(PDO::
FETCH_ASSOC))) { // [..]} Thanks to Elizabeth Smith and Richard Lynch who helped me in the right direction.
Relevant reading and links:
If you are connecting from a Microsoft server, then these links are relevant:
