Click here to Skip to main content
14,736,050 members
Home » Discussions » Database
   

Database

 
AnswerRe: Entity Framework 6 - Set Connection String at Runtime Pin
Richard Deeming12-Jan-18 9:23
mveRichard Deeming12-Jan-18 9:23 
QuestionProblem with Access Pin
JackMisani10-Jan-18 8:51
MemberJackMisani10-Jan-18 8:51 
AnswerRe: Problem with Access Pin
Tim Carmichael12-Jan-18 4:50
MemberTim Carmichael12-Jan-18 4:50 
GeneralRe: Problem with Access Pin
JackMisani12-Jan-18 5:23
MemberJackMisani12-Jan-18 5:23 
GeneralRe: Problem with Access Pin
Tim Carmichael12-Jan-18 6:13
MemberTim Carmichael12-Jan-18 6:13 
GeneralRe: Problem with Access Pin
JackMisani16-Jan-18 23:07
MemberJackMisani16-Jan-18 23:07 
GeneralRe: Problem with Access Pin
Tim Carmichael17-Jan-18 3:24
MemberTim Carmichael17-Jan-18 3:24 
QuestionOLEDB connection to a SQL server database hosted in an availability group (repost) Pin
kmoorevs2-Jan-18 12:38
Memberkmoorevs2-Jan-18 12:38 
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. Smile | :)

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 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! Smile | :)
"Go forth into the source" - Neal Morse


modified 6-Jan-18 15:15pm.

AnswerRe: OLEDB connection to a SQL server database hosted in an availability group (repost) Pin
PIEBALDconsult2-Jan-18 15:12
professionalPIEBALDconsult2-Jan-18 15:12 
GeneralRe: OLEDB connection to a SQL server database hosted in an availability group (repost) Pin
kmoorevs4-Jan-18 9:33
Memberkmoorevs4-Jan-18 9:33 
AnswerRe: OLEDB connection to a SQL server database hosted in an availability group (repost) Pin
Joe DiNatale4-Jan-18 12:10
MemberJoe DiNatale4-Jan-18 12:10 
GeneralRe: OLEDB connection to a SQL server database hosted in an availability group (repost) Pin
kmoorevs4-Jan-18 12:33
Memberkmoorevs4-Jan-18 12:33 
GeneralRe: OLEDB connection to a SQL server database hosted in an availability group (repost) Pin
S Douglas7-Mar-18 12:35
professionalS Douglas7-Mar-18 12:35 
AnswerRe: OLEDB connection to a SQL server database hosted in an availability group (repost) Pin
S Douglas7-Mar-18 12:31
professionalS Douglas7-Mar-18 12:31 
QuestionMore than interesting... Pin
Kornfeld Eliyahu Peter31-Dec-17 22:05
professionalKornfeld Eliyahu Peter31-Dec-17 22:05 
AnswerRe: More than interesting... Pin
Jörgen Andersson1-Jan-18 4:44
professionalJörgen Andersson1-Jan-18 4:44 
GeneralRe: More than interesting... Pin
Kornfeld Eliyahu Peter1-Jan-18 5:31
professionalKornfeld Eliyahu Peter1-Jan-18 5:31 
GeneralRe: More than interesting... Pin
Jörgen Andersson2-Jan-18 6:28
professionalJörgen Andersson2-Jan-18 6:28 
SuggestionRe: More than interesting... Pin
Richard Deeming8-Jan-18 8:22
mveRichard Deeming8-Jan-18 8:22 
GeneralRe: More than interesting... Pin
Jörgen Andersson8-Jan-18 10:03
professionalJörgen Andersson8-Jan-18 10:03 
GeneralRe: More than interesting... Pin
Richard Deeming9-Jan-18 2:26
mveRichard Deeming9-Jan-18 2:26 
GeneralRe: More than interesting... Pin
Jörgen Andersson9-Jan-18 9:39
professionalJörgen Andersson9-Jan-18 9:39 
AnswerRe: More than interesting... Pin
jschell1-Jan-18 8:04
Memberjschell1-Jan-18 8:04 
GeneralRe: More than interesting... Pin
Kornfeld Eliyahu Peter1-Jan-18 9:23
professionalKornfeld Eliyahu Peter1-Jan-18 9:23 
GeneralRe: More than interesting... Pin
ZurdoDev8-Jan-18 9:05
professionalZurdoDev8-Jan-18 9:05 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.