OLEDB connection to a SQL server database hosted in an availability group (repost)
-
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. :) https://www.codeproject.com/Questions/1222282/OLEDB-connection-to-a-SQL-server-database-hosted-i[^] 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:
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
-
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. :) https://www.codeproject.com/Questions/1222282/OLEDB-connection-to-a-SQL-server-database-hosted-i[^] 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:
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
<oldnews> Oh, right,
MultiSubnetFailovercannot be set via OleDb -- OleDb considered harmful. Without that setting our OleDb connections failed about half the time. We started hearing the "Microsoft is deprecating OleDb" rumor a few years back (2012?) but we hadn't had any issues until this past summer when one of our partners changed their configuration. Our partner said we had to use the SQL Client via ADO.net and I said I bet we don't -- so we use the Native Client via ODBC via ADO.net . ODBC can setMultiSubnetFailoverSQL Server Native Client Support for High Availability, Disaster Recovery | Microsoft Docs[^] Microsoft is Aligning with ODBC for Native Relational Data Access – Microsoft SQLNCli team blog[^] SQL Server Native Client OLE DB Provider Will Not Ship after SQL Server 2012[^] </oldnews> Joyous news! THIS JUST IN! (two months ago) Announcing the new release of OLE DB Driver for SQL Server – Microsoft SQLNCli team blog[^] -
<oldnews> Oh, right,
MultiSubnetFailovercannot be set via OleDb -- OleDb considered harmful. Without that setting our OleDb connections failed about half the time. We started hearing the "Microsoft is deprecating OleDb" rumor a few years back (2012?) but we hadn't had any issues until this past summer when one of our partners changed their configuration. Our partner said we had to use the SQL Client via ADO.net and I said I bet we don't -- so we use the Native Client via ODBC via ADO.net . ODBC can setMultiSubnetFailoverSQL Server Native Client Support for High Availability, Disaster Recovery | Microsoft Docs[^] Microsoft is Aligning with ODBC for Native Relational Data Access – Microsoft SQLNCli team blog[^] SQL Server Native Client OLE DB Provider Will Not Ship after SQL Server 2012[^] </oldnews> Joyous news! THIS JUST IN! (two months ago) Announcing the new release of OLE DB Driver for SQL Server – Microsoft SQLNCli team blog[^]First, thanks PIEBALDconsult for the help and information! :) Second, I've just gotten the connectivity issue worked out...partially. Our program connects and works perfectly on the client's application server. Previously, we were trying to run the program from one of the user's desktop. (actually from a shared drive on the same application server) We had made sure that there was no firewall on the local machine so I still don't understand how/where the connection from the client to the database server was being blocked. :confused: Third, it really sucks when the quality of your software gets called into question due to a problem on the client's end. :mad: X| Of course the problem had to be that old sqloledb driver! Why are you still using that ancient technology? So I spent many hours modifying the available sql connection options to include the sql native driver/provider (both with and without ODBC) only to find that the changes were not really necessary. I have a feeling this is going to be a difficult client... :sigh:
"Go forth into the source" - Neal Morse
-
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. :) https://www.codeproject.com/Questions/1222282/OLEDB-connection-to-a-SQL-server-database-hosted-i[^] 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:
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
I had a similar problem a while ago. Sometimes it would connect and other times it would time out depending on which server was the current primary. I believe there is a DNS setting you can use to get around this but that was not an option in our case. The only solution I came up with was to set a long timeout (300 seconds) in the connection string. So now sometimes it connects right away and the rest of the time it will take about a minute. Not ideal but we've been able to live with it. Hope this helps.
-
I had a similar problem a while ago. Sometimes it would connect and other times it would time out depending on which server was the current primary. I believe there is a DNS setting you can use to get around this but that was not an option in our case. The only solution I came up with was to set a long timeout (300 seconds) in the connection string. So now sometimes it connects right away and the rest of the time it will take about a minute. Not ideal but we've been able to live with it. Hope this helps.
Thanks Joe! :thumbsup: You may be on to something here...extending the timeout. It was the one thing I didn't try. Still, it's strange that their app server is able to connect instantly...maybe a hosts file entry???:confused: This has been confirmed on two end user workstations...reports back that the server can't be found or having trouble locating server, depending on which provider is in play. I will give this a shot tomorrow...also checking into remote app as a possible solution for them. I really hope I don't need to extend the timeout as I and my customer consider more than a few seconds to connect to an intranet resource unacceptable. X|
"Go forth into the source" - Neal Morse
-
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. :) https://www.codeproject.com/Questions/1222282/OLEDB-connection-to-a-SQL-server-database-hosted-i[^] 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:
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
Interesting, I've setup a couple of Availability groups for applications but never had any issues (well once I got the devs to stop trying to use the IPs). I wonder if the issues you faced are related to [Create or Configure an Availability Group Listener (SQL Server) | Microsoft Docs](https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/create-or-configure-an-availability-group-listener-sql-server) [Snippet] Issue: If your Availability Group or Failover Cluster Instance has a listener name (known as the network name or Client Access Point in the WSFC Cluster Manager) depending on multiple IP addresses from different subnets, and you are using either ADO.NET with .NET Framework 3.5SP1 or SQL Native Client 11.0 OLEDB, potentially 50% of your client-connection requests to the availability group listener will hit a connection timeout. Workarounds: We recommend that you do one of the following tasks. If do not have the permission to manipulate cluster resources, change your connection timeout to 30 seconds (this value results in a 20-second TCP timeout period plus a 10-second buffer). Pros: If a cross-subnet failover occurs, client recovery time is short. Cons: Half of the client connections will take more than 20 seconds If you have the permission to manipulate cluster resources, the more recommended approach is to set the network name of your availability group listener to RegisterAllProvidersIP=0. For more information, see "RegisterAllProvidersIP Setting” later in this section. Pros: You do not need to increase your client-connection timeout value. Cons: If a cross-subnet failover occurs, the client recovery time could be 15 minutes or longer, depending on your HostRecordTTL setting and the setting of your cross-site DNS/AD replication schedule.[/Snippet] In all likely hood the issue was the RegisterAllProvidersIP. If the HA Group is running in a multi subnet cluster. Then for every Node in the HA group its IP is published to DNS, So if there are 5 nodes that it can live on, there are five different IPs listed in DNS. Traversing them all can take some time to find the right one currently active. Easy fix, set RegisterAllProvidersIP to 0.
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
-
I had a similar problem a while ago. Sometimes it would connect and other times it would time out depending on which server was the current primary. I believe there is a DNS setting you can use to get around this but that was not an option in our case. The only solution I came up with was to set a long timeout (300 seconds) in the connection string. So now sometimes it connects right away and the rest of the time it will take about a minute. Not ideal but we've been able to live with it. Hope this helps.
Have your cluster manager review [Create or Configure an Availability Group Listener (SQL Server) | Microsoft Docs](https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/create-or-configure-an-availability-group-listener-sql-server) In a powershell admin window, run the following command to get a listing of RegisterAllProvidersIP Get-ClusterResource "*Network*" | Get-ClusterParameter | Where-Object {$_.name -eq "RegisterAllProvidersIP" -and $_.Value -ne "0"} If that setting is not 0 then all IPs associated with the cluster resources are published to DNS.
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.