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

      • Zoho Commerce + MCP + Claude

        Hi everyone, I wanted to share a really impressive experience I had with Zoho Commerce + MCP + Claude. I created an MCP server using Zoho MCP Server and connected my Claude instance to my store built with Zoho Commerce. For safety, I enabled only product

      • auto add as member the contact owner

        is there a way that i can make a zoho flow that will add the owner of the contact as a member of the chat after the round robin assignment?

      • Get Files Associated to Data Template via API

        I have a data template with multiple files associated to it, and trying to write a Deluge script that will fetch files associated with this data template. I created the script below based on the WorkDrive API documentation, one request uses the data templates

      • Zoho Book - Banking Module - Cash Credit account

        I have a CC account with a bank. I initially added the account as a 'Bank Account' under the banking module in Zoho Books. However, this CC Bank Account is showing as an asset instead of a liability. I have added the account as a credit card account but

      • Adding a custom formula field from sub-forms

        I have used my 5 Aggregated Sum fields in the subform. I have another column I want to add a summary of. This can be in a separate section/field. The formula field won't pull in information from the sub-form. How do I pull information from the sub-form

      • Remove Zoho Header from Portals

        I have a portal page with custom domain. But when I print directly from a webpage, the Zoho CRM header shows. It kind of kills the branding aspect. Is there a way to get rid of this?

      • Creator List not showing

        I've edited a list in creator, but when I open it in the live version the list is completely different. Anyone know why this is happening? Edit Version: Live Version:

      • Please design a better spam filter

        Hi Team, While I love Zoho Mail for all the awesome features you have designed - this is the one you need to do a lot of work on - Spam Filter. Your spam categorization seems to be very inefficient - any email containing brand images or a tracking pixel

      • Host not reachable when sending

        For few partner that I send regular emails now got error and they don't receive emails Temporary failure fam-xxx.hr/185.xx.xx.xxx Host not reachable. Have more than 20 mails in https://mailadmin.zoho.eu/cpanel/home.do#mailSettings/retryEmail please

      • IP flagged as abusive

        I'm getting the error that 136.143.188.15 is listed as abusive. I've checked with mxtoolbox.com and it is indeed in the list

      • App passwords stop working

        Some of my app passwords appear to stop working after awhile. Seems to happen every 6 months or so. Just had to change one again for the second time in a year. Is this a bug or a feature? Or perhaps changes are being made on the back end on occasion that

      • Duplicate entries for contacts birthdays

        Good morning I have recently started to use my Zoho calendar and noticed that there are multiple birthday events showing for some of my contacts. I have checked my contacts and there were duplicates for some contacts which I have now rectified but the

      • Problem: New Email Consistently Failing to Sync/Display in Inbox

        Recently, I have noticed that my Zoho Email inbox does not indicate I've received a new email, until I go to *another* folder and then return to the inbox. Only then will new emails be displayed. This has happened 100% of the time, during the past few

      • Zoho IMAP Access to mail doesn't sync messages

        As stated in the topic - I have company account email address setup, and it has ( as in the picutre ) ActiveSync and Imap access enabled. However the messages does not show up while logged in in Zoho Mail however when I log in in thunderbird everything

      • 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

      • Temps working custom view in Zoho Recruit

        How do you create a custom view of temps working for this week.  I have tried multiple combinations of job status, start date, end date etc but I am unable to find the correct combinations to show the temps I have working this week?? Any suggestions on how I can do this. 

      • Client Scripts similar to CRM?

        Hi, Is there any plans for Client Scripts to become a feature of Recruit? Feel this will be a great benefit to a number of users. Would be great for example in cases where a Client has banned / blacklisted a Candidate - so that if that candidate is associated

      • Announcing Zoho Sheet desktop app for macOS and Windows (Beta)

        Hello Sheet users, We know you’ve been waiting for this one. It has always been the top priority on our roadmap to provide a single native desktop app for macOS and Windows that works both online and offline. Today, we are excited to announce that the

      • Automated entries past the current month in a calendar report

        Hi all, I have an automation problem. I have a form which on successfull 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

      • Billing Status Update

        Hello Latha, I’m working on a new automation (deluge) to fulfill one of our requirements. In this automation, there is a step to update the Work Order billing status from “Not Yet Invoiced” to “Non-Billable.” I tried to find the API information relevant

      • 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

      • Many Zoho POS Issues

        Can not apply credits from a customers account as a form of payment. It shows that you can but there is a bug that does not execute the action. Reported many times. Can not view Sessions from Zoho POS WebView, throws a JQUERY error Workflows and actions

      • HOW I CAN GET BETA VERSION FOT TESTING

        HI. ZOHO I AM INTERESTED IN YOUR BETA SOFTWARE TO EXPERIENCE NEW FEATURES.

      • Multiple EAN code for product

        option to add multiple EAN code for product???

      • Issue adding/changing mobile number for OTP

        Hi Zoho Community, I’m trying to add or change my mobile number, but I keep getting this error: “We’re unable to send OTP to this mobile number. Please contact support-as@eu.zohocorp.com” Because of this, I can’t verify my number or continue the setup.

      • Zoho Payroll Canada does not work

        We submitted payroll within the required timelines. The money was withdrawn from our account and our employees have not been paid. Impossible to get a hold of someone at Zoho. I can’t believe the company operates like this. What a bloody disgrace. Shame

      • Enhance Appointment Buffers in Zoho Bookings

        There was previously a long-standing feature request related to enhancing the way appointment buffers work in Zoho Bookings, but it looks like the original post has been deleted. I am therefore adding a new request that Zoho Bookings adjust how appointment

      • Functionality based URL to find records due today or in the next week

        I need to construct a URL to filter a view to records where the evaluation is due soon (i.e. in the next 7 days). To me, that includes today, but Zoho has a different opinion. Zoho defines 39 as NEXT_7_DAYS but this gives unexpected results. https://creatorapp.zoho.eu/...#Report:My_Evaluations?Evaluation_due_by_op=39

      • Allow customers to choose meeting venue and meeting duration on booking page

        My business primarily involves one-to-one meetings with my clients. Given the hybrid-work world we now find ourselves in, these meetings can take several forms (which I think of as the meeting "venue"): In-person Zoom Phone call I currently handle these

      • Event Time Zone in meeting invites are confusing users

        When sending calendar invites to internal and external users, the first section "Event Time Zone" is confusing people and they are automatically declining events. Can this section please be removed??? It already shows the correct time zone next to the

      • Forwarding without verification

        Hi, I use Tripit to keep track of all my business travel. I've recently moved over to Zoho and wanted to set up a forwarding rule to send various travel confirmation emails automatically to plans@tripit.com Obviously this is an email address I don't control,

      • Filter by user in Pivot Chart

        I have a Pivot chart where the data should be filtered by user. The user enters the system and should see only the data that correspond to it. Can anyone help me?

      • All new Address Field in Zoho CRM: maintain structured and accurate address inputs

        Availability Update: 29 September 2025: It's currently available for all new sign-ups and for existing Zoho CRM orgs which are in the Professional edition exclusively for IN DC users. 2 March 2026: Available to users in all DCs except US and EU DC. 24

      • Whatsapp Limitation Questions

        Good day, I would like to find out about the functionality or possibility of all the below points within the Zoho/WhatsApp integration. Will WhatsApp buttons ever be possible in the future? Will WhatsApp Re-directs to different users be possible based

      • 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

      • #2 The Tax Talk Nobody Wants to Have

        "We are also able to manage our GST invoices without worrying about compliance issues. With Zoho Invoice, we are happy, our tax consultant is happy, and so are our customers," rejoices Shivprateek Habib, Partner, Flutterboots Services LLP. That's usually

      • Bulk upload images and specifications to products

        Hi, Many users have asked this over the years and I am also asking the same. Is there any way in which we can bulk upload product (variant) images and product specifications. The current way to upload/select image for every variant is too cumbersome.

      • Workflows fail silently in Zoho CRM and there is no native way to know

        Workflow automation is honestly one of the biggest reasons my clients choose Zoho. But there is one problem I keep running into across almost every implementation. When a workflow fails, nobody finds out. Email alerts hit daily limits and just stop. Custom

      • Zoho Commerce and Third-party shipping (MachShip) API integration

        We are implementing a third-party shipping (MachShip) API integration for our Zoho Commerce store and have made significant progress. However, we need guidance on a specific technical challenge. Current Challenge: We need to get the customer input to

      • Is there a way to update bounce domain after verification process has started

        Hi Zeptomail Team, I've created a domain and we did not update the bounce domain to our custom record... the validation has already started and I am now unable to update the bounce domain. Is there any way to get this updated on the backend without deleting

      • Next Page