Menu

Set ConnectWise client portal passwords using SQL

Google+0
LinkedIn0

Update Notice: If you are using ConnectWise version 2016.3 or later, the schema has changed. Check out this updated post for the changes.

ConnectWise doesn’t automatically assign a client portal password to contact records when they’re created or imported using the Lead Import tool. To do this, you must find the contact record, click Set Customer Portal Options, enter a password, and click the Save button.

It’s not an onerous process, per se, but if you have to do this frequently or for more than just a few contacts at a time (e.g. as part of on boarding a new client with 30 employees) it can become quite tiresome.

If you have access to the SQL server that hosts your ConnectWise Database, the following SQL script allows you to easily set a password (in this example, bananas!) for contacts in ConnectWise.

USE [cwwebapp_site]
GO
UPDATE Contact
SET    Contact.CM_Password = (SELECT [dbo].[udf_EncrDecr] ('bananas!', 'E'))
FROM   Contact
       INNER JOIN Company
         ON Contact.Company_RecID = Company.Company_RecID
       INNER JOIN Company_Status
         ON Company.Company_Status_RecID = Company_Status.Company_Status_RecID
       INNER JOIN Company_Type
         ON Company.Company_Type_RecID = Company_Type.Company_Type_RecID
WHERE  ( Company_Status.Company_Status_RecID = 1 )
   AND ( Company_Type.Company_Type_RecID = 1 )
   AND ( Contact.CM_Password IS NULL )
   AND ( Contact.Inactive_Flag = 0 )

Lines 1 and 2 connect you to the ConnectWise database. Make sure you change line 1 to reference the name of your ConnectWise database. Lines 3 and 4 actually set the password. Lines 5-15 make sure you’re only setting a password for contacts that:

  1. Belong to companies with company status of Active and company type of Client
  2. Don’t already have a password
  3. Aren’t marked as Inactive within ConnectWise

Of course, you can set the password to anything you like by changing the word passed to the faux-encryption function (udf_EncrDecr) in line 4. Or take it one step further by creating an individualized password consisting of the first 4 characters of the contact’s first name plus the first 3 digits of the company’s phone number:

SET    Contact.CM_Password = (SELECT [dbo].[udf_EncrDecr] (Lower(
                                     LEFT(Contact.First_Name, 4)) +
                                     LEFT(Company.PhoneNBR, 3), 'E'))

Related Posts