0

I'm having some trouble setting up a dev instance of a C# based website using SQL Server. I'm used to doing this all with MySQL, so this is all a bit alien for me. Having read through MS troubleshooting, my setting appear to be OK, but this is still not working, so clearly I'm falling short somewhere.

I have set the web.config file to point to the new database using the following configuration;

<connectionStrings>
    <add name="External" 
         connectionString="Data Source=91.208.99.2,33114; Initial Catalog=sqldatabase;User ID=sqluser;Password=1234567890" 
         providerName="System.Data.SqlClient"/>
</connectionStrings>

However, the connection still fails with the following error. Any ideas what I could be missing?

[Win32Exception (0x80004005): The system cannot find the file specified]

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

Here's the rest of the stack trace;

System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +6569310
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +717
   System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover) +6595752
   System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover) +219
   System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) +6598063
   System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) +6598643
   System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData) +942
   System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) +1162
   System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) +72
   System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) +6601897
   System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) +103
   System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) +2102
   System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) +116
   System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) +1079
   System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) +6606391
   System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry) +233
   System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) +278
   System.Data.SqlClient.SqlConnection.Open() +239
   SubSonic.SqlDataProvider.CreateConnection(String newConnectionString) +48
   SubSonic.AutomaticConnectionScope..ctor(DataProvider provider) +58
   SubSonic.SqlDataProvider.GetDataSet(QueryCommand qry) +383
   SubSonic.StoredProcedure.GetDataSet() +64
   _usercontrols_layout_header.Page_Load(Object sender, EventArgs e) in \\sambad\sites\dev.aciemgroup.co.uk\public_html\_usercontrols\layout\header.ascx.cs:57
   System.Web.UI.Control.LoadRecursive() +71
   System.Web.UI.Control.LoadRecursive() +190
   System.Web.UI.Control.LoadRecursive() +190
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3178
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1235285
  • 87
  • 2
  • 17
  • Is the server engine actually running on the server? The problem can be on the server itself. Can you run the 'SQL Management Studio' on the server and connect to the Database using those credentials that you have in your connection string? – Sparrow Oct 05 '16 at 01:53
  • Search this site for *sql-server A network-related or instance-specific error occurred while establishing a connection*. There are literally dozens (if not more) existing questions (and answers) that can help resolve this issue. – Ken White Oct 05 '16 at 01:57
  • Yes, I am able to connect to the server using SQL Management Studio. When I set this up, the host did have to add my local IP to allow remote access to the SQL server, perhaps the web server IP is not allowed access? They did set up the entire environment though, so I hope so - still waiting to hear back on that ticket. – user1235285 Oct 05 '16 at 01:57
  • are you using sqlexpress? you may not need the port modifier 331114 or you need to specify an instance name. Look at some connection string examples. – Sql Surfer Oct 05 '16 at 02:35
  • How if you specify the network protocol in connection strings? `Network Library=DBMSSOCN`. More [details](https://www.connectionstrings.com/define-sql-server-network-protocol/). See also [related question](http://stackoverflow.com/questions/20463119/the-system-cannot-find-the-file-specified) – putu Oct 05 '16 at 02:45
  • I've tried without the port modifier, same error unfortunately. SQL Management Studio does require the port, so I would assume same for the webserver. – user1235285 Oct 05 '16 at 02:52
  • Unfortunately adding the `Network Library=DBMSSOCN` or `Network=DBMSSOCN` results in the following error; `HTTP Error 500.19 - Internal Server Error The requested page cannot be accessed because the related configuration data for the page is invalid.` – user1235285 Oct 05 '16 at 02:56
  • Did you add it inside `connectionString` attribute (do not create new attribute in `add` element)? What is the `HResult code`? – putu Oct 05 '16 at 03:08

2 Answers2

0

The error itself already said incorrect server configuration on web.config:

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)

Since it says "Server not found", the SQL Server connection string probably become main issue.

If you have a SQL Server instance/server name on remote server (e.g. SERVERNAME), append it after IP address or machine name, just before server port to connect with:

<connectionStrings>
<add name="External" connectionString="Data Source=91.208.99.2\SERVERNAME,33114; Initial Catalog=sqldatabase;User ID=sqluser;Password=1234567890" providerName="System.Data.SqlClient"/>
</connectionStrings>

In case of SQL Server Express instance, the connection string should be like this one:

<connectionStrings>
<add name="External" connectionString="Data Source=91.208.99.2\SQLEXPRESS,33114; Initial Catalog=sqldatabase;User ID=sqluser;Password=1234567890" providerName="System.Data.SqlClient"/>
</connectionStrings>

About HTTP 500 error when adding Network Library=DBMSSOCN into connection string on web.config, see MS KB reference below for further details depending on HResult code. If the HResult code returns 0x8007000d, you need to modify the target server inside connection string properly as explained above.

The connection string after adding network library should be like:

<connectionStrings>
<add name="External" connectionString="Data Source=91.208.99.2\SERVERNAME,33114;Network Library=DBMSSOCN;Initial Catalog=sqldatabase;User ID=sqluser;Password=1234567890" providerName="System.Data.SqlClient"/>
</connectionStrings>

PS: Check and modify additional security/machine/user permissions for remote server if any, ensure the DB connection working afterwards.

References:

MS KB 942055 - https://support.microsoft.com/en-us/kb/942055

The right connection string for Remote SQL server for C#

ConnectionString for remote SQL Server

Community
  • 1
  • 1
Tetsuya Yamamoto
  • 24,297
  • 8
  • 39
  • 61
0

Thank you everyone for the input, turns out the hosting company (despite claiming everything was fine), were at fault. Very sorry to have wasted anyones time, it's been more than slightly frustrating getting them to admit fault too! In the end, we setup a separate MS SQL server and connected just fine.

user1235285
  • 87
  • 2
  • 17