DaffyDuo 0.1.0

DaffyDuo

DaffyDuo is a SQL Server gateway that gives one client connection up to three SQL Server connections behind it. Connect SSMS, an application or any SQL Server client to DaffyDuo once, and move between servers and databases with one line of T-SQL:

EXEC daffy.connect 'reporting'

Every connection keeps its own server session, so nothing is lost by switching: a #temp table, SET options and the current database are still there when you switch back. The one thing that stops a switch is an open transaction, and DaffyDuo tells you so plainly.

There is nothing to install on the servers and nothing to change in the client: it connects to DaffyDuo exactly as it would to SQL Server. Administration happens in T-SQL too. An admin connects like anyone else and runs EXEC daffy.help.

• Simple - three commands for everyone: daffy.connect, daffy.status, daffy.help.
• Transparent - daffy.status shows where you are, which connections are open, and the server and database behind each. Every refusal is an ordinary SQL Server error with a number and a sentence that says what to do.
• Safe - connections never switch on their own, and a server session that ends is never reopened behind your back. Encryption is mandatory; passwords are hashed or encrypted at rest and never logged.

dotnet tool install -g DaffyDuo      # install
dotnet tool update -g DaffyDuo       # upgrade
daffyduo --version

It shares its TDS front end with DaffyTee and DaffyDB.

• Quick start · Switching · Transactions
• Connection pooling · Databases · When a server session ends
• Administration · Passwords and secrets · Entra ID
• Locked out? · Command reference · Errors
• SSMS and other tools · Running it · Limits · License

Quick start

1. Set it up. On the machine DaffyDuo will run on, create the first admin and the first connection that admin will use:

daffyduo init

  first admin login: scott
  DaffyDuo password for scott: ********
  again, to confirm: ********
  connection name (e.g. sales): sales
  connection string: Server=tcp:sql01,1433;Database=Sales;User Id=svc_sales;TrustServerCertificate=True
  password for target login svc_sales: ********
  testing svc_sales@sql01:1433/Sales (Mandatory (TDS 7.4), certificate trusted without validation) ...
  ok - Microsoft SQL Server 16.0.4295, database Sales, sql01, 21 ms
  Saved: admin 'scott', default connection 'sales' -> svc_sales@sql01:1433/Sales

Secrets are typed at a masked prompt, never put on a command line. The connection is tested before anything is saved.

2. Start it:

daffyduo

It listens on 127.0.0.1:1433 (--port, --bind to change that) and prints the connection strings to use.

3. Connect with SSMS or any client, as scott, with Encrypt=Mandatory and "Trust server certificate", then:

EXEC daffy.help

4. Add connections and people, still in T-SQL:

EXEC daffy.add_connection 'reporting', 'Server=tcp:sqlrpt01;Database=Reports;User Id=svc_rpt;Password=...;TrustServerCertificate=True'
EXEC daffy.add_user 'bob', 'Pa55word!'
EXEC daffy.grant_connection 'bob', 'sales'        -- bob's first connection: his default
EXEC daffy.grant_connection 'bob', 'reporting'

5. Bob connects as bob, lands on sales, and switches whenever he likes:

EXEC daffy.status
EXEC daffy.connect 'reporting'
SELECT Region, Amount FROM dbo.Totals;             -- runs on sqlrpt01
EXEC daffy.connect 'sales'

TrustServerCertificate=True keeps the connection to SQL Server encrypted but accepts a certificate nobody vouches for - which is what most SQL Servers present until they are given a real one. Once a server has a certificate this machine trusts, leave it out.

Switching

EXEC daffy.connect 'reporting'

Now on 'reporting' (sqlrpt01, Reports).

connection  current  default  state  server    database  spid
reporting   yes               open   sqlrpt01  Reports   74

• It sticks. Every statement after it runs on reporting until you switch again.
• It opens the connection the first time. A connection you never use costs its server nothing.
• It keeps everything. Each connection has its own server session for as long as you are connected. A #temp table made on sales is still there after a trip to reporting and back; so are your SET options, and the database you last USEd.
• It answers like USE. The server's own database and collation changes come back to the client, so SSMS's database list follows, SqlConnection.Database is right, and varchar parameters are encoded for the database you are now in.
• It returns the database you are in now: the connection's database the first time; after that, wherever you last USEd on that connection.
• It is refused, never half-done. If the switch cannot happen - a transaction is open, the name is not one of yours, the server cannot be reached - you get an error and stay where you were.

daffy.status shows the whole picture at any time:

connection  current  default  state     server    database  spid
sales                yes      open      sql01     Sales     61
reporting   yes               open      sqlrpt01  Reports   74
archive                       not open  sql01     Archive

state is open, not open or lost (see When a server session ends). The server and database are shown to everyone; the SQL Server login behind a connection is shown only to admins.

Run each command on its own. A daffy.* command is answered by DaffyDuo and never reaches a server, so it must be the only statement in its batch. SELECT 1; EXEC daffy.status is refused with error 50104 rather than half-run. In SSMS, select just that line before pressing F5, or put GO before and after it. The EXEC and the quotes are optional: daffy.status and EXEC daffy.connect reporting work too.

From code it is an ordinary stored procedure call:

using var cmd = new SqlCommand("daffy.connect", connection) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddWithValue("@connection", "reporting");
cmd.ExecuteNonQuery();

Transactions

A transaction belongs to the server it was started on. Switching away would leave it behind, holding its locks, while the client carried on elsewhere. So an open transaction is the one thing that stops a switch:

BEGIN TRAN;
UPDATE dbo.Customers SET Name = N'Ada King' WHERE Id = 1;
GO
EXEC daffy.connect 'reporting'
GO

Msg 50101, Level 16, State 1
DaffyDuo: cannot switch to 'reporting': a transaction is open on 'sales'. COMMIT or ROLLBACK first.

COMMIT;
GO
EXEC daffy.connect 'reporting'      -- Now on 'reporting' (sqlrpt01, Reports).
GO

Nothing else stops a switch. Temp tables and cursors stay on their own connection, so they are safe.

How DaffyDuo knows - two checks, the second only if the first finds nothing:

1. The transaction descriptor. Every request a client sends carries one: zero outside a transaction, the server's id for it inside one. A switch request with a non-zero descriptor is refused at once, without asking the server. This catches BEGIN TRAN, SqlTransaction / BeginTransaction(), and transactions started by SET IMPLICIT_TRANSACTIONS ON - checked with Microsoft.Data.SqlClient, the ODBC driver (sqlcmd) and go-mssqldb (go-sqlcmd).
2. @@TRANCOUNT on the current server. One small round trip, only when switching, so the rule holds for any client, whatever its driver sends.

Connection pooling

Pooling works and is recommended. There is one rule to know: when a pooled connection is reused, it starts again on its default connection.

A pool hands the same connection to one piece of code after another. Each time, the client tells the server "reset this connection", and the server forgets the last user's temp tables, settings and open transaction. DaffyDuo treats that request as "as you were at login":

• the session goes back to the login's default connection,
• the default's server session is reset, exactly as a direct connection's would be, and
• the other connections are closed; their servers drop what they held.

var cs = "Server=tcp:duohost,1433;Encrypt=Mandatory;TrustServerCertificate=True;User Id=bob;Password=...";

using (var cn = new SqlConnection(cs))                  // Pooling=True is SqlClient's default
{
    cn.Open();                                          // bob starts on 'sales', his default
    new SqlCommand("EXEC daffy.connect 'reporting'", cn).ExecuteNonQuery();
    new SqlCommand("SELECT COUNT(*) FROM dbo.Totals", cn).ExecuteScalar();     // on reporting
}                                                       // back to the pool - still "on reporting"

using (var cn = new SqlConnection(cs))                  // the same pooled connection, reused
{
    cn.Open();
    var db = new SqlCommand("SELECT DB_NAME()", cn).ExecuteScalar();
    // "Sales": the reset took it back to 'sales', and the 'reporting' session was closed.
}

Why: without it, the second block - code that knows nothing about the first - would silently run on reporting, a server it never asked for. A pooled connection must look the same every time it is handed out, and its default connection is the only place that is true of.

So, with pooling: switch after opening the connection, not once at startup. Treat daffy.connect like a USE: part of the unit of work that needs it.

Databases

Every connection names a user database, and lands in it:

• daffy.add_connection refuses a connection string without Database= (or Initial Catalog=), and refuses master, model, msdb and tempdb. A session never lands in master by accident.
• On login, the default connection opens in its database - unless the client asks for one (Initial Catalog= in an application's connection string, "Connect to database" in SSMS), which it then gets, if the server allows it. Applications that name their database keep working.
• Other connections always open in their own database.

DaffyDuo decides where you land; SQL Server's permissions decide where you can go. A session can still USE master if the connection's login may. That is the server's rule to enforce, and DaffyDuo does not second-guess it.

When a server session ends

A server restarts, a network drops, a DBA runs KILL. DaffyDuo never quietly reconnects. A new session would look fine and have lost your temp tables and settings. Instead:

• If it was not in use, the connection is marked lost (daffy.status shows it), and the next statement for it says so:

  Msg 50105, Level 16, State 1
  DaffyDuo: connection 'reporting' was lost (the server closed the session). Its temp tables are gone.
  Run EXEC daffy.connect 'reporting' to open a new session, or switch to another connection.
  

  EXEC daffy.connect 'reporting' then opens a fresh session. You asked for it, so it is no surprise.

• If a transaction was open on it, or it ended in the middle of answering a request, DaffyDuo closes the client's connection. The client still believes in that transaction, and only a broken connection tells every driver and pool the truth. This is what a direct connection would have seen.

When the client disconnects, every server session behind it is closed, and each server rolls back whatever was left open.

Administration

Admins are logins with the admin flag. They use the same connections as everyone else, plus the admin commands, on any connection. There is no separate admin password and no special admin connection. A login that is not an admin gets error 50106 for an admin command.

-- connections: defined once, granted to logins
EXEC daffy.add_connection 'reporting', 'Server=tcp:sqlrpt01;Database=Reports;User Id=svc_rpt;Password=...'
EXEC daffy.test_connection 'reporting'
EXEC daffy.set_connection_string 'reporting', 'Server=tcp:sqlrpt02;Database=Reports;User Id=svc_rpt'   -- password kept
EXEC daffy.set_connection_password 'reporting', '...'
EXEC daffy.remove_connection 'reporting'          -- refused while granted; says to whom
EXEC daffy.connections

-- logins
EXEC daffy.add_user 'bob', 'Pa55word!'            -- , @admin = 1 for another admin
EXEC daffy.set_user_password 'bob', '...'
EXEC daffy.disable_user 'bob'                     -- enable_user, remove_user, set_admin 'bob', 1
EXEC daffy.users

-- who may use what
EXEC daffy.grant_connection 'bob', 'reporting'    -- three at most; the first is the default
EXEC daffy.set_default 'bob', 'reporting'         -- grants it too, if needed
EXEC daffy.revoke_connection 'bob', 'archive'

-- who is connected
EXEC daffy.sessions
EXEC daffy.kill_session 12

• Connections are global. reporting means the same server, database and SQL login to everyone who has it, and a password is changed in one place. Two people who need different SQL logins on the same database get two connections (sales_ro, sales_rw).
• Three connections per login, admins included. It is a fixed limit.
• A connection is tested before it is saved, the way a switch would open it, so "saved" means "works". A connection added without a password is saved untested and cannot be granted until it has one.
• A login's default cannot be revoked - make another one the default first.
• The last admin stays. The last enabled admin cannot be removed, disabled or demoted.
• Changes count at once. Admin rights and grants are checked on every command, so a revoke or a demotion applies to sessions already open. A session already on a revoked connection stays there until it switches away or disconnects; daffy.kill_session ends it sooner.
• Admin commands are logged with who ran them, and never with their passwords or connection strings.

Connection strings take SqlClient's keywords: Server, Database/Initial Catalog, User Id, Password, Encrypt (Mandatory - the default - Optional or Strict), TrustServerCertificate, HostNameInCertificate, Connect Timeout, and Authentication for Entra ID. Settings that belong to the client (Pooling, Application Name, MultipleActiveResultSets, ...) are accepted and ignored. Anything else is refused by name.

Passwords and secrets

DaffyDuo passwords are kept only as salted hashes (PBKDF2-SHA256). Connection passwords have to be sent to SQL Server, so they are encrypted instead. The whole store is sealed with AES-256-GCM, not even the list of logins is readable on disk, and its key is protected by DPAPI on Windows (owner- only file permissions elsewhere). No password is ever written to DaffyDuo's log.

There are three ways to give DaffyDuo a connection password:

1. In T-SQL - the convenient way:

EXEC daffy.set_connection_password 'reporting', 'S3cret!'

The connection to DaffyDuo is encrypted, so the password is safe on the wire. But what you type in SSMS can stay behind in its query history, AutoRecover files and saved scripts, just as with CREATE LOGIN ... WITH PASSWORD.

2. On the host - the most secure way. Add the connection without a password, then set it at a masked prompt on the machine DaffyDuo runs on. It never crosses the network and is never typed where it is echoed:

EXEC daffy.add_connection 'reporting', 'Server=tcp:sqlrpt01;Database=Reports;User Id=svc_rpt'
-- Saved 'reporting' without a password, so it is not tested yet and cannot be granted.

daffyduo connection password reporting
  password for target login svc_rpt: ********
  testing svc_rpt@sqlrpt01:1433/Reports ...
  ok - Microsoft SQL Server 16.0.4295, database Reports, sqlrpt01, 18 ms
  Saved.

3. From your own workstation, without a script file - call the command as a stored procedure with the password as a parameter, read at a masked prompt. It is never in any query text:

$admin  = Get-Credential -Message "Your DaffyDuo admin login"
$secret = Read-Host "New password for the connection 'reporting'" -AsSecureString

$cn = New-Object System.Data.SqlClient.SqlConnection(
    "Server=tcp:duohost,1433;Encrypt=True;TrustServerCertificate=True;" +
    "User Id=$($admin.UserName);Password=$($admin.GetNetworkCredential().Password)")
$cn.add_InfoMessage({ param($s, $e) Write-Host $e.Message })
$cn.Open()
$cmd = $cn.CreateCommand()
$cmd.CommandText = "daffy.set_connection_password"
$cmd.CommandType = [System.Data.CommandType]::StoredProcedure
[void]$cmd.Parameters.AddWithValue("@connection", "reporting")
[void]$cmd.Parameters.AddWithValue("@password", [System.Net.NetworkCredential]::new("", $secret).Password)
[void]$cmd.ExecuteNonQuery()
$cn.Close()

The same works for daffy.add_user and daffy.set_user_password.

Entra ID

Toward Azure SQL, a connection can log in as an Entra ID application (a service principal) with the same keywords as SqlClient, the application (client) id as User Id and a client secret as Password:

EXEC daffy.add_connection 'azsales',
  'Server=tcp:myserver.database.windows.net,1433;Authentication=ActiveDirectoryServicePrincipal;User Id=<application id>;Password=<client secret>;Database=Sales'

DaffyDuo gets the access token itself and sends the secret only to Entra ID, never to SQL Server. It follows Azure SQL's redirects itself, so clients never connect around it. The client secret is a password like any other, so all three ways above apply. Opening an Entra connection needs outbound HTTPS to login.microsoftonline.com; if Entra ID cannot be reached, the switch fails with 50103 and you stay where you were.

From clients, DaffyDuo takes its own logins only. Entra ID and Windows logins to DaffyDuo are refused, and Entra user identities (password, interactive, managed identity) toward servers are not supported yet.

Locked out?

Recovery happens on the machine DaffyDuo runs on, as the account it runs as. Whoever can do that can already read the store's key, so these commands ask for no admin password. Nothing can be recovered over the network.

Situation	On the host
An admin forgot their password	daffyduo admin passwd LOGIN - masked prompt, typed twice; a running gateway uses it at once
Nobody remembers which logins are admins	daffyduo admin list
The only admin has left	daffyduo admin add LOGIN CONNECTION - a new admin, on an existing connection (or a new one: it asks for the connection string)
An admin's default connection is down, so they cannot log in	daffyduo connection password NAME if its password changed on the server; or daffyduo admin default LOGIN OTHERCONNECTION
store.key is lost, or the store was copied to another Windows account	It cannot be opened, by design. daffyduo init --new-store starts again (the old files are kept as .bak)

Back up store.dat and store.key together. One is no use without the other. Both are in %LOCALAPPDATA%\DaffyDuo (~/.local/share/DaffyDuo elsewhere), or wherever --config-dir or DAFFYDUO_HOME puts them. On Windows the key opens only for the account that created it, or, with --machine-scope, only on that machine.

Command reference

Everyone:

Command
EXEC daffy.connect 'name'	switch to another of your connections; it stays current until you switch again
EXEC daffy.status	your connections: current, default, open, server, database, spid
EXEC daffy.help ['command']	what you can run, with examples

Admins, on any connection:

Command
daffy.add_connection @connection, @connection_string	define a connection; tested first, or saved untested without a password
daffy.test_connection @connection	log in the way a switch would, and say how it went
daffy.set_connection_string @connection, @connection_string	change where it goes; the password is kept if the new string has none
daffy.set_connection_password @connection, @password	change its password or client secret; tested first
daffy.remove_connection @connection	remove one nobody has
daffy.connections	every connection, who has it, its last test
daffy.add_user @login, @password [, @admin]	create a login
daffy.set_user_password @login, @password	change a login's password
daffy.enable_user, daffy.disable_user @login	let a login in, or stop it (open sessions continue)
daffy.set_admin @login, @admin	make a login an admin (1) or not (0)
daffy.remove_user @login	remove a login
daffy.grant_connection @login, @connection	let a login use a connection (three at most)
daffy.revoke_connection @login, @connection	take one away (not the default)
daffy.set_default @login, @connection	where its sessions start, and where a pooled reset returns them
daffy.users	every login
daffy.sessions	who is connected, and on which connection
daffy.kill_session @session	disconnect a session by its id in daffy.sessions

Arguments go by position or by name (@login = 'bob'), as for any stored procedure. Names put the verb first, and none is a T-SQL reserved word, so every command is valid T-SQL that tools accept as written.

On the host:

daffyduo init [--new-store]               set up: the first admin and its connection
daffyduo admin list | passwd LOGIN        recovery
daffyduo admin add | default LOGIN CONNECTION
daffyduo connection list | password NAME
daffyduo cert [trust | untrust]           the certificate clients see
daffyduo [--port N] [--bind ADDR] ...     run the gateway (daffyduo --help for every option)

Errors

DaffyDuo's own errors are ordinary SQL Server errors at severity 16, numbered from 50101, with messages that start DaffyDuo:. Applications can catch them by number.

#	Meaning	What to do
50101	A transaction is open; the switch was refused	COMMIT or ROLLBACK, then switch
50102	Not one of your connections	EXEC daffy.status lists yours
50103	The connection could not be opened (server down, login failed, mismatched settings)	You are still where you were; an admin can daffy.test_connection it
50104	A daffy.* command shared its batch with other SQL	Run it on its own
50105	The connection's server session ended	EXEC daffy.connect 'name' opens a new one
50106	An admin command from a login that is not an admin
50107	A connection string without a user database	Add Database=
50108	A connection without a password	Set it (see Passwords and secrets)
50109	The login already has three connections	Revoke one first
50110	The client asked for MultipleActiveResultSets (at login)	Remove MultipleActiveResultSets=True
50111	Unknown command, or wrong arguments	The message shows the usage; EXEC daffy.help 'name'
50112	No such login or connection, or one that already exists
50113	Refused by a rule: the last admin, a default, a connection still granted, your own session	The message says which
18456	Login failed	As SQL Server says it. The reason is in DaffyDuo's log, never told to the client

When the server refuses the client's own request at login - most often a database it cannot open (4060), or an Azure SQL database that is resuming (40613) - that error is passed on under its own number, so retry logic works.

SSMS and other tools

• Connect as you would to SQL Server: the server is DaffyDuo's address, SQL Server Authentication with your DaffyDuo login, Encryption Mandatory, and Trust server certificate ticked (or trust the certificate once).
• Each query window is its own session. Switching in one does not move the others.
• The database list follows a switch. IntelliSense keeps the first server's object names until you refresh it: Ctrl+Shift+R. It may underline daffy.* names it cannot find; they run.
• Object Explorer opens its own connection, on your default connection, and stays there.
• Run a daffy.* command alone: select the line, or separate it with GO.
• Other clients connect the same way; a JDBC URL for DBeaver and Java is printed at startup. Checked so far: Microsoft.Data.SqlClient, System.Data.SqlClient (Windows PowerShell), the ODBC driver's sqlcmd and go-sqlcmd.

Running it

daffyduo [--port 1433] [--bind 127.0.0.1] [--log-file "daffyduo-{date}.log"] [--no-repl] [--quiet]

• Listens on 127.0.0.1 until told otherwise: --bind 0.0.0.0.
• Encryption is mandatory. A self-signed certificate is generated on first run; --cert-file presents a real one. Encrypt=Optional clients are upgraded; clients that cannot encrypt are refused (--allow-plaintext relaxes that, for development). On Windows, daffyduo cert trust adds the generated certificate to your own trusted roots, for clients that cannot be told TrustServerCertificate=True; it is offered once at the prompt.
• The prompt runs alongside the listener: :status, :sessions, :kill ID, :log, :cert, :quit. It changes nothing about logins or connections - that is T-SQL's job.
• The log names every login, switch, lost connection and refusal, with the reason a client is not told. --log-file also writes it as JSON lines with stable event codes (session.open, session.switched, connection.lost, session.login_refused, admin.command, ...) to alert on. {date} starts a new file each day; keep the quotes in PowerShell.
• TCP keepalive is on for every connection, so dead peers are noticed.

Limits

• MultipleActiveResultSets is not supported. A client asking for it is refused at login with error 50110, which says so. Switching servers under MARS is out of scope for this version.
• A few features are not negotiated with the servers, because a client keeps what it negotiated with its first server for the whole session, whichever server is answering: connection resiliency (DaffyDuo never reconnects silently), Always Encrypted, data classification metadata, Azure elastic transactions, and the native json and vector types - such values arrive as text, as they do for any client that did not ask for them.
• Every connection a session uses must agree with the first on TDS version and packet size. If one does not - a client connecting with Encrypt=Strict to one server and another that does not support it, say - switching to it fails with 50103, which says so.
• SqlClient and UTF-8. After a session moves from a UTF-8 database to one that is not, Microsoft.Data.SqlClient 6.1 misreads varchar text from the second (café comes back as caf�). That is SqlClient's own behaviour after USE, on direct connections too; a switch reports collations exactly as USE does. nvarchar is unaffected.
• Commands inside sp_executesql are not looked into: a parameterised EXEC daffy.connect @c reaches the server, which answers "Could not find stored procedure". Call daffy.connect as a stored procedure instead, as above.
• Clients log in with DaffyDuo logins; Windows and Entra ID logins to DaffyDuo are refused. Toward servers: SQL logins, and Entra ID applications for Azure SQL. Windows authentication toward servers is not supported yet.
• Named instances need their TCP port (host,port); SQL Browser lookup is not implemented.
• Not a Windows service yet; it runs in a terminal, or with --no-repl under anything that keeps a process running.

License

Business Source License 1.1: free for non-production use by everyone, and in production for personal, academic and non-profit use, small organizations, and a 60-day evaluation by anyone else; each version becomes MIT four years after it is published. Commercial licenses are not yet on sale. Copyright (c) 2026 TroBeeOne LLC. Third-party components keep their own licenses: THIRD-PARTY-NOTICES.txt.

TDS began at Sybase in the 1980s and has many independent implementations - FreeTDS, jTDS, Tiberius, and Babelfish for PostgreSQL among them. DaffyDuo is one more, built from Microsoft's published [MS-TDS] specification; it contains no Microsoft code and is not affiliated with or endorsed by Microsoft. SQL Server, Azure and Entra are trademarks of Microsoft Corporation.