Connecting SQL Server to SaaS applications with a Linked Server

By:   |   Updated: 2023-12-21   |   Comments   |   Related: More > Import and Export


In today's cloud era, everything is increasingly online. Not so long ago, everything was on-premises: the databases, the CRM system, the ERP system, the web server. You name it; it was all installed on your servers. When you needed data from a system, you had to obtain the security credentials, connection information, and maybe open a port in the firewall. Sometimes, you needed to install software on your machine to make the connection work, an ODBC connection, or an OLE DB provider (and figure out if you needed 32-bit or 64-bit).

But nowadays, most services are in the cloud. They're not on your servers; they're somewhere else. With most software-as-a-service (SAAS) offerings, you don't have many options to configure connectivity. What if you need data from one SaaS service into another? For example, you need to connect Salesforce to Google Sheets, but there isn't a native connector. Where do you install the drivers? If you're lucky, there might be some APIs, but to work with them, technical expertise is required. With the rapid nature of the cloud, new data sources pop up constantly, and business units are using those new products that need data integration. IT can't keep up.


CData Connect Cloud answers those problems by offering a "cloud-based data virtualization" solution that is fully integrated with SQL Server via Linked Servers. Using a Linked Server means you can connect to the data sources using 4-part names (server – database – schema – table), just as in SQL Server. This is accomplished without having to install or manage a client tool to access cloud data directly from SQL Server.

With an easy-to-use interface from CData, you can create a data virtualization layer between the data sources and the consumers. This layer acts as a middleware where you can centrally manage your connections, users, and their credentials. You can then query those data sources using the familiar SQL language via a Linked Server. It's even possible to write queries spanning multiple data sources. And if the data source supports it (along with the correct permissions), you can execute INSERT, UPDATE, and DELETE statements as well as execute stored procedures.

CData Cloud Connect Use Cases

CData Cloud Connect provides the infrastructure to access data without the need to install and manage any software and executes familiar SQL queries across multiple clouds, with the ability to return a single result set. With this functionality, Developers and DBAs can use the product for:

SQL Server Integration

  • Query cloud data directly using SQL Server queries with a Linked Server
  • Ability to retrieve data from multiple cloud applications with a single query and result set
  • Use familiar T-SQL logic to JOIN data across multiple clouds
  • Time savings to access data across multiple clouds without the need to perform ETL \ ELT operations
  • No additional software management needed, CData centralizes the management and auditing for data access

SQL Server Linked Server Capabilities

Using the SQL language, interesting features become available with CData Cloud Connect via a Linked Server:

  • Use the 4-part name to access the data from a source
  • Create federated queries that join data across data sources (and thus over different clouds)
    • For example, you can join data from Hubspot (where your leads are) with data from NetSuite (where your accounts are)
    • Here is an example of querying with Salesforce and Snowflake after the CData Cloud Connect data sources have been setup:
FROM [Salesforce1].[Salesforce].[Account]
JOIN [Snowflake1].[Website].[LoginHistory]
ON [Salesforce1].[Salesforce].[Account].[Id] = [Snowflake1].[Website].[LoginHistory].[SFAccountId]
  • Advanced Filtering
    • If the filter can be pushed down to the source – for example, the API supports it – then the filtering will occur on the server side, which leads to better performance
    • If it is not supported, CData will do the filtering and aggregation in memory
  • Aggregations with GROUP BY logic are supported
  • CDATA query API has built-in functions available to help write business logic
  • Create views or stored procedures. For example, you can create a view on top of Facebook data to simplify logic and hide complexity from users. Views can be federated queries as well.
  • Supports DDL commands such as CREATE/ALTER/DROP table.
  • If supported (and with the correct permissions), data can be modified with UPDATE, DELETE, or INSERT.
  • A complete overview of possibilities is available in the SQL reference doc page.

ETL Projects

  • CData Connect Cloud doesn't replace ETL processes but complements them. Connect Cloud is ideally suited for the ingestion phase of the data pipeline.
  • If you want more complex ETL processing, you can use SSIS or ADF. You can use Connect Cloud in both as a source. There are connectors for both ADF and SSIS available, so your ETL tool of choice can use Connect Cloud to gain access to your cloud data. This is good news for SSIS developers since there haven't been many new connections made available in the past few years.
  • If you want real-time ad-hoc access to a data source – typically not supported by ETL – then you can use Connect Cloud instead.

Power BI

  • When you need Power BI to access data, but you don't have a gateway (or you don't want to install one) CData Connect Cloud can deliver the data.
  • There's a certified connection in Power BI for CData Connect Cloud from Microsoft. This is an OData connection from Power BI to CData Connect Cloud.
cdata connector in Power BI

Power Apps

  • You can use CData Connect Cloud in Power Apps, Power Automate, or Azure Logic Apps (through the virtual SQL Server). Build new applications using your cloud data sources using the low code/ no code platforms.
  • The use case "Snowflake to Power Apps" with CData is demonstrated in this YouTube video.

Cloud to Cloud Connectivity

  • Access data from AWS, Office 365, or Google Cloud

Excel or Google Sheets

excel addin for cdata connect cloud
  • With this plugin, you can connect your workbook to the CData Connect Cloud environment and access your cloud data sources live
  • You have the option to build the query graphically or to write SQL code
  • Write-back in Excel is supported; data can be pushed back into the source system
  • You can get a walkthrough of this CData feature on YouTube, where they connect Google Sheets with Salesforce.

Configuring CData Connect Cloud Data Sources Example

Let's walk through a specific use case to see how the product works.

We're going to connect an Azure Logic App to a Snowflake database. With CData Connect Cloud, this problem is easily solved. To follow along with this example, use your existing accounts or download free trials for Snowflake, Azure, and CData Connect Cloud.

free trial page

After registering and creating an account, a wizard will appear to guide you through the first connection.

set up your first connection

Select Data Source

In Data Sources, select Snowflake, or type Snowflake in the Search field, then select it, as seen in the image below.

select snowflake as data source

Add Connection

Next, provide the connection information for the Snowflake database.

snowflake connection info

You need to specify a name for the connection as well. This is important, as we will use it later in our SQL queries. As the authentication schema, we use Password since, in our use case, Snowflake doesn't utilize Azure AD (another authentication provider such as Okta might be implemented). After filling in and saving the information, Connect Cloud will automatically test the connection as it proceeds to the next step.

connection to snowflake tested succesfully

Select and Configure Client

Now, we need to select our client. Azure Logic Apps is currently not supported (but it is coming soon as a client tool in Connect Cloud), so let's use the virtual SQL Server to connect to Snowflake instead.

select sql server as client

A personal access token (PAT) is generated in the last step. You will need to save this as it is necessary to create the connection later. You can always create new PATs using the Connect Cloud portal:

personal access token for sql server linked server

CData Management Interface

Once the connection is created, you'll proceed to the portal.


In the Overview tab, a dashboard is available at a glance to determine who has been querying which systems.

dashboard in connect cloud


In the Connections tab, you can find the Snowflake connection just created:

overview of data connections

Data Explorer

In the Data Explorer tab, you can preview the data using the SQL query language:

data explorer in connect cloud


Another useful tab is Logs, where you can view all the executed queries:

logged queries

The Audit Log provides more information about actions that have taken place in Connect Cloud itself, such as users that have been added, tokens that have been created, and so on.

audit log

Connect to Snowflake with CData SQL Server Linked Server Example

Now that we setup the CData data source as shown above (i.e.,14333), now let's connect our Logic App to the Snowflake database using the SQL Server Linked Server.

In the Logic App, add a new SQL Server action.

add SQL Server action

If we want to read data, there are two options: the Get Rows (V2) or the Execute a SQL query (V2).

list of SQL Server actions

Connecting to the CData SQL Server Linked Server

No matter the choice, you'll need to create a connection via the Linked Server that was setup in the prior section.

setup connection to sql server

We need to use SQL Server authentication:

  • Server name:,14333 (the main virtual SQL Server we mentioned earlier).
  • Database name: The name of the connection,
  • Username: Your CData Connect Cloud username
  • Password: The token we generated earlier when the connection was created.

There's an option to use a gateway, but leave this blank.

When using the Get Rows action, select the table name (reuse the server and database name from the connection):

select table to get rows from

When the Logic App runs, it will fetch a single page with records from the table (pagination must be used if more is needed).

logic app has run succesfully

The output is a JSON file with data returned from the OData protocol.

single page of data returned

Another option is to specify a SQL query. This allows more control over what is returned, like excluding columns or performing calculations. For demonstration purposes, it is wise to use a TOP clause to limit the number of rows returned.

output from execute sql statement

In this case, the JSON output can be viewed directly in the action. While viewing the query history in Snowflake, we can see all the queries that Azure Logic Apps has sent to the database:

query history in snowflake

As you might have noticed, the query was run once without the TOP clause, which returned almost 85 million rows. When you use the TOP clause, CData Connect Cloud translates it to a Snowflake query with the LIMIT operator (since TOP is not supported in Snowflake):

final query in snowflake

CData Connect Cloud Value

The CData Connect Cloud offers many benefits:

  • Data Access: All data access is via a familiar SQL Server Linked Server.
  • Centralized Governance: Manage all your cloud connections in 1 place.
  • Familiar SQL Language: Data sources can be accessed with SQL code without knowing the table structures of the source system or the API
  • Connectivity: There are three types of connectivity options based on the data source functionality:
    • REST API
    • OData Web Service
    • Virtual SQL Server
  • Time Savings by Simplification: Some data sources have APIs, and learning to work with each one is time-consuming. When you only want to move raw data, go through an ETL process and report on that data, CData standardizes this process regardless of the source system.
  • Real Time: Real-time data access
  • Multiple Data Sources: Access multiple sources and return a single result set
  • Security: Multi-level security
  • Centralized Logging of All Issued Queries: A dashboard shows the queries by connection, the number of queries over time, the number of rows transferred, etc.
  • Software-as-a-Service: There are no software updates to run; it is taken care of for you. Everything is centralized and managed in one place.
  • Data Sources: Below is an image with some of the available data sources:
part of all the data sources supported by cdata cloud connect


CData Connect Cloud offers a centralized and secure environment for managing all your cloud data sources. It provides a direct connection from the client tool to the data source via a SQL Server Linked Server, such as a live connection from Google Sheets to Salesforce. You can use the familiar SQL language to query cloud data sources, creating a single data set over different clouds.

With CData Connect Cloud, managing all your cloud connections is simplified, including the users who need access to these sources and their credentials. The CData portal offers a user-friendly dashboard to track how many queries were issued and how many rows of data were transferred.

Additionally, CData Connect Cloud is a software-as-a-service product, providing stress-free software or driver updates handled for you.

Download the free trial to test drive CData Connect Cloud.

Next Steps Product Spotlight sponsored by CData.

About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-12-21

Comments For This Article