42 posts from: SQL Protocols
Windows 7 AppCompat changes and SQL Server client and setup hangs
(Indexed 2010-08-13):
Windows 7 and Windows Server 2008 R2 include many changes to the core operating system. This blog post is targeted at resolving the impact on SQL Server client drivers and SQL Server setup of one breaking change in Windows 7 and Windows Server 2008 R2.The changes to behavior of the GetOverlappedResult API, which SQL Server client drivers use for performing network reads and writes, is described here: http://msdn.microsoft.com/en-us/library/dd371711(VS.85).aspx. When SQL Server client drivers get...(truncated)...
CurCmd in TDS DONE token
(Indexed 2010-05-19):
Several customers approached us asking what are the possible values for CurCmd in TDS DONE token. The TDS DONE token is defined at http://msdn.microsoft.com/en-us/library/dd340421(PROT.13).aspx. Similar rules apply to DONEPROC and DONEINPROC. In the TDS protocol, we have:CurCmdThe token of the current command. The token value is provided and controlled by the application layer, which utilizes TDS. The TDS layer does not evaluate the value.As it says, we dont put the possible value in the TDS spe...(truncated)...
TDS traceflags in SQL Server
(Indexed 2010-05-18):
Microsoft SQL Server has a traceflag framework through which users can set specific server characteristics or to switch on/off a particular behavior. For details about how to use traceflags, please refer to http://msdn.microsoft.com/en-us/library/ms188396.aspxIn this blog, I will introduce two useful traceflags related to TDS, -T4052/-T4055.-T4052 can be used to print the TDS packet sent to client (i.e. output) to console.-T4055 can be used to print the TDS packet received (i.e. input) to consol...(truncated)...
SQL Server "Login Failure" troubleshooter
(Indexed 2010-04-02):
Our colleagues over in the SQL Server Security team have developed an open tool for troubleshooting "Login Failure" errors. If you're having an issue with "Login Failure" errors and can't resolve it from the other SQL Protocols blog articles here, check out their blog post and give their tool a try: http://blogs.msdn.com/sqlsecurity/archive/2010/03/29/sql-ser...(truncated)... Dan BenediktsonSQL Server ProtocolsDisclaimer: This posting is provided "AS IS" with no warranties, and confers no rights...(truncated)...
SQL Server Native Client survey
(Indexed 2010-01-16):
Dear SQL Server developers and users:Our sister team, the SQL Server Native Client (SNAC), has a survey open to ask for your feedback as they take a step back to review the priorities and goals for the long term and identify areas that will benefit from investments.As a whole, we view YOU and your organization as a key stakeholder in this process and would like to gather your inputs in this survey, which should take no more than 5 - 10 minutes. The feedback you provide is very valuable and be re...(truncated)...
Selectively using secure connection to SQL Server
(Indexed 2009-10-19):
Secure connection to SQL Server can be enforced by different ways, e.g. forcing encryption by using ForceEncryption property under the Network Configuration in the server. But this setting will force all clients to use encryption and any client that is not able to use an encrypted connection will fail.If you need to connect to SQL Server selectively here is how to achieve this:You can take advantage of the two client settings, Force Protocol Encryption and Trust Server Certificate and two connec...(truncated)...
We would like to hear your opinion...
(Indexed 2009-10-02):
Dear SQL Server developers and users: It is this time of the year when we, in the SQL Connectivity product team, take a step back, review the priorities and goals for the long term and identify areas that will benefit from investments. We view you and your organization as a key stakeholder in this process and would like to gather your inputs in this survey, which should take no more than 5-10 minutes and a few other surveys that we will conduct in the next few months. The feedback you provide is...(truncated)...
Connection from a Windows Service could be blocked by firewall even if firewall is disabled
(Indexed 2009-09-21):
Recently, I worked on a customer issue. The customer encountered an "error: 26 - Error Locating Server/Instance Specified" error although he has applied/verified every steps I posted in the blog:http://www.bing.com/search?q=error+26+blog+protocol&for...(truncated)... some investigation, we found that: Windows Firewall is disabled on both server and client machine. Running Portqry from a command window also show that correct information was returned by SQL Browser and the UDP packet is received ...(truncated)...
Understanding the error An operation on a socket could not be performed because the system lacked sufficient buffer space or because a queue was full.
(Indexed 2009-03-09):
This error message, which is associated with the Winsock error WSAENOBUFS and actually comes from Windows rather than SQL Server directly, has two well-understood root causes. However, it still stumps people today, probably because there is no single source which explains both root causes and offers solutions for both. Here is a description of the two common situations where you may see this error and quick solutions for each:OS runs out of memory for TCP buffersWhen a powerful client machine, ...(truncated)...
TCP listening ports and ENDPOINT ports behavior
(Indexed 2008-11-05):
Endpoint configuration seems to be creating some confusion. I hope that this will clarify some of the behavior as it applies to TCP endpoints.With a default configuration of SQL Server, try the following:> select name, port, is_dynamic_port, state from sys.tcp_endpoints> goname port is_dynamic_port state--------------------------- ---- --------------- -----Dedicated Admin Connection 0 1 0TSQL Default TCP 0 1 0At first,...(truncated)...
Understanding Data Source=(local) in SQL Server Connection Strings
(Indexed 2008-09-19):
Lately we have noticed many misunderstandings surrounding the usage of the Data Source keyword in connection strings, caused by people generalizing from an example demonstrating a connection string for local connectivity and creating their own connection string for a remote connection. Here is one such example connection string for local connectivity as it would be used in VB.Net:Public con As New System.Data.SqlClient.SqlConnection("Integrated Security=SSPI;Initial Catalog=TestDatabase;Data So...(truncated)...
Configuring SQL Protocols through Windows Powershell
(Indexed 2008-08-29):
Sometimes we are asked about the possibility of configuring SQL Server protocols through PowerShell. In SQL Server 2008, the sqlps tool incorporates WMI and SMO into this powerful Windows administrator tool, making it easy to manage SQL Server protocols through PowerShell. To get started, run (elevated, if on Windows Vista or Windows Server 2008) sqlps.exe, which by default is located at the %ProgramFiles%Microsoft SQL Server100Toolsbinnsqlps.exe; or, if your arch...(truncated)...
Connection failure because of mismatched TDS version
(Indexed 2008-07-15):
Some customers have reported connection failure because of wrong TDS version. The client application gets the following error message: (or similar depends on the protocol used)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) When you check the errorlog of your SQL Server, you see the following error message:...(truncated)...
Troubleshooting SQL Server Cluster Installations
(Indexed 2008-06-17):
On a Windows Cluster it is possible to install a SQL Server Cluster, which will be a separate entity with disctinct Virtual Server Name, IP address, and pipe name. The nodes of the SQL Cluster must be able to communicate with one another. This communication may fail if the TCP port on which SQL Server is listening is not excepted from firewalls on the machines. This port can be found by using the Configuration Manager utility by viewing the properties of the TCP protocol or by checking the SQL S...(truncated)...
Connectivity troubleshooting in SQL Server 2008 with the Connectivity Ring Buffer
(Indexed 2008-05-20):
SQL Server 2008 contains a new feature designed to help troubleshoot particularly tricky connectivity problems. This new feature, the Connectivity Ring Buffer, captures a record for each server-initiated connection closure, including each kill of a session or Login Failed event. To facilitate efficient troubleshooting, the ring buffer attempts to provide information to correlate between the client failure and the servers closing action. Up to 1k records in the ring buffer are persisted for as lo...(truncated)...
Understanding the error message: Login failed for user ''. The user is not associated with a trusted SQL Server connection.
(Indexed 2008-05-03):
Understanding the error message: Login failed for user ''. The user is not associated with a trusted SQL Server connection.This exact Login Failed error, with the empty string for the user name, has two unrelated classes of causes, one of which has already been blogged about here: http://blogs.msdn.com/sql_protocols/archive/2005/09/28/474698.aspx. In addition to an extra space in the connection string, the other class of causes for this error message is an inability to resolve the Windows accou...(truncated)...
Steps to troubleshoot SQL connectivity issues
(Indexed 2008-04-30):
We have been seeing and trying to resolve SQL connectivity issue all the time. I guess it would be helpful if we can put some guidance on how to resolve connectivity issues. Here comes a proposal based on my experience. Basically, when you failed to connect to your SQL Server, the issue could be:1) Network issue, 2) SQL Server configuration issue. 3) Firewall issue, 4) Client driver issue, 5) Application configuration issue. 6) Authentication and logon issue. Usually, customers see connectivit...(truncated)...
Could not connect to X64 named instance
(Indexed 2008-04-20):
We see a couple of cases where customer could not connect to named instance on X64. They all have the following symptons.1) Customer has no problem to connect X86 instance on the same server machine even if it's a named instance. Customer can even connect to some of the X64 named instance, but always see problem when connect to some specific named X64 instance. 2) SQL Server is confirmed up and running. The server is listening on TCP and/or NP protocols. 3) SQL Browser is confirmed in running st...(truncated)...
Understanding Connection forcibly closed by remote host Errors Caused by TOE/Chimney
(Indexed 2008-04-08):
Sporadic Connection forcibly closed by remote host errors with SQL Server connections can be very difficult to troubleshoot and resolve. This blog post is targeted at diagnosing TOE/Chimney issues that may lead to this client error message. Chimney is a feature introduced in the Windows Server 2003 Scalable Networking Pack, which was included in Windows Server 2003 SP2. Chimney increases network performance when using a network card which implements TOE, TCP/IP Offload Engine, which is a hardwar...(truncated)...
Why Browser is installed under 90shared directory rathar than 100Shared?
(Indexed 2008-04-07):
After installing SQL Server 2008, some users might notice that sqlbrowser.exe is installed under %ProgramFiles%Microsoft SQL Server90Shared, rather than %ProgramFiles%Microsoft SQL Server100Shared as some users expect. When setup fails for some reason, some user may think this is the culprit. As matter of fact, this is by design, not a bug. Some backgound here. SQL Server 2005 is also knowns as SQL Server 9 (code name Yukon), and SQL Server 2008 is also known as SQL Server 10 (code name Katmai)....(truncated)...
Service Principal Name (SPN)
(Indexed 2008-04-02):
Did you know that beginning with SQL Server 2008, support for service principal names (SPNs) has been extended to enable mutual authentication across all protocols. Administators can now define their own SPNs. Thus SQL Server 2008 makes secure authentication more manageable and reliable by allowing clients to directly specify the SPN to use.Fore more information, click here:http://msdn2.microsoft.com/en-us/library/cc280459(SQL.1...(truncated)... LondonSQL Server ProtocolsDisclaimer: This posting...(truncated)...
TCP Provider: No connection could be made because the target machine actively refused it
(Indexed 2007-07-19):
In this post, I am trying to list out potential cause and general solution to this specific error when you are making connection to SQL Server 2005.First, It is due to TCP connection fail, since you can tell from the title, "TCP Provider: .xx", which indicates that either your connection string explicitly use 'tcp:" prefix, say "Server=tcp: ...." or your client protocol setting ask for first try Named Pipe connection, if fails, then fallback to TCP connection. For eg, in "Client NetworkUtility",...(truncated)...
SSL Cipher Suites used with SQL Server
(Indexed 2007-06-30):
When enabling channel encryption between the application and SQL Server, users may wonder what encryption algorithm is being used to protect their data. Unfortunately, this isn't an easy question to answer and here's why.SQL Server (both 2005 and 2000) leverages the SChannel layer (the SSL/TLS layer provided by Windows) for facilitating encryption. Furthermore, SQL Server will completely rely upon SChannel to determine the best encryption cipher suite to use. Incidently, a cipher suite is a s...(truncated)...
Login failed for user , Msg 18456, Level 14, State 1
(Indexed 2007-06-30):
This post focus on a typical cause of sql login failure issue described as the title. In real world, a customer log on to the machine as a domainuser or local machine accout, he can connec to SQL Server through SQL Server Management Studio. However, he fails to connect by using SQL Login, namely, in connection string specify User as the NT account, and the password. An example:He did: "Create login [domainuser] from windows", then try - osql -S -U [domainuser] -P xx or osql -S -U[machineaccount]...(truncated)...
Install a self-signed test certificate that can be loaded by SQL Server automatically
(Indexed 2007-06-27):
In the blog Certificate for SQL Server 2005, I explained the requirements of a certificate for SQL Server 2005. Customer usually needs to purchase a certificate from certificate authorities (such as VeriSign), or apply a test certificate from some of the free providers available from Internet. The process is usually complicated and time-consuming. If you just want a certificate for testing purpose, there is an easy way to get one. You can use the makecert tool to generate a self-signed certifica...(truncated)...
Connecting to SQL Server 2005 on Vista and Longhorn
(Indexed 2007-06-18):
Some customers have experienced problems connecting to SQL Server 2005 on Vista and Longhorn. Even though they are trying to connect using an account that is an administrator on the box, they get a "Login failed for user" error message with a state of 11.The reason for this is that Windows Vista includes a new feature, User Account Control (UAC), which helps administrators manage their use of elevated privileges, and Windows Vista users that are members of BUILTINAdministrators are not automatic...(truncated)...
Named Pipes Provider, error: 40 - Could not open a connection to SQL Server (Microsoft SQL Server, Error: XXX)
(Indexed 2007-05-16):
This error message is the most frequent error message when connecting to SQL Server. You see this error message when you use SqlClient. In SNAC, the error message is slightly differently as follows:C:>osql -E -Syourserver[SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [xxx].[SQL Native Client]Login timeout expiredBasically, this error message just tell you that the client cannot make a connection to the server. It's equvalent to "SQL Server does not exist or ac...(truncated)...
SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified
(Indexed 2007-05-13):
Users often see this error message when connection to a SQL Server and don't know where to start to solve the problem. In most forums, people says this is because remote connection is not enabled on the server. This is not exactly correct. Actually, this error message give customers very specific information and the solution is quite simple. First of all, you get this error message only if you are trying to connect to a SQL Server named instance. For default instance, you never see this. Why? Be...(truncated)...
Connecting to SQL Server from a workgroup using Windows Authentication
(Indexed 2007-05-12):
When SQL Server and/or client application is not in a domain, users usually could not use Windows Authentication. In this case, SQL Authentication is recommended. However, there are some corner cases you can use Windows Authentication from a workgroup (i.e. non-domain machine). I will explain two methods here. Both methods are not recommended. Since some customers have already used the methods and their application depends on the success of the methods. I think it might be helpful to explain the...(truncated)...
Named Pipes Provider, error: 40 - Could not open a connection to SQL Server
(Indexed 2007-03-31):
This error was most frequently hitted by our customers, and in this post, give a brief summary of troubleshooting tips for this specific error message.First, take a look at below MSDN forum link lists about this topic:http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=...(truncated)... The various causes fall into five categories:1 Incorrect connection string, such as using SqlExpress.2 NP was not enabled on the SQL instance.3 Remote connection was not enabled.4 Server not started, or point t...(truncated)...
Building a WebMethod FORMAT=NONE Stored Procedure
(Indexed 2007-02-25):
Commonly for Web Services, the exposed interfaces (webmethods) must conform to some pre-defined contract. To assist customers developing web services which conforms to these contracts, SQL Server 2005 Native Web Services provides the ability for customers to construct and format the output XML themselves. To do this, the Stored Procedure (or CLR Stored Procedure) must return only one column of type 'nvarchar' (any length including "max") with the column name of "XML_F52E2B61-18A1-11d1-B105-008...(truncated)...
TDSSNIClient initialization failed with error 0x50, status code 0x50
(Indexed 2007-02-01):
In this post, I want to talk about a special cause of this specific error message when you try to start SQL 2005 instance. The symptom here is: The NP and TCP protocol were disabled in your SQL 2005 default instance, and you tried to start it but failed. you went to Server ERRORLOG, and found following error message:Server local connection provider failed to listen on [ .pipesqlquery ]. Error: 0x50Error: 17182, Severity: 16, State: 1.TDSSNIClient initialization failed with error 0x50, status cod...(truncated)...
Connection Alias
(Indexed 2007-01-07):
SQL Server client stack has a facility to let user define connection aliases. Connection Alias can usually help user make connection eaiser, faster and with more convenience. However, if it's not used properly, it sometimes results in connectivity issues which might be difficult to isolate.User can take advantage of connection alias in both MDAC and SNAC. The information is saved in registry. SQLClient does not read information from registry and thus won't use alias. There are two tools can be u...(truncated)...
"Cannot Generate SSPI Context" error message, Poisoned DNS
(Indexed 2007-01-02):
Incorrect DNS can lead to various network connectivity issues. In this post, I explain how it affects your connection to SQL Server. In some cases, you may see the well-known "Cannot Generate SSPI Context" error message. Suppose your SQL Server is located on a machine called HostA. Its IP address is A.B.C.D and its FQDN is HostA.mydomain.com. However, your DNS is poisoned and wrong results are returned to your DNS query. Then, you may have problems to connect to your server. There are two forms ...(truncated)...
Understanding Kerberos and NTLM authentication in SQL Server Connections
(Indexed 2006-12-02):
In this post, I focus on how NTLM and Kerberos are applied when connecting to SQL Server 2005 and try to explain the design behavor behind several common issues that customers frequently hit.On this page: Kerberos VS NTLM.Requirements for Kerberos and NTLM in SQL Connections.When are Kerberos and NTLM are applied when connecting to SQL Server 2005.Common issues and workaround.Troubleshooting Tips checklist. I. Kerberos VS NTLMNTLM Authentication: Challenge- Response mechanism.In the NTLM protoco...(truncated)...
Building T-SQL Custom WSDL generator
(Indexed 2006-11-07):
We've gotten feedback from customers who have asked if it is possible to write a custom WSDL generator in T-SQL. For those customers who do not wish to enable SQL CLR support, the following sample T-SQL SP can be used as a starting point for generating your own custom WSDL. Please be advised that this sample is just as that, a sample. It is NOT production level code and is provided as a technical demonstration that it is possible.NOTE: The server response format for a SP can not be changed, u...(truncated)...
SQL Server 2005 Remote Connectivity Issue TroubleShoot
(Indexed 2006-09-30):
With shipping SQL Server 2005, we heard from customer feedback about suffering make successful remote connection against SQL 2k5, plus, if running SQL Server on XP/WIN2K3/VISTA behind firewall, such problem occurs more frequently and harder for customer to get clue of behind reason.I. Background of remote connectivity issue.The reason we saw the issue comes out w/ SQL 2k5 instead of SQL 2k is because two breaking change in SQL 2k5 compared to SQL 2000.1) Network Protocols Off by Default: In orde...(truncated)...
SQL Linked Server Query failed with Login failed for user
(Indexed 2006-08-10):
SQL Server 2005: Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.SQL Server 2000: "Login failed for user "(null)". Reason: Not associated with a trusted SQL Server connection". The errors listed above is very typical when deploy linked server with delegation. They actually are thrown by the linked server and pass by middle server to the client application. In this post, I will discuss how to properly configure SQL instances and Windows environment in most common scenario and try to make conf...(truncated)...
Troubleshooting Netlib that Comes with MDAC2.8 SP1/NET1.0, SNAC, SQL Server 2005 and NET 2.0 with ETW Tracing
(Indexed 2006-08-04):
Ever have issue with GNE (general network error)? Using ETW tracing can help. For feature description about ETW tracing for data access components, please refer to http://msdn.microsoft.com/library/default.asp?url=/library/e...(truncated)... Note that all commands used in this blog are shipped with OS by default since WINDOWS 2000. [Steps] 1. Setup registry. C:tempreg add HKEY_LOCAL_MACHINESOFTWAREMicrosoftBidInterfaceLoader /v ":Path" /t REG_SZ /d msdadiag.dll /f 2. Compose a ctrl.guid file to ...(truncated)...
"No process is on the other end of pipe" caused by bad certificate
(Indexed 2006-07-26):
In this blog, I am focusing on one specific error message which I think a little confusing for you to troubleshoot the connectivity issue against SQL Server 2005. To be clear, please make sure you saw the following exact same symptom before you apply the resolution that this blog provide since the same error message could also caused by other reasons which is out of scope of this blog.1) OS on the server could be WinXP/Win 2K/ Win 2K3/ Vista.2) SQL Server 20053) Server was started and from Serve...(truncated)...
Error Messages of SQL Server 2005 Start Up Failure
(Indexed 2006-04-28):
SQL Server 2005 could fail to bring up due to various causes, there are several great blogs that talk about different reason: http://blogs.msdn.com/sql_protocols/archive/2006/01/10/51133...(truncated)... this blog, I am trying to summary all possible root cause and give resolution tips. Part I - Read correct error info and find the exact error status code. Tipcally, you will see following sentence in ERRORLOG if server fail to start:2006-04-20 18:42:26.10 Server Error: 26055, Severity: 16, ...(truncated)...
Getting netmon sniff on local machine
(Indexed 2006-04-24):
To be able to do this, you need more than one network interface cards on the local machine.You can do the same for one interface too (although it is a hack). The key here is to ask for any loopback traffic (127.0.0.0) to go thru your new route (the gateway) and add a metric that is smaller than the default one. Depending on the protocol that you want to sniff this may/ may not be helpful (basically anything above TCP should be OK). The way route works is by trying to match the address as best as...(truncated)...