How to set the ConnectWise contact portal password in SQL

THIS IS NOW OUTDATED AS WE NO LONGER USE CONNECTWISE PORTAL PASSWORDS, YOU CAN SET PASSWORDS IN THE ADMIN CONSOLE UNDER THE USERS CONTACT PAGE.

 

Hat tip to Brad Benner of Nexnow for this topic. Check out his awesome blog at http://www.nexnow.com/blog

Adapted from original post here http://www.nexnow.net/blog/2011/11/24/set-client-portal-passwords-using-sql/

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.

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, deskdirector!) for contacts in ConnectWise.

USE [cwwebapp_site]
GO
UPDATE Contact
SET Contact.CM_Password = (SELECT [dbo].[udf_EncrDecr] ('deskdirector!', 'E'))
FROM Contact
 INNER JOIN Company
 ON Contact.Company_RecID = Company.Company_RecID
WHERE ( Company.Company_ID = 'deskdirector' )
 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-10 make sure you're only setting a password for contacts that:

  1. Belong to companies specified by Company_ID (change this to the ID of the company you want to alter
  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'))

 

To take it a step further you could also create this password as a random string. The following will create an 8 charactor password with a random combination of lowercase letters and numbers:

SET Contact.CM_Password = (SELECT [dbo].[udf_EncrDecr] (SUBSTRING(
 LEFT(CONVERT(VARCHAR(255), NEWID())) 0, 9), 'E'))

 

How did we do?

Contact