Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Part III – Connection Fail when SqlClient connects to Sql Server 2005

When you connect to SQL Server 2005 either using "SQL Server Managment Studio" or any application compiled with .NET Framework 2.0, you are using SqlClient provider(Access data from within a CLR database object by using the .NET Framework Data Provider for SQL Server.)


Error Message 1:

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)

Troubleshoot:
1) Make sure your sql service is running, use either "net start" or "sc query <InstanceName>" or run services.msc, check status of the server; If server start fail, go to ERRORLOG to see what happened there, fix the problem and restart server.

2) You might explicitly use "np:"prefix which ask for connect through named pipe. However, client can not connect to server through the pipe name that specified.Double check the server is started and listening on named pipe if you enabled Named Pipe. One way is that see the ERRORLOG of the server, search follow keywords:

Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ] or [\\.\pipe\mssql$<InstanceName>\sql\query]

Notice that "sql\query" is the default pipe name, so you need to know server is listening on which pipe name. eg: if you specify server pipe name is "sql\query1", then you would see in the errorlog that server listening on [ \\.\pipe\sql\query1 ], and go to SQL Server Configuration Manager, click client Named Pipe properties, see whether the pipe name is same with the one server listening on.

3) You might specify named pipe protocol in connection string, but did not enable named pipe on the server, check ERRORLOG.

4) You might use FQDN/IPAddress/LoopbackIP to connect to the server when only shared memory was enabled, you can change to <machinename> to resolve this.

5) You might explictly specify "lpc:" prefix in your connection string, but shared memory was not enabled. To resolve this, either remove the prefix as long as named pipe or tcp was enabled or enable shared memory.

Error Message 2:

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. 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: 0 - No process is on the other end of the pipe.)

1) You might explicitly use "np:"prefix which ask for connect through named pipe and specify FQDN/LoopbackIP/IPAddress as server name in the connection string.
2) You might use FQDN/IPAddress/LoopbackIP to connect to the server.

To resolve 1) and 2), you can specify <machinename> instead of FQDN/IPADress/LoopbackIP.

Error Message 3:

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

Troubleshoot:
1) You might explicitly use "lpc:"prefix which ask for connect through shared memory. Either server instance was not started successfully or shared memory was not enabled on the server.To resolve this, you remove "lpc:"prefix in your connection string as long as Server is listening on other protocols or enable shared memory and restart server.

2) You explicitly use "lpc:"prefix and connect to a local named instance through form [./(local)/localhost/<machinename>]\<InstanceName>, but Sqlbrowser service was not started. To resolve this, you need to enable sqlbrowser.

3) You might connect through "lpc:" which not includes any server name, to fix this, add correct server name in your connection string.

Error Message 4:

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: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)

Troubleshoot:
1) You might explicitly use "tcp:"prefix which ask for connect through TCP/IP, however either server was not listening on TCP/IP, to resolve this, either remove "tcp:"prefix in your connection string or enable tcp protocol.

2) You might not connect through the exact port that server is listening on, to verify this, go to SQL Configuration Managner
choose "Protocols for <InstanceName>" and click properties for TCP/IP, see which port is configured for server listening and then try connect through the port, like in connection string "tcp:<machinename>,<portnumber>".

3) The instance that you want to connect through TCP was not started, check server ERRORLOG and restart server.

Error Message 5:

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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

1) You might have named pipe or tcp enabled and connect to a named instance, but SQL Browser service was not started or enabled. To enable browser, First, Use net start or go to sql configuration manager(SSCM), check whether sqlbrowser service is running, if not, start it; Secondly,You still need to make sure SqlBrowser is active. Go to SSCM, click properties of sqlbrowser service -> Advanced-> Active “Yes” or “No”, if sqlbrowser is running but is not active, the service would not serve you correct pipe name and Tcp port info on which your connection depends.

Error Message 6:

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: SQL Network Interfaces, error: 41 - Cannot open a Shared Memory connection to a remote SQL server)

1) You might explicitly use "lpc:"prefix and connect to a named instance but specify FQDN/LoopbackIP/IP as <servername>, eg, your connection string looks like "lpc:<FQDN>\<InstnaceName>" or "lpc:127.0.0.1\<InstanceName>"..

2) You might explicitly use "lpc:"prefix and give the wrong server name in your connectionstring, eg: "lpc:xx" <xx> is not the hostname of your machine.

Error Message 7:

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: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol)

1) You might explicitly use "np:" prefix in your connection string and try to connec to a named instance, but named pipe was not enabled on the remote server, to resolve this, enable the remote named pipe and restart instance or remove "np:"prefix if remote server is listening on TCP/IP.

2) You might explicitly use "tcp:" prefix in your connection string and try to connec to a named instance, but TCP/IP was not enabled on the remote server, to resolve this, enable the remote TCP/IP and restart instance or remove "tcp:"prefix if remote server is listening on Named Pipe.

Error Message 8:

An error has occurred while establishing a connection to the server. When connectiong 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:TCP Provider, errror:0-A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)

1) This is because connection blocked by Windows firewall. To resolve this, take follow steps:

a. Enable SqlBrowser, see the info in Message 4. Plus, add sqlbrowser.exe into Firewall exception list: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SharedAccess\Parameters\FirewallPolicy\DomainProfile\AuthorizedApplications\List
b. Add Tcp port to Firewall exception list. (eg, Name-1433:TCP, Value-1433:TCP:*:Enabled:Tcp 1433).
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SharedAccess\Parameters\FirewallPolicy\DomainProfile\GloballyOpenPorts\List.For more detailed operation, see http://support.microsoft.com/default.aspx?scid=kb;en-us;287932

Summary:

1) In any case, SqlClient should be able to connect to SQL Instance through any of the protocols(Shared Memory/Named Pipe/TCP) as long as SQL Instance was started successfully.

2) And if you speculate any protocol in connection string ("lpc:"/"np:"/"tcp:"), the error message would display "<Protocol> Provider, error <Num1> -....<Num2>." <Protocol> stands for "Shared Memory" or "Named Pipes" or "TCP"; If you do not speculate
any protocol, the error message indicates that connection fails when connecting through specific <Protocol>.

3) In the error message format for SqlClient, please notice two different error number. <Num1> stands for internal error thrown out by SQL Protocols, <Num2> is the OS error(eg: 233 - No process is on the other end of pipe). When you see <Num1>=0, that means the connection fails due to OS error not caused by SQL Protocols, under this situation, you can use "net helpmsg" to check specific OS info.

Finally, if you were developing .NET framework application and came across above issues in your client app, the best way is first try SQL Server Management Studio to connect to SQL Server using the exact same connection string in your app, and watch the error message, normally, there is additional error info at the end of error string, eg ( Microsoft SQL Server, Error:87) which gives you clue(net helpmsg 87) that problem inside your connection string.

MING LU

SQL Server Protocols

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

 

Leave a Comment
  • Please add 5 and 5 and type the answer here:
  • Post
  • Matt,

    Thank you for your response. I know we have #3 set up as you mention, and we're looking into #1 and #2. Unfortunately my knowledge of certificates is limited so I don't know how to set or even check on these properties without doing some research first.

    We were able to open mmc and add the certificates snap-in, but we couldn't find any EKU or private key properties on the cert itself.

    Thanks again.

    -Matt F.
  • Open MMC snapin and locate your certificate and double click on it to open the "Certificate" dialog box.

    On the General tab you should see something like:

    Issued to: xyz
    Issued by: xyz
    Valid from <start date> to <end date>

    Then below the Valid from... you should see:

    "You have a private key that corresponds to this certificate"

    If you don't see this, then the cert does not contain the private key.

    For Enhanced Key Usage go to the Details tab and scroll down until you see "Enhanced Key Usage" section.  If you select this you should see:

    Server Authentication (1.3.6.1.5.5.7.3.1)

    You may see more things in here, this is fine, but Server Authentication is required.

    I know I ran into difficulty getting #2 to work with some methods of obtaining the certificate.  Let me know how you are obtaining the certificate.
  • Also pay attention to
    (1) whether the issue to: xyz match the FQDN of your machine name,

    (2) the expriation date of your certificate.

    These two checks are added for sql servr 2005 among others.
  • Speaking with my sys admins, they do see "You have a private key that corresponds to this certificate" as you mentioned and the cert does have server authentication checked in the EKU section.

    Interestingly, when the cert is imported onto my machine I do not see "You have a private key that corresponds to this certificate" listed on the same cert. Also, I'm importing a cert with a p7b file extension and a FQDN, but it installs not with the FQDN. I know I'm missing s step here.

    They are creating the cert using CA. We are currently trying different combinations of where the cert lives to try to resolve.

    -Matt
  • One other note.... Just tried to install a .cer with the correct FQDN and it tells me it installed correctly, but it does not appear in the cert list on ie.
  • This sounds like when your admins are generating the certs, they are not enabling the following options when generating the cert.  These options are under the "Key Generation Options" section ->

    #1. You must check "Use local machine store".
    #2. You must check "Mark keys as exportable".

    Sorry this is so confusing, the cert folks seem to never make life easy for us mere mortals. (G)
  • Matt,
     You can try to post more info on
    http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1
     the SQL Server Data Access Forum.
  • Hi,Matt

      Beside the suggestions from Matt Neerincx, I would add following:

    1) Your sql service should run under admin account, otherwise it has no permissing to the private key and thus the cert would not show up in the drop down list of SSCM, visit KB article for more detail:
    http://support.microsoft.com/?kbid=900495.


    2) SQL Server 2005 has a new kind cert called self-signed certificate, which means when server can not find a good cert to load in the cert store, it will load such a self-signed cert. In another word, you always can make encryption connection if you force server encryption, but, if you force client encryption, you will get error"certificate was not trusted" unless you specify trusting server cert through configuration,search more information in Books online.

    3)Before upgrad to 2005, if you set any cert in the registry key, you should be careful whether it already expired or match condition of valid cert for 2005, otherwise, after installing 2005, you might not be able to see them in the cert list and server would load a self-signed cert.

    Hope this helps!
    Ming.

  • Ming,

    The link you provided under #1 is clearly incorrect. Goes to a celebrities page of some sort.

    Anyway, as soon as we upgraded, all of our ASP.NET apps that were using Encrypt=True in their connection strings simply stopped working. Nothing else on either box, the server or the client, was changed. We changed the connection string to Encrypt=False and the apps work like they should, aside from not being encrypted. So my question is, what could cause this?

    We have tried just about every MS KB article and recommendation we can find. We created new certificates using a CA server. We made sure they have had private keys and allowed server authentication. Force protocol encryption is set to YES on the server.

    W still have never been able to get the certificate we create to appear in the list. We are logging on the server as the domain admin and starting the service under the same account.

    Any ideas why we can't see the cert in the list or why the encryption failed after a SQL upgrade?
  • Hi, Matt

       First, Sorry about the wrong link, I meant to point to our KB article, it is http://support.microsoft.com/?kbid=900495.

       Secondly, the encryption failure you saw is due to the reason that I desicribed in #2 of my last answer. Your server might load a self-signed cert(To verify this, you can go to server errorlog and see there is keyword "self-signed certificate"), and you forced client encryption through setting "Encrypt=True". If you already forced server encryption, the connection should be encrypted. And if you require forcing client encryption, you need to have server loading a certificate issued by a trusted CA.

       Thirdly, please run "certutil.exe -v -store my", the tool would verify whether the certs you installed are valid, and send us info, that would help us to identify why cert not appear in the list; and try change sql service running under LocalSystem account, see whether any cert appear in the list.

       Finally, we have questions about your scenario, whether you installed a cert in SQL 2000 before you upgrading and specifying server using such a cert? what is the reason you trying to install a new cert?

    Let us know how things going.

  • Thank you for your quick responses Ming, I really appreciate it!

    Looking at the errorlog, I see the following:
    A self-generated certificate was successfully loaded for encryption.

    We need to force client encryption. How do we force the server to load a specific certificate for sql 2005? We have valid certificate installed.

    We ran certutil.exe -v -store my and got the output. Do you want that displayed as well? I'm concerned that might pose a security risk, only because I don't know the details.

    In terms of our scenario, we had a certificate installed for SQL 2K but as soon as we upgraded and the encrypted connections didn't work, we thought it was a certificate issue so we recreated hoping that would solve the problem.
  • Here is the error we recieve from asp.net 2.0 when Encrypt=True...

    "System.Data.SqlClient.SqlException: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)
      at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
      at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
      at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
      at System.Data.SqlClient.SqlConnection.Open()
      at System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState)
      at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
      at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
      at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
      at Security.GetSystemData() in C:\Inetpub\screen\App_Code\Security.vb:line 109
      at Security.SecurityCheck() in C:\Inetpub\screen\App_Code\Security.vb:line 94"
  • Check Server ERRORLOG and see if server load a self-signed cert? If you need force client encryption, you really need install a good certificate that valid for SQL Server 2005, otherwise, you can force server encryption to make encrypt connection if you are fine with the self-signed cert.

    Thanks!
    Ming.
  • Since you are using ASP.NET 2.0, if you force client encryption and server load a self-signed cert, you can add one more connection string property "TrustServerCertificate=True" to make connection through. See detail in  http://msdn2.microsoft.com/en-US/library/system.data.sqlclient.sqlconnection.connectionstring(VS.80).aspx

    Thanks!
    Ming.
  • One more thing might be related to the error is that your server is not running under an admin account if you think you installed a valid certificate but server still load a self-signed cert. To fix that,change the service account to localsystem or an admin account, but this might not you want, recommend keep sql service running under low priviliage accoun and use the suggestion that I posted in last comments.

    Thanks!
    Ming.
Page 3 of 15 (217 items) 12345»