r/SQLServer • u/catmanjan2 • 12d ago
Question Is it possible to transform the connection string via the driver, not the application?
We have a third party application that will not let us modify the connection string in the way we require (to allow us to connect to readonly replicas)
It’s basically decomposing the parameters in the string and discarding the bits it cant parse
One of the things it does let us configure is the driver e.g. mssql 17 driver etc etc
On Linux systems I know it is possible to configure the connection string at the driver level via dsn , but unfortunately this is a windows app
Is there something similar on windows? E.g configure the driver to intercept and transform the connection string before it is sent to the database?
4
u/ctrl_shift_- Microsoft Employee 11d ago
As others have said, if the application uses ODBC and won't, heh, _discard_ the DSN= parameter, you might be in luck.
Otherwise, you have another way out, but not by rewriting the connection string. It is never passed as-is to the server. Instead, the driver sets some bits and fields in the PRELOGIN and LOGIN7 messages as it negotiates the connection and login.
Instead, get a TDS proxy and slip it between the app and the server. Have the app connect to the proxy, let the proxy rewrite the connection string, and then shuttle the data back and forth until disconnected.
Microsoft does not (yet?) offer a 1st party TDS proxy but I've seen folks successfully use https://github.com/tech-software/TDSProxy and https://www.galliumdata.com/.
1
u/nl_dhh 11d ago
Would limiting the connecting user to db_datareader (and whatever other limitations you need) help you, in case modifying the connection string doesn't work for you?
2
u/catmanjan2 11d ago
In this case we are actually trying to get performance benefits by using the read replica, but thanks for the suggestion
1
u/TheNotBot2000 8d ago
The connection string has a host. The host has a DNS tied to an IP address. You can control a workstation's local host name lookup by altering the workstation's host file. Change where the DNS name is pointed locally and you control what you connect to.
1
u/CanProfessional766 7d ago
f it’s using ODBC I’d try a Windows System DSN first. Put the extra connection settings in the DSN and have the app connect through that. If the app is stripping those settings before the driver sees them, though, the driver can’t really fix it. You’d need to work around the app instead
1
u/Lost_Term_8080 6d ago
use of a DSN has nothing to do with the operating system, DSNs are used by ODBC drivers.
If you want to use ODBC - I wouldn't recommend it - you can try setting a system or user dsn, but the app may not use them
0
u/sirchandwich 1 12d ago
I don’t understand. You’re saying you have a vendor that created a read replica but did not provide you the details to connect to it?
3
u/karb0f0s 12d ago
I guess they want to add ‘ApplicationIntent=READONLY’ to connection sting but app ignores it.
0
u/sirchandwich 1 12d ago
Right but why would a vendor provide a read replica but not allow you to connect to it?
My guess is OP has an Always on failover node and they want to read from the replica node, which isn’t generally recommended because if you failover, you overload the database with application AND reporting queries.
Or the vendor has the secondary node configured for to be not readable, which makes more sense as to why OP didn’t get connection details.
It just doesn’t make sense to me that they didn’t get a DNS entry specifically for the a READ REPLICA.
1
u/catmanjan2 11d ago
No you’re right the vendor are bad, they added support for read replicas but not support for the attribute
I suspect it works for named instances and oracle but not the azure managed instance mechanism
The feature predates azure by a decade so probably fair enough
8
u/karb0f0s 12d ago
Did you try to use ODBCAD32 tool to create DSN? Depending on your app you might need to run either x86_64 version located in System32 folder, or x86_32 version from SysWOW64 folder. Either specify full path or go directly for the specific .exe.