Skip to main content

Record SQL Server

proxymock records the calls your app makes to Microsoft SQL Server and shows them in every database view: Live SQL, inspect a statement, link queries to requests, the SQL inventory and SQL script export. The Speedscale dashboard shows the same calls for traffic recorded in Kubernetes; see Databases.

It reads the TDS protocol that SQL Server speaks, so it works with SQL Server, Azure SQL Database, Amazon RDS for SQL Server and Azure SQL Managed Instance, and with any client that connects to them, such as SqlClient for .NET, mssql-jdbc for Java, go-mssqldb for Go and sqlcmd. Encrypted connections are recorded too: proxymock decrypts them the way it decrypts HTTPS (see Encryption).

SQL Server traffic is recorded, inspected and mocked: proxymock mock answers your app's SQL Server calls from a recording, so the app runs without its database (see Mock SQL Server). The recorded statements also replay against a real SQL Server to regression or load test it; see Load and Regression Test a Database.

To try every step on a sample app first, the SQL Server lab runs the same shop API in Go, Java, Node.js and Python against SQL Server 2022 in Docker, then records it, mocks it and replays its SQL against the database.

Start the recorder​

Map a local port to your SQL Server and start the recorder:

proxymock record --map 11433=localhost:1433 --app-port 8080

The recorder listens on port 11433 and forwards to SQL Server on 1433. proxymock recognizes SQL Server by the PRELOGIN message that opens every connection, not by the port, so a named instance on another port is mapped the same way, such as --map 11433=localhost:50123. See the architecture page for how the recorder works.

Point your app at the recorder​

Change the server address in your connection string to the mapped port. Keep your encryption setting, and let the driver accept the certificate proxymock presents, as the next section explains.

SqlClient (.NET):

Server=localhost,11433;Database=shop;User Id=app;Password=<password>;Encrypt=True;TrustServerCertificate=True

mssql-jdbc (Java):

jdbc:sqlserver://localhost:11433;databaseName=shop;encrypt=true;trustServerCertificate=true

go-mssqldb (Go):

sqlserver://app:<password>@localhost:11433?database=shop&encrypt=true&TrustServerCertificate=true

sqlcmd:

sqlcmd -S tcp:localhost,11433 -d shop -U app -N m -C

Change these settings only for the environment you record, such as a local or test database. Keep the driver checking the server certificate everywhere else.

Encryption​

proxymock decrypts encrypted SQL Server connections and records them in full, the way it records HTTPS. It ends the driver's TLS session with a certificate signed by the proxymock certificate authority, and opens its own TLS session to SQL Server. Every encryption setting is recorded, including the login, so each call knows its user, database and application name:

Driver settingHow the driver encryptsWhat the driver must trust
No encryption, such as go-mssqldb encrypt=disableNothingNothing
Encryption off, such as SqlClient Encrypt=False, mssql-jdbc encrypt=false, sqlcmd -N o, or the default of older driversThe login packet onlyThe proxymock certificate, or TrustServerCertificate=True
Encryption on, such as Encrypt=True or Encrypt=Mandatory, the default of SqlClient 4.0 and later and mssql-jdbc 10.2 and laterThe whole connectionThe proxymock certificate, or TrustServerCertificate=True
TDS 8.0 strict, such as SqlClient Encrypt=Strict or mssql-jdbc encrypt=strictThe whole connection, from its first byteThe proxymock certificate. Strict encryption always checks the certificate and ignores TrustServerCertificate.

To make the driver trust proxymock's certificate authority instead of skipping the check, add ~/.speedscale/certs/tls.crt to the trust store your driver uses. For Java, proxymock admin certs --jks creates ~/.speedscale/certs/cacerts.jks with the password changeit, which mssql-jdbc reads with trustStore=<path>;trustStorePassword=changeit or the JVM's javax.net.ssl.trustStore setting. See Java.

For strict encryption the certificate proxymock presents carries the host name the driver connects to. If the driver checks a different name, set it with HostNameInCertificate in SqlClient or hostNameInCertificate in mssql-jdbc.

The Speedscale sidecar in Kubernetes decrypts SQL Server connections when outbound TLS interception is on, with the sidecar.speedscale.com/tls-out: "true" annotation, and the app trusts the Speedscale certificate authority. See TLS support. Without it the sidecar records what the driver does not encrypt: everything after the login on a connection that encrypts only the login packet, with the user and application name unknown, and only the PRELOGIN on a fully encrypted connection.

What gets recorded​

Open the recording in proxymock web:

proxymock web

Each call is one RRPair:

  • SQL batches with their full T-SQL text. A batch can hold several statements and return several result sets.
  • RPC calls: sp_executesql, which SqlClient, go-mssqldb and the first execution in mssql-jdbc send; sp_prepexec and sp_execute, which mssql-jdbc and SqlClient use for prepared statements, where an sp_execute shows the SQL it was prepared with; and stored procedures called by name. Each carries its parameters with their types and values.
  • Responses: rows of every built-in type, row counts, return values and return status.
  • Errors with the SQL Server error number and message, such as 2627 for a duplicate key, and informational messages, such as PRINT output and RAISERROR at severity 10 or below.
  • Transactions: BEGIN, COMMIT and ROLLBACK sent as T-SQL or as the driver's transaction manager requests.
  • Attention: a cancel the driver sends when a command times out or the app cancels it.

Every call also carries the connection's user, database, application name and session id (SPID), and whether it ran inside a transaction. Database traffic lists every fact and how to filter on it.

In the statement view, With values fills the parameters into the SQL as T-SQL literals, such as N'text' for a string and 0x0A1B for binary, so you can copy the statement into sqlcmd or SQL Server Management Studio. An exported script ends each batch with GO.

Passwords, Windows (SSPI) authentication data and Microsoft Entra ID access tokens are never recorded.

Mock SQL Server​

Start the mock with the same port map the recording used, and keep the app's connection string pointed at the mapped port:

proxymock mock --map 11433=mssql://localhost:1433 -- your-app

The mock answers each SQL batch and RPC with the reply recorded for the same statement, matched the way PostgreSQL and MySQL mocks are: on the normalized SQL, not on the values bound to it. See Mock a database for what happens when nothing matches exactly and how a transform makes a bound value part of the match. A call with no recording gets SQL Server error 50000, Speedscale: responder miss, and appears as a miss in the mock's output.

The mock accepts any SQL Server login, whatever its password, and answers it for the database the app asks for. It accepts Windows logins too: an NTLM login gets a challenge and whatever answer follows is taken, and a Kerberos ticket is taken without being checked, since the mock has no domain to check them against. SqlClient, mssql-jdbc and go-mssqldb sign in to it with integrated security unchanged. It negotiates the same encryption a server does, so the connection strings above work unchanged against it, encryption on or off, and so does TDS 8.0 strict encryption. A driver that checks the certificate needs to trust the proxymock certificate authority, as for recording (see Encryption).

Prepared statements work across the mock: an sp_prepexec gets a statement handle of the mock's own, and the sp_execute calls that reuse it are answered from the recordings of the same SQL. Requests a driver or pool sends for its own housekeeping are answered even when the recording does not hold them: SET options, BEGIN TRANSACTION, COMMIT and ROLLBACK as T-SQL or as transaction manager requests, SELECT @@SPID, @@VERSION and @@TRANCOUNT, a pooled connection's reset, and an attention.

To key the mock on bound values, so a lookup recorded with several ids answers each id with its own result, use sql_key_params. It keys on every bound value by default; set its params to 1 to key on only the first, such as @P0.

To read or rewrite one bound value, for example to store the id a lookup asks for in a variable, use the mssql_param extractor. It does not change what the mock matches on.

Limits​

  • An Azure SQL Database client inside Azure uses the redirect connection policy by default: after the login the server sends the driver to the database node, and the driver reconnects there directly, around the recorder. Record from outside Azure, where the proxy policy is the default, or set the server's connection policy to Proxy for the recording.
  • An inbound SQL Server, one that proxymock or the sidecar receives connections for rather than connects to, records encrypted connections as the sidecar does without tls-out. See Encryption.
  • Connections with MultipleActiveResultSets (MARS) turned on, such as MultipleActiveResultSets=True in SqlClient, are not decoded past the PRELOGIN. Turn MARS off for the recording.
  • Bulk load data sent by BCP or SqlBulkCopy, table-valued parameters and Always Encrypted columns are not decoded.
  • The mock accepts SQL Server logins and Windows logins over NTLM or Kerberos. NTLM inside Negotiate (SPNEGO), which a Windows client falls back to when it cannot get a Kerberos ticket, gets error 18452; for the mocked run, give the app a Kerberos ticket, NTLM directly, or a SQL Server login. An app that logs in with Microsoft Entra ID needs a SQL Server login for the mocked run.
  • The mock does not offer MARS, column encryption (Always Encrypted) or session recovery: a driver that asks for them goes on without them.
  • A replay against a database signs in with a SQL Server login, Microsoft Entra ID, or a Windows login over NTLM or Kerberos, and skips server cursors, bulk load and MARS sessions.

In Kubernetes​

When your app runs in Kubernetes with the Speedscale operator, the sidecar records SQL Server calls with the rest of the workload's traffic, with no --map. Turn on outbound TLS interception to record encrypted connections; see Encryption. The calls appear on the dashboard's Traffic page with the database columns and filters; see Watch live SQL.

Loading search...