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 3 and 6 and type the answer here:
  • Post
  • Hi, Agron

      It seems you installed SQLEXPRESS. Normally, it is installed as a named instance and with TCP and Named Pipe are disabled by default.

    To resolve this:

    1)If you have shared momery disabled, either enable it or enable tcp,named pipe and restart SQL Server.

    2)If 1) is not the case, and you still fail to connect even with TCP and Named Pipe enabled, please check which port or pipe name server is listening on( go to SQL Server Configuration Manager, click properties for *Protocols for MSSQLSERVER*), then specify exact port in your connection string:
    data source=AGRON\SQLEXPRESS,<PortNumber> ..; if that works, that might be sqlbrowser issue. But remember double check whether server is listening on the configured port.

    Here is useful info for SqlExpress

    http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

    Let me know if you have further  questions.

    Ming.
  • Thank you Ming,

    I just uninstalled and reinstalled SQL Server Express, and now everything seems to be working fine.

    Thank you for your help,

      Agron
  • A connection I used for SQL 2000 doesn't work for 2005.  App servers are in DOMAIN1 and SQL servers are in DOMAIN2.  There is no trust between the domains.

    Create DOMAIN1\User1 and DOMAIN2\User1 with identical passwords.

    From App servers (DOMAIN1) I can create a DSN that uses Windows NT Authentication to SQL 2000 (DOMAIN2).  Connection works fine.

    From App servers (DOMAIN1) if I create the same DSN that uses Windows NT Authentication to SQL 2005 (DOMAIN2) - it fails.

    [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user ''. The user is not associated with a trusted SQL Server connection.

    The domains aren't trusted because a firewall separates them.  The app I am using requires NT Authentication -- I can't do SQL Server Authentication to get around this.

    Any suggestions?
  • Hi, Joe

       To identify your problem, we need further information:
       1) Does your SQL 2000 run on the same box as SQL 2005?
       2) What OS are they installed? Was SQL 2000 installed on WIN2K or WIN2K3?
       3) How do you make connection to SQL 2000 and SQL 2005? Could you provide the connection string, which protocol you use to connect different server, TCP or Named Pipe?
       4) Could you attach any info inside the Server Log file? The error you saw was returned by server response, so we believe helpful info should be logged.
       5) Since the domain aren't trusted, the SQL Server 2005 behavior seems right. Could you provide more detail info about your domain credential configuration?

    Thanks!
    Ming.
  • 1) Does your SQL 2000 run on the same box as SQL 2005?  
    No - separate servers.
      2) What OS are they installed? Was SQL 2000 installed on WIN2K or WIN2K3?
    SQL 2000 - Windows 2000
    SQL 2005 - Windows 2003
      3) How do you make connection to SQL 2000 and SQL 2005? Could you provide the connection string, which protocol you use to connect different server, TCP or Named Pipe?
    TCP -- I am using the System DSN (under Admin tools, Data Sources).
      4) Could you attach any info inside the Server Log file? The error you saw was returned by server response, so we believe helpful info should be logged.

    Date 3/13/2006 5:23:47 PM
    Log SQL Server (Current - 3/14/2006 2:00:00 AM)

    Source Logon

    Message
    Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: 10.3.1.73]
    -----------------
    Date 3/13/2006 5:23:47 PM
    Log SQL Server (Current - 3/14/2006 2:00:00 AM)

    Source Logon

    Message
    Error: 18452, Severity: 14, State: 1.
    ---------------------
    Date 3/13/2006 5:23:47 PM
    Log SQL Server (Current - 3/14/2006 2:00:00 AM)

    Source Logon

    Message
    SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 10.3.1.73]

      5) Since the domain aren't trusted, the SQL Server 2005 behavior seems right. Could you provide more detail info about your domain credential configuration?
    Sorry - I don't understand the question.
  • Joe,

     The solution to hop over untrusted domain are. Basically the authentication uses NTLM.

    1) create local account with identical password on both machine. Say, create "testuser" on both machineA and machineB with password "testuserpass".

    2) grant testuser permission to access SS2k5 on machine A.

    3) run your client app as testuser on machine B.

    In your case, eventhough the username are same, but the difference between domains make the access token SID actually different. By using machine local account, NTLM do not check domain credentials.

    Hope this helps.


  • Joe,
     In both cases, ss2k and ss2k5, you can check the login use name using "select suser_name()"

  • Hi, I'm in a bit of a panic.

    I have successfully been running SQL Server 2005 (Integrated Security) on Windows Server 2003 SP1 for the last few months.

    Ever since I installed the latest Critical Updates from Windows Updates this afternoon, all remote connections to SQL Server are being refused. The updates installed were:
    Update for Windows Server 2003 (KB904942)
    Update for Windows Server 2003 (KB912945)
    Windows Malicious Software removal Tools - March 2006 (KB890830).

    I am unable to connect to my remote server using SQL Server Management Studio on my local machine. None of my applications using the same connection string: "Data Source=my.server.com;Initial Catalog=myCatalog;Integrated Security=True" are working - each attempt to connect to the server returns this error message:

    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) (Microsoft SQL Server, Error: 1214)

    If I access the server through an internet application (i.e. one running on the same machine as SQL Server) - I have no problems, in that case the connection string is: "Data Source=(local);Initial Catalog=myCatalog;Integrated Security=True".

    The server was under a sustained attack from some prick this morning trying the usual username = root, admin, sa, sql etc.

    Please let me know if there is any more information I can provide to help in working out what's happened.

    I am absolutely desperate ......

    Regards
    Jeremy Holt
    jholt@nospam.amberwoodtrading.com (please remove the nospam bit).
    Tel: +5585 3273 3444



    The error log is

    2006-03-15 16:52:58.84 Server      Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
    Oct 14 2005 00:33:37
    Copyright (c) 1988-2005 Microsoft Corporation
    Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

    2006-03-15 16:52:58.84 Server      (c) 2005 Microsoft Corporation.
    2006-03-15 16:52:58.84 Server      All rights reserved.
    2006-03-15 16:52:58.84 Server      Server process ID is 236.
    2006-03-15 16:52:58.84 Server      Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
    2006-03-15 16:52:58.84 Server      This instance of SQL Server last reported using a process ID of 3948 at 3/15/2006 4:52:20 PM (local) 3/16/2006 12:52:20 AM (UTC). This is an informational message only; no user action is required.
    2006-03-15 16:52:58.84 Server      Registry startup parameters:
    2006-03-15 16:52:58.84 Server       -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
    2006-03-15 16:52:58.84 Server       -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
    2006-03-15 16:52:58.84 Server       -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
    2006-03-15 16:52:58.85 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
    2006-03-15 16:52:58.85 Server      Detected 1 CPUs. This is an informational message; no user action is required.
    2006-03-15 16:52:58.95 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
    2006-03-15 16:52:59.01 Server      Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
    2006-03-15 16:52:59.04 Server      The Microsoft Distributed Transaction Coordinator (MS DTC) service could not be contacted.  If you would like distributed transaction functionality, please start this service.
    2006-03-15 16:52:59.04 Server      Database Mirroring Transport is disabled in the endpoint configuration.
    2006-03-15 16:52:59.07 spid5s      Starting up database 'master'.
    2006-03-15 16:52:59.22 spid5s      Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
    2006-03-15 16:52:59.50 spid5s      SQL Trace ID 1 was started by login "sa".
    2006-03-15 16:52:59.57 spid5s      Starting up database 'mssqlsystemresource'.
    2006-03-15 16:53:00.33 spid9s      Starting up database 'model'.
    2006-03-15 16:53:00.39 spid5s      Server name is 'STAR'. This is an informational message only. No user action is required.
    2006-03-15 16:53:00.69 Server      A self-generated certificate was successfully loaded for encryption.
    2006-03-15 16:53:00.70 Server      Server is listening on [ 'any' <ipv4> 1433].
    2006-03-15 16:53:00.70 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
    2006-03-15 16:53:00.71 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
    2006-03-15 16:53:00.71 Server      Server is listening on [ 127.0.0.1 <ipv4> 1434].
    2006-03-15 16:53:00.71 Server      Dedicated admin connection support was established for listening locally on port 1434.
    2006-03-15 16:53:00.72 Server      The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
    2006-03-15 16:53:00.72 Server      SQL Server is now ready for client connections. This is an informational message; no user action is required.
    2006-03-15 16:53:00.79 spid12s     Starting up database 'msdb'.
    2006-03-15 16:53:00.79 spid13s     Starting up database 'ReportServer'.
    2006-03-15 16:53:00.79 spid14s     Starting up database 'ReportServerTempDB'.
    2006-03-15 16:53:00.81 spid15s     Starting up database 'AdventureWorksDW'.
    2006-03-15 16:53:03.95 spid9s      Clearing tempdb database.
    2006-03-15 16:53:04.22 spid12s     Starting up database 'AdventureWorks'.
    2006-03-15 16:53:04.31 spid15s     Starting up database 'ATRBE'.
    2006-03-15 16:53:04.40 spid14s     Starting up database 'Florenzano'.
    2006-03-15 16:53:04.45 spid13s     Starting up database 'Iracema'.
    2006-03-15 16:53:08.98 spid14s     Starting up database 'StockControlSQL2001'.
    2006-03-15 16:53:09.21 spid12s     Starting up database 'StockControlSQL2002'.
    2006-03-15 16:53:13.26 spid13s     Starting up database 'aspnetdb'.
    2006-03-15 16:53:16.75 spid9s      Starting up database 'tempdb'.
    2006-03-15 16:53:19.16 spid13s     The Service Broker protocol transport is disabled or not configured.
    2006-03-15 16:53:19.16 spid13s     The Database Mirroring protocol transport is disabled or not configured.
    2006-03-15 16:53:20.07 spid13s     Service Broker manager has started.
    2006-03-15 16:53:20.25 spid52      Using 'xpstar90.dll' version '2005.90.1399' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
    2006-03-15 16:53:20.68 spid5s      Recovery is complete. This is an informational message only. No user action is required.
    2006-03-15 16:53:22.14 spid52      Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
    2006-03-15 16:53:22.36 spid52      Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
    2006-03-15 16:53:22.56 spid52      Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
    2006-03-15 16:53:24.33 spid52      Using 'xpsqlbot.dll' version '2005.90.1399' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.
    2006-03-15 16:53:25.07 spid52      Using 'xplog70.dll' version '2005.90.1399' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
  • Hi Jeremy,

    Sounds like someone perhaps tweaked the settings on Windows Firewall on your SQL Server machine (perhaps in response to someone trying to bust in as sa? Not sure).

    Check Windows Firewall on the machine and make sure that there is a way for external applications to use tcp port 1433 to your SQL Server.

    Take a look at this article for details on setting this up properly:

    841249 How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;841249

    Note that this article applies equally well to Windows 2003 server as far as the Firewall settings go.
  • Yes - you're absolutely correct. Our ISP had put a complete block on incoming packets to 1433 after I reported the attacks (but forgot to mention this to me).
    By the time I'd sent this message to you, I had started smoking again - and I only gave up a week ago!!

    By the way,  what is the recomended way to protect SQL Server from attacks? The database is really an intranet - limited access to a very small group of users - however the users are located in various parts of the world, and for the most part use dynamic ip addresses provided by their local ISP.

    Regards
    Jeremy
  • Thanks for the help.  I broke down and setup the domain trust.  It is working.  I just didn't want to poke those holes through the firewall, but I guess I have to.
  • Hi,

    I am having a problem in regards to "Cannot generate SSPI context". Initially if the firewall was disabled (using windows2k3 and sql server 2005).. I am able to connect to SQL Server without having problem at all. Setting the suggested parameters in the firewall and enabling it, I am now stuck with this problem. I have tried following the troubleshooting procedures set to diagnose this but to no avail. Before trying to do anything else is there any other way to tweak the firewall settings? In addition I noticed if I initially disabled the firewall and connected to SQL server and disconnected the firewall again. Connecting to SQL server will not result with the same error? why is that?
  • Julius,

      "Cannot generate SSPI context" in your case indicates that your client machine can't access domain controller. Might be blocked by enabled firewall. Your second scenario is because Windows caches server credential when connect succussfully with domain controller and use that credential in subsequent connections.

    To further understand your problem,
    (1) What OS is on your client/server machine?
    (2) Does your client/server machine part of a domain?
    (3) Is your client login a domain account?
    (4) Where the firewall seat at, network topologically?

    Thanks,
  • Hi,

    We recently upgraded from SQL 2K to SQL 2005 on WINDOWS 2003 Server. The entire upgrade process has run pretty smoothly aside from encryption.

    We have ASP.NET 1.x apps that connect to an internal server but fail when encrypted. We have tried all of the steps available on the MS KB and still have not resolved the issue.

    On http://msdn2.microsoft.com/en-us/library/ms191192.aspx, we find the following:
    To configure the server to accept encrypted connections

      1. In SQL Server Configuration Manager, expand SQL Server 2005 Network Configuration, right-click Protocols for <server instance>, and then selectProperties.
      2. In the Protocols for<instance name> Properties dialog box, on the Certificate tab, select the desired certificate from the drop down for the Certificate box, and then click OK.
      3. On the Flags tab, in the ForceEncryption box, select Yes, and then click OK to close the dialog box.
      4. Restart the SQL Server service.

    The problem is when we get to step 2, there are NO certificates available in the dropdown box.

    This problem is solely the result of installing SQL Server 2005. The apps were successfully encrypting data connections prior to the install. All internal and non-encrypted connections work correctly and no other changes were made to the box aside from the SQL 2005 upgrade.

    What can I do to get certificates to appear in the Certificate tab of the Properties dialog box?

    Many thanks.

    -Matt
  • Hi Matt,

    The certificates dialog in SQL Server 2005 is actually trying to help you.  It will only display certificates that are valid for usage with SQL 2005 encryption.  So it verifies the following things:

    1. Enhanced Key Usage section of cert contains:

    Server Authentication (1.3.6.1.5.5.7.3.1)

    2. Certificate has private key.

    3. Certificate is in correct store (should be in Local Computer store under Personal Certificates for SQL running under localsystem or network service).  If your SQL is running under domain account, then it will look in the store for the domain account.

    So take a look at your cert and verify the above 3 items and it should show up in the dialog.
Page 2 of 15 (217 items) 12345»