none
Connecting to SQL Server 2012 with .NET

    Question

  • Hi,

    I have installed sql server 2012. My c# .NET 4.0 app is using sql server 2008 R2. Im trying to update it to use SQL Server 2012 but I cannot find any connection string. Please have in mind that Im planning to use the option MultiSubnetFailover. Is there anyone who can send me the connection string?

    Thanks in advance

     

    vendredi 27 avril 2012 12:02

Réponses

  • OK I am sorry,  now I understand what you are trying to achieve

    You are connecting your .net application to a SQL Server that uses AlwaysOnAvailability group and yes,   you are using listener

    in that case connection string would look like below

    Server=tcp:AGListener,1433;Database=AdventureWorks;IntegratedSecurity=SSPI; MultiSubnetFailover=True

    Now, if you are getting an error for MultiSubNetFailover as keyword not supported make sure you are using .net framework update 4.0.2 as mentioned in this MS support article http://support.microsoft.com/kb/2544514

    For more info refer to the following

    SqlClient Support for High Availability, Disaster Recovery

    http://msdn.microsoft.com/en-us/library/hh205662.aspx



    • Modifié Chirag Shah vendredi 27 avril 2012 14:22
    • Marqué comme réponse pantonis21 vendredi 27 avril 2012 18:02
    vendredi 27 avril 2012 14:20

Toutes les réponses

  • When you use clustering it won't matter whether node is in same subnet or different subnet. You need to use SQL Server network name (or virtual name) and as long as you use that connectiong string is no different compared to connecting to single instance SQL server.

    For SQL Server connection string refer to

    http://www.connectionstrings.com/sql-server-2008

    • Marqué comme réponse pantonis21 lundi 30 avril 2012 06:26
    • Non marqué comme réponse pantonis21 lundi 30 avril 2012 06:26
    vendredi 27 avril 2012 13:26
  • Thanks for your reply.

    Maybe my question was not so clear. I dont want a connection string example for 2008. I'm looking for a connection string for MS SQL Server 2012. Is it possible to give me an example?

    Thanks.

    vendredi 27 avril 2012 13:44
  • There is no difference in connection string for .net application connecting to SQL 2008/ 2008 R2 and SQL 2012 as long as you are using SQLClient and it has not changed in since the inception of .net

    using integrated security (windows authentication) here is the example

    Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

    vendredi 27 avril 2012 13:50
  • instead of myServerAddress I want to use a Listener. How do I do that?

    What about specifying MultiSubnetFailover=Yes to the connection string?

    I get an error "Keyword not supported: 'multisubnetfailover'."

    Thanks,

    vendredi 27 avril 2012 14:00
  • OK I am sorry,  now I understand what you are trying to achieve

    You are connecting your .net application to a SQL Server that uses AlwaysOnAvailability group and yes,   you are using listener

    in that case connection string would look like below

    Server=tcp:AGListener,1433;Database=AdventureWorks;IntegratedSecurity=SSPI; MultiSubnetFailover=True

    Now, if you are getting an error for MultiSubNetFailover as keyword not supported make sure you are using .net framework update 4.0.2 as mentioned in this MS support article http://support.microsoft.com/kb/2544514

    For more info refer to the following

    SqlClient Support for High Availability, Disaster Recovery

    http://msdn.microsoft.com/en-us/library/hh205662.aspx



    • Modifié Chirag Shah vendredi 27 avril 2012 14:22
    • Marqué comme réponse pantonis21 vendredi 27 avril 2012 18:02
    vendredi 27 avril 2012 14:20
  • Excellent thanks a lot that did the trick although the alwayson is not 2 seconds as microsoft suggests but it takes about 10-12 seconds. 

    Forgot to tell that a space is needed between 'Integrated and 'Security'. So it should look like this:

    Server=tcp:AGListener,1433;Database=AdventureWorks;Integrated Security=SSPI; MultiSubnetFailover=True

    Thanks again

    vendredi 27 avril 2012 18:02
  • Change Connection to:

    "Provider=SQLOLEDB; Data Source=.;User ID=Username; Password=Password; Initial Catalog=Database Name"

    Have a good time.

    vendredi 6 décembre 2013 15:33