SQL Server

This page details how the Power Omni integration with SQL Server works and how to install it.

Power Omni can connect to your database through Stored Procedures defined by you or your team, allowing you to have an AI capable of extracting or inserting data directly into your database through controlled logic that combines the best of both worlds: artificial intelligence (deciding what to execute and when) and security (you maintain full control over what it can read or modify).

For this to work, you need to deploy an HTTP API that sits between Power Omni and your SQL Server instance. This API is an exclusive service provided free of charge to Power Omni customers. This documentation will guide you through the different ways you can configure this API, whether your SQL Server is on-premises or in the cloud.

Making Power Omni communicate with your SQL Server basically involves the following steps:

1. Create access in your SQL Server instance

2. Deploy the integration API

3. Connect Power Omni to the API

4. Create and document the procedures

The diagram below provides a summary of the data flow of how this integration works.

If you want to understand the full architecture of this integration, see the Architecturepage for more technical details.

Create Access in SQL Server

The first step is to prepare your SQL Server instance. Basically, you will need to create a database (or use an existing one) and a Login and Password.

We recommend creating a new database so you can better isolate and control the procedures that Power Omni will invoke.

Below is an example SQL script that creates a database and user called PowerOmni (copy and paste into Management Studio, for example):

-- Execute with a user that has permissions and/or talk to the DBA to follow best practices for your environment
USE master
GO
CREATE LOGIN PowerOmni WITH PASSWORD = 'SomeVeryStrongPassword';
GO
CREATE DATABASE PowerOmni; -- Creates a database where PowerOmni will connect
GO
USE PowerOmni --> Changes the context to the PowerOmni database
GO
CREATE USER PowerOmni FROM LOGIN PowerOmni; --> creates the PowerOmni user (mapped to the PowerOMni login)
GRANT EXECUTE, VIEW DEFINITION TO PowerOmni; --> minimum required permissions
GO

The script above creates everything Power Omni needs for a minimum integration test. You can adjust names according to your company’s standards. The only requirement is the permissions listed above.

The integration API supports only SQL Authentication. Future versions may add other authentication methods.

Finally, ensure that your SQL Server instance is accessible to the API you will deploy. Identify the host name or port the API will use to access your instance. You may need to involve your network team to adjust firewall rules, NAT, VPN, etc.

If deploying on your own VM or container, SQL Server must be accessible to that host. If using a cloud service such as Azure App Service, your instance must allow access from the appropriate IP range.

Deploy the Integration API

Power Omni does not connect directly to your SQL instance. It requires an intermediary service — an API created by Power Tuning — that identifies procedures and transforms them into a format Power Omni can use.

You can deploy this API in multiple ways. In the GitHub repository, there are ready-made examples using Docker Compose, which you can use in your own infrastructure (VM or container).

The documentation provides guides such as:

• Deploy Azure App Service (few clicks)

• Using Docker with Traefik (more advanced, self-hosted infrastructure)

You may adapt examples to use nginx, Kubernetes, etc.

This service generates logs in standard output, which you can use for debugging connection errors.

After deployment, you will have:

• Your API URL (example: https://sqlpoweromni.example.com)

• One or more API Keys

API Security

The API must run on HTTPS (port 443) and be protected by API Keys configured or generated by you.

Ensure that no ports other than 443 or 80 are exposed.

Keep API Keys secure in a password vault and avoid sharing them publicly. Treat them as sensitive data.

For additional protections, contact Power Omni Support.

Connect Power Omni

At this point, you already managed to deploy the integration API, and you already have the API KEYS needed to access the service. Now it is time to test the connection with your Power Omni. Let's assume that the URL you have is https://poweromni.exemplo.com and that one of the api keys is Abc123.

First, create a procedure in the database that you configured, example:

CREATE PROCEDURE spOmniGetDate --> Creates a procedure to test the integration!
AS
SELECT DataAtual = GETDATE()

Let's make PowerOmni invoke this procedure to obtain the current date from the database.

Confirm that the integration can list the procedure, accessing in your browser:

https://poweromni.exemplo.com/openapi?apikey=Abc123

If you configured your SQL correctly and created the procedure, the browser should display a JSON and somewhere you will see the name of the procedure (use CTRL + F to search by the name). If it did not return a JSON or did not return the procedure, check the service log for errors.

Once you confirmed that the JSON is working, let's create a connector of the OpenAPI Type in PowerOmni.

This integration uses a native Power Omni feature: OpenAPI Connector.

Read more in the OpenAPI connector doc if you want to learn more about it and the configurations

Go to: Settings -> Artifacts -> Connectors. Create a connector of type OpenAPI, and fill in the fields as follows:

Field	How to Fill
Project	Choose in which project to create
Name	Give a name to the connector. Only accepts uppercase or lowercase letters
Description	Brief description. Example: Execute procedures in a SQL database
Authentication Type	Choose API Key, and in connection configuration, choose "Create New Connection"
Connection Name	In "connection name", choose a name of your preference. Example: SqlConector1 Then click on "Add API Keys"
Key Name	Type "apikey", without the quotes
Key Value	Paste the value of one of the api key keys that you have. In our example, it would be Abc123
In OpenAPI Specification	Choose the option "Automatic"
Base URL	Put only the API domain https://poweromni.exemplo.com
Specification Endpoint	Put the following value, changing the apikey: /openapi?reload=1&apikey=Abc123 Click on "Fetch specification" and PowerOmni will try to read that same JSON that you read above If there were no errors, it will return the json and you can check by clicking the "View Schema" button

Once you filled everything and the Schema was loaded successfully, save the connector. At this point, your Power Omni can already identify the available procedures.

Now, just associate this connector with an Agent:

• Go to Artifacts -> Agents

• Choose the desired agent (or create a new one)

• In the instructions, make sure to put something like: "Use the tools to answer the user"

• In relationships, click on "Connectors" and choose the connector you created.

• Confirm that you have permission on the agent

Now, let's test if the agent can identify the list of procedures. Open a new conversation and ask: "Which tools are available?". It should list the name of the procedures (or something similar).

Then, ask it to execute the procedure, for example: "Give me the database date". At this moment, it should trigger the invocation of the date procedure, through the SQL connector that you configured.

Done! Your Power Omni is successfully connected with your SQL Server, and you can start testing more advanced procedures, that receive parameters, and obtain data from other databases.

If there was any error in the execution of the procedure, either due to lack of permission, or due to logic, the integration will capture the error and log it in the standard output. And it will send a response to omni, generic, indicating that there was an error in the execution. Then, Power Omni will generate the response of something related to the error.

If Power Omni generates an error alert (with an id), it means that there was another error in the processing and it must be investigated.

You can contact the Power Omni support team to help you.

Create and Document the Procedures

Now, your Power Omni is already configured and can reach your instance. The next steps are to create more procedures so that your Power Omni can really do something useful with your data.

As you may have noticed, you must create your procedures with the prefix spOmni. The name of your procedure can contain only uppercase or lowercase letters and underscore. These rules may be relaxed in future versions.

Here is an example of how you create a procedure that accesses another database and brings information of orders of a customer:

CREATE PROCEDURE spOmniListOrders (
	@customer varchar(100)	
	,@StartDate datetime = NULL
	,@EndDate datetime = NULL
)
AS 
/*omni
description: Lists the orders of a given customer and a specific month
params:
  customer: The name of the customer
  StartDate: "The start date of the order, in the format YYYYMMDD HH:mm:ss. If not informed, retrieves the last 24h"
  EndDate: "The end date of the order, in the format YYYYMMDD HH:mm:ss. If not informed, uses the current date"
*/

IF @StartDate IS NULL 
	SET @StartDate = DATEADD(HH,-24,GETDATE())

IF @EndDate IS NULL 
	SET @EndDate = GETDATE()

SELECT
	 OrderId
	,TotalValue
	,DeliveryDate
FROM
	Sales.dbo.Orders P
	JOIN
	Sales.dbo.Customers C
		ON C.id = P.customer_id
WHERE
	C.name = @customer

The procedure above presents some new elements that were not shown until now:

• Now there is a list of parameters. The AI model that you chose in Power Omni will be able to send the parameter value according to the context of the conversation and the agent instructions. And the SQL connector is responsible for building the secure SQL code that will be invoked, passing the parameters with the appropriate data type

• A comment was added, right at the beginning of the procedure body. This comment is used by the SQL connector to generate the documentation that Power Omni needs. This documentation helps your agent to have more information about the procedure, and how it should use it. This comment is a YAML code (do not use tabs for indentation).

If you try to invoke this procedure in your agent, it will not be able, for 2 reasons:

1. You need to update the schema by going to Settings -> Artifacts -> Connectors -> Choose the connector, click the edit icon, and at the end of the page, click 'Fetch specification'.

2. You need to grant permissions for the Login. In this case, now it will need to access the Sales database, Orders and Customers tables. You need to grant the permissions (or request to the team responsible for the database)

Now that you updated metadata and permissions, just go to the agent. In this case, if I ask "Give me the orders of customer ContosoCorp", it will invoke passing these filters. For more details about using Procedures, including tips and examples, see the Procedures Guide