Issue connecting to SQL Server with new ASP.NET Core application

0

I've deployed a new .NET Core app to a production web server, and I'm having trouble connecting to a SQL Server database on another server. The web server currently has applications running on ASP.NET web forms and connecting to the database successfully.

The application is developed in ASP.NET Core 2.1. The database server is running SQL Server 2012 Express. The database is in a named instance called SQLEXPRESS which is using dynamic ports. I prefer not to change that since it's a production environment.

Here is the connection string that I'm using in my new app (potentially sensitive info omitted)

  "ConnectionStrings": {
    "NLogDb": "Server=<server>\\SQLEXPRESS;Database=<database>;User Id=<user>;Password=<password>;",
    "DefaultConn": "Server=<server>\\SQLEXPRESS;Database=<database>;User Id=<user>;Password=<password>;"
  },

I know that the servers are able to talk to one another because I currently have running applications talking back and forth. I also used portQry to test from the web server and got the following:

c:\PortQryV2>portqry.exe -n intinsap01 -p UDP -e 1434

Querying target system called:

 <servername>

Attempting to resolve name to IP address...


Name resolved to 10.190.190.10

querying...

UDP port 1434 (ms-sql-m service): LISTENING or FILTERED

Sending SQL Server query to UDP port 1434...

Server's response:

ServerName <serverName>
InstanceName SQLEXPRESS
IsClustered No
Version 11.0.6020.0
tcp 65069
np \\<serverName>\pipe\MSSQL$SQLEXPRESS\sql\query

ServerName <serverName>
InstanceName RAPIDLOG
IsClustered No
Version 11.0.6020.0
tcp 1433
np \\<serverName>\pipe\MSSQL$RAPIDLOG\sql\query

Z   ΒΆ

==== End of SQL Server query response ====

UDP port 1434 is LISTENING

The other application is using the following for a connection string:

<add name="<name>" 
     connectionString="Data Source=<server>\SQLEXPRESS;Initial Catalog=<database;Persist Security Info=True;User ID=<username>;Password=<password>" 
     providerName="System.Data.SqlClient" />

When the application tries to query the database I get the following error:

System.Data.SqlClient.SqlException (0x80131904): 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)

I suspect an issue with my connection string, but I'm not sure where to go with it.

Any help is appreciated.

sql
asp.net
sql-server
asp.net-core-mvc
asked on Stack Overflow Nov 11, 2019 by NickBonnell • edited Nov 11, 2019 by marc_s

1 Answer

1

I figured out what my problem was. I kept trying different variations in my connection string in my appsettings.json file. Eventually I noticed that there was an additional file named appsettings.production.json. The connection string in that was improperly formatted (not sure what happened there). The connection string that I originally posted was the right one.

answered on Stack Overflow Nov 11, 2019 by NickBonnell

User contributions licensed under CC BY-SA 3.0