SuperAPI AI Directory
Partner with us中文AI newsFavorites
Skillslangchain-and-llamaindex-on-azure-sql

langchain-and-llamaindex-on-azure-sql

Agents & models

View repository

Updated 10-16 00:31

About this skill

LangChain and LlamaIndex on Azure SQL Database

Both frameworks connect to this engine on the first try and then get three things wrong that the connection test cannot show. This is not a text to SQL tutorial and not a vector search reference.

Measured 2026-08-28 by installing the packages, reading what ships, and running every statement below against a local Azure SQL Database container (SERVERPROPERTY('EngineEdition') returns 5). Every argument name was re-checked 2026-09-03 against the LangChain and LlamaIndex reference documentation. The pinned versions move fast, so re-run the reference file before trusting one.

Reproduce it first, in two minutes

python -m venv .venv && .venv/bin/pip install \
  "langchain==1.3.18" "langchain-community==0.4.2" "langchain-classic==1.0.8" \
  "langchain-sqlserver==1.0.1" "llama-index-core==0.14.24" "sqlalchemy==2.0.52" "pyodbc==5.3.0"

import os, urllib.parse
from langchain_community.utilities import SQLDatabase
from langchain_community.agent_toolkits.sql.prompt import SQL_PREFIX

odbc = urllib.parse.quote_plus(
    "DRIVER={ODBC Driver 18 for SQL Server};SERVER=<host>,<port>;DATABASE=<database>;"
    "UID=<user>;PWD=" + os.environ["SQL_PASSWORD"] + ";Encrypt=yes;TrustServerCertificate=yes")
db = SQLDatabase.from_uri(f"mssql+pyodbc:///?odbc_connect={odbc}")

print(db.dialect)                                       # mssql
print(SQL_PREFIX.format(dialect=db.dialect, top_k=10))  # "always limit your query to at most 10"
print(db.get_table_info())                              # DDL plus three real rows per table

Three prints, three defects: the dialect is a string from the URL, the prompt tells the model to limit, and the schema tool hands live rows to the model before anyone asked it a question.

The correction

1. The dialect is the string mssql, and the agent prompt still says "limit". It comes from the URL, not the server, so it is the same for the container and the cloud, and it is interpolated into a prompt that never learned T-SQL. Section 1.

2. The framework-created vector table cannot take a vector index. langchain-sqlserver 1.0.1 creates it with PRIMARY KEY NONCLUSTERED, so the table is a heap and CREATE VECTOR INDEX is refused with Msg 42254. The package holds no approximate search path at all, so retrieval is a full scan for the life of the table, with correct results and latency that only grows. LlamaIndex has no Azure SQL Database vector store, so an agent installs a neighbour and wires the wrong database. Section 2.

3. Read-only, table scoping and no-DML are prompt text, not access control. With include_tables set to one table, the query tool still ran a SELECT against an excluded table and returned a row; db.run executed an INSERT and a CREATE/DROP pair. Section 3.

Capability LangChain LlamaIndex
SQL toolkit over an existing schema SQLDatabaseToolkit, four tools NLSQLTableQueryEngine
Per-dialect text to SQL prompt Only on the legacy chain path None. One template per engine
Native vector column store langchain-sqlserver, first party None. Keep retrieval in SQL
Approximate vector search Not implemented Not applicable
Table info includes live rows Yes, three by default No, columns and types only

1. The dialect prompt, and the error that names the wrong token

There are two prompt surfaces in LangChain and the per-dialect fix reached only one of them.

Surface What it tells the model Verdict
SQL_PROMPTS['mssql'] in langchain_classic.chains.sql_database.prompt, used by create_sql_query_chain "at most {top_k} results using the TOP clause as per MS SQL" Correct
SQL_PREFIX in langchain_community.agent_toolkits.sql.prompt, the default prefix of create_sql_agent "a syntactically correct {dialect} query ... always limit your query to at most {top_k} results" Wrong, dialect-blind
The hub prompt langchain-ai/sql-agent-system-prompt, and the system_prompt written out in LangChain's own SQL agent guide The same "always limit your query" wording Wrong
DEFAULT_TEXT_TO_SQL_TMPL in llama_index.core.prompts.default_prompts {dialect} only. No row cap, no pagination guidance, no per-dialect variant Silent

The fix exists and sits on the chain path current documentation steers people away from.

The failure is loud in the wrong place

Measured through QuerySQLDatabaseTool, which is what the model reads before it retries:

Query the model wrote What comes back
SELECT customer_id, full_name FROM customers LIMIT 10 Msg 102, Incorrect syntax near '10'
SELECT ... FROM customers ORDER BY full_name LIMIT 10 Msg 102, Incorrect syntax near 'LIMIT'
SELECT customer_id FROM customers OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY Msg 102, Incorrect syntax near '0'

Without an ORDER BY, the error names the number and never mentions LIMIT. The reason is measured, not inferred:

SELECT LIMIT.customer_id FROM dbo.customers LIMIT;   -- succeeds, returns every row

T-SQL parses LIMIT as a table alias and then trips over the integer that follows it. A model handed Incorrect syntax near '10' has no reason to remove a keyword the message does not mention, so it edits the number, or the column list, and tries again. Two things make that worse:

  • The toolkit's sql_db_query_checker tool, which its own tool description says to call before every query, uses a prompt listing NOT IN with nulls, UNION versus UNION ALL, BETWEEN, type mismatches, quoting, argument counts, casts and join columns. Dialect pagination is not on the list. The checker passes the query through.
  • QuerySQLDatabaseTool calls db.run_no_throw, so the failure never raises in the application. It becomes a string in the model's context and a longer trace.

Replace the prompt. Do not pass the default and hope

prefix must still contain the {dialect} and {top_k} variables, so keep them and put the syntax instruction in a sentence the dialect string is not carrying:

from langchain_community.agent_toolkits import create_sql_agent

TSQL_PREFIX = """You are an agent designed to interact with an Azure SQL Database ({dialect}).
Write T-SQL. Return at most {top_k} rows with SELECT TOP ({top_k}), which is how this dialect caps
a result set. LIMIT does not exist in T-SQL and the parser reports it as a syntax error on the
number that follows it, not on the keyword. To page, write
ORDER BY <column> OFFSET <n> ROWS FETCH NEXT <m> ROWS ONLY; the ORDER BY is mandatory.
Never issue INSERT, UPDATE, DELETE, DROP or any other DML statement."""

agent = create_sql_agent(llm, db=db, agent_type="tool-calling", prefix=TSQL_PREFIX, top_k=10)

LlamaIndex takes the same correction through text_to_sql_prompt, whose template needs {dialect}, {schema} and {query_str}:

from llama_index.core import PromptTemplate
from llama_index.core.query_engine import NLSQLTableQueryEngine

tsql_tmpl = PromptTemplate(
    "Given an input question, write a syntactically correct T-SQL query for Azure SQL Database "
    "({dialect}). Cap rows with SELECT TOP (n). LIMIT is not valid T-SQL. Page with "
    "ORDER BY <column> OFFSET <n> ROWS FETCH NEXT <m> ROWS ONLY.\n"
    "Only use the tables below.\n{schema}\n\nQuestion: {query_str}\nSQLQuery: ")

engine = NLSQLTableQueryEngine(sql_database=lidb, tables=["customers"],
                               text_to_sql_prompt=tsql_tmpl)

Keep the prompt short. The dialect rules themselves belong in t-sql-correctness, not in a prompt string that then goes stale.

2. Retrieval, and the index the vector store cannot have

langchain-sqlserver is the supported LangChain vector store for this engine and it does use the native type. embedding_length is required and is not derived from the embedding function; distance_strategy defaults to cosine:

from langchain_sqlserver import SQLServer_VectorStore

store = SQLServer_VectorStore(connection_string=f"mssql+pyodbc:///?odbc_connect={odbc}",
                              embedding_function=embeddings, embedding_length=1536,
                              table_name="lcv_docs")

The table it then creates, read back off the engine, with the dimension from embedding_length:

CREATE TABLE lcv_docs (
    id               UNIQUEIDENTIFIER NOT NULL,
    custom_id        VARCHAR(1000) NULL,
    content_metadata json NULL,
    content          NVARCHAR(max) NOT NULL,
    embeddings       vector(1536) NOT NULL,
    PRIMARY KEY NONCLUSTERED (id)          -- this line is the problem
);

PRIMARY KEY NONCLUSTERED leaves the table a heap, and a heap cannot carry a vector index. Add the clustered index immediately after the store first initialises, before it holds volume, because adding it later rewrites the table's storage:

CREATE VECTOR INDEX vi_lcv ON dbo.lcv_docs (embeddings) WITH (METRIC='cosine', TYPE='diskann');
-- Msg 42254, Clustered index is required on table 'dbo.lcv_docs' to create a vector index.

CREATE CLUSTERED INDEX ix_lcv_docs_cl ON dbo.lcv_docs (id);
SET QUOTED_IDENTIFIER ON;
CREATE VECTOR INDEX vi_lcv ON dbo.lcv_docs (embeddings) WITH (METRIC='cosine', TYPE='diskann');
-- Msg 42266 once the clustered index clears the first gate: 100 non-null vectors are required.

Msg 42266 is the expected second gate and proves the clustered index fixed the first. Without QUOTED_IDENTIFIER ON the statement fails with Msg 1934 before reaching either.

The emitted query is the exact-scan shape, and that is not configurable

similarity_search_with_score(k=2) on a vector(8) store, captured off the connection with the vector literal written out:

SELECT TOP 2 lcv_docs.id, lcv_docs.custom_id, lcv_docs.content_metadata, lcv_docs.content,
       VECTOR_DISTANCE('cosine', cast ('[0.1,0.2,0.3,0.4,0.5,0.6,0.7,0.8]' as vector(8)),
                       embeddings) AS distance
FROM lcv_docs ORDER BY distance ASC;

TOP and not LIMIT, because SQLAlchemy compiles the row cap for the dialect even when the prompt does not. But the package contains no VECTOR_SEARCH, no WITH APPROXIMATE and no CREATE VECTOR INDEX anywhere in its source, so there is no option to turn on. Below roughly tens of thousands of rows, accept it. Above that, do the retrieval in SQL and keep the framework for chunking, prompting and orchestration. Read vector-search-azure-sql before writing that query: the shape that reaches the index is a different statement, not a tuning option.

Behaviour Detail
A 3072 dimension model Msg 2717 at construction: the size exceeds the maximum allowed (1998). Decide the dimension budget first, which is rag-on-azure-sql
embedding_length mismatched to the function Msg 42204 at write time: the vector dimensions do not match
Metadata filters run inside the search query WHERE JSON_VALUE(content_metadata, ?) = ?, parameterised, applied before the top-k. Good
Numeric metadata filters cast to NUMERIC(10, 2) {"ts": {"$gt": 1756500000}} returns Msg 8115, arithmetic overflow converting nvarchar to numeric. The cast is on the stored column, so one oversized value poisons every numeric filter on that key, and it throws at read time, never at write. Keep epoch times, large ids and money out of metadata
Batch size is capped at 419 Larger raises before any round trip
Auth falls back silently No Uid/Pwd and no Trusted_Connection=yes switches to DefaultAzureCredential, so a typo in the credential keywords fails as a token acquisition, not as a bad password

LlamaIndex has no Azure SQL Database vector store

Checked on the package index and in the integrations tree on 2026-08-28. Do not let an agent install a neighbouring package because the name looks close. The two honest options are to keep retrieval in T-SQL and feed the rows in as nodes, or to use the SQL toolkit for structured questions and put the semantic half where this catalog already covers it. Producing the embedding inside the engine is embeddings-and-external-models.

3. Read-only, no schema dumping, parameterised

Treat all three as things to switch on. None of them is a default.

Read-only is a login, not a setting

Neither framework has a read-only mode. SQLDatabase.run executed an INSERT that committed and a CREATE TABLE/DROP TABLE pair in one call; LlamaIndex's run_sql executed an UPDATE. The DO NOT make any DML statements line is a sentence in a prompt, so any instruction reaching the model through retrieved content competes with it on equal terms. The control is a dedicated least-privilege login. Measured through the query tool with that login:

Statement Result
SELECT TOP (2) full_name FROM customers Rows
UPDATE customers SET city = N'Owned' WHERE customer_id = 1 Msg 229, The UPDATE permission was denied on the object 'customers'
DROP TABLE customers Msg 3701, Cannot drop the table ..., because it does not exist or you do not have permission
EXEC sp_executesql N'SELECT 1' Runs. The tool does not restrict itself to SELECT text either

Create that login only with the read-only grants shown here, because the grant is the whole guardrail.

include_tables scopes the prompt, not the connection

Worth measuring yourself, because the parameter name suggests otherwise:

db = SQLDatabase.from_uri(uri, include_tables=["customers"], sample_rows_in_table_info=0)
print(db.get_usable_table_names())     # ['customers']

from langchain_community.tools.sql_database.tool import QuerySQLDatabaseTool
print(QuerySQLDatabaseTool(db=db).invoke("SELECT TOP (1) id FROM lcv_docs"))   # returns the row

include_tables decides what the model is told about. It does not decide what the connection can reach. Scope the login to the schema instead.

The schema tool returns live rows by default

sample_rows_in_table_info defaults to 3, so the schema tool returns the CREATE TABLE text plus three real rows, every column, into the model's context and into whatever stores the trace. On a customer table that is three real customers.

db = SQLDatabase.from_uri(uri, sample_rows_in_table_info=0)                    # schema only
db = SQLDatabase.from_uri(uri, custom_table_info={"customers": FAKE_DDL})      # or fake samples

Error text is a second channel: run_no_throw returns the driver message verbatim, database, schema and object names included, as in Msg 229 and Msg 3701 above.

Parameterised means the parts you write

The framework's own SQL binds its values. The query tool takes one argument, a SQL string the model wrote, so a text to SQL agent is by construction a machine that executes generated SQL and there is no parameterisation to add there; the containment is the login. In the code you write around the framework, bind the values, and never build a query out of a chunk retrieved from the corpus.

Check it worked

Four assertions against the wiring you are about to ship. The first two fail on a default install:

import os, re
from langchain_community.tools.sql_database.tool import QuerySQLDatabaseTool

generated_sql = os.environ["AGENT_SQL"]      # one query your agent actually produced
assert "SELECT TOP" in TSQL_PREFIX and "LIMIT does not exist" in TSQL_PREFIX
assert "3 rows from" not in db.get_table_info()
denied = QuerySQLDatabaseTool(db=db).invoke("UPDATE customers SET city = N'x' WHERE 1 = 0")
assert "permission was denied" in denied, denied
assert not re.search(r"\bLIMIT\b", generated_sql, re.I), generated_sql
print("prompt, schema exposure, write permission and generated SQL all check out")

Expected: the script prints that line and exits 0. A failure on the get_table_info assertion means sample_rows_in_table_info is still 3; a failure on the denied assertion means the agent's login can write, which is the finding and not a test bug. Then confirm the vector table can carry an index at all, with the password in SQLCMDPASSWORD:

sqlcmd -S <host>,<port> -d <database> -U <user> -C -Q \
  "SELECT type_desc FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.lcv_docs') AND type_desc = 'CLUSTERED';"

Expected: one row reading CLUSTERED. An empty result set means the table is still the heap the store created, and CREATE VECTOR INDEX on it will return Msg 42254.

Do not

  • Do not accept the default SQL agent prompt on this engine. It renders as mssql, tells the model to limit, and the error it earns names the wrong token.
  • Do not assume the per-dialect prompt applies to the agent. It exists only on the chain path.
  • Do not conclude the query is fine because the checker tool approved it, and do not fix a LIMIT failure by editing the number the error points at.
  • Do not let the vector store create its table and then plan on a vector index later. The heap is the blocker and the migration gets more expensive with every row.
  • Do not expect approximate vector search from langchain-sqlserver. There is no code path.
  • Do not install a LlamaIndex vector store package whose name merely resembles this engine's.
  • Do not rely on include_tables or on a prompt sentence to stop a write. A login stops a write.
  • Do not ship with sample_rows_in_table_info at its default and call the agent read only.
  • Do not put an epoch timestamp or a large id in metadata that anything filters on numerically.
  • Do not re-teach VECTOR_DISTANCE, VECTOR_SEARCH or the vector index here. Those are vector-search-azure-sql.

References

  • Open references/framework-prompts-and-vector-store.md when a claim above disagrees with what you are seeing, which after a version bump it will: it holds the install, every probe script, the raw engine output and the reproduction steps.
  • Read vector-search-azure-sql before writing the retrieval query yourself, for the vector type, its restriction list and the query shape that reaches the index rather than scanning.
  • Read rag-on-azure-sql before choosing embedding_length, because the dimension budget, the chunk schema and the permission filter are decided there. For offline use, keep embedding application-side and preserve the same schema and permission filter.
  • Read t-sql-correctness when the generated SQL is wrong in a way that is not pagination, connect-from-python when the failure is the driver, the connection string or the token rather than the framework. Apply the read-only grants in section 3 before exposing the connection.
  • Read LangChain's SQL agent guide to check whether the shipped prompt has changed before trusting section 1, and LlamaIndex structured data for where the prompt argument goes on the query engine.