Architecture

Describes how the SQL Server connector works

This page contains more technical details about how the SQL Server Integration with Power Omni works. Use it as a reference to understand all aspects so you know exactly what you are configuring in your environment.

Basically, the integration is an API service that sits between Power Omni and the SQL Server instance. This service is responsible for exposing the procedures of a database as an API, allowing them to be executed through HTTPS POST calls, passing the parameters as JSON.

The following diagram shows the data flow that occurs, and through which components it passes, when Power Omni decides to invoke a procedure from a SQL instance:

How Power Omni accesses the Procedures

Power Omni does not have direct access to your database. The one that connects to the database is the API service created by Power Tuning, which you configure during installation.

This service connects to the instance (using the connection string information), retrieves the procedures and generates an OpenAPI schema based on the metadata and comments of each procedure. This is the schema that you configure in the Power Omni connectors.

This schema is associated with a connector of type OpenAPI, natively supported by Power Omni. This is where the magic happens that makes your Power Omni know your procedures: thanks to the OpenAPI connector and the service that translates procedures into an OpenAPI schema, we are able to create a bridge with your database. And the effect we have is this: an agent that knows your procedures and parameters, which can be invoked via HTTPS.

How the API accesses the SQL Instance

Now that you understand the role of the API service, it is important to better understand how it works.

As shown in the previous image, the API sits between Power Omni and your SQL instance. It is the one that actually reaches your SQL Server. And you configure it by providing the connection string to your SQL Server.

In other words, all permissions it uses are under your control and/or the team that manages the database. What the service does all the time is:

• List the procedures it has access to and create the endpoints

• Execute the procedures when Power Omni requests

One of the main points that guided us to develop a solution that uses only procedures, and not AI-generated SQL code, is security. By defining procedures in advance, you have full control over what code is executed in your database. Power Omni will only request the invocation of procedures according to their parameters, and it is your responsibility to create the procedure code. This brings advantages and disadvantages:

• Advantages

  • There is no risk of the AI executing dangerous commands or commands that may cause slowness

  • Reduces the chance of hallucination in responses, since you can guide toward the correct result

  • You have full control over which objects and T-SQL commands will be executed

• Disadvantage

  • The AI does not have freedom to dynamically assemble and discover tables

  • You need to create the code and logic

Even with procedures, you can still allow your agent in Power Omni to execute queries by creating a procedure that, for example, returns the tables and database descriptions, and another that runs an SQL command. Although this is not a practice we recommend in this version of the integration yet.

In future versions, we may bring better solutions that combine more precise responses with greater freedom and security for your agents! Stay tuned!

API Security

As mentioned, the API is a web application built with Node.Js. Natively, it only listens on HTTP endpoints and performs some operations periodically. It also supports API KEY definitions using environment variables, which can be easily changed.

However, you can add several layers of security. The simplest and mandatory one is to place it behind HTTPS, since Power Omni does not accept a different protocol in the OpenAPI connector. The deployment guides in this article always show you how to deploy using HTTPS and API KEYS.

You can enhance security according to your company’s policies in several ways:

• Place it behind your firewall, proxy, etc., and limit the IP range

• Add rate limit checks or brute force access attempt protections

Our team also continuously performs security reviews, seeking to keep the core updated, robust and secure.