July 21, 2016 at 5:32 am
Hi,
I have a SQL Server Multi Subnet cluster across Data Centres. Yesterday I set up an availability group for one of our SQL 2012 databases (11.0. 5623).
We have an ODBC connection to the database from one of our app servers which we were hoping to enable MultiSubnetFailover on but we started getting the following error:
Connecting to a mirrored SQL Server instance using the multiSubnetFailover connection property is not supported
After trawling the internet and finding nothing which matched our specific error (i.e. not having database mirroring enabled and still getting the error above), i found a specific sitation which causes this error in the ODBC driver.
When running through the ODBC wizard, IF the default database is changed from default or master, and Multisubnetfailover is checked, then the error is received.
Microsoft SQL Server Native Client Version 11.00.2100
Data Source Name: test2
Data Source Description:
Server: MyServer
Use Integrated Security: No
Database: myuserdatabase
Language: (Default)
Data Encryption: No
Trust Server Certificate: No
Multiple Active Result Sets(MARS): No
Mirror Server:
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Regional Settings: No
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes
Multisubnet Failover: Yes
Microsoft SQL Server Native Client Version 11.00.2100
Running connectivity tests...
Attempting connection
[Microsoft][SQL Server Native Client 11.0]Connecting to a mirrored SQL Server instance using the MultiSubnetFailover connection option is not supported.
Disconnecting from server
TESTS FAILED!
Changing the database back to default:
Microsoft SQL Server Native Client Version 11.00.2100
Data Source Name: test2
Data Source Description:
Server: MyServer
Use Integrated Security: No
Database: master
Language: (Default)
Data Encryption: No
Trust Server Certificate: No
Multiple Active Result Sets(MARS): No
Mirror Server:
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Regional Settings: No
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes
Multisubnet Failover: Yes
Success
Microsoft SQL Server Native Client Version 11.00.2100
Running connectivity tests...
Attempting connection
Connection established
Verifying option settings
Disconnecting from server
TESTS COMPLETED SUCCESSFULLY!
This happens regardless of the privilege set of the login used as i've tried as sysadmin, and with some other permission sets and makes no difference.
(obviously there are workarounds, such as setting the default database on the sql login and leaving the ODBC default.)
Just wondering if anyone else has seen the same issue - cant find anything on how to fix this or if its a bug.
March 9, 2023 at 7:43 pm
I ran into this issue recently (Feb 2023) and spent some time trying to figure out what's going on. I knew this must have been something simple in the configuration causing SQL Server to assume Mirroring where there is no mirroring present on the failover cluster.
So we were getting the same errors as you. We checked all the drivers to ensure they're supported. We checked DC connectivity. We checked a lot of settings and finally we checked the one that was causing the issue. If you have the setting "Readable Secondary" off in your AG properties window, the error message above is what you get complaining about not supported.
Once you turn on the "Readable Secondary" on all nodes, the multisubnetfailover checked and everything started to work.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply