Hello everyone!
Welcome back to the Kaizen series!
Many organizations manage workforce data such as employee designations, contact details, salary bands, and joining dates in an HRMS backed by Microsoft SQL Server, while their sales teams work in Zoho CRM. When a deal needs an assigned pre-sales engineer or a CSM hand off, reps must leave CRM, open the HR portal, and manually look up the right person.
This post shows how to use the Queries feature in Zoho CRM to connect to a Microsoft SQL Server database, fetch employee records with a parameterized SQL query, and display them directly on a Deal Canvas without leaving CRM. We will cover source creation, SSL and Read-Only configuration, writing the query, serializing the response, and a brief overview of how the same query can be reused across Canvas, Custom Related List, and Kiosk.
Prerequisites
- Zoho CRM Enterprise or Ultimate edition (SQL sources require Enterprise or above)
- A running Microsoft SQL Server instance with a database user that has SELECT privileges on the EMPLOYEE table
- The SQL Server instance must be reachable from Zoho CRM either publicly or Zoho CRM IP ranges must be whitelisted in the firewall rules
- The SQL Server hostname must be added to Trusted Domains in Zoho CRM (Setup > Developer Hub > Trusted Domains) if using a DNS hostname
- Developer role or System Administrator access in Zoho CRM
- A custom "Employee ID" field (Single Line) on the relevant CRM module (e.g., Deals or Contacts) to store the external employee reference if passing a single employee ID as a variable. Not required if the query fetches the full list (no variable).
Use Case
Sales reps at a professional services firm need to assign internal team members (solutions engineers, CSMs) to deals, but employee contact and designation data lives only in a Microsoft SQL Server HRMS requiring a manual lookup outside CRM every time.
Solution
Connect Zoho CRM Queries to the HRMS database. A parameterized SELECT on the EMPLOYEE table filtered by {{EMP_ID}}, resolved from a custom field on the Deal record, fetches the assigned employee's profile and displays it on the Deal Canvas in real time.
Follow the steps to implement this solution.
Step 1: Whitelist Zoho CRM IPs in SQL Server Firewall
Before creating the source in Zoho CRM, ensure the SQL Server instance can accept inbound connections from Zoho CRM's servers.
- The SQL Server instance must be accessible over the internet on port 1433 (SQL Server default).
- Zoho CRM's IP ranges must be whitelisted in the firewall rules governing the SQL Server. Refer to the Zoho CRM IP Whitelist guide for the full list of IP ranges for your data center.
- Ensure SQL Server Browser service is running and TCP/IP is enabled in SQL Server Configuration Manager.
- If the SQL Server uses a DNS hostname, also add it to Setup > Developer Hub > Trusted Domains in Zoho CRM.
Step 2: Create a Dedicated Read-Only SQL Server Login
On your SQL Server instance, create a login with the minimum permissions needed.
Do not use 'sa' (SQL Server's built-in System Administrator login) or any admin account. 'System Administrator login has unrestricted access to all databases and server settings.
Step 3: Add Microsoft SQL Server as a Database Source
- In Zoho CRM, go to Setup > Developer Hub > Queries.
- Click the Sources tab.
- Click Add Source.
- From the list of source types, select Database.
- From the available database options, choose MicrosoftSQL.
- Fill in the source configuration as shown in the following image.

Step 4: Configure Advanced Settings
Expand the Advanced configuration section before saving.
Use SSL
Enable the Use SSL toggle. This action,
- Encrypts the connection between Zoho CRM and SQL Server using TLS.
- When a DNS hostname is used, the system also validates the server certificate and hostname match before establishing the connection.
- SQL Server supports SSL/TLS natively. Ensure the SQL Server instance has a valid TLS certificate configured or that the certificate is trusted by the host.
Note
- The SSL setting is IMMUTABLE after the source is successfully created. If your SSL configuration needs to change, you must delete and recreate the source.
- If you add the hostname to Trusted Domains, the system bypasses certificate and hostname verification. Only add genuinely trusted hosts.
Read Only
Enable the Read Only toggle. This action,
- Restricts this source to only SELECT operations at the Zoho CRM level.
- Blocks INSERT, UPDATE, and DELETE queries even if the zoho_crm_reader SQL login is later granted write permissions at the database level.
Since this source is used purely to display employee information in CRM views, you must enable Read-Only.
You can change this setting after the source is created, unlike SSL.
Time Zone
Set Time Zone to match the time zone of your SQL Server instance (e.g., UTC, or the server's local time zone).
The DATE_OF_JOIN and MODIFIED_ON columns are stored as DATETIME in SQL Server (without time zone information). Setting the correct source time zone allows Zoho CRM to convert these values to each viewing user's local time zone for accurate display.
Example: If the server stores MODIFIED_ON = 2026-04-15T18:30:00 in IST, a user in UTC will see 2026-04-15 13:00.
Connection Timeout
Set Connection timeout to 10 seconds. This action defines how long Zoho CRM waits for the SQL Server to accept the connection before giving up.
10 seconds accommodates typical network latency while preventing the CRM UI from hanging indefinitely during server downtime or network issues.
Click Validate and Save.
Zoho CRM tests the connection. On success, the source is saved and available for queries.
Step 5: Create the Query
- Go to Setup > Developer Hub > Queries.
- Click Create Query.
- In the dialog box enter the name of the query.
- Click Next.
- Under Configuration, click Change and select the source we added.
- Click Done.

Step 6: Write the SQL Query
In the query editor, enter the following query.
SELECT EMP_ID,EMP_FIRST_NAME,EMP_LAST_NAME,EMP_GENDER,EMP_EMAIL,EMP_PHONE,EMP_DESIGNATION,EMP_SALARY,DATE_OF_JOIN,MODIFIED_ON FROM EMPLOYEE WHERE EMP_ID = {{EMP_ID}} ORDER BY EMP_FIRST_NAME |
Zoho CRM automatically detects {{EMP_ID}} as a query variable. At runtime, the value of the "Employee ID" custom field on the Deal record is passed to this variable.
Step 7: Execute and Validate the Query
- Click Execute Query.
- Enter a valid employee ID from your SQL Server database.
- Click Next.
- Review the raw response.
- Review the Schema panel and verify CRM Field Type mapping.

Step 8: Add a Serializer
The serializer combines the first and last name, formats dates, and computes the employee's tenure.
- Click Add Serializer.
Enter the following JavaScript.
return result.map(record => { // Compute tenure in years from DATE_OF_JOIN const joinDate = new Date(record.DATE_OF_JOIN); const today = new Date(); const tenureYears = Math.floor( (today - joinDate) / (1000 * 60 * 60 * 24 * 365) );
return { emp_id: record.EMP_ID, full_name: record.EMP_FIRST_NAME + " " + record.EMP_LAST_NAME, gender: record.EMP_GENDER, email: record.EMP_EMAIL, phone: record.EMP_PHONE, designation: record.EMP_DESIGNATION, date_of_join: record.DATE_OF_JOIN ? String(record.DATE_OF_JOIN).split("T")[0] : "N/A", tenure: tenureYears + (tenureYears === 1 ? " year" : " years"), last_modified: record.MODIFIED_ON ? String(record.MODIFIED_ON).split("T")[0] + " " + String(record.MODIFIED_ON).split("T")[1].slice(0, 5) : "N/A" }; }); |
This serializer:
- Combines EMP_FIRST_NAME and EMP_LAST_NAME into a single full_name field
- Computes tenure in years from DATE_OF_JOIN to today, a derived field not stored in the database
- Formats date_of_join as YYYY-MM-DD (strips the time component)
- Formats last_modified as YYYY-MM-DD HH:MM.
Click Execute Query again to preview the serialized output.
Click Save to save the query.
Conclusion
The Queries feature in Zoho CRM makes it straightforward to surface employee records from an external Microsoft SQL Server HRMS directly within CRM without any middleware, scheduled sync, or manual copy-paste. The advanced configuration options (SSL, Read-Only, Time Zone, Connection Timeout) ensure the connection is secure, non-destructive, and accurate across time zones.
With a single parameterized query and a lightweight serializer, sales reps can see the full profile of an assigned employee such as designation, contact info, tenure etc., right on the Deal Canvas when they need it most. The same query can be reused across multiple CRM UI components for different team workflows.
This approach is best suited when:
- The employee data is authoritative in an external HRMS and should not be duplicated into CRM fields.
- Real-time accuracy matters (recently onboarded or transferred employees are immediately visible).
- The organization wants to avoid building and maintaining a custom sync integration.
Associating the query with CRM's UI components
You can associate a query with multiple UI components such as Canvas, Kiosk, Custom Related Lists based on the workflow.
Canvas List View
Associate the query with the Canvas List View of the Deals (or Contacts) module.
Map {{EMP_ID}} to the "Employee ID" custom field on the record. When a sales rep opens a Deal, the assigned employee's full profile, including name, designation, phone, email, tenure etc., appears as an "Assigned Employee" panel on the canvas. Refer to our Kaizen post for associating a query with Canvas List View.
Kiosk
Associate the query with a Kiosk Decision component to build an internal lookup screen, say a self-service screen, where a sales rep types an Employee ID, and the Kiosk fetches and displays that employee's profile from the HRMS. The Decision component evaluates the query response (e.g., checking if EMP_DESIGNATION matches a required role) and branches the Kiosk flow accordingly.
Map {{EMP_ID}} to a Kiosk input variable collected in a preceding Screen component.
Custom Related List
Use a modified version of the query (filtered by ACCOUNT_ID instead of EMP_ID) to display a related list of all employees assigned to a given account on the Account record. This is useful when multiple internal team members like solutions engineers, CSMs, support contacts, are mapped to an account in the HRMS.
Map {{ACCOUNT_ID}} to the Account record's identifier during association, and the related list will show all employees assigned to that account, updated automatically as the HRMS changes.
For step-by-step instructions on associating a query with a Custom Related List, refer to this Kaizen post.
We hope you found this post useful.
Cheers!
=================================================================================
Recent Topics
KB Articles from Zoho Learn - Import as .md
I love the concept of being able to use articles from Zoho Learn as knowledge base articles for Zia Agents. This will absolutely help with proper documentation and organization of company processes and SOPs. This was a great concept. However, even when
Why don't Zia agents support file uploads?
I am trying to build a Zia Agent that allows uploading of a PDF file and uses the GLM5 model to process it and extract information. But agents.zoho.com has no way to enable file uploads on the agent. Additionally, GLM5 based agents keep outputting their
Building Toppings #10 - Using signals to build real-time notifications between Bigin and Zoho Desk
Hello Biginners! In our previous post, we covered Bigin Variables and Widgets. In this post, we'll explore another component: Signals. To show how Signals work, we will build a real-time use case integrating Bigin and Zoho Desk. It connects Zoho Desk
Cliq iOS can't see shared screen
Hello, I had this morning a video call with a colleague. She is using Cliq Desktop MacOS and wanted to share her screen with me. I'm on iPad. I noticed, while she shared her screen, I could only see her video, but not the shared screen... Does Cliq iOS is able to display shared screen, or is it somewhere else to be found ? Regards
¡Gracias por acompañarnos en los Workshops de Zoho España!
Los pasados 3 y 4 de noviembre celebramos una nueva edición de los Workshops de Zoho España en el Hotel NH Las Ventas de Madrid. Dos jornadas de formación práctica, demostraciones en directo y encuentros con expertos de Zoho en las que compartimos conocimientos,
Feature Request: Fully customizable record layouts in Zoho FSM (movable sections and custom subform tables)
Hi Zoho FSM Team and Community, We’d like to suggest a feature that we think would help many FSM users: full control over the layout of record detail pages, starting with the Work Order view. Current situation When you open a Work Order, its sections
Mass Update Option for Changing Work Order Owner
Hello FSM Team/Mr. Abid, We would like to inquire if there is an option to mass update or bulk change the Owner of Work Orders in Zoho FSM. Current Situation: At the moment, the Owner of a Work Order can only be changed individually, which becomes time-consuming
Never miss a follow-up with personal reminder agent | Zoho Cliq AI Use Case #03
A task mentioned mid-conversation rarely survives the conversation. Someone asks you to follow up, you say you will, and by the time you remember, the message is three hundred lines back, buried under two meetings and a dozen other threads. Personal Task
Calendar won't load
I run into this problem about once a week, where I try to open my Zoho calendar in a web browser, and it just gets stuck, waiting forever to load (see attached screenshot). I'm usually using Edge or Duck-Duck-Go browser. Sometimes it loads just fine,
Change 'default' project dashboard
Is it possible to change the default project dashboard for all projects? Both in layout and in what widgets are shown? We have a lot of (short-running) projects and I want to quickly see the tasks and milestones when I open a Project, but I don't want to change the layout for all my projects manually. Thanks, Menno
Multiple subscription plan line items in Zoho Billing quotes
I run an IT company and sell a lot of licences. A client might have multiple subscription plans including software licences in different categories. A client might have Microsoft licences, antivirus licences, web hosting and maintenance package services.
[ZohoDesk] Improve Status View with a new editeble kanban view
A kanban view with more information about the ticket and the contact who created the ticket would be valueble. I would like to edit the fields with the ones i like to see at one glance. Like in CRM where you can edit the canvas view, i would like to edit
Live connection to DS files in Zoho Creator / ChatGPT
As an idea, we are using ChatGPT to build with Zoho Creator (outside of Zia), which is works brilliantly to assist write deluge, provide test suggesitons etc. Currently we are uploading the DS files which are very point in time, it would be great to have
Unable to integrate Whatsapp Messaging into Zoho CRM - Business Messaging
Hello, I want to set up Business Messaging in our Zoho CRM and am struggling to set it up. I have logged into the account and our facebook business page, our business shows in the dropdown for WhatsApp Business Account but does not for WhatsApp Business
Files in Teamspaces: keep your team's documents where the work happens
Release Note: This feature is available for all accounts across all DCs Hello Everyone, A Teamspace is built around a specific team and a shared objective, and it holds the modules and projects that team works with every day. The document that team depends
Script to access a CBPX file from zoho crm
Hello everyone, I'm planning to write a script in Python or Java in Zoho CRM functions, to access a CBPX file. Is it possible to write this script in the dev console of Zoho? What are the best practices to do this ? Would you have references in the Zoho
Calculate hours
Is there any chance that the the "formula" will have the ability to do calculation using the "time" field in the future? Our company has a form set up for tracking hours worked and we use 2 time fields currently. One for the clock-in time, and one for the clock-out time. It's great for keeping that record, but if there were a way to calculate the hours to show on the report would be awesome. Previously we had used JotForm and their calculator had that ability, but it seems that Zoho doesn't accept
Introducing parent-child ticketing in Zoho Desk [Early access]
Hello Zoho Desk users! We have introduced the parent-child ticketing system to help customer service teams ensure efficient resolution of issues involving multiple, related tickets. You can now combine repetitive and interconnected tickets into parent-child
Zoho Books | Product Updates | November 2026
Hello users, As the year winds down, we've introduced several enhancements to make compliance, inventory management, and everyday bookkeeping easier in Zoho Books. This month's updates include an improved TDS experience, a new way to exchange transactions
Sheet co-edit: no live presence — also on Collaborate demo?
Two Sheet co-edit sessions open OK (same document_id, document.edit: true), but there’s no real-time collaboration: no cell highlights, Group Chat → Users stays empty. Writer co-edit works on the same Office Integrator setup. We see the same on the official
Zoho Writer workflows now support custom stages, parallel reviews, and external approvers
Hello everyone, Zoho Writer now lets you build your own approval chains. The pre-built workflows also get new review modes, completion criteria, and external approver support. What’s new Custom workflows: Design review and approval processes from scratch,
Expanding sales, marketing, and support capabilities in Zoho CRM Plus 3.0
Hey everyone, It's been more than 10 years since we launched Zoho CRM Plus back in 2014. This is quite a milestone, and we've come a long way in empowering you with everything you need to offer an exceptional customer experience. On that note, we expanded
Erase function in Zoho PDF editor does not truly redact
Using the "erase" function in the Zoho PDF editor online does not redact the text underneath when opening the file in Adobe. When I used the erase function to whiteout the areas I didn't want to be seen, I opened the supposed redacted files in Adobe.
Error creating an XML file
I am creating an xml as string in the function Employee.generatePartA_XML_String, and then I want to save it as a file to the field "Part_A_XML", but I get the response: {"code":3700,"message":"Unable to upload a file. Please check and try again."} How
Thunderbird cannot authenticate Zoho on android
I keep getting a message from Thunderbird on Android that it could not authenticate for my account, dadashton@zoho.com., and to update my server settings. Now the settings are exactly the same for my other devices (1 Windows, 1 Mac and 2 Linux) Can anybody
Mailchimp Integration
Hello, I am needing some help. Does anybody have a recommended source for information on the Zoho-Mailchimp integration. We are working on a system to import large amounts of records into Zoho that will sync into Mailchimp for us to run a mail campaign out of. Let me explain what the process we are trying to implement - any information or advice is appreciated. Import record data base into Zoho Leads module --> use Lead source to ID records that are part of this mail campaign Zoho Leads (specific
[Free webinar] Zoho Creator meets AI with MCP | Creator Tech Connect (November 2026)
Hello everyone, We are excited to invite you to another edition of the Creator Tech Connect webinar. About Creator Tech Connect The Creator Tech Connect series is a free monthly webinar consisting of purely technical sessions, where we dive deep into
Invalid Client - Client ID passed does not exist
I tried connecting the Zoho campaign with the "Uncanny Automator". It shows an error " Invalid Client - Client ID passed does not exist". There is no option to add manual API. URL: https://accounts.zoho.eu/oauth/v2/auth?response_type=code&client_id=1000.MSFIUOA1G8YBK7IFRL9Z0NINM6DW9C&scope=ZohoCampaigns.contact.ALL&redirect_uri=https%3A%2F%2Fapi.automatorplugin.com%2Fv2%2Fzoho-campaigns&prompt=consent&access_type=offline
Field of Lookup - 5 Field Association Limit.
This query relates to the post at: https://help.zoho.com/portal/en/community/topic/introducing-the-field-of-lookup-in-crm-public-early-access-2023 My simple (and common) scenario: I select an account / customer lookup and want their address pulled into
Task status - completed - other options
I have a dumb question I know i can make custom statuses for the tasks - but is there anyway to make additional "completed" statuses like for instance if i have a task "call back customer" and i leave a vm for them to call back marking it "completed -
#20 I've Sent It and It's Wrong
Meera spots a mistake in an invoice just four minutes after sending it. She has billed a photoshoot at the half-day rate instead of the full-day rate. Her first thought is to delete the invoice and start again. But is deleting the right thing to do? It
Zoho Forms API
Is there any way to get all form entry list using API? Looking forward to hear from you
BNZ Bank Feeds No Longer Available
The BNZ (New Zealand) bank feeds are still failing and it's been months since they last worked! Uploading bank statements (CSV's) mean waiting to the end of the month when they become available. It is unacceptable to keep my clients waiting for payment
MCP is now easier to access and configure in Bigin
Greetings! We hope you're all doing well. We've previously introduced Zoho MCP for Bigin, which lets you connect Bigin with AI tools and interact with your Bigin data using natural language. Now we're making MCP easier to access and configure directly
Launch agents from more custom button locations and attach files in Ask Zia
Hello all, Your agents now meet your team where the work happens. You can add agents to custom buttons in list views, create and edit pages, and related lists, so users launch them without leaving the page they’re on. What’s new Agents in more custom
Phone system to integrate with Zoho CRM
I am looking for phone software that would allow my agency to do the following within Zoho CRM: Make calls from zoho Track calls and notes Leave pre-recorded messages after reaching voicemail of lead Only 2 members, so need company who will allow just
Introducing PRM: Build, manage, and scale your partner ecosystem
Growing a partner ecosystem is a good problem to have. What starts as a handful of partners can quickly turn into a maze of spreadsheets, emails, and disconnected processes. More partners mean more opportunities, greater reach, and new ways to grow
Zoho Projects connector with Zoho CRM (Related List module) features
Hi community, I use Zoho Projects connected to Zoho CRM, where we associate Projects with Deals, clients, etc.. I am looking to change/edit the fields that are available in the integration. Currently it shows Project Status, Project Completion, Milestones,
Feature Request: Render Markdown (.md) files in Zoho Cliq
Hi, We regularly share Markdown (.md) files in Zoho Cliq. However, when we open these files in Cliq, the content does not render as Markdown—it displays as plain text. This forces us to copy/paste the content into an external Markdown viewer to read it
Free webinar: Zoho Sign for Microsoft apps
Hello, Did you know Zoho Sign works right inside the Microsoft apps you already use? A signature request shouldn't mean leaving Teams for another tab, or downloading an Outlook attachment just to sign it. Zoho Sign integrates with Microsoft 365, Teams,
Next Page