Kaizen 238 Fetching Employee Data from Microsoft SQL Server into Zoho CRM Using Queries

Kaizen 238 Fetching Employee Data from Microsoft SQL Server into Zoho CRM Using Queries


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

  1. Zoho CRM Enterprise or Ultimate edition (SQL sources require Enterprise or above)
  2. A running Microsoft SQL Server instance with a database user that has SELECT privileges on the EMPLOYEE table
  3. The SQL Server instance must be reachable from Zoho CRM either publicly or Zoho CRM IP ranges must be whitelisted in the firewall rules
  4. The SQL Server hostname must be added to Trusted Domains in Zoho CRM (Setup > Developer Hub > Trusted Domains) if using a DNS hostname
  5. Developer role or System Administrator access in Zoho CRM
  6. 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.
  1. The SQL Server instance must be accessible over the internet on port 1433 (SQL Server default).
  2. 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.
  3. Ensure SQL Server Browser service is running and TCP/IP is enabled in SQL Server Configuration Manager.
  4. 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

  1. In Zoho CRM, go to Setup > Developer Hub > Queries.
  2. Click the Sources tab.
  3. Click Add Source.
  4. From the list of source types, select Database.
  5. From the available database options, choose MicrosoftSQL.
  6. 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,
  1. Encrypts the connection between Zoho CRM and SQL Server using TLS.
  2. When a DNS hostname is used, the system also validates the server certificate and hostname match before establishing the connection.
  3. 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.

Notes
Note
  1. 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.
  2. 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,
  1. Restricts this source to only SELECT operations at the Zoho CRM level.
  2. 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

  1. Go to Setup > Developer Hub > Queries.
  2. Click Create Query.
  3. In the dialog box enter the name of the query.
  4. Click Next.
  5. Under Configuration, click Change and select the source we added.
  6. 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

  1. Click Execute Query.
  2. Enter a valid employee ID from your SQL Server database.
  3. Click Next.
  4. Review the raw response.
  5. 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.
  1. Click Add Serializer.
  2. 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:
  1. Combines EMP_FIRST_NAME and EMP_LAST_NAME into a single full_name field
  2. Computes tenure in years from DATE_OF_JOIN to today, a derived field not stored in the database
  3. Formats date_of_join as YYYY-MM-DD (strips the time component)
  4. 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:
  1. The employee data is authoritative in an external HRMS and should not be duplicated into CRM fields.
  2. Real-time accuracy matters (recently onboarded or transferred employees are immediately visible).
  3. 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.
For more details, refer to our Kaizen post on Handling Query variables in Zoho CRM and associating it in Kiosk.

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.
Writer to us at support@zohocrm.com or let us know your thoughts in the comments.

Cheers!

=================================================================================




      • Sticky Posts

      • Kaizen #198: Using Client Script for Custom Validation in Blueprint

        Nearing 200th Kaizen Post – 1 More to the Big Two-Oh-Oh! Do you have any questions, suggestions, or topics you would like us to cover in future posts? Your insights and suggestions help us shape future content and make this series better for everyone.

      • Kaizen #226: Using ZRC in Client Script

        Hello everyone! Welcome to another week of Kaizen. In today's post, lets see what is ZRC (Zoho Request Client) and how we can use ZRC methods in Client Script to get inputs from a Salesperson and update the Lead status with a single button click. In this

      • Kaizen #222 - Client Script Support for Notes Related List

        Hello everyone! Welcome to another week of Kaizen. The final Kaizen post of the year 2025 is here! With the new Client Script support for the Notes Related List, you can validate, enrich, and manage notes across modules. In this post, we’ll explore how

      • Kaizen #217 - Actions APIs : Tasks

        Welcome to another week of Kaizen! In last week's post we discussed Email Notifications APIs which act as the link between your Workflow automations and you. We have discussed how Zylker Cloud Services uses Email Notifications API in their custom dashboard.

      • Kaizen #216 - Actions APIs : Email Notifications

        Welcome to another week of Kaizen! For the last three weeks, we have been discussing Zylker's workflows. We successfully updated a dormant workflow, built a new one from the ground up and more. But our work is not finished—these automated processes are

        • Recent Topics

        • DYK 4 - Reactions

          Did You Know that you can react to comments in Zoho Projects? In a collaborative workspace, comments come in throughout the day. Some need a detailed reply, others just need to be acknowledged, and the more the replies, the harder it is to follow the

        • Atlassian Jira integration live in Zoho Apptics

          Tracking crashes and fixing them often happen with different tools. You might use Zoho Apptics to track crashes and understand what went wrong and Jira to resolve those issues. If you use both, you're probably familiar with the manual effort involved

        • GLM 5 not available

          Hello, I am trying to setup a Zia Agent using agents.zoho.com. The settings says that GLM5 is among the list of free zoho hosted models available. However, when I try to setup an agent and pick a model from the list only GLM 4.7 Flash is available. How

        • Native QuickBooks integration for Zoho CRM: Connecting sales and finance

          Greetings, I hope all of you are doing well. We're excited to announce Zoho CRM's integration with QuickBooks Web, which is designed to synchronize your CRM data with your QuickBooks accounting records and bridge the gap between sales and finance. This

        • Knowledge base articles is now available in the Zoho Desk mobile app!

          Hello all,   As a customer service agent, every day you might have to deal with many questions and issues reported by the users. With Knowledge Base, you can reduce the issue resolution life cycle for your organization.   We are delighted to announce that we have brought in support for 'Knowledge Base articles' in the Zoho Desk iOS mobile app.  This feature is already available for Android users.   KB articles are available to iOS users in the latest version of the app (v2.4.9). You can update the

        • Urgent Issue – Draft Reply Fails to Save When Message Contains Code Snippets

          Hello Zoho Desk Support Team, We are experiencing a critical issue in the ticket reply editor that is directly impacting our support operation. Whenever we write a reply containing code snippets, URLs, or technical content (for example, terms like “curl

        • 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 CRM Meetings Module Issues

          We have a use-case that is very common in today's world, but won't work in Zoho CRM. We have an SDR (Sales Development Rep) who makes many calls per day to Leads and Contacts, and schedules meetings for our primary Sales Reps. He does this by logging

        • Huge confusion in zoho crm and zoho analytics

          Context => We have reporting based hierarchy in zoho crm and basically there will be one sales head and couple sales managers and 10 pre sales excutives divided between 2 sales managers we have maintained that in zoho crm and there is complex reporting

        • information on a suggestion how to treat old records in the - pipeline deals, notes, activities (tasks- events-calls) and emails etc.

          Hi, I would like information or a suggestion on how to treat old records in the - pipeline deals, notes, activities (tasks- events-calls) and emails etc. ? As each option (pipeline deal, notes, activities, emails etc.) counts as a record and as we advance

        • Inventory batch details

          Hi there, I'm trying to get the batch details of an item, here's what I've done so far. I've sent cUrl request to the below endpoint and I get a successful response. Within in the response I find the "warehouses" property which correctly lists all the

        • Automated entries past the current month in a calendar report

          Hi all, I have an autmation problem. I have a from which on succesfull entry adds either 5 or 10 more of these entries with a slight change so our customers can see it throug a calendar report on the webiste. The entry put in manually shows up perfectly

        • What's New in Zoho POS - April 2026

          Hello everyone, Welcome to Zoho POS’s monthly update, where we share our latest feature updates, enhancements, events, and more. Let’s take a look at how April went. Access and manage other web applications in Zoho POS with Web Tabs You can now access

        • Zoho Trident Windows - Streams Not Visible

          Namaste We’re having an issue with Streams not being visible in Trident (Windows), which is important for us as we share many emails internally. It appears that the feature to show Streams above the Inbox folder, as seen in the default mailbox view, is

        • Search through email contents

          Is there a way to search through the email history of a lead? Meaning if among the 50 emails to a lead about different topics I want to find all the ones with the word "pizza" in the body of the email, is that possible? Thanks for your help, Hanan

        • Adding Multiple Products (Package) to a Quote

          I've searched the forums and found several people asking this question, but never found an answer. Is ti possible to add multiple products to a quote at once, like a package deal? This seems like a very basic function of a CRM that does quotes but I can't

        • Early Access: Check Printing in Zoho Books

          Hello Everyone,   Are you constantly writing checks to pay your vendors?   We've got a great news to share with you! You can now pay your vendors by writing and printing a check directly from Zoho Books. The feature is ready and we'll be rolling it out to our customers in phases.  It is available in the  US Edition of Zoho Books and also in the Global edition, where the country is selected as USA and the currency is USD.   Here’s a summary of what’s possible:   1. Write and print a check. 2. Make

        • Trash - Delete All

          We use 6 inbox shared (via IMAP). The Trash bin contains a lot of messages. Is it possible to delete them all permanently? Is there a rule to empty trash bin after xx days? Deleted permanently message in TeamInBox are deleted permanently in the related

        • Internal mails on our company domain (managed by Zoho) do not get delivered

          Hi last week Thursday and Friday a colleague had sent me two emails which did not show up at all in my inbox, spam or anywhere else. What this a problem with Zoho mail in general or did this affect just us? From the forum is reads like many problems had

        • Upgraded sentiment analysis model for more accurate detection

          Hello everyone! Sentiment Analysis in Zia is being upgraded to a newer model to improve how customer sentiment is detected and interpreted. This transition is aimed at getting better contextual understanding across all supported channels. As part of this

        • Paste Options don't work

          I've always wondered about this, as I've experienced this issue for quite some time now. Why don't the right-click Paste options work properly in Zoho Writer? I can use Ctrl + V without any issue, but if I right-click and use one of the Paste menu options,

        • What's New - April 2026 | Zoho Backstage

          Hello everyone, April has been focused on improving speed, reliability, and overall platform performance. Here are a few updates from this month. Safaricom M-PESA for event payments in Kenya Organizers hosting events in Kenya can accept payments through

        • Enrich your contact and company details automatically using the Data Enrichment topping

          Greetings, I hope you're all doing well. We're happy to announce the latest topping we've added to Bigin: The Data Enrichment topping, powered by WebAmigo. This topping helps you automatically enhance your contact and company records in Bigin. By leveraging

        • Adding contact to account automatically in helpdesk?

          Hello, We're using Zoho desk to managed our helpdesk tickets.  Is there a way to add contacts (email senders) automatically to an account based on their emails domain name?  For example:  when UserA@companyx.com send an email to our support mailbox, a trigger or a workflow would automatically add the userA to Companyx account if it does exists  Please advise Thank you 

        • Consolidate your workflows and be more productive - Introducing Multiple Conditions in Workflow Rules

          Workflow rules in Zoho CRM helps you automate your routine tasks and save time. Usually, while creating a workflow rule, you'd provide the basic details of the rule, specify when the rule should be triggered and associate set of actions to be executed whenever the rule criteria is met. And you’d create a new workflow rule for every new automation that you required.  Not anymore! You can now combine multiple conditions and the corresponding actions to follow in a single workflow rule. Let’s look at

        • How has Zoho still not resolved Daylight Savings Time?

          According to these forums Zoho has been working on DST for 12 years. Totally unacceptable. Am I missing something? Why are other customers who observe DST not screaming for this to be fixed? Are there reasonable workarounds? This is a must-have for

        • Overview on users IMAP settings

          We have about 30 users who all have the channels/email/email configuration/IMAP integration/O365 enabled and emails are synchronized. Here my problem: Passwords for the email accounts are expiring on individual bases and most of the users forget to update

        • Digest Avril - Un résumé de ce qui s'est passé le mois dernier sur Community

          Chers utilisateurs, Un nouveau mois riche en nouveautés vient de filer à toute vitesse chez Zoho Community France ! Jetons un œil à ce qu’il s’est passé. Des webinaires à votre image avec Zoho Webinar Vos pages d’inscription sont souvent le premier point

        • Introducing SlyteUI : From Idea to a Working Interface in Minutes

          SlyteUI is now live across all DCs as of 4th May 2026! Hello everyone! Are you spending hours building basic UIs? Does even the smallest customization feel like a major task? CRM customization should feel intuitive and straightforward, not time consuming

        • Integrate QuickBooks with Bigin and streamline your sales and accounting!

          If your business relies on Bigin for customer management and QuickBooks for accounting and invoicing, this new integration is here to make your operations more efficient. By connecting these two platforms, you can now manage your CRM and financial processes

        • Updates to native forms in Zoho LandingPage

          Hello all, We have a couple of updates to native forms in Zoho LandingPage that we think you will find useful. Country code on phone number fields You can now enable a country code dropdown on the phone number field. Visitors select their country code

        • Import KB template OR Export template for zoho desk?

          Greetings. Can you tell me if there is a way to get an EXPORT of my KB articles? OR is there a template you supply for importing KB articles into my zoho desk? I am looking for a method of understanding what fields can be imported, and what their possible

        • Set Custom Icon for Custom Modules in new Zoho CRM UI

        • [Bug] WebAuthn passkey registration blocked on rpIds with TLDs longer than 6 characters (.accountant, .technology, etc.) — isValidDomain regex too strict

          Hi, Filing on behalf of an enterprise customer where Zoho Vault is deployed across the company. The Chrome extension blocks WebAuthn passkey registration on legitimate sites whose Relying Party ID (rpId) has a TLD longer than 6 letters. This affects every

        • Customer Support Portal

          Hi, As I am dealing with and waiting for help with a serious Zoho Mail issue, I tried to submit requests and manage my requests by logging into the Customer Support Portal, but it keeps saying that I do not have a valid email address and cannot login.

        • Rich Text Type Format for Notes Field

          Has it been discussed or is there a way to insert a table in the notes field? We sometimes receive information in a table format, and it would be beneficial to have it in the same format as a note on a record.

        • Can not send or reply to mails

          Hello, I can not send mails or reply. If I try to send a mail i get "Unable to send message;Reason:553 Replaying disallowed. Invalid Domain - invata-programare.ro" Can you help me, please? Thank you!

        • Urgent: Subscription Correction Needed - Org ID 805493328

          Dear Zoho Billing Team, I am writing to request an urgent correction regarding a recent renewal for my account, Master Bed Trading (Organization ID: 805493328). Due to a mistake during the checkout process, I accidentally renewed our Zoho Forms subscription.

        • Zoho Sites Designer/Developer

          Hello, Besides Upwork, are there any other places to find experienced Zoho Sites designers/developers? I couldn't seem to find a page on Zoho that lists these professionals that are certified with Zoho Sites.

        • HTML PDF Templates / Build From Scratch option not visible for Custom Modules

          Hi everyone, I am working with Zoho Books Custom Modules and trying to create a custom 4x4 package label PDF template using HTML/CSS. According to the official Zoho Books documentation for HTML PDF Templates, there should be an option like: Settings →

        • Next Page