Cannot connect to SQL Server Express from NET Core

Question

I'm starting a DEV on NET Core 2.2, I have a local DB in SQL Server Express that I'm trying to connect, well, I wrote the following code.

This razor is pulling from Startup Class the Connection string returning successfully:

@{
        Startup s = new Startup(null);
        string connstring = s.ConnectionStringReturn();
        var dbh = new BDHandlercs(connstring);
    }

// This code will display the connection string on the html page for testing purposes
//    @string.Format("Connstring: {0}", connstring);
// Returning
// Connstring: Server=.\sqlexpress|Database=PV_REPUESTOS|User id=SystemUser|Password=Service1023;

After knowing what it loads, I ran the method like this:

dbh.GetConnection();

public SqlConnection GetConnection()
{
     SqlConnection connection = new SqlConnection(this.ConnectionString);

     if (connection.State != ConnectionState.Open)
         connection.Open();

     return connection;
}

Nevertheless I got the following:

An unhandled exception occurred while processing the request.
Win32Exception: The network path was not found
Unknown location

SqlException: 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, object providerInfo, string newPassword, SecureString newSecurePassword, bool redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, bool applyTransientFaultHandling)

Which doesn't make sense to me, I can connect using SQLCMD, SQL Server Management Studio and everything, I have a WCF service that connect as well to the DB, I have the named pipes and the ports open, I think that is a problem on NET Core, any thoughts?

It's a bit of a longshot but are those actually pipes | in your connectionstring? I wasn't aware pipe was a delimiter for anything but an entity framework resources. Have you tried replacing the pipes with semicolons? — Charleh
Why do you use the | as separator between the connectionstring keys? It should be the semicolon — Steve
Does the WCF service use exactly the same connection string? It certainly doesn't look like one I've seen before. You might want to try this approach to generate the string for you. social.technet.microsoft.com/wiki/contents/articles/… — Nick.McDermaid
Hi Guys, well, I switched to ; from |, I tried that because I was testing, I forgot to revert back, even tho, after the reversal, I have the same behaviour — Jerimy386
After trying to create the conn string using the UDL, I got this: Provider=SQLNCLI11.1;Persist Security Info=False;User ID=UserSystem;Initial Catalog=PV_REPUESTOS;Data Source=.\SQLEXPRESS;Initial File Name="";Server SPN="" Tested the source and it was successful, right now I'm stuck I think. — Jerimy386

Jerimy386 Jerimy386 · Accepted Answer · 2020-01-14T14:31:04

Ok Guys, after failing so much with this, I decided to change my code and the structure

I was working on the index page, so I went to the controller and added the following code:

public class HomeController : Controller
    {
        private readonly IConfiguration configuration;

        public HomeController(IConfiguration config)
        {
            this.configuration = config;
        }

        public IActionResult Index()
        {
            string connectionstring = configuration.GetConnectionString("DefaultConnection");
            SqlConnection sqlconnection = new SqlConnection(connectionstring);
            sqlconnection.Open();


            sqlconnection.Close();
            return View();
        }

Appears to connect without any issue, I hope that this will help somebody else, I was stuck with this for a day.

My question now is how I can recycle this code in other class?? But I think I can figure out.

Thanks for the help.

Cannot connect to SQL Server Express from NET Core

1 Answers