none
Connection string for MVC 5 with SQL Enterprise RRS feed

  • Question

  • As the question is really about MVC 5, there may be a better forum to ask this question but the error is a SQL Error 26. 

    I've tried a host of  connection strings for SQL Server, among them servername,port, servername\instance, localhost,1433, and some of the weird connection strings like (localdb)\Mssqlserver (which seems like it should be a sqlexpress not an enterprise connection), another that includes "AttachDbFileName=<path>\mydatabasename.mdf"  (also weird because the database connected by the create database command wants to live in a different folder) I'm running SQL Server 2017 Enterprise locally.  I am unfamiliar with "localdb" and if you review, you'll see I am attempting to use the normal "localhost" as well.  Firewall is punched through (so 1433 should get through as well as UDP 1434) and the instance has a default port of 1433 (although I suppose it could be changed as the firewall is allowing the sqlservr.exe rather than just a simple port to open).  Also, checked to be sure database service is actually running.  SQL allows Remote Connections (although shouldn't be needed for localhost).

    Result of multiple tries is an "error 26" after a default timeout following the initial connection.   I'm not sure what localdb\v.11.0 is but I suspect that as my enterprise server is v.14.0 perhaps it may be the more correct connection.  Also of note, Visual Studio 2017 enterprise version...   New to MVC 5-  probably need to catch up on Entity Framework as well.  I have no way of knowing if I need the Multiple Active Result Sets - I am still learning the MVC (in general).  I suspect there may be some queries that need MARS, some that don't.

    The mdf file is attached using the Web App_Start.   Perhaps that is a mistake?

    Just need a working connectionstring value for the web.config (I think)

    <connectionStrings>
        <!--  <add name="DefaultConnection" connectionString="Data Source=localhost\MSSQLSERVER; Integrated Security=True;AttachDbFilename=C:\MSSQLSERVER\Data\Vidly.mdf;Initial Catalog=Vidly" providerName="System.Data.SqlClient"/> -->
        <!--<add name="DefaultConnection" connectionString="Data Source=(LocalDB)\MSSQLSERVER;AttachDbFileName=c:\MSSQLSERVER\Data\Vidly.mdf;Initial Catalog=Vidly;Integrated Security=SSPI;" providerName="System.Data.SqlClient;MultipleActiveResultSets=True" />-->
        <!-- <add name="ConnectionStringName"
        providerName="System.Data.SqlClient"
        connectionString="Data Source=(LocalDB)\v11.0;
        AttachDbFileName=|DataDirectory|\DatabaseFileName.mdf;InitialCatalog=DatabaseName;
        Integrated Security=True;MultipleActiveResultSets=True" />-->
        <!--<add name="DefaultString" providerName="System.Data.SqlClient" connectionString="Server=<servername>,1433;Database=Vidly;Integrated Security=SSPI;" />-->
        <!--<add name="DefaultConnection" connectionString="Data Source=localhost,1433; Integrated Security=True;AttachDbFilename=C:\MSSQLSERVER\Data\Vidly.mdf;Initial Catalog=Vidly" providerName="System.Data.SqlClient"/>-->
    
        <add name="DefaultString"
            providerName="System.Data.SqlClient"
            connectionString="Data Source=<servername>;
        Initial Catalog=Vidly;Integrated Security=True;
        MultipleActiveResultSets=True" />
      </connectionStrings>

    https://www.connectionstrings.com/sql-server-2016

    Named Pipes are not enabled on this system.

    ERROR:

    "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"

    An article with information on the set of errors can be found here:  https://blog.sqlauthority.com/2016/06/25/sql-server-fix-error-948-downgrade-path-not-supported/

    R, J

    • Edited by Crakdkorn Sunday, July 14, 2019 7:51 PM
    Sunday, July 14, 2019 3:49 PM

Answers

  • I don't know what MVC5 is, but how would you connect to that database through SQL Server Management Studio? Connection strings are not really any different.
    Skip AttachDbFilename, that is a connection-string option I have never understood the point with.
    localhost\MSSQLSERVER seems wrong. MSSQLSERVER is usually the instance id for the default instance, but you cannot use it an instance name to connect; only localhost should do. (But to confuse, you may need that instance specifier when you use localdb.)

    When it comes to Integrated Security, I always specify it as SSPI, but I guess True works as well.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Crakdkorn Sunday, July 14, 2019 9:01 PM
    Sunday, July 14, 2019 8:11 PM

All replies

  • I don't know what MVC5 is, but how would you connect to that database through SQL Server Management Studio? Connection strings are not really any different.
    Skip AttachDbFilename, that is a connection-string option I have never understood the point with.
    localhost\MSSQLSERVER seems wrong. MSSQLSERVER is usually the instance id for the default instance, but you cannot use it an instance name to connect; only localhost should do. (But to confuse, you may need that instance specifier when you use localdb.)

    When it comes to Integrated Security, I always specify it as SSPI, but I guess True works as well.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Crakdkorn Sunday, July 14, 2019 9:01 PM
    Sunday, July 14, 2019 8:11 PM
  • <embarassed>Not sure why I did not try just localhost</embarassed>

    Thanks Erland, that was the fix.

      <connectionStrings>
        <add name="DefaultConnection"         
             connectionString="Data Source=localhost;\aspnet-Vidly-20160330105730.mdf;Initial Catalog=aspnet-Vidly-20160330105730;Integrated Security=True"
             providerName="System.Data.SqlClient"/>
      </connectionStrings>

    R, J

    Sunday, July 14, 2019 9:01 PM