Click here to Skip to main content
15,391,277 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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!
Posted
Updated 23-Dec-17 4:33am
v2

1 solution

Can you try without the "provider=sqloledb" clause?
Vanilla SQL Server itself doesn't like Provider clauses - read "it fails if you supply one" - but I have no idea about Availability Groups.
   
Comments
kmoorevs 23-Dec-17 10:30am
   
Thanks OG! The provider parameter is required for my application...leaving it out (connecting to a local sql database) fails with 'no default driver specified'. I just tried the SQLNCLI11 (sql native) driver and found that it works locally, but unfortunately I still have no idea if it will solve the problem connecting in an Availability Group. Thanks!
[no name] 6-Jan-18 23:02pm
   
What worked?
kmoorevs 7-Jan-18 16:57pm
   
I had reposted the question under the Database forum and noted there (in an update yesterday) that the problem was likely something on the customer's end. We are now successfully connecting to the availability group database using the sqloledb driver. We are now forcing protocol and connecting on the listener. (tcp: ag-listener) Even this was not working until they switched something on their end...then it miraculously started working for all the end users! According to their guy, switching the provider/driver to sql native 11 was the key, but after it started working, I switched it back to sqloledb and it works...and it means the end users don't have to waste time installing the native driver(s).

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900