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. 

See: https://learn.microsoft.com/en-us/sql/relational-databases/security/securing-sql-server

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.
   

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!

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

See Also

1. Queries in Zoho CRM
   
2. Query Types
   
3. Queries - Limits
4. Queries - Best Practices
   
5. Queries in Custom Related List
   
6. Queries in Canvas List View
   
7. Kaizen Directory
   

• Topic Participants

• Shylaja S

• 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

• Sort by Project Name?

  How the heck do you sort by project name in the task list views??? Seems like this should be a no-brainer?

• Project Statuses

  Hi All, We have projects that sometimes may not make it through to completion. As such, they were being marked as "Cancelled". I noticed that these projects still show as "Active" though which seems counter intuitive. In fact, the only way I can get them

• I have a requirement to integrate Zoho Books with Zoho Projects at both project and task levels.

  Currently, when i create transactions in Zoho Books (Expenses, Invoices, Bills), we can only map them at the project level. However, our requirement is to: Map records at both project and task levels Sync these transactions back to Zoho Projects under

• What’s New in Zoho Inventory — Latest Features, Integrations & Updates | December 2025

  Zoho Inventory has evolved significantly over the past months, bringing you smarter, faster, and more connected tools to streamline your operations. Whether you’re managing multichannel sales, complex fulfillment workflows, or fast-moving stock, our newest

• Add Multiple Modules in Automation

  Right now I am trying to automate sending customer statements in WhatsApp, if they have overdue invoices, since customer has multiple invoices overdue I don't want to send repetitive message for those. Right now in automation you can only select 1 module,

• Feature Request in Zoho Books : Tracking Inventory for Service Items

  At the moment, ZOHO Books don't allow to track inventory of Service Items (I just talked with customer care executive for confirmation). MY PROBLEM: I resell services Digital Signatures Certificates ( SAC ‐ 998319) and other similar services, I purchase

• Create Tasklist with Tasklist Template using API v3

  In the old API, we could mention the parameter 'task_template_id' when creating a tasklist via API to apply a tasklist template: https://www.zoho.com/projects/help/rest-api/tasklists-api.html#create-tasklist In API v3 there does not seem to be a way to

• Audio Recording Button needs work

  People struggle to understand how to record the audio - the mic button is tiny and barely visible. Please make the recording option more prominent and obvious and the upload file function should be secondary... (not taking up the majority of the space).

• Enhancing user experience with Audio/Video Upload in Zoho Forms

  Hello form builders! Today, interactive forms are an integral part of websites and applications. While text-based inputs serve a variety of purposes, audio and video uploads can open up a world of possibilities for businesses. Imagine you are a talent

• Early Preview for an Upcoming Enhancement to Zoho One - App Adoption and Feature Discovery

  Hello, Enterprise Support Community, We're excited to give you an early sneak peak at an upcoming enhancement to the Zoho One platform: new App Adoption & Feature Discovery components, designed to help our customers adopt the right tools to enhance their

• You cannot send this email campaign as it doesn't have any eligible contacts in the selected mailing list. You can try adding contacts or choose other mailing lists.

  please help

• Add Days In Stage to Filter Options for Pipeline

  We use the days in stage to see how long a ticket has been in a certain stage. But there is no option to see this via filters. eg if i wanted to see how many tickets over 5 days in a stage, there no way to do this. Currently we have to export a report

• Integration problem between zoho crm and zoho forms for an update in zcrm, with two mapped custom fields

  Hello everyone, I need to correct an existing integration between Zoho CRM and Zoho Forms: the use case is that a user needs to send an email to a contact, who will click on a button in this email, redirecting to a Zoho Forms. The contact can update or

• Purchase Order Quantity Validation Not Enforced During Bill Approval

  Hello Team, I would like to report a potential issue in the Purchase Order to Bill workflow. Steps to reproduce: Create a Purchase Order for an item with Quantity = 100. Approve/sign the Purchase Order. Convert the Purchase Order into a Bill. Change the

• How do I add multiple contacts in a deal

  How do I add multiple contacts in a deal

• Blueprint Not Triggering When Lead Status Is Updated by Workflow (IndiaMART Integration)

  I have set up a blueprint that triggers when a lead’s status is “New Lead.” Our CRM is integrated with IndiaMART, and when leads are created from IndiaMART, their Lead Status is initially set to None. To handle this, I created a workflow that automatically

• Add multiple users to a task

  When I´m assigning a task it is almost always related to more than one person. Practical situation: When a client request some improvement the related department opens the task with the situation and people related to it as the client itself, the salesman

• Journey vs Campaign Confusions

  Hi there! I hope you're all doing great! I'm new to the Zoho MA and I'm confused between Journey and Campaigns. I'm not sure if these two work together or not. I hope you can enlighten me. What I'm trying to do is setting up a Newsletter. 1. We have a

• The 3.1 biggest problems with Kiosk right now

  I can see a lot of promise in Kiosk, but it currently has limited functionality that makes it a bit of an ugly duckling. It's great at some things, but woeful at others, meaning people must rely on multiple tools within CRM for their business processes.

• Zoho Webinar not sending calendar entry into Outlook or other calendars

  Dear All, I am using Zoho Webinar for last few months and noted that when a attendee registers at the webinar link he gets an email will intimating his registration and link to webinar. He also get few file ( for Outlook, Google calendar etc) which he

• Ticket Status

  HI, Any idea on how to create other options for this header??? I want to add an "Ordered" status. Its under "tickets" in Overview, I need a new status created (see second picture)

• Turning off the recorded welcome in Zoho Webinar

  Is there a way to turn off the recorded voice that comes up at the beginning of every webinar session? It devalues the experience for attendees from feedback, interrupting their connection with our brand and delaying webinar start unnecessarily.

• Client Script | Update - Client Script Support For Custom Buttons

  Hello everyone! We are excited to announce one of the most requested features - Client Script support for Custom Buttons. This enhancement lets you run custom logic on button actions, giving you greater flexibility and control over your user interactions.

• Save embed widget personalizations

  Ok, Zoho, Great work on providing PRICING TABLES via the embed widget. Thanks so much. This changed the game for me Only one slight problem....I can't seem to save my widget settings. I'm still building my products and plans but I'm testing how they look

• Problem with UTM Parameters: Zoho Forms - Zoho Desk Integration

  Hi Zoho Support Team, I want to automatically capture UTM Parameters from my website URLs and pass it from Zoho Forms into Zoho Desk. I have activated the UTM tracking feature. I've integrated the UTM Tracking code in my website footer on all pages. I've

• Team folder not created when creating project using zoho flow

  When I try to automate project creation using zoho flow, and I have enabled workdrive integration to automatically create team folders to attach to the project, this only works when I create a new project through the UI. But I am trying to automate project

• Add an option to deactivate Zoho Meeting "Welcome" message

  My request is to provide an option to deactivate the annoying Zoho Meeting "Welcome" voice when participants join meetings... or remove it all together. First impressions count, especially with new clients. This notification reminds me of the AOL "You've

• Service line items

  Hello Latha, Could you please let me know the maximum number of service line items that can be added to a single work order? Thanks, Chethiya.

• SalesIQ > My Chat sort by Unread or Follow-up

  Hi Zoho SalesIQ Team, I would like to submit a feature request regarding the My Chat > Sorting in the SalesIQ UnRead Follow-up Conversation tags Thank you for considering. Best regards, CJ

• Record sharing for Activities modules in CRM

  Hello everyone, We've got a few quick enhancements to what we covered in this previous announcement: record sharing is now available for Activities modules. 1. Sharing Tasks, Meetings, and Calls Until now, activity records could only be shared indirectly

• SalesIQ : How to disable "Idle chat handling" ?

  Hello SalesIQ Team. SalesIQ, How to disable "Idle chat handling" ? I would like to disable the option “Automatically close chats that have been idle for a specified amount of time.”

• How do I create an update to the Cost Price from landed costs?

  Hi fellow Zoho Inventory battlers, I am new to Zoho inventory and was completely baffled to find that the cost price of products does not update when a new purchase order is received. The cost price is just made up numbers I start with when the product

• Facturation électronique 2026 - obligation dès le 1er septembre 2026

  Bonjour, Je me permets de réagir à divers posts publiés ici et là concernant le projet de E-Invoicing, dans le cadre de la facturation électronique prévue très prochainement. Dans le cadre du passage à la facturation électronique pour les entreprises,

• Function and workflow to create customer payment and send receipt

   I am attempting to set up a workflow/custom function for the automatic creation of a customer payment and sending the email receipt, but am receiving the error "Improper Statement Error might be due to missing ';' at end of the line or incomplete expression" I've been over everything several times and cannot see where the error is (code is copied into the attached document).  I haven't used custom functions before with Deluge, so it's very likely something very simple, or I've completely mucked

• Smart Alerts: Protect users with configurable email alerts

  Email-based threats are becoming harder to identify and manage. Administrators need proactive ways to protect users from phishing, fraud, and policy violations. Standard filters can block emails, but blocking alone isn't always the most effective response.

• Power up Zoho CRM with project intelligence

  Dear user, You're probably one of those businesses using your CRM as a single source of truth. It's where sales, project execution, and finance teams go to analyze past decisions and formulate future strategies. But what happens when project data is either

• Print multiple uploaded images in an HTML snippet in a Page

  I have a Form: Job_Preparation It stores details of each new item that must be built by the fabricators in our workshop. The form has a field: Documents I upload 4 image files to the Documents field. I want to print a sheet for our workshop staff with

• "Track Inventory for this item" is forced checked by default for goods items (eTims issue?)

  Hello, Since connecting our Zoho books to eTims (Kenya) the "Track Inventory for this item" is forced checked by default (eTims issue?) in the Item creation page for any type of goods. So when purchasing anything that the company does not intend to sale,

• Why can't we choose Fixed Asset account for Purchased Items? (eTims issue?)

  Hello, When the company purchase items not for sale and not supposed to be in the inventory stock, like equipment for operational use, there is no way to access the Fixed Asset accounts in the drop down list. Is that an eTims limitation again? Or something

• Zoho Team Inbox - roadmap

  Hi, would be good to understand the Teaminbox roadmap, in particular: 1. API / Zoho Deluge connections. We have a process where the each email needs to be either tagged or assigned daily. It would be great if we could automate a 5pm alert for any exemptions

• Next Page