|This question was originally asked over a week ago in quick answers.
Edit: (2018-01-06) There is no problem getting the sqloledb driver to connect to an availability group. The problem cited below has been resolved. (possibly by forcing protocol as in tcp: ag-listener_name for the server) The client's dba tweaked some settings and it all works now...not sure what he did. Thanks to all responders.
I am posting it again here due to bad timing (right before holiday break) which possibly resulted in this question not being noticed by an expert before getting pushed back to page 50+. @OriginalGriff did post a solution, albeit not a viable one. The original question is as follows:
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:
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)
3) Check the port numbers being used
4) 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!
Update: The customer is requesting another remote and conference tomorrow to try to resolve this problem. I have a few things now to try, and they have senior staff on hand that probably will know exactly how to reach the server/AG. What I'm still in the dark about is if I will need to add another connection parameter for it to work. If that's the case, it will require an update to the code that constructs/saves/recalls the connection details. Thanks again for any hints!
"Go forth into the source" - Neal Morse
modified 6-Jan-18 15:15pm.
General News Suggestion Question Bug Answer Joke Praise Rant Admin
Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.