I've been connecting to SQL Server databases for almost 20 years, but my latest client has me baffled. For the first time that I know of, our database has been made part of an Availability Group under SQL Server 2012. This is a new client and so far, their impression of me is probably not that great.
Quite simply, the problem is that I am unable to connect a legacy application using the OLEDB provider to a database hosted in a SQL 2012 Availability Group. Despite 2 hours of working with a junior tech. on their end, and trying many different combinations, my application is not able to find the server. What I'm wondering, is if there is any special parameter required in the connection string when connecting to an availability group?
The current form of the connection string that is failing is:
Provider=sqloledb;Server=servername\instance;Database=databasename;User ID=username;Password=password
While on the remote with the junior tech., we were able to remote into the server and verify that the database was available, and that our sql login was setup correctly...also verified that sql authentication was enabled. (I've been bit by that dog before) Everything seems to be setup correctly...even made sure that the client firewall was disabled. No joy!
This fiasco took place on the last business day of this year for the client, so I have been unable to try anything since learning a little more about AGs/Listeners. It would be ideal to replicate the customer's environment for testing, but setting up a failover cluster and AG seems like overkill when there's probably a simple parameter that I am missing.
Other info: From the workstation having the problem, I can successfully ping the database servers. (primary and replica) The error message returned is 'The Server does not exist or access is denied'. It seems more like a timeout than a permissions issue. Also, if it matters, the application uses ADODB connections/objects.
It will be over two weeks until the customer returns from the holidays. In the meantime, I'm charged with explaining to their lead tech. (who was unavailable when we were having problems) what the problems are, and what we are going to do to fix it. (in addition to a lot more crap they want now)
What I have tried:
0:) Searched connectionstrings.com
1:) Spent the last two days reading up on Availability Groups and client connections. I have a few things to try, but it'll be trial by fire unless I invest the time to replicate the environment.
Things I would try if I were able:
0:) Prepend the server name (in the connection string) with tcp: (is this important?)
1:) Use the listener name instead of server/instance (even though all the documentation I read says this should work)
2:) Add a connection string parameter (MultiSubnetFailover=True)
2:) Check the port numbers being used
3:) Try a different variation of OLEDB connection string
What I'm hoping is that someone here may have access to an AG test environment and can help confirm that it is even possible to connect with the OLEDB provider per the connection string example given. Any help or hints are greatly appreciated!