SQL Server 2005 Connectivity Issue Troubleshoot – Part I


This post provides some tips to troubleshoot Sql Server connection problems based on various displayed error messages. And, I will describe connection problems according to different client stack: SNAC/MDAC/SQLClient. Thus, there are 3 parts for this topic.

 

First Part – Troubleshoot SNAC connect to SQL Server 2005

Second Part – Troubleshoot MDAC connect to SQL Server 2005

Third Part – Troubleshoot SqlClient connect to SQL Server 2005

 

Before start, it is very important to identify which client connected to Server and failed. Here, I list out follow key terminology in Sql Server Connection.

 

SNAC – A new data access technology that is new in SQL Server 2005, and is a stand alone data access application programming interface that is used for both ODBC and OLEDB.

 

MDAC – Microsoft Data Access Component contains core data access components, such as OLEDB provider and ODBC provider.

 

SqlClient  – Access data from within a CLR database object by using the .NET Framework Data Provider for SQL Server.

 

Protocol Prefix: explicitly specify which protocol you want to use to make connection. Supported protocol prefix in Sql Server 2005 includes: “lpc:”, “np:”, “tcp:”, “via:”

 

Last connect cache – contains the fully resolved/specified connection strings for the instances that were successfully connected to. 

 

Part I – Connection Fail when SNAC connects to Sql Server 2005

 

Use osql.exe to simulate the connection string in your application and quick troubleshoot if your application uses ODBC provider and use sqlcmd.exe for OLEDB provider. They are located in %SYSTEMDRIVE%Program FilesMicrosoft Sql Server90toolsbinn.

 

Basic connection string:          osql(sqlcmd) /S[prefix]<servername> /E

                                                Osql(sqlcmd) /S[prefix]<servername><Instance> /E

In each follow Message, there are two, one is from ODBC, and the other one is from OLEDB.

 

Message 1:

 

[SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [53].
[SQL Native Client]Login timeout expired
[SQL Native Client]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.


-OR-

 

HResult 0x35, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [53].
Error: Microsoft SQL Native Client : 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..

Error: Microsoft SQL Native Client : Login timeout expired.

 

This indicates that your target server can not be accessed or does not exist. Try to use “ping <servername>” , ” ping <ipofserver>” , “ping -a <ip>”, If either of the pings time out, fail, or do not return the correct values, then either the DNS lookup is not working properly or there is some other networking or routing issue that you will need to resolve.

 

Message 2:

 

[SQL Native Client]Named Pipes Provider: Could not open a connection to SQL

Server [2].

[SQL Native Client]Login timeout expired

[SQL Native Client]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.

 

OR-

 

HResult 0x2, Level 16, State 1

Named Pipes Provider: Could not open a connection to SQL Server [2].

Error: Microsoft SQL Native Client : 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..

Error: Microsoft SQL Native Client : Login timeout expired.

 

This error message means that the server was not found or not running or can not make Named Pipe connection through the pipe name that client specified. To verify this:

 

1) From the command line, do “sc query mssqlserver” or “sc query mssql$<instancename>” to check whether sql instance present.  Then open sql server configuration manager -> check the state of the sql service, if it is not running, start it. If server started fail, check event log or server error log, see what happened there.

2)  if you are sure the service is running and shared memory/Named Pipe enabled, please try connection if it is local default instance “osql /Snp:\.pipesqlquery”; or try connection “osql /Snp:\.pipemssql$<InstanceName>sqlquery” if it is local named instance. if you still get error 2, then go to step 3).

3) 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 [ \.pipesqlquery ] or [\.pipemssql$<InstanceName>sqlquery] 

Notice that “sqlquery” 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 “sqlquery1”, then you would see in the errorlog that server listening on [ \.pipesqlquery1 ]. 

4) See your connection string, whether you explicitly specify the pipe name and does it match the pipe that server is listening on? Or whether you just specify server name( like “.”,”(local)”, etc), but you specify the wrong pipe name on client side Named Pipe configuration.eg, go to SQL Server Configuration Manager, click client Named Pipe properties, see whether the pipe name is same with the one server listening on. 

Note: For remote connection, you need to verify step 2) and 3).  

Message 3:

 

[SQL Native Client]Named Pipes Provider: Could not open a connection to SQL

Server [233].

[SQL Native Client]Login timeout expired

[SQL Native Client]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.

 

 

OR-

 

HResult 0xE9, Level 16, State 1

Named Pipes Provider: Could not open a connection to SQL Server [233].

Error: Microsoft SQL Native Client : 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..

Error: Microsoft SQL Native Client : Login timeout expired.

 

Note: the difference between Message 2 and Message 1 is “Could not open a connection to SQL Server [233].” – error state.

 

You might specify the server name as FQDN/127.0.0.1/ IP Address and NP was disabled on the server. To resolve this, first way is to replace server name as the machine name or “.” or”(local)” or “<machinename>” and you should be able to connect as long as server listening on Shared Memory; second way is to enable named pipe from sql configuration manager and restart server.

 

Message 4:

 

[SQL Native Client]Named Pipes Provider: No process is on the other end of the pipe.

[SQL Native Client]Communication link failure

[SQL Native Client]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.

 

OR-

 

HResult 0xE9, Level 16, State 1

Named Pipes Provider: No process is on the other end of the pipe.

Error: Microsoft SQL Native Client : Communication link failure.

Error: Microsoft SQL Native Client : 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..

 

1) Local connection: You might specify the server name as FQDN/127.0.0.1/ IP Address in the connection string and speculate connection through Named Pipe provider. To resolve this, either change server name to <machinename> as long as the server is listening on Shared Memory or enabled NP.

2) Remote connection: the server is not listening on Name Pipe. To resolve this, enable name pipe on the remote server and restart the server.

 

Message 5:

 

[SQL Native Client]SQL Network Interfaces: Server doesn’t support requested protocol [xFFFFFFFF].

[SQL Native Client]Login timeout expired

[SQL Native Client]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.

 

OR-

 

HResult 0xFFFFFFFF, Level 16, State 1

SQL Network Interfaces: Server doesn’t support requested protocol [xFFFFFFFF].

Error: Microsoft SQL Native Client : 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..

Error: Microsoft SQL Native Client : Login timeout expired.

 

1)     Local connection: You might explicitly specify protocol prefix “np:” and connect to a named instance and NP was disabled.

2)     Remote connection: You might connect to remote named instance and remote server is not listening on name pipe.

To resolve this, enable NP if you only want to use name pipe protocol or you can remove “np:” prefix to let connection over shared memory locally.

 

Message 6:

 

[SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance Specified [0xFFFFFFFF].

[SQL Native Client]Login timeout expired

[SQL Native Client]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 remot the connections.

 

OR-

 

HResult 0xFFFFFFFF, Level 16, State 1
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

Error: Microsoft SQL Native Client : 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..

Error: Microsoft SQL Native Client : Login timeout expired.

 

You might connect to local named instance and explicitly specify protocol prefix “tcp:” or “np:” in the connection string, however, SqlBrowser service was not running.

To resolve this, you should enable Sqlbrowser service on the server

1)     Use net start or go to sql configuration manager(SSCM), check whether sqlbrowser service is running, if not, start it.

2)     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.

 

Message 7: Shared Memory provider error

 

HResult 0x2, Level 16, State 1

Shared Memory Provider: Could not open a connection to SQL Server [2].

Error: Microsoft SQL Native Client : 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..

Error: Microsoft SQL Native Client : Login timeout expired.

 

OR-

 

 

[SQL Native Client]Shared Memory Provider: Could not open a connection to SQLServer [2].

[SQL Native Client]Login timeout expired

[SQL Native Client]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.

The error indicate you speculated shared memory as connection protocol and server is not listening on shared memory, plus you probably specified “.”/”(local)”/<machinename> /localhost as the server name in the connection string. To resolve this, enable shared memory protocol and restart the server.

 

Message 8:

 

[SQL Native Client]SQL Network Interfaces: Cannot open a Shared Memory connection to a remote SQL server [87].

[SQL Native Client]Login timeout expired

[SQL Native Client]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.

 

OR-

 

HResult 0x57, Level 16, State 1

SQL Network Interfaces: Cannot open a Shared Memory connection to a remote SQL server [87].

Error: Microsoft SQL Native Client : 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..

Error: Microsoft SQL Native Client : Login timeout expired.

The reason is same as the one of Message 6, just you might specify FQDN/127.0.0.1/IP Address as server name in the connection string.

 

Message 9: TCP specific

 

[SQL Native Client]TCP Provider: No connection could be made because the target machine actively refused it.

[SQL Native Client]Login timeout expired

[SQL Native Client]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.

 

OR-

 

HResult 0x274D, Level 16, State 1

TCP Provider: No connection could be made because the target machine actively refused it.

Error: Microsoft SQL Native Client : 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..

Error: Microsoft SQL Native Client : Login timeout expired.

 

The reason is server is not listening on TCP, probably TCP protocol was not enabled.

 

-Follow messages are some special case –

 

 

Message 10:

 

[SQL Native Client]Unable to complete login process due to delay in opening server connection.

 

Reason:

1)     There are spaces after Instance name in the connection string eg. osql /S”<machinename>Instance  “ /E, to resolve this, you need to remove the trailing space.

2)     Connect through 127.0.01.

3)     Remote connection and WINS was disabled on the client machine and you connect using FQDN as server name. To resolve this, One way, turn on “File and Printer Sharing” and explicitly use name pipe protocol. Another is enlarge the connect timeout to around 30 secondes.

 

Message 11: – Firewall specific

 

HResult 0x274C, Level 16, State 1
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.)

 

OR-

 

Error: Microsoft SQL Native Client : 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..
Error: Microsoft SQL Native Client : Login timeout expired.

 

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

1)     Enable SqlBrowser, see the info in Message 4. Plus, add sqlbrowser.exe into Firewall exception list: HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesSharedAccessParametersFirewallPolicyDomainProfileAuthorizedApplicationsList
2)     Add Tcp port to Firewall exception list. (eg, Name-1433:TCP, Value-1433:TCP:*:Enabled:Tcp 1433).

HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesSharedAccessParametersFirewallPolicyDomainProfileGloballyOpenPortsList

F


Comments (143)

  1. Sten says:

    Thanks. This resolved my issue!

  2. Bob says:

    I still get the error 2 ("Named Pipes Provider: Could not… [2]") even when I’ve make sure that the server is running, all protocols are enabled and the firewall is deactivated (i don’t know if it can cause problems, but…). I’ve already tried to reinstall (CTP, September) with the same results, so the problem is likelly a configuration one. Are there more locations to re-verify that the server is up and running apart the Control Manager itself or Control Panel -> Services? Thanks.

  3. Hi,Bob

    This error message means that the server was not found or not running or can not make Named Pipe connection through the pipe name that client specified. To verify this:

    1) if you are sure the service is running and shared memory/Named Pipe/Tcp/ip were enabled, please try connection if it is local default instance "osql /Snp:\.pipesqlquery"; or try connection "osql /Snp:\.pipemssql$<InstanceName>sqlquery" if it is local named instance. if you still get error 2, then go to step 2).

    2) 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 [ \.pipesqlquery ] or [\.pipemssql$<InstanceName>sqlquery]

    Notice that "sqlquery" 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 "sqlquery1", then you would see in the errorlog that server listening on [ \.pipesqlquery1 ].

    3) See your connection string, whether you explicitly specify the pipe name and does it match the pipe that server is listening on? Or whether you just specify server name( like ".","(local)", etc), but you specify the wrong pipe name on client side Named Pipe configuration.eg, go to SQL Server Configuration Manager, click client Named Pipe properties, see whether the pipe name is same with the one server listening on.

    Note: For remote connection, you need to verify step 2) and 3).

    If you still face problems, please attach the connection string and your server log file to the comments.Thanks!

  4. Kevin says:

    I installed the SQL 2005 express yesterday with the SQL Server Management Studio Express CTP. I did launch the management studio once just to see if the installation was successful.

    Today, I could not launch this studio anymore. So I tried to run sqlcmd.exe, and it failed to connect to the SQL server.

    After reading this page, I did find the last comment regarding the named pipe setup solve my connection problem. So I update the named pipe attribute for the native client protocol through the SQL Server Configuration Manager and saved it.

    But I still can not lauch the studio, and I can not connect to the server by running the sqlcmd.exe without specifying the named pipe parameter.

    I assumed the saved configuration should make the tool work since it should grab them as defaults when it is not speified at the command line. But it does not seem to be true. I also still can not launch the management studio. Is there anything I am missing?

    Appreciate your help.

  5. Hi,Kevin

    1) As for Management Studio,is there any error displayed when you were trying to lauch it? You can go to "%ProgramFiles%Microsoft SQL Server90Setup BootstrapLOG"Summary.txt to see whether there is error or warning?

    2) What is the error message you saw when you tried to make connection through sqlcmd.exe,how do you make connection?("sqlcmd /S /E" or others?) what if you try osql.exe? If you can attach the error message and the connection string, I can help you identify the cause much faster.

    Thanks!

  6. Tony says:

    I am getting the following login error using SQLCMD.exe(the actual IP was x’ed out):

    2006-01-05 09:19:05.76 Logon Error: 18456, Severity: 14, State: 16.

    2006-01-05 09:19:05.76 Logon Login failed for user ‘sa’. [CLIENT: xxx.xxx.xx.xxx]

    This error is happening intermittently when running a bunch of .sql files one at a time as part of an installation.

    This is the command string:

    sqlcmd.exe -S myServerName -U sa -P myPassword -i "C:myPathmyFile.sql" -b -d myDatabase -o "C:myPathmyLog.LOG"

    When I run this from the command prompt, it works. It is just when our install is running, I get the login error intermittently andor on different files.

    Here is the configuration:

    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)

    Install that is having intermittent connection problems is local to the machine with SQL Server installed

    Max number of concurrent connections = 0 (unlimited)

    Shared Memory = Enabled

    Named Pipes = Disabled

    TCP/IP = Enabled

    VIA = Disabled

    Client Protocols:

    Shared Memory Order 1

    TCP/IP Order 2

    Named Pipes Order 3

    Please Advise,

    Thanks,

    Tony

  7. Tony, in the error "Logon Error: 18456, Severity: 14, State: 16" state 16 implies a problem switching the database context to the user’s database. Given the arguments you’re passing to sqlcmd.exe it looks like this would be "myDatabase". Try looking for any reason why this database would be unavailable at the time the login attempt occurred. Specifically operations that might take the database offline like backup or restore.

    Hope this helps,

    Vaughn

  8. Hi Tony,

    I’ve run into this one before when the target database I was logging into was marked suspect or was offline. You can verify this by logging into master then trying USE myDatabase, this will usually give you a better error message, for example:

    E:JDBC6JDBCmaintests>osql -Stcp:mattn1 -Utds -PMyPassword!

    Login failed for user ‘tds’.

    Cannot open user default database. Login failed.

    C:>osql -Stcp:mattn1 -Utds -PMyPassword! -dMaster

    1> use tds

    2> go

    Msg 952, Level 16, State 1, Server MATTN1, Line 1

    Database ‘tds’ is in transition. Try the statement later.

    1>

  9. Gary says:

    Fantastic – spent days try to connect and this has sorted it. Thanks a lot!

  10. norman says:

    C:>sqlcmd -S zyltestip01.china.***.comSQLEXPRESS

    HResult 0xFFFFFFFF, Level 16, State 1

    SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

    Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi

    shing a connection to the server. When connecting to SQL Server 2005, this failu

    re may be caused by the fact that under the default settings SQL Server does not

    allow remote connections..

    Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired

  11. serwei says:

    been trying the error [2] but in osql I get "No user selected", I then try my current login and still it does not work.

    I’m using SQLExpress 2005 that comes with Visual C++.

    Are the Start/Stop service UIs the only tools?

  12. Hi, serwei

       1)The reason you got "No user selected" by using osql, you might not specify authentication type. eg" osql /S",you need to connect through "osql /S<ServerName> /E" or "osql /S<ServerName> /U<sqllogin> /P<loginpwd>".

       2)With SQLExpress, by default it is installed as a named instance whose name is "SqlExpress" and named pipe and TCP are disabled. You can use net start to check whether sql instance MSSQL$SQLExpress is running and go to SQL server Configuration Manager to check whether your named pipe or Tcp were enabled. Based on the error 2 you came across,please follow the troubleshooting list.

    More useful info about sqlexpress 2005: http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

    Thanks for your comments.

    Ming.

  13. ypae says:

    Hello,

    I had the following error message while I am installing SQL Server 2005 Standard Edition:

    Shared Memory Provider: No process is on the other end of the pipe

    This always happens when I try to change the installation path for "Data files" feature (e.g. D:Microsoft SQL Server)

    So installing everything to the default location (C:Program filesMicrosoft SQL Server) works fine.

    Is there any workaround for this?

    Thanks,

  14. ypae says:

    Please ignore my posting above.

    It didn’t work even with default install.

    Fortunately, since I was installing SQL 2005 on Microsoft Virtual Server 2005 R2, I simply undo all changes (reverted back to sysprep) and started from the scratch and now the installation is successful.

    It doesn’t explain why it didn’t work in the first place…

    Thanks,

  15. Hi, ypae

       The error you saw in the first place might due to you disabled shared memory on the server or server was not stared properly during installation. Next time, when you came across any setup problem, please go to c:program filesMicrosoft SQL Server90Setup BootstrapLOG, there is a summary.txt which points you that setup log files. With the log, you can figure out what was going on during installation.

    Thanks!

    Ming.

  16. Thara says:

    I tried to connect to the SQL server Express 2005 database from my Pockect PC Emulator(Visual Studio 2005). I use C#.net and Remote Data Access method. so I used pull method of the RDA object. But there is a error on my Pockect PC Application like {"Failure to open SQL Server with given connect string. [ connect string = Provider=172.16.10.48,1433; Data Source=172.16.10.48\SQLEXPRESS;Initial Catalog=LocalDBInsurance;User ID=Niluka;Password=windowsloginpassword ]"}

    and

    I also used the connection string as Provider=SQLOLEDB.1; Data Source=172.16.10.48\SQLEXPRESS;Initial Catalog=LocalDBInsurance;User ID=Niluka;Password=windowsloginpassword. But these 2 were not worked.

  17. Thara says:

    Please overcome this

  18. Hi, Thara

       1) The first connection string you used has wrong value for the "Provider" part, please search connection string properties in SQL Server Book Online for OLEDB provider.

       2) The second connection string you use seems good, but I do not know why you fail to connect. Please visit our Express blog to troubleshoot:

    http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx

       3) We also noticed that it seemed that you try to use windows authentication from "windowsloginpassword", however, you should use connection property "Trusted_Connections=yes" instead of explicitly inputting password of windows account;unless you use SQL Login, you should use "User ID and Password".

    Thanks!

    Ming.

  19. Lim says:

    Hello,

    I have the following error while running this command:

    C:>sqlcmd -S <servername>SQLEXPRESS

    HResult 0xFFFFFFFF, Level 16, State 1

    SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

    Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi

    shing a connection to the server. When connecting to SQL Server 2005, this failu

    re may be caused by the fact that under the default settings SQL Server does not

    allow remote connections..

    Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

    When I run the above sqlcmd command on the client workstation (with SQL 2005 Express installed),

    I still get above error even though

    1. SQL Server Browser service is already running & active.

    2. All Share Memory, Named Pipes, TCP/IP Protocols for SQLEXPRESS & Client Protocols are Enabled.

    3, ‘Local and remote connections-Using both TCP/IP and named pipes’ are enabled.

    Are there anything I miss?

    Thanks

  20. Hi,Lim

      Are you sure your sql server is running? Or whether you restarted the service after enabling the TCP or NP?

    1) If it is local connection, please see the SERVER ErrorLog to double check.

    2) If it is remote connection, please follow the instruction below http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx.

    For best practice and troubleshoot tips to make connection to SqlExpress, please visit our blog:

    http://blogs.msdn.com/sql%5Fprotocols/

    Thanks!

    Ming.

  21. John says:

    Hi there,

    I am wondering if I can get some help with connectivity to SQL Server Standard 2005.

    The obvious problem that I have is there is no protocols found in sql server 2005 network configuration (in SQL server configuration manager).

    The next thing I did was to run sqlcmd from command prompt and I received message as follow:

    Microsoft Windows XP [Version 5.1.2600]

    (C) Copyright 1985-2001 Microsoft Corp.

    C:>sqlcmd

    HResult 0x2, Level 16, State 1

    Named Pipes Provider: Could not open a connection to SQL Server [2].

    Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi

    shing a connection to the server. When connecting to SQL Server 2005, this failu

    re may be caused by the fact that under the default settings SQL Server does not

    allow remote connections..

    Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

    I think this is server-side related problem since none of the server’s network protocol can be found. I have tried reinstall SQL server 2005 and .NET 2.0 but to no avail.

    Thanks.

  22. Matt Neerincx [MSFT] says:

    I have seen that this problem can occur if you install SQL when the machine has no network hardware installed or enabled (sql can’t find any networking support).  Does your machine have a network card?

  23. Hi, John

       The error indicates that your sql server might not be started successfully. Go to server ERRORLOG, see what error you saw, did you see info like " TDSSNIClient initialization failed with error <xxx>, status code <xxx> " ? The status code can give us clue.

    Also, Matt posted his comments, hope that yours is the same one.

    Thanks!

    Ming.

  24. Kekakos Elias says:

    Hello, I have a serious problem and I need a resolve as fast as you can. I have installed sql server 2005 express edition. By default the shared memory is enable and all others are disable. I enabled all of them and I tried to connect through the SQL MANAGER EXPRESS EDITION. Anyway I can login only with Network protocol SHARED MEMORY. But when I run the sqlcmd I have the following error:

    C:Program FilesMicrosoft SQL Server90ToolsBinn>sqlcmd -U sgvaultuser

    Password: HResult 0x7E, Level 16, State 1

    Shared Memory Provider: The Shared Memory dll used to connect to SQL Server 2000 was not found [126].

    Sqlcmd: Error: Microsoft SQL Native Client : 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..

    Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

    Can any one tell me why I cannot connect through the sqlcmd utility? I have WinXP SP2

    Thanks Elias

  25. Hi, Elias

       The problem here is that you were trying to connect to a default instance instead of SQL Express instance. By default, sql server 2005 express edition was installed as a named instance, which means, when you make connection you should give the instance name in your connection string:

    “sqlcmd -S.SQLEXPRESS -U sgvaultuser”

    The reason you can not connect through ” sqlcmd -U sgvaultuser” might be that you do not have SQL 2000 server installed on you box, and when SNAC try to connect to 2000 Server, it can not load “dbmslpcn.dll”, check whether you have this module under “%windir%system32”.

    To fix your problem:

    Please follow the best practice of connection to sql server 2005 express according to below blog:

    http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx

    Good Luck!
    Ming.

  26. Persone los pioneros non rabata. Great…

  27. Jamie says:

    I get the following when trying to log in using

    osql -S instancename -U sa

    [SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [53].

    [SQL Native Client]Login timeout expired

    [SQL Native Client]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.

    if I run it as

    osql -S 127.0.0.1 -U sa

    it logs in.

    Shared Memory, TCP/IP, Named Pipes all enabled, and in that order.

    Named Pipes not working with \.pipemssql$instancenamesqlquery    or

    \.pipesqlquery

    stopped and started services after each change.

    any help?

  28. Jamie says:

    Figured it out.  The instancename was not the same as the server name.  When I do

    osql -S servername -U sa

    it works.   It’s the only instance on this machine.  I’m an idiot.

  29. rsantos says:

    Hi there, i hope i can get some help here 🙁

    I’ve been for about 3 days trying to connect VS2005 to my MS-SQLServer2005, what is working :

    ->I can connect trough SQL Server Management Studio , and Add, remove, edit any database

    ->I have allowed remote connections in SQL Server Surface Area Configuration

    ->My winXP SP2 firewall is off, and i don’t have other

    ->In VS2005 i configure the webparts,membership providers to MSSQL2005 and work just fine

    Now the things that don’t work:

    ->sqlcmd -S XPTO_NAMEMSSQLSERVER

    HResult 0x57, Level 16, State 1

    SQL Network Interfaces: Connection string is not valid [87].

    Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi

    shing a connection to the server. When connecting to SQL Server 2005, this failu

    re may be caused by the fact that under the default settings SQL Server does not

    allow remote connections..

    Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

    ->When i try  in VS2005 create a Data Connection (under the server explorer) the only Server name is the instanse name of SQLEXPRESS, i already try to put the .MSSQLSERVER2005, but when i try to select any database show none, and the test connection faild

    Some configuration that i have:

    -> in the web.config of my application, the connection string that VS2005 use to save webparts information : <add name="LocalSQLServer" connectionString="&#xA;      server=(local);&#xA;      Trusted_Connection=False;&#xA;      uid=sa;&#xA;      password=12345;&#xA;      database=aspnetdb&#xA;" />, this work just fine.

    ->I have two sql server, the 2005 express and the 2005 developer edition

    Now anyone can help me to figure out whats wrong ? why the sqlcmd doesn’t work ?

  30. Hi, rstons

    1)

    sqlcmd -S XPTO_NAMEMSSQLSERVER

    HResult 0x57, Level 16, State 1

    SQL Network Interfaces: Connection string is not valid [87].

    The problem: "MSSQLSERVER" is not a valid instance name for SQL Server. If you installed a default instance, <machinename> can be the representitive of the default instance, hence, you need use "sqlcmd /S<machinename> /E"

    2)SQLExpress has fixed instancename, sqlexpress, so, when you make connection to the named instance sqlexpress, you should use ".sqlexpress" instead of ".MSSQLSERVER2005".

    More info about sqlexpress, please check another blog:

    http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx

    Thanks!

    Ming.

  31. rsantos says:

    Thanks Ming,

    But i still can connect to MS SQL Server 2005, can you help construct the connection String that i must use to access throw visual studio 2005 to my BD ?

    Thanks

    Rui

  32. Hi, Rui

       If you try to connect to default instance, use following connection string:

       

    "Provider=SQLNCLI; server=<macinename>;  Trusted_Connection=No;     uid=sa;password=12345;database=aspnetdb"

    In your connection string, you did not specify provider, "sqlncli" is SQL2K5 OLEDB provider, and also, it is "No" not "false" in "trusted_connection" field.

    2) If you connect to named instance, let’s say 2k5 express, try following connection string

    "Provider=SQLNCLI; server=<macinename>sqlexpress;  Trusted_Connection=No;     uid=sa;password=12345;database=aspnetdb"

    Good Luck!

    Ming.

  33. Rob Hendriks says:

    Complaint: server is slow to very slow.

    Remedy:

    After the heavy burden of reinstalling MSSQLServer2005Express and .NET Framework, users, etc., we still had the same chain of timeouts on the test client.

    Finally MS Access was reinstalled on the client, extended with all(2?) features relating to .NET Framework. And that made things fly again like old times.

    Hard to prove to be the cause, but my client MS Access got corrupted suddenly after the last MS XP update, Thursday 14. September 2006.

    Hope to have helped out my fellow sufferers.

  34. Frank says:

    I am trying to connect sql2005 remotely but it always gives

    login failed for user "xyz"

    Server Name: serverName

    Error Number: 18456

    Severity: 14

    State: 1

    The login ID/password is coreect as I have checked it from different machines and it works fine. Only my machine can’t connect to the server.

    Please help me in this regard.

    Thanks

  35. Il-Sung Lee says:

    Hi Frank,

    Take a look at this blog posting to understand how to determine the root cause of the login failure: http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx

    Il-Sung.

  36. Nachi says:

    Hi

    I tried it says

    [Sql Native Client]Tcp Provider: No Such host is known.

    and the remaining error Messages.

    Rgds,.

  37. Hi, Nachi

       Can you provide your connection string, which server were you trying to connect, you can " ping <machinename>" to see whether the server is reachable.

    Thanks!

    Ming.

  38. krewekomedi says:

    For those of us who are used to SQL Server 2000, the solution is a change to the query string.  SQL Server 2000 only required the server name in the query string.  2005 Express requires "servernameSQLEXPRESS".

  39. Jamal182 says:

    Hi i keep getting this error when trying to connect to SQL.

    Sqlcmd: Error: Microsoft SQL Native Client : Cannot generate SSPI context.

  40. Altruist says:

    Another reason for this issue is the use of square braces when specifying the data source in the code.

    E.g [ServernameInstance] will not let you connect and generate -26 error

  41. Murali Krishna K says:

    I am getting this error on all machines except the one where I started developing.

    "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)"

    The issue here is I am trying to connect to SQL Server 2000 database.

    The statement I used in web.config is

    <appSettings>

       <add key="connectionString" value="Data Source=192.168.50.5;Initial Catalog=tanger;Persist Security Info=True;User ID=sa; Password=sa"/>    

     </appSettings>

    Can you suggest any solution to overcome this.

  42. Hi, Murali

       If your connection is remote connection:

       1) Did your sql server started successfully?

       2) Did you have SQL 2k5 and SQL 2K side by side on the box? If so, can you enable sqlbrowser service and add it to firewall exceptionlist if your remote serveris behind firewall?

       3) Is your sql server a named instance? If so, please change your connection string "Data Source= 192.

    168.50.5\<InstanceName>",By opening SQL Server Configuration Manager, you can see what sql instances installed on the box, and the instance name.

       4) See SQL server 2k ERRORLOG, find out which tcp port sql is listening, then add this port to firewall exception list.

       5) For remote connection, you should at least enable NP or TCP in your SQL 2000, did you?

    There are troubleshooting lists especial for remote connection in Blog:

    http://blogs.msdn.com/sql_protocols/archive/2006/09/30/SQL-Server-2005-Remote-Connectivity-Issue-TroubleShooting.aspx

    Good Luck!

    Ming.

  43. Milos says:

    Hi,  

    I am having trouble connecting to a remote SQL Server database.  I am using SQL Server Express (2005) and receive the following message when I try to access the remote server/database (SQL2005):

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

    On the client side:

    1. I have confirmed username and password

    2. TCP and named pipes are enabled

    3. I can ping the remote server

    4. I can telnet on port 14330 but not port 1433 – server only allows access via these two ports.

    5. Using SQL Server Configuration Manager | SQL Native Client Configuration | Client Protocols, Named pipe and tcp are enabled; Click properties of Client Protocols, Np and tcp are in enabled protocols, plus, TCP is on the top of the order.

    6. Using osql or sqlcmd to try " osql /S<remoteserver> /E" or "osql /S<remoteserver><Instancename> /E" verifies the connection works

    7. Windows FW is off.

    I have tried to resolve this for the last week but am at a complete loss.  Any wise words would be greatly appreciated.

    Many thanks,

    Milos

  44. Hi, Milos

    On Server side:

       1) Did you enable FW on your server which has SQL Server installed? If so, did you open sql port in the exception list? and did you add sqlbrowser.exe to the exception list?

       2) Did your SQL Server started successfully? Can you double check the server ERRORLOG? or use "sc query mssql$sqlexpress", what you got? Remember, by default sql express is a named instance, and you need to enable tcp or np on the server side to make remote connection, also, you can get tcp port from errorlog that sqlexpress was listening on, try "telnet <remoteserver> <port>" on your server box, see whether it works?

    Client side:

    3) You said, by using "osql or sqlcmd" connection works, so how does your connection string in your client application look like? Can you enlarge connection timeout?

    Finally, are your client and server in the same domain?

    Check out following sqlexpress blog about best practice to connec to sqlexpress and blog about troubleshooting list for sql remote connection.

    http://blogs.msdn.com/sql_protocols/archive/2006/09/30/SQL-Server-2005-Remote-Connectivity-Issue-TroubleShooting.aspx

    http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx

    Good Luck!

    Ming.

  45. Mia says:

    Please let me know how to increase user connections SQL Server 2005?

  46. Tomo says:

    I am having connection issues…

    using the command:

    C:>osql -S <servername>SQLEXPRESS -U sa

    Password:

    [SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance

    Specified [xFFFFFFFF].

    [SQL Native Client]Login timeout expired

    [SQL Native Client]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.

    ALL of the protocols are on and should be working.  I can connect locally.  Any ideas?!

  47. Hi, Tomo

       The issue you came across is because you might not enable remote connection in sqlexpress. Please check following blog:

      http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx

    Good Luck!

    Ming.

  48. Hi, Mia

       Can you be more specific to the question: "Please let me know how to increase user connections "?

    1) Does your client application  run out of tcp port? check following KB:

    http://support.microsoft.com/kb/328476

    2) Do you mean you want to increase cucurrent connections to SQL Server? Then search "Set User Connections" in SQL Server 2005 booksonline.

    Good Luck!

    Ming.

  49. Mohan says:

    Hi,

       I have upgraded one of my servers from SQL 2000 to SQL 2005 SP1.

       I have tried running one of the common SQL  scripts from command prompt and it gives the following error

    HResult 0xE9 Level 16, State 1

    Shared memory Provide: No process is on the other end of the pipe

    SQLCMD: Error: Microsoft SQL Native Client : Communication link failure

    The exact line where I am getting the error is

    –  sqlcmd.exe -Q "<any valid query>"

    Could you let me know what is the cause of the error

    Following line is successful though

    – sqlcmd.exe -Q "<any valid query>" -U sa -P <valid password>

    But I would need the first statement without giving the username and password to work fine. Is there any way i could make it work?

    Thanks

    Mohan.J

  50. Hi, Mohan

       1) First check whether it is the issue described in the following blog:

    http://blogs.msdn.com/sql_protocols/archive/2006/07/26/678596.aspx

       2) Whether you enable NP protocol on your server side, if not please enable it, or go to SQL Server Configuration Manager, click properties of client protocols, and put ‘TCP’ on top of ‘NP’ in the protocol order.

      3) try to change the connection string" sqlcmd -Snp:<machinename><Instance> -Q” "

    see whether that works? be sure that you type the correct instance name, if it is default, then just <machinename>, otherwise need the instance name.

      4) If you were makin remote connection, double check whether your server box that have sql instance has firewall enabled, and make sure "File and printer sharing" is in the exception list.

      5) Whether this error happened intermittently, can try lower CPU comsumption.

    Good Luck!

    Ming.

    Good Luck!

    Ming.

  51. Nadir Viqar says:

    Hi,

    I’m trying to configure the connection string in web.congfig. It currently looks like this:

    <add name="DoxxDBConnectionString" connectionString="Server=209.200.235.3;Database=DoxxDB;User ID=xxxx;Password=xxxx;" providerName="System.Data.SqlClient"/>

    I keep on getting the 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: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)

    I’ve tried variations in my connection string but it did not help. My database is on a Web host and they told me that remote connections are disbaled for security reasons. What do I do now with the connection string?

    Nadir

  52. Hi, Nadir

       1) If you were making remote connection, please double check whether you enable sqlbrowser service, and add it to the firewall exception list.

       2) Try enable np or tcp on your remote sql server, and restart the sql service, plus enable "File and printer sharing" in firewall, or add your sql tcp port to firewall exception list.

       3) Double check whther you were making connection to a named instance, if so, please modify your connetion string: "… Server = <ip><Instnacename> …"

       Finally, for more troubleshooting tips of remote connection, please see the following blog:

    http://blogs.msdn.com/sql_protocols/archive/2006/09/30/SQL-Server-2005-Remote-Connectivity-Issue-TroubleShooting.aspx

    Good Luck!

    Ming.

  53. Hey,

    Please use persons who talk proper english when writing manuals… I find it hard to understand this article… Here is an example of the bad language…

    "From the command line, “net start “check whether sqlservr.exe is present.  Go sql server configuration manager -> check the state of the service, if not running, start it. If server started fail, check event log or server error log, see what happened there.

    "

    PLEASE try to be a bit more Human friendly when describing your stuff!!

    /Jesper

  54. Yesid says:

    Hi,

    I’m trying to use a DTS between two different servers but I get the "Message 1". Whish ports do I have to add to the firewall exception list?

  55. Hi, Yesid

       Were you make remote connection? So, please make sure

       1) "File and Printer Sharing" is in your firewall exceptionlist. you can verify by "net view \<yourserver>" from your client.

       2) Check sql errorlog, see whether your sql has tcp enabled and which tcp port it is listening on. Then add this port to the exception list.

      If your sql server is default instance, then it is port 1433, otherwise, you need to double check the ERRORLOG.

    Good Luck!

    Ming.

  56. James says:

    since I upgraded to SQL2005 I’m having trouble running a osql command.

    I copyed the new version of osql.exe and osql.rll to my windows 2003 terminal server.

    when I run the command osql -S sqlsrv01 -U pmadmin -P ******* -Q "BACKUP DATABASE API_PM_PSMN303_LIVE to PAYROLL with init"

    I receive the following error.

    [ODBC Driver Manager] Data source name not found and no default driver

    specified.

    All application that access the sql2005 databases from this box work fine.

    Any ideas?

  57. Hi, James

       Does your client application use same driver or provider as Osql which is using odbc driver. The error indicated that your client driver was not correcl installed, so, suggest you reinstall client_components from SQL2005 installatin package.

    Good Luck!

    Ming.

  58. Harry says:

    Hi,

    I have sql server running on a remote host and i am not able to connect to the machine without specyfing the port.

    like

    sqlcmd -S bentleysqlexpress -U salil -P Qpppwd1234 -d salil

    does not connect but

    sqlcmd -S bentleysqlexpress,1173  -U salil -P Qpppwd1234 -d salil

    does.

    Kindly help me with the settings.

  59. rifist says:

    hi everyone How can I connect to remote sql server behind router? How can I connect with passive connection?

  60. Matt says:

    SQL has two hurdles to deal with when connecting.  

    #1. Dynamic port resolution, or "What port is SQL running on?".

    #2. Once I know the port, can I connect to the port.

    #1 dynamic port resultion is blocked by most firewalls by default today, this is UDP port 1434.  If you want dynamic port resolution, you need to open up UDP port 1434 on your firewall and also ensure that the SQL Browser Service is running for SQL 2005.  Also note that dynamic port resolution only works on named instances, it does not work for default instances.  So if you changed the tcp port for the default instance, #1 will never work for you, the client MUST know the port to connect to.

    #2. Now that I know the port, why can’t I connect to the port?  This one is just basic tcp-ip 101.  Can I ping the IP?  Can I telnet to the port (telnet 123.123.123.123 1433)?  You could be blocked by the firewall or router or IPSec policy, you need to open these up for the specified target port and then everything will work.

    So to successfully resolve #1 and #2, you need to know what port your SQL is running on.  Start SQL and examine the ERRORLOG to see what tcp-ip port it is listening on.  Perhaps you have not configured SQL to listen on TCP-IP at all, check the configuration tools and enable TCP-IP for the instance, etc…

  61. Peter says:

    When I try to connect to a default named Express instance (computernameSQLEXPRESS) in Management Studio at the machine (Windows XP SP2) where the instance is installed, I’m getting the following error:

    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) (Microsoft SQL Server)

    What does provider: SQL Network Interfaces mean? Does it mean the Shared Memory, Named Pipes and TCP/IP?

    Additional information:

    1.  The following Network Protocols are enabled: Shared Memory, Named Pipes, TCP/IP thru Configuration Manager

    2.  SQL Server Express default instance is running using NT AUTHORITYNetworkService.

    3.  SQL Server Browser service is not running. I believe this is not needed unless I want to connect to the instance from another machine.

    4.  Has tried to disable Windows Firewall but I believe it should not be matter in this particular case since this is within the same machine.

    5.  Can connect to the instance in ODBC Administrator using System DSN thru Named Pipes or TCP/IP.

    I also want to confirm my understanding that a remote connection means you’re connecting to a SQL Server instance installed on another computer and it will not mean to connect to a SQL Server instance installed on the same machine regardless of which network protocol is used.  

    I don’t understand why sql browser service is required for a local named instance that is mentioned in the paragraph (see below) in Message 6.

    — copy from message 6

    You might connect to local named instance and explicitly specify protocol prefix “tcp:” or “np:” in the connection string, however, SqlBrowser service was not running.

    To resolve this, you should enable Sqlbrowser service on the server

    1)     Use net start or go to sql configuration manager(SSCM), check whether sqlbrowser service is running, if not, start it.

    2)     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.

    —– end of message 6

  62. Peter says:

    In my previous message, I forget to ask whether Management Studio using a connection string which specifies protocol prefix "tcp:" or "np:" when it tries to connect to a local named instance?  If yes, what is the connection string look like and how to view it?

  63. Matt Neerincx (MSFT) says:

    There is no need to start SQL Browser service if you are connecting locally.  This is the default configuration for SQLExpress and SQL Server in general.

    Q. SQL Network Interfaces mean?  

    A. This is just the name of the underlying network interface that the driver is using.  MDAC drivers use dbnetlib interface and the newer SNAC drivers use SNI (SQL Network Inteface).  To most customers this is not really that important, like knowing if you have copper or steel pipes, you don’t care as long as the water comes out.

    Q. Does it mean the Shared Memory, Named Pipes and TCP/IP?  

    A. Yes.  SNI implements these protocols (and so does dbnetlib).

    Q. I also want to confirm my understanding that a remote connection means you’re connecting to a SQL Server instance installed on another computer and it will not mean to connect to a SQL Server instance installed on the same machine regardless of which network protocol is used.  

    A. Yes, this is correct.

    The driver will first try hitting SQL Browser and secondly check the local registry to determine how to connect to the named instance SQLEXPRESS, this is how it works locally.  So if your client application cannot access the registry for some reason, then it could possibly fail to get information about the SQLEXPRESS instance.

    Go to SQL Server Configuration Manager and go under the SQL Native Client Configuration tool and verify Shared Memory is enabled and at the top of the list, this will probably fix it.

  64. Matt Neerincx (MSFT) says:

    Q. In my previous message, I forget to ask whether Management Studio using a connection string which specifies protocol prefix "tcp:" or "np:" when it tries to connect to a local named instance?  If yes, what is the connection string look like and how to view it?

    A. Management studio does not make any changes to the connection string, it just uses the given server name and the server to connect to. You can actually prefix the servername with tcp: or np: if you want and it will pass this thru, for example ->

       tcp:MyServer,5555

    This will connect to server on tcp port 5555.

  65. Peter says:

    Hi Matt,

    Thanks for your posts.  

    I have tried few different settings in SQL Server Configuration Manager (SSCM).

    1. Enabled only the Shared Memory network protocol under SQL Server 2005 Network Configuration (SSNC) and disabled all the client protocols under SQL Native Client Configuration (SNCC).  I can connect in SSMS.  However, restarting SQL Server service in SSCM will get the message "The request failed or the service did not respond in a timely fashion…"  Sometimes, the message will just stay there and the CPU will run 100% and I need to reboot the machine.  If I stop and start the SQL Server service in SSCM, it does not have this problem.  It seems to me that the Client Protocols under SNCC can be all disabled and SSMS will still work.

    2.  Disabled all network protocols under SSNC.  Enabled only Shared Memory under SNCC.  Starting SQL Server service in SSCM will get the message "The request failed or the service did not respond…"  Does it mean that SQL Server service can only be started if at least one of the network protocols is enabled?

    3.  Enabled only Named Pipes or TCP/IP under SSNC.  Disabled all client protocols under SNCC.  Trying to connect in SSMS will get error: 26 Error Locating Server/Instance Specified if SQL Server Browser service is not running.

  66. Peter says:

    Hi Matt,

    Forget to ask this in my last message.

    You mention that connection issue may be caused by registry entries not being accessible.  What are the registry entries that I need to check?  Who should able to access those registries when the SQL Server service is running under NT Authority/networkservice?

    Thanks,

    Peter

  67. Peter says:

    SQL Server 2005 Express is installed in a Windows XP SP2 machine in a peer-to-peer network.  When the windows firewall is on, only 1 of the workstations within the network can connect to the SQL Server.  When windows firewall is off, all the workstations within the network can connect to the SQL Server. I cannot figure out why only 1 workstation can connect when the windows firewall is on.

    Thanks,

    Peter

  68. BIG Ah Ha!  For a number of you, this may have been obvious, but I didn’t see this one.  When trying to connect to my sql instance, such as using the SQL Server Manager; where Microsoft asks for a server, they REALLY MEAN Server AND Instance!  i.e. serverinstance (MYPCSQLEXPRESS)

    Hope this helps some of you!

  69. Peter says:

    I want to understand the IP Addresses tab of the TCP/IP Properties in SQL Server Configuration Manager.  The following settings are from the default named instance of SQL Server 2005 Express (computernameSQLEXPRESS) on a Windows XP SP2 machine with IP Address 192.168.1.151.

    IP1 has the following settings:

    Active: Yes

    Enabled: No

    IP Address: 192.168.1.151

    TCP Dynamic Ports: 0

    TCP Port: 1433

    IP2 has the following settings:

    Active: Yes

    Enabled: No

    IP Address: 127.0.0.1

    TCP Dynamic Ports: 0

    TCP Port:

    IPAll

    TCP Dynamic Ports: 1121

    TCP Port:

    Is the instance using Dynamic Port or Static Port?  Normally, I will see blank for TCP Port when TCP Dynamic Ports is 0 in IP1 but in this case, TCP Port has 1433.

    My guess is that the instance is using Dynamic Port and the port is 1121.

  70. Hi, Peter

       You are right, sql server named instance use Dynamic port. And from the configuration you posted, your sql express is listening on 1121 if tcp enabled. Since, IP1 and IP2 were not enabled, which means you server is listening on all IP Address, hence it use the port 1121.

    You can check out following blog for sqlexpress connectivity guidline and troubleshooting tips.

    http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx

    Good Luck!

    Ming.

  71. Alina says:

    It is not enough to set a firewall exception for sqlservr.exe. The 1433 port also has to be added as an exception.

  72. Peter says:

    I’m trying to create an ODBC System DSN to connect to a remote SQL Server 2005 default instance (MSSQLSERVER).  Information for the remote SQL Server 2005:

    1. SQL Browser service running under LocalSystem

    2. Windows Firewall is disabled

    3. Shared Memory, Named Pipes, and TCP/IP are enabled.

    4. TCP/IP’s Listen All is set to Yes and IPAll is using dynamic port which is 1905.

    The problem I have is that the ODBC System DSN won’t able to connect to the remote SQL Server 2005 if I check Dynamically determine port but able to connect if I specify the port number 1905.  

    If I issue telnet 192.168.1.151 1905, I will the blank command window titled Telnet 192.168.1.151 so I guess this mean the port is accessible.

  73. Peter says:

    I have just submitted a feedback in connect.microsoft.com.  I think I should include it in here since it is a connection issue:

    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=259022

  74. Peter says:

    The issue I posted on Monday, February 19, 2007 4:08 PM is also caused by the issue I posted on Monday, February 19, 2007 7:41 PM.

  75. Shum says:

    Hey!!

    Im a new user of sql server 2005… i want to connect sql server between two remote computers… When i do it, i get the following message:

    TITLE: Connect to Server

    —————————————-

    Cannot connect to PC1_V6810administrator.

    —————————————-

    ADDITIONAL INFORMATION:

    Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=-2&LinkId=20476

    —————————————-

    Any body has any idea how to resolve it… Im not an expert so tell  me in the easiest language..

  76. joan says:

    Hi,am trying to connect a remote database to sql server 2005 but it keeps giving me error report.what could be the problem?

  77. JOAN says:

    am trying to connect to a remote database using sql server 2005 but it keeps giving an error messege.whats happening?

  78. Juliano says:

    I have the error below, I try everything, If I can’t solve this error the solucion will be only one, change to Oracle.

    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)

    juliaojoa@msn.com

  79. Rupert says:

    I’m having a connection problem.

    When I run the website with Visual Dev 2005 & SQL 2005 Express, the site fires up great. If I try to connect to the ASPNETDB while the site is on, I cant connect.

    If I add a new connection via SSMS, show some table data and refresh the web page, I get an error stating that (MyServerName)ASPNET user doesn’t have access.

    At 1st I thought that the ASPNET acct didn’t have the correct permissions until I stumbled on the true error by restarting and firing up the website to find the error inaccurate. The login is ok, its just that SSMS becomes a Dictator and locks the aspnet user acct out.

    To bring the site back up, I have to close connections to SSMS b4 anyone notices.

    I have the sql browser svc on, tcp & named pipes enabled.

  80. Hi, Juliano

       The error is very general, it indicated you probably connected to a named instance, is it sqlexpress?

       1) In your connection string, gave a wrong sql insrance name.

       2) You were making remote connection, and you did not enable tcp or np on you SQL 2k5.

       3) You did not add tcp port and sqlbrowser.exe into firewall exception list.

       4) Your sql instance is not started.

       5) You do not have a sql named instance, but you specify a named instance in your connection string.

    To help out your problem, please check out the following blogs:

    http://blogs.msdn.com/sql_protocols/archive/2006/09/30/SQL-Server-2005-Remote-Connectivity-Issue-TroubleShooting.aspx

    http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx

    Good Luck!

    Ming.

       2)

  81. Juliano says:

    Only aspnetdb don’t work in my production environment, access to any other database work fine and in developer machine running the project, the access work too.

    Below my connection String.

    My server is a multhomed machine using Ras and the internal firewall disable, in local nic.

    I can’t see why that error, because to any other connection works fine.

    Any help, really apreciate.

    <connectionStrings>

    <remove name="SqlConnectionString" />

    <add name="SqlConnectionString" connectionString="Data Source=zeussql2005;Persist Security Info=True;Initial Catalog=aspnetdb;User Id=AspNet; Password=***; "/>

     </connectionStrings>

  82. Ted says:

    Hi,

    I am trying to create a system dsn on a client pc but i am getting the

    following error:

    ‘Connection failed.

    SQL state ‘01000’

    SQL Server Error:10061

    [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]

    ConnectionOpen(COnnect()).

    Connection Failed.

    SQL State ‘08001’

    SQL Server Error:17

    [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Serever does not

    exist or access denied.’

    I have unchecked ‘dynamically determine port’ in ‘client configuration’ but

    it still not working.

  83. StuBeDo says:

    Hi

    I have the Message 1 type error:

    "TITLE: Connect to Server

    ——————————

    Cannot connect to "SQL Server Name".

    ——————————

    ADDITIONAL INFORMATION:

    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: 53)

    ——————————

    BUTTONS:

    OK

    ——————————

    "

    I can ping to the machine name and ip ok. Remote connections are allowed on the SQL server instance. I can login locally to the SQL server.

    sqlcmd -S "sqlservername" -E gives the following error

    HResult 0x35, Level 16, State 1….

    Any ideas how to reslove this

  84. Hemendra Singh says:

    hello sir,

               i download MSQSL server 2005 from MicroSoft site now i run successfully but connection making is fail with DSN because of wrong Login Name and Password so plz tell the right Login Name and password for making DSN(data source name ) and run the test successfully

    thank you

    Hemendra Singh

  85. Matt Neerincx (MSFT) says:

    By default SQL 2005 will install with integrated security login only.  So by default you just connect using the follwing connection string:

    DSN=Foo;Integrated Security=SSPI;

    Don’t enter a userid or password in other words.  As long as you are admin on the box you should be able to log in.

  86. Martin Cook says:

    Hi,

    Regarding the error Named Pipes Provider, error: 40 – Could not open a connection to SQL Server.

    I was receiving this error when attempting to add the server MCXP-DEV (my local sql server instance) in server explorer in VS2005.  This was the only option available in the drop down.  By adding LOCAL to this string (as I had seen this in connection strings I have generating previously) the server was adding no problem.  Maybe someone else could shed some light on why the dropdown showed the machine name as the ID for the sql server instance when however the fully qualified instance name of MCXP-DEVLOCAL was required to connect successfully.

    Regards,

    Martin

  87. pioner says:

    Hi, I have this problem

    TITLE: Microsoft SQL Server Management Studio Express

    ——————————

    Cannot show requested dialog.

    ——————————

    ADDITIONAL INFORMATION:

    Cannot show requested dialog. (Microsoft.SqlServer.Express.SqlMgmt)

    ——————————

    Failed to connect to server PRUEBASPRUEBASEXPRESS. (Microsoft.SqlServer.Express.ConnectionInfo)

    ——————————

    A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=233&LinkId=20476

    ——————————

    BUTTONS:

    OK

    ——————————

    that occur when I try to open the properties of anything, server, tables, etc. Or try to modify the structure of any table.

    can anyone help with that?    

    I restart the sql services but that don’t help.    

    thanks

  88. gq says:

    I can’t telnet my SQL2k5 server within the same network, but I can use SQL Server Mangement Studio to configure and manage it now problem. I have a SQL2K server on the same network, both Telnet and Studio work without any problem.

    please help

  89. Matt Neerincx (MSFT) says:

    Most likely the port 1433 is blocked on this SQL Server by Windows Firewall.  Even if port 1433 is blocked by Windows Firewall, tools like SQL Management Studio can connect over named pipes, which goes over a different port.  The client will try tcp-ip and then named pipes and if one succeeds you can connect.

    So solution is to open TCP port 1433 on the SQL Server machine you cannot telnet to.

  90. GrahamC says:

    Thanks. Solved my connection problems

  91. Diyashil says:

    Here is an interesting scenerio I came across while migrating from SQL 2000 to SQL 2005.

    After migrating to SQL 2005 all users could connect to the server through the SQL management studio. But they were unable to make an ODBC connection to the server.

    After spending almost 2 days on this issue  I finally  dropped the login  and recreated it again. I was finally able to make an ODBC connection to the server.

    Interesting!!.

    Shilpi Balan

  92. Rakesh says:

    Hi,

    I have a connectivity problem in accessing SQL server 2000 using dotnet provider(SQL client). I tried connecting using visual web developer 2005.

    1) I can connect to SQL server using OLEDB provider but dotnet provider(SQL client) is failing with exception "Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding"

    2) I can connect to other SQL servers without any problem.

    So I guess the problem is related to this particular SQL server. I wonder any extra configuration setting should be required in SQL server to communicate with Dot net provider.

    Any help is greatly appreciated.

    Thanks,

    Rakesh

  93. Redcell says:

    Hi, I am having a connection failed error in SQL. This is the full error we are getting.

    Connection faiiled:

    SQLState:’01000′

    SQL Server Error: 10060

    [Microsoft][ODBC SQL Sever Driver][TCP/IP Sockets]ConnectionOpen (connect()).

    Connection failed:

    SQLState:’08001′

    SQLServer:17

    [Microsoft][ODBC SQL Server Driver]SQL Server dose not exist or access denied.

    Any help would be great or if you could point me in the right direction. Thank you.

  94. Hi, Redcell

       Can you answer the question by followin the below guidence for us to further help you:

    http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1

    Thanks!

    Ming.

  95. helen steve says:

    I don’t think that would be the reason.

  96. Ashwani Kumar says:

    I m getting this error during the login process, i m unable to login even though i m entering the right username and password. Plz help me for this. First Time Error – "A connection was successfully established with server, but then an error occured during the login process (provider : Shared Meomary Provider, error : 0 – No Process is on the other end of the pipe) (Microsoft SQL Server, Error : 233)" followed by error (Repeated error) login failed for user ‘sa’ (microsoft SQL Server Error, 18456.

  97. Hi, Ashwani

       Please check out the server errorlog, which will give you the info that why your client connection got rejected.

    Good Luck!

    Ming.

  98. Pradeep says:

    Unable to connect to server MMSINDIA:

    Server: Msg 17, Level 16, State 1

    [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.

    please help me

  99. nemo11 says:

    Memo: The same error is generated if you have two or more TCP/IP addresses on your server. This error is usully removed if you call your server not by DNS name, but by address, as

    123.45.67.89SQLEXPRESS

    in ODBC or in SQL Management Studio.

  100. Ken McLeod says:

    I’m puzzled. None of these is the message I get. Besides, I spent the best part of a morning copying a database from SQL2K to this instance, debugging, and getting a connection from a client app. When I left, it was all working, and I did the whole thing by remote desktop connect to the server and running Mgmt Studio on the server by remote connection.

    A day later, the client can’t connect and when I try and use Mgmt Studio by remote connect to the SQL Server I get:

    Cannot connect to SQLSERVE.

    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: 2)

    Help on the error says the service is not started, but it is.

    ERRORLOG shows no info since I successfully accessed the SQL server on the 19th.

    Two questions:

    1. How to resolve?

    2. How did this get changed?

  101. Pete Ocasio says:

    I am trying to run the replication merge agent for the first time for  a replication.  Several servers already replicate to the same server without problems.  I have replicated all the previous Publishers to my subscriber with the identical setup with the exception of the user login into the server.  The user login sees the appropriate tables in the subscriber yet the first synchronization errors with the following:

    rror messages:

    The process could not connect to Subscriber ‘hera’. (Source: MSSQL_REPL, Error number: MSSQL_REPL20084) Get help: http://help/MSSQL_REPL20084 Named Pipes Provider: Could not open a connection to SQL Server [53].

    (Source: MSSQLServer, Error number: 53)

    Get help: http://help/53

    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. (Source: MSSQLServer, Error number: 53) Get help: http://help/53 Login timeout expired (Source: MSSQLServer, Error number: 0) Get help: http://help/0 The merge process failed to execute a query because the query timed out.

    If this failure continues, increase the query timeout for the process.

    When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source:

    MSSQLServer, Error number: 0)

    Get help: http://help/0

    The subscription to publication ‘PUB_1’ could not be verified. Ensure that all Merge Agent command line parameters are specified correctly and that the subscription is correctly configured. If the Publisher no longer has information about this subscription, drop and recreate the subscription.  (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201019) Get help: http://help/MSSQL_REPL-2147201019

    Have any of you seen this error before?  Do you know of a possible solution?

    Thank you,

    Pete Ocasio

    Senior Software Developer

  102. Red4Con1 says:

    do you have same page for Server 2008?

  103. Pete Ocasio says:

    This situation occurs in SQL Server 2005.  While I have sql server 2008 Nov beta installed in another system, i have not even started testing replication in 2008.  I have 85 replicated databases in 2000 and 8 in 2005. Any ideas on solving the problem?

  104. Pete Ocasio says:

    The solution to my issue was to place a record for the subscriber in the hosts file in sys32.  Apparently what I have done for over 8 years, establishing and alias for the subscriber is not the correct thing to do and Microsoft does not support DNS resolution for replication but WINS …. Who figures.

  105. Pete Ocasio says:

    The solution to my issue was to place a record for the subscriber in the hosts file in sys32.  Apparently what I have done for over 8 years, establishing and alias for the subscriber is not the correct thing to do and Microsoft does not support DNS resolution for replication but WINS …. Who figures.

  106. SQL 2005 says:

    Useless and junk product, also bundled with a scrap product reporting services. A product should be simple, like even creating a user is kept like taking time to give birth to a baby from starting.

  107. Dave says:

    I am using Vista Ultimate.  I still get the error "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) "

    I’ve tried most of the things in this forum.  My named pipes are enabled and I’ve tried changing the connection string to both use SQL Authentication and Windows.  

    I am setting the connection string in my web.config using the Enterprise Library config tool.  This doesn’t do anything wierd, it just sets the connection string up and inputs that into your web.config per the database, authenticaion method (windows or sql), logon info (if using sql), etc.   You can even test the connection while in the config tool and it works fine either way so I don’t understand why when I try to load my .aspx I am still getting this error.

    The user of course has rights to the database or else the config testing of the connection string in enterprise manager wouldn’t work anyway.

    Here’s a couple of connection strings that were created that I tried but I get the same error for all t hese:

    <add name="Connection String" connectionString="Data Source=(local);Initial Catalog=Elbalazo;Persist Security Info=True;User ID=ElbalazoAdmin;Password=******"

         providerName="System.Data.SqlClient" />

    <add name="Connection String" connectionString="Data Source=(local);Initial Catalog=Elbalazo;Integrated Security=True"

         providerName="System.Data.SqlClient" />

    I checked in the SQL Server Configuration Manager and named pipes is enabled across the board.

    The connection string test to be good in Enterprise Library Config tool (what was created above) when it tests that string.

    I don’t now what else to do.  I of course am part of the Administrators group on my PC.  This is a home PC using Vista Ultimate.

  108. Dave says:

    You gotta love it.  Microsoft increases security in both Vista and SQL Server 2005 but cannot give us decent error messages nor give us common scenario list that I’m sure they came across when they tested and their clients beta tested connection strings within their applications to SQL 2005.

    I mean in my case we’re talking about a simple .aspx that has a correct connection string clearly in the web.config, the user has the rights, typical sh** and still there is an entire dedicated page to this BS.

    Common Microsoft,  you should be shipping a box of Fail with instructions on how to undo your mysterious mazes.

  109. Patrick S says:

    Still having trouble figuring out the problem with our linked server issue.  Looking at the servers having the problems, we are running MSSQLServer under an end user’s domain account…  I checked his account profile under the delegation tab, the settings are as follows:

    -Do not trust this user for delegation

    -Use Kerberos only.

    Our problem is when we try to do cross-server queries, the link is supposed to authenticate on the other server as the user running the query since we have the connection set to "Be made using the login’s current security context" on the link object itself.  However, when the query fails, we get this error:

    OLE DB provider "SQLNCLI" for linked server "Administration" returned message "Login timeout expired".

    OLE DB provider "SQLNCLI" for linked server "Administration" returned 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.".

    Msg 53, Level 16, State 1, Line 0

    Named Pipes Provider: Could not open a connection to SQL Server [53].

    Msg 18456, Level 14, State 1, Line 1

    Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’.

    Msg 18456, Level 14, State 1, Line 1

    Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’.

    Msg 18456, Level 14, State 1, Line 1

    Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’.

    Msg 18456, Level 14, State 1, Line 1

    Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’.

    Msg 18456, Level 14, State 1, Line 1

    Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’.

    Why would it say it was an anonymous login if it is windows authentication?

    Testing the link object itself (by right clicking and do a "test connection" gives us a similar error:

    "The test connection to the linked server failed."

    ——————————

    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ——————————

    Named Pipes Provider: Could not open a connection to SQL Server [53].

    Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’.

    Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’.

    Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’.

    Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’.

    Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’.

    OLE DB provider "SQLNCLI" for linked server "Administration" returned message "Login timeout expired".

    OLE DB provider "SQLNCLI" for linked server "Administration" returned 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.". (Microsoft SQL Server, Error: 53)

    FYI, We use only windows authentication for SQL.  Any ideas?

  110. Patrick S says:

    On the server which we’re trying to pull the data from (via the linked object) a quick glance at the SQL Server Logs reveals this error:

    Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’.

    Error: 18456, Severity: 14, State: 11.

  111. Hi Patrick,

     It looks like this is your problem:

    -Do not trust this user for delegation

     The SQL Server’s user account needs to be trusted for delegation, otherwise it won’t be able to pass on its credentials to the next SQL Server account.  Try changing this to trust the SQL Server account for delegation and see if that helps.  

     If it isn’t already, you will also need to set the account of the user that is connecting to the SQL Server and issuing the query (i.e., the user your application runs under) so that it is trusted for delegation.

    Hope this helps.

    Dan

  112. Patrick S says:

    Dan,

    Thanks for the quick response!!!  One more question.  I haven’t seen a delegation tab under any other users except for this account (happens to be the company president) that’s running the SQL services….  Why would that tab not be there for anyone but him?  Does the tab automatically appear if your user account is used to run a service on the domian like SQL?  Or is it a setting somewhere else?

  113. Hi Patrick,

     I don’t have an Active Directory box handy to look at this, but I think the Delegation tab appears for any user that doesn’t have "This account is sensitive and cannot be delegated" selected.  Can you make sure that this is not selected for the user(s) you are trying to enable delegation for?

    This MSDN article might be helpful:

    http://msdn2.microsoft.com/en-us/library/ms189580.aspx

  114. Patrick S says:

    Dan,

    Well, I solved our problem and answered your question all in one shot.

    I believe the delegation tab appears if you have a service running under your user account on the domain.  The reason I think this is because I just changed both our dev SQL boxes to run the SQL service under "NT AuthorityNetworkService" and that solved our linked server problem.  I went back and checked the user’s account that it was running under before, and viola!  No more delegation tab.  For the record, I have no idea why these boxes were setup to run under his domain account (instead of a built-in one) in the 1st place, but I’m sure he had a good reason at the time.

    Thanks for all your help on this..  I’ll keep you guys posted if anything changes.

    Patrick-

  115. Kalyan Dey says:

    Hi There,

    I am trying to configure SQL Server 2005 express in a local system. It does not have any network connection. I have the services stared but facing the following issues when trying to connect.

    When I type osql/snp:\localpipesqlquery at the command prompt

    I am getting the fowllowing error messagre

    [Shared Memory]SQL Server does not exist or access denied.

    [Shared Memory]ConnectionOpen (Connect()).

    When I look into the configuration manager I see both

    SQL Server(SQLEXPRESS) and SQL Server Browser running.

    I have the following Protocol for SQLExpress enabled:

    Shared Memory

    Named Pipes

    TCP/IP

    Further,when I try to run sqlcmd at the command prompt I am getting the following error message:

    HResult 0x2, level 16, State 1

    Named Pipe Provider: Could not open a connection to SQL Server [2].

    Sqlcmd: Error: Microsoft SQL Native Client: An error has occured 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..

    Sqlcmd:Error: Microsoft SQL Native Client : Login timeout expired.

    Any hlep to resolve the above issue will be highly appreciated.

    Thanks,

    Kalyan.

  116. Tim says:

    I recently install game called Perfect World. Installation was succesful also patching. But when i started the game i got this error:

    [SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [53]

    i have no idea what to do, because I dont have any experiance regarding SQL. I have SQL Server Compact edition insatlled v3.1.

  117. tim* says:

    sory for another posthere is more detiled error

    Microsoft SQL Native Client error ‘80004005’

    Named Pipes Provider: Could not open a connection to SQL Server [53].

    /ann-list.asp, line 67

  118. Maxx says:

    Guys, in Delphi I’ve resolved "SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]" error by simple changing of Provider property of ADO Connection component from SQLNCLI.1 to SQLNCLI, the same I did in ConnectionString.

    I believe the same works for VB.

  119. Deepak pathak says:

    TITLE: SQL Server Import and Export Wizard

    ——————————

    Could not set up data flow connections.

    The connection type "OLEDB" specified for connection manager "{2674C80A-233C-4394-A978-EE8B07D623A8}" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.

    ——————————

    ADDITIONAL INFORMATION:

    The connection type "OLEDB" specified for connection manager "{2674C80A-233C-4394-A978-EE8B07D623A8}" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.

    ({472FD15D-0A3F-440F-9CA7-53F399921BF0})

    ——————————

    BUTTONS:

    OK

    ——————————

    TITLE: SQL Server Import and Export Wizard

    ——————————

    Could not connect source component.

    The connection "SourceConnectionOLEDB" is not found. This error is thrown by Connections collection when the specific connection element is not found.

    ——————————

    ADDITIONAL INFORMATION:

    The connection "SourceConnectionOLEDB" is not found. This error is thrown by Connections collection when the specific connection element is not found.

    ({472FD15D-0A3F-440F-9CA7-53F399921BF0})

    ——————————

    BUTTONS:

    OK

    ——————————

  120. SQL server authentication problem (how to set login password) says:

    hello sir,

    i m having problem…problems i must say in connection to MS sql server 2005.

    1) I works fine with windows authentication…but does not work with sqlserver authentication.

    2) I program it give error 40…something like that.

    3) Please help me configure sql server fro sqlserver authentication and connection

  121. GAMA says:

    Yo tengo una aplicacion hecha en VB 2005, mi aplicacion la monte en un servidor 2008 con sql 2005, mi aplicacion hace una búsqueda en una base de datos y muestra un GridView mismo que puede paginar la informacion. Puedo ingresar a mi aplicacion (algunos componentes cargan datos de la base de datos), logro hacer la busqueda y el grid view la pagina, el problema es que cuando quiero ver lasiguiente pagina u otra que no sea la primera me manda el error. Puedo ingresar a mi aplicacion (algunos componentes cargan datos de la base de datos), logro hacer la busqueda y el Vista grid la pagina, el problema es que quiero ver cuando lasiguiente pagina u otra que no sea la primera me manda el error.

    "Error mientras se establecía la conexión con el servidor. Al conectar con SQL Server 2005, el error se puede producir porque la configuración predeterminada de SQL Server no admite conexiones remotas. (provider: Proveedor de canalizaciones con nombre, error: 40 – No se pudo abrir una conexión con SQL Server) " "Error mientras se establecía la conexión con el servidor. Al conectar con SQL Server 2005, el error se puede producir porque la configuración predeterminada de SQL Server no admite conexiones remotas. (Proveedor de: proveedor de canalizaciones con nombre, error: 40 – No se pudo abrir una conexión con SQL Server) "

    La opcion de conexiones remotas esta marcada y mi FireWall esta configurado para que permita la conexion, si no fuera asi no me deberia permitir ingresar a mi aplicacion pues mi aplicacion al cargarse utiliza la base de datos. La opcion de conexiones remotas esta marcada y mi firewall está configurado para que permita la conexión, si no fuera asi no me deberia permitir ingresar a mi aplicacion mi aplicacion pues al cargarse utiliza la base de datos.

    espero me puedan ayudar muchas gracias. espero me puedan ayudar muchas gracias.

  122. idomalik says:

    plz tell me about the error 40

    its hapening here when i wants to connect with remote server. and its also tell dat sqlserver doesnot connect with remote server in default setting, is dere any genious who tell me about dis problem

  123. Goyal says:

    Hi,

      I am trying to connect to the SQL Server2005 database through SQlConnection in VS2005 using .NET2.0. It works great for direct internet connections but does not work when the client computer is connected to internet through a proxy server. Any pointers on what I should be doing ?

    Atul

  124. Vincent says:

    Hello,

    I am running SQL 2005 Server management studio. I can connect remotely to other DB’s in the domain where the SQL server is located without any problems.

    The problem I have is when I try to connect to a DB in another domain I get the following error message: "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Also, I’ve tried connecting via the osql prompt but I get the same error.

    There are other SQL 2005 servers in the domain that are workking fine. It’s only this one server that is causing this problem.

    Thanks for your help!

  125. andrew says:

    why oh why did i ever decide to ‘get into computers’

  126. tlipham says:

    I keep getting this error….

    Connection failed:

    SQLState:’01000′

    SQL  Server Error:2

    [Microsoft][ODBC SQL Servier Driver][DBNETLIB]Connection Open

    (Connect()).

    SQL State:’08001′

    SQL Server Error:17

    [Microsoft]{ODBC SQL Server Driver][DBNEBLIB]SQL Server does not exist or access denied.

    but your instructions will not work for Windows Vista. I am just a baby when it comes to this stuff. Please Help

  127. Anand says:

    I tried to establish a remote connectivity with another instance of sql server 2005 in a remote computer and inspite of following all the steps,I coudn’t make it and i got the following error:

    "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)

    (.Net SqlClient Data Provider) "

    Please write down step by step procedure to resolve this issue.

    Thankyou

  128. IvanK says:

    use the connection string in this way and if you follow all the instructions that the users give before maybe you can solve the problem…

    static SqlConnection cn = new SqlConnection("Server=YOURPC\SQLEXPRESS; Database=YOURBASE; Trusted_Connection=true");

  129. Steve Lang says:

    In Message 3, you indicate that error 233 is related to "Could not open a connection to SQL Server"

    How is it that sysmessages shows error 233 as "The column ‘%.*ls’ in table ‘%.*ls’ cannot be null."?

  130. Delijohn says:

    [Hi,

     I am trying to connect to the SQL Server2005 database through SQlConnection in VS2005 using .NET2.0. It works great for direct internet connections but does not work when the client computer is connected to internet through a proxy server. Any pointers on what I should be doing ?]

    Somebody asked this… can we find an answer?

  131. Prashant W says:

    Thank you very much for your post you have solved my gr8 problem.

  132. Nishant Deep says:

    I am using the connection string in the following  way but i couldnot overcome of (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) problem, even i declared impersonate="true" in config file

    static SqlConnection cn = new SqlConnection("Server=YOURPC\SQLEXPRESS; Database=YOURBASE; Trusted_Connection=true");

  133. vamshi says:

    Hi guys,

    I work as a DBA and i configured replication from server A to B. but iam geting an error 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.) (Microsoft SQL Server, Error: 10061).

    snapshot agent is running on server A but server A is not able to connect to server B

    when i check in replication monitor ican see the error can connect to server and the above error…can anyone help me on this issue..iam working on it since 2 ddays but no luck.

    iam able to ping server B from server A using 1433 port. even tcp/ip is enabled on both servers and there is no windows firewall enabled on server B. i even enabled sql server browser. i dont understand what is the issue.

  134. techieg says:

    I get this error message and I am not sure what the issue is, any help will be appreciated;

    TITLE: Connect to Database Engine

    ——————————

    Cannot connect to TECHWORXS2MSSQLSERVER.

    ——————————

    ADDITIONAL INFORMATION:

    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: SQL Network Interfaces, error: 25 – Connection string is not valid) (Microsoft SQL Server, Error: 87)

    For help, click: go.microsoft.com/fwlink

    ——————————

    BUTTONS:

    OK

    ——————————

  135. techieg,

     It is better to post questions at: social.msdn.microsoft.com/…/threads.

    In your error message, it says, "Connection string is not valid". From the rest of your error, I can see that it's because you've specified the instance name as "MSSQLSERVER", which is not permitted (you should never specify the instance name with a default instance). Please try specifying "TECHWORXS2" as the server name, rather than "TECHWORXS2MSSQLSERVER".

  136. Malvika says:

    i cannot access my remote connection

    my local connection is working but on the other system is not working….plz help me

    i was creating a dsn for local system….can i craete dsn for remote systems so i can access database on other system easily……………

  137. Revin says:

    Please see the below scenario.

    We have 2 SQL Servers in our production. They are in different domains. Both of them have tcp-ip & np enabled. I need to pull the data from Server1 to Server2.

    Server1 is configured to port 1433 while the Server2 is configured to use port 65333.

    I am able to connect from Server1 to Server2 through SSMS using the IP Address. However, I am not able to connect from Server1 to Server2 from my SSIS package and so cannot bring in the data. The SSIS package uses "Native OLEDBSQL Server Native Client 10.0 provider".

    I am assuming that it is not a firewall / port issue since i am able to connect tru SSMS.

    SQL Browser is not enabled on Server1. The telnet from both the Servers (using ports) do not work either.

    What else could be the issue here? Any ideas? Thanks in advance!

  138. webhostingmatters says:

    i read this article and it solve my problem very informative article indeed thanks to writer

    http://www.webhostingmatters.com

  139. Leo says:

    Hi…I have an issue. My computer using SQL 2005. and i want to create Link Server to Server that using SQL 2008. But the case is, in the server there are SQL 2005 and SQL 2008 installed. when i create the link server, the link server always connect to SQL 2005 by default. how to connect to the SQL 2008? thx

  140. fustrated says:

    "Some issues that you would need to resolve"

    Alteast be a bit more helpful or else dont bother writing articles

  141. vijay says:

    Microsoft][ODBC SQL server Driver][TCP/IP Sockets] SQL Server does not exist or access denied. Getting this error connecting from one server to another.

    reply me please anybody at vijay.ahire8@gmail.com