Fixing MSSQL Error 40, Named Pipes Provider - Could not open a connection to SQL Server

After installing Microsoft SQL Server 2012 Express installation some users may be greeted upon connection with a different welcome Message, similar to the one that follows:
Netzwerkbezogener oder instanzspezifischer Fehler beim Herstellen einer Verbindung mit SQL Server. Der Server wurde nicht gefunden, oder auf ihn kann nicht zugegriffen werden. Überprüfen Sie, ob der Instanzname richtig ist und ob SQL Server Remoteverbindungen zulässt. (provider: Named Pipes-Provider, error: 40 - Verbindung mit SQL Server konnte nicht geöffnet werden)

[SqlException (0x80131904): An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)]

That is usually followed with a neat Stacktrace:

   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735091
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +820
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Data.Common.DbDataAapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
System.Web.UI.WebControls.Repeater.GetData() +50
System.Web.UI.WebControls.Repeater.CreateControlHierarchy(Boolean useDataSource) +232
...it gets boring after here
System.Data.SqlClient.SqlException: Netzwerkbezogener oder instanzspezifischer Fehler beim Herstellen einer Verbindung mit SQL Server. Der Server wurde nicht gefunden, oder auf ihn kann nicht zugegriffen werden. Überprüfen Sie, ob der Instanzname richtig ist und ob SQL Server Remoteverbindungen zulässt. (provider: Named Pipes-Provider, error: 40 - Verbindung mit SQL Server konnte nicht geöffnet werden)
bei System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
bei System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
bei System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
bei System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
bei System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
bei System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
bei System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
bei System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
bei System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
bei System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
bei System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
bei System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
bei System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
bei System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
bei System.Data.SqlClient.SqlConnection.Open()
bei MiniSqlQuery.Core.DbModel.GenericSchemaService.GetDbObjectModel(String connection)
bei MiniSqlQuery.PlugIns.DatabaseInspector.DatabaseInspectorForm.ExecLoadDatabaseDetails()
bei MiniSqlQuery.PlugIns.DatabaseInspector.DatabaseInspectorForm.Settings_DatabaseConnectionReset(Object sender, EventArgs e)
bei MiniSqlQuery.ApplicationSettings.ResetConnection()
bei MiniSqlQuery.MainForm.toolStripComboBoxConnection_SelectedIndexChanged(Object sender, EventArgs e)
bei System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
bei System.Windows.Forms.ComboBox.OnSelectedIndexChanged(EventArgs e)
bei System.Windows.Forms.ComboBox.WndProc(Message& m)
bei System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
bei System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)



Fix:

As already mentioned in this MSDN blog, change the Location Source of your Connection, as follows. But first, download MiniSqlQuery for having a small, managed SQL Client that works with MSSQL.
Open the application and in its Menu click: Edit the Connections File. (Sure, you could do this visually with the user-interface. But forfeit the joy of XML? Never!)

Copy and paste a new DbConnectionDefinition Tag into the Definitions Tag container.

   
<DbConnectionDefinition>
<Comment />
<ConnectionString>Data Source=.\SqlExpress;Initial Catalog=master;Integrated Security=True</ConnectionString>
<Name>Default - MSSQL Master@localhost</Name>
<ProviderName>System.Data.SqlClient</ProviderName>
</DbConnectionDefinition>


Start your SQL Server Configuration Manager (which is a Microsoft Management Console, opened as:  C:\Windows\SysWOW64\mmc.exe /32 C:\Windows\SysWOW64\SQLServerManager11.msc)

Make sure your Named Pipes are Enabled (Right Click and select Enable. Enable TCP as well.) Named-pipes, basically allow for inter-process communication of data without involving a sophisticated network stack or more importantly be confined to the network stack and its performance penalties. Similar to a listening Port on UDP or TCP/IP a program may also set up a pipe named for instance \\.\pipe\sql\query . Contrarily cannonical pipes are unnamed and exist only for the duration of a running process. So much to the theory, following is a more practical side:


Check the Status and Number of the Client Protocols as well (See Image above).
Make sure your Server is actually running, set Log on as to Local System, and click Ok, as follows:

The Server should restart. If you still cannot connect, temporarily disable your Firewall and retry again: Press your Windows Key, enter Firewall, hit Enter. Click on Properties as shown below, and Select Turn Off



Start the connection to your MS SQL Database:


If something still doesn't work let me know in the comments.
LihatTutupKomentar