By: Jeremy Kadlec | Updated: 2023-05-18 | Comments (2) | Related: > Application Development
Problem
SQL Server environments are growing more complicated, in part due to the need to interface with an increasing number of third-party technologies. Providing business units and partners with real time SQL Server data access has only added to the challenge, due to the need to manage access in a secure, manageable, and compliant fashion. As SQL Server professionals, we’ve relied on SQL Server Integration Services (SSIS) to assist with these challenges, however SSIS struggles with real time reporting requirements and facilitating the exchange of data across the enterprise. Alternatively, we can use native SQL Server Replication, but this too is difficult to configure and maintain within organizations tasked with managing highly dynamic database needs.
Beyond the purely technical data exchange difficulties, data security is always a concern and needs to be top-of-mind whenever granting access to third-parties. Limiting data access, assigning appropriate permissions and securing data transmissions is imperative to properly protect client and internal data.
With these challenges in mind, how can we build a secure conduit between our applications and begin meeting our business requirements for data exchange, real time reporting and data security?
Solution
Traditionally, as SQL Server Developers and DBAs, we think of solving remote data access issues by consolidating data with SQL Server Integration Services (SSIS) or Replication. Although these are common approaches, both require significant time and money to build, test, deploy and maintain. Further, due to the increasing use of Continuous Integration \ DEVOPS \ SCRUM methodologies, databases and applications are changing more frequently than ever, posing additional challenges for SSIS and Replication managers.
If you’re looking for a streamlined and secure solution for accessing SQL Server database objects (stored procedures, functions, tables, views), then perhaps it is time to consider a new option to securely address data access across the enterprise. Beyond SQL Server, DreamFactory can auto-generate and manage APIs and documentation for a wide variety of data sources, including:
- 21+ Databases - SQL Server, Oracle, SalesForce, Snowflake, AWS, MySQL, etc.
- Big Data - Apache Hive, Hadoop, etc.
- Remote SOAP to REST services
- Universal Web Connector
- Support for many identity providers, including Active Directory and OAuth
- Automated generation of REST APIs for version control (GitHub and BitBucket)
- FTP and SFTP servers, Excel, flat files, etc.
In the simplest terms, DreamFactory is middleware that connects all of your applications across the enterprise in a secure manner. DreamFactory interrogates your database and associated objects, then builds a REST API that can be used for seamless JSON- and XML-based data access and integration. Using the DreamFactory management interface, you can create a secure REST API in minutes without writing a single line of code! Let’s dive in and check it out.
Tutorial - Build a REST API for SQL Server
Building a REST API in DreamFactory starts with the Service creator. You’ll start by specifying a Namespace, label and description are specified. The latter two are just used for referential purposes within the administration console, while the namespace serves a more important role of being used as a namespace within the generated API URI structure.
Next, you’ll specify the database hostname, database name, and connecting username and password. Also note in this example that no schema is specified to demonstrate that objects across all schemas can be accessed. If you’d like to limit the API to a specific schema, you can declare it here.
Additional security options on this interface pertain to generating a read-only API Read Only data access and configuring SSL-based communication.
Once the API configuration is saved, DreamFactory builds the API in a matter of seconds (even with thousands of objects). It also generates interactive Swagger documentation. To view the documentation, you can navigate to the API Docs Tab where you can see all the available endpoints including those used to access schemas, tables, views, functions and stored procedures:
To learn more about the API generation process, check out the video Generating a SQL Server API (web services - JSON or XML) in Less Than 5 Minutes (dreamfactory.com).
Test the REST API from SQL Server
Once the DreamFactory API has been built, testing can begin for functionality, performance and security. Starting with functionality, we will show a simple test to retrieve data from the Sales.Customer table in the WorldWideImporters database. In this example, rather than returning all of the columns, only the CustomerName column is returned. This can be easily modified to include additional columns based on your needs.
Here are the associate records for this API call:
This second example demonstrates calling the Website.SearchForCustomers stored procedure in the WorldWideImporters database, including passing two input parameters to the stored procedure (SearchText and MaximumRowsToReturn):
In this example, the result set returned is JSON:
DreamFactory’s Getting Started Guide offers many additional examples demonstrating various CRUD-based API calls.
DreamFactory Roles to Secure Database Objects
DreamFactory’s role-based access control manager is a crucial feature used by administrators to grant fine-grained permissions to specific database objects. The process starts with Roles Overview where the Role Name, Description and Role Status are specified.
Next, you’ll choose the desired service (API), which then prompts you to choose which corresponding Endpoints (Schemas, Tables, Functions and Stored Procedures) are accessible through the API.
The next step on the Access Overview is to specify the Access (GET, POST, PUT, PATCH or DELETE) for the Endpoint.
In this example, multiple stored procedures are granted permissions from a single service, but there could be multiple services and hundreds of Endpoints specified to properly restrict permissions needed in an enterprise application.
Multitenant Database Security Example
DreamFactory includes native support for multitenant database security. A specific value such as a CustomerID or variable can be passed with all query or stored procedure executions. In the example below, we can specify a field equal to a specific value such as “WHERE CustomerID = 15” that will ensure proper security across the database.
For a time comparison between DreamFactory vs. creating an API from scratch, check out this resource - API Calculator - DreamFactory Software.
Preventing Denial-of-Service API Attacks with DreamFactory’s Volume Limiting Feature
Another layer of security in DreamFactory is Limits, which restricts access to an API to prevent a DOS attack during a specific period of time. If traffic exceeds a particular threshold for an Instance, User, Service, Role, etc., then DreamFactory will begin returning a 429 status code and cease further interaction with the backend data source.
Data Masking with DreamFactory
Protecting sensitive data is critical to ensuring adherence to various compliance and regulatory requirements regardless of industry. If data masking is not possible at the database level, then DreamFactory is able to mask or entirely anonymize the data with the internal DreamFactory scripting engine. The scripting engine supports multiple languages, including NodeJS, Python (versions 2 and 3), PHP, and Ruby (coming soon in 2021 Q2). Using these languages, you can easily add data masking logic which can manipulate both the request and response.
See this article to learn more about De-identifying sensitive API data.
Consolidating Result Sets with DreamFactory
Another valuable feature in DreamFactory is the ability to combine result sets from multiple tables across multiple databases and have the data appear as a single data source. This feature is known as Data Mesh, and can be configured using the Schema Manager where virtual relationships among data from different database platforms can be established. This means data from your ERP, accounting, and CRM systems can be returned as a single result set for decision making and reporting that could not be accessed in real time without DreamFactory.
DreamFactory Use Cases
DreamFactory helps organizations rapidly address numerous core business needs in a secure and high-performance manner. Let’s dive into a number of scenarios to understand DreamFactory’s value.
Enterprise Business Intelligence
As Enterprises have expanded globally as well as grown via mergers and acquisitions, the associated data infrastructure has become very complex. What has not changed is Mid-Level and Executive Management’s need for real time data across numerous platforms for accurate decision making. Traditionally, these reporting needs were addressed with Data Warehouse \ Data Mart projects, which can be very expensive and time consuming. Further this data is often not available in real time. With DreamFactory, enterprises are able to seamlessly access data from the API to consolidate data from numerous systems across numerous locations and visualize the data in a single report with Power BI or Tableau. DreamFactory’s flexible and high-performance API enables the consolidation of data with the ability to scale as the Enterprise grows and shifts to meet client needs.
SQL Server Security Lockdown
SQL Server stored procedure data access has long been relied upon to encapsulate business logic, secure data and limit the application surface area. With DreamFactory, an API can be built just for specific stored procedures across numerous servers and databases. This enables the SQL Developers to properly build and tune the T-SQL code, return only the needed data and setup permissions while the Front-End Developers can call the stored procedure and focus on the application aesthetics, enterprise reporting and enterprise metrics. You no longer have to be impacted by Linked Server performance, scheduled SSIS extracts or Replication snafus. DreamFactory enables your team to focus on their strengths and act as the needed conduit to enable the necessary data access seamlessly across the enterprise.
Check out this additional resource on How to Code a REST API for Microsoft SQL Server: The Hard Way and the Easy Way.
Low Code Developer Tool Integration
With the popularity of Low Code Developer tools to quickly build and deploy applications, DreamFactory enables SQL Server DBAs and Developers to design and build the data model, then code stored procedures for database access. Then they can meet the Front-End Developers at the DreamFactory middleware to connect the front-end tool with the back end stored procedures from a single SQL Server, a cloud-based instance or from numerous databases distributed across the organization. DreamFactory helps to simplify and standardize database access, reduce the time needed for application development and deployment, as well as help Low Code Developer Tools meet their goal of rapidly building applications to meet the organizational needs.
Enterprise Data Silos
Whether it is ERP, CRM, SCM, PM or HR applications, in most organizations these systems are mission critical and are siloed data stores with a proprietary interface. Accessing this data can be extremely difficult when building custom reports and dashboards for decision making. DreamFactory enables your system Subject Matter Experts to unlock valuable data for the enterprise with a simple and secure API.
Multi-Platform Relational Databases
In large enterprises, multiple database platforms including public clouds are common. Unfortunately, accessing data in real time across these disparate databases is time consuming, a moving target and can quickly become a mess with duplicate data, overlapping code and more. In some circumstances there are native access options, and typically ETL (Extract, Transform and Load) processes with SQL Server Integration Services (SSIS), Azure Data Factory or Azure Synapse Analytics are needed to move data or files between systems. DreamFactory’s access to 50+ applications and 20+ databases, simplifies this data access process and eliminates Linked Servers, Replication and ETL nightmares.
SQL Server Express Proliferation @ Retail Locations
At retail branches around the United States and the globe, SQL Server Express is often installed on a local machine to support day to day operations on the front lines for the organization. In this scenario, the Retailer needs to ensure consistent operations at the store even if connectivity is unavailable or a centralized application is down. However, data (pending orders, purchases, inventory, shipments, employee scheduling, etc.) from each retail branch are critical to understand trends, customer demand, promotional performance and more. This valuable data in SQL Server Express (as a free product) has limited options for data sharing. This is another distributed environment common scenario where DreamFactory demonstrates significant value by building an API for real time data access to the SQL Server Express data across the enterprise, which can be consolidated into a single report or dashboard to drive decision making.
DreamFactory Value Proposition
DreamFactory delivers significant time savings serving as middleware to build APIs across numerous database and application platforms. With DreamFactory, SQL Server Developers and DBAs no longer have to generate and move flat files, create SSIS Packages, support Replication or rely on Linked Servers to gain real time insights into enterprise data. DreamFactory enables enterprises to:
- Accelerate application development across all database platforms
- Unlock access to all enterprise data for decision making
- Consolidate data from numerous platforms into a single result set
- Encrypt data during transmission
- Assign granular data access permissions
- Protect multitenant databases
- Seamlessly plugin to your enterprise
Next Steps
- How do I get started with DreamFactory?
- How can I learn more about DreamFactory?
MSSQLTips.com Product Spotlight sponsored by DreamFactory.
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.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-05-18