Web API SQL Connection only works when debugging

Question

I'm trying to make an ASP.NET Web API connect to SQL Server and it's not working. I'm building it from Visual Studio's Web API template, and when I run the code in debug mode it opens a browser to localhost:54723 and it works just as I want it to.

The problem arises when I publish this same exact code to a real website. It no longer opens a connection to the SQL Database, instead giving me this lengthy error (the important part seems to be tagged ExceptionMessage):

<Error>
<Message>An error has occurred.</Message>
<ExceptionMessage>
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)
</ExceptionMessage>
<ExceptionType>System.Data.SqlClient.SqlException</ExceptionType>
<StackTrace>
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover) at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover) at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) at System.Data.SqlClient.SqlConnection.Open() at WebApplication2.Services.HelpdeskHealthHistoryRepository.getAllHelpdeskHealthHistoryInfo() at lambda_method(Closure , Object , Object[] ) at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass10.<GetExecutor>b__9(Object instance, Object[] methodParameters) at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.Execute(Object instance, Object[] arguments) at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary`2 arguments, CancellationToken cancellationToken) --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Controllers.ApiControllerActionInvoker.<InvokeActionAsyncCore>d__0.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__2.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Controllers.AuthenticationFilterResult.<ExecuteAsync>d__0.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__1.MoveNext()
</StackTrace>
<InnerException>
<Message>An error has occurred.</Message>
<ExceptionMessage>The network path was not found</ExceptionMessage>
<ExceptionType>System.ComponentModel.Win32Exception</ExceptionType>
<StackTrace/>
</InnerException>
</Error>

I've searched this website and others and found many potential solutions to the problem, but none seem to work. I've connected to this database before from a Java program in Eclipse so I know I'm allowed to connect to it externally.

I suspect that the issue has something to do with the database not allowing access to foreign computers, as outlined somewhat in this post: Visual studio debug access to external database but how can I solve this problem if I want any computer to be able to access this?

Answer 1

You need to configure SQL Server to accept remote connections.

SSMS

1. Open SQL Management Studio and right-click server name in the left pane and select Properties.
2. Select Connections in the left pane and make sure that checkbox Allow remote connections to this server is selected

SQL Server Configuration Manager

1. Open SQL Server Configuration Manager and click on “SQL Server Services” in the left pane.
2. In the center pane, is a column that lists the Process ID for each running service. Look for the PID in the row for SQL Server. Identify the port that that PID is listening on by typing this into a command prompt: netstat -ano | find /i “PID-Number-Of-SQL-Server”
3. If there is no results from the command executed in step 3 its because TCP/IP protocol is disabled and must be enabled. In SQL Server Configuration Manager click on SQL Server Network Configuration in the left pane and right-click TCP/IP protocol and select option Enable.
4. Restart SQL Server service and identify the process ID assigned to SQL service.
5. In the command prompt execute command: netstat -ano | find /i “6524”
6. In SQL Server Configuration Manager and click on SQL Server Network Configuration in the left pane and right-click TCP/IP protocol and select option Properties. Goto IP Address tab and scroll-down to APAll section. Remove value for TCP Dynamic Ports and enter the port 1433 for TCP Port.
7. Restart SQL Server service, identify new process ID assigned to SQL service and in the command prompt execute command: netstat -ano | find /i “3948”.
8. Open SQL Server Configuration Manager and click on “SQL Server Services” in the left pane, right-click SQL Server Browser service and select Properties.
9. Go to Service tab and for Start Mode option change start type to Automatic. Click Start button to start SQL Browser service. Confirm that SQL Server Browser service is up and running.

Windows Firewall

1. A port exception for TCP Port 1433. In the New Inbound Rule Wizard dialog, use the following information to create a port exception:
   • Select Port
   • Select TCP and specify port 1433
   • Allow the connection
   • Choose all three profiles (Domain, Private & Public)
   • Name the rule “SQL – TCP 1433”
2. A port exception for UDP Port 1434. Click New Rule again and use the following information to create another port exception:
   • Select Port
   • Select UDP and specify port 1434
   • Allow the connection
   • Choose all three profiles (Domain, Private & Public)
   • Name the rule “SQL – UDP 1434
3. A program exception for sqlservr.exe. Click New Rule again and use the following information to create a program exception:
   • Select Program
   • Click Browse to select ‘sqlservr.exe’ at this location: [C:\Program Files\Microsoft SQL Server\MSSQL11.\MSSQL\Binn\sqlservr.exe] where is the name of your SQL instance.
     
   • Allow the connection
   • Choose all three profiles (Domain, Private & Public)
   • Name the rule SQL – sqlservr.exe
4. A program exception for sqlbrowser.exe Click New Rule again and use the following information to create another program exception:
   • Select Program
   • Click Browse to select sqlbrowser.exe at this location: [C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe].
   • Allow the connection
   • Choose all three profiles (Domain, Private & Public)
   • Name the rule SQL – sqlbrowser.exe

Source
http://blog.citrix24.com/configure-sql-express-to-accept-remote-connections/