SQL Protocols

Understanding the error message: “Login failed for user ''. The user is not associated with a trusted SQL Server connection.”

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 account trying to connect to SQL Server.  This list is not intended to be exhaustive, but here are several known root causes for this error message. 

1)      If this error message occurs every time in an application using Windows Authentication, and the client and the SQL Server instance are on separate machines, then ensure that the account which is being used to access SQL Server is a domain account.  If the account being used is a local account on the client machine, then this error message will occur because the SQL Server machine and the Domain Controller cannot recognize a local account on a different machine.  The next step for this is to create a domain account, give it the appropriate access rights to SQL Server, and then use that domain account to run the client application.  Note that this case also includes the special accounts “NT AUTHORITY\LOCAL SERVICE” and “NT AUTHORITY\NETWORK SERVICE” trying to connect to a remote SQL Server, when authentication uses NTLM rather than Kerberos.

One very common case where this can occur is when creating web applications with SQL Server and IIS; often, the web page will work during development, then errors occur with this message after deploying the web site.  This occurs because the developer’s account has access to SQL Server, but the account IIS runs as does not have access.  To fix this specific problem, refer to this kb article about impersonating a domain user in ASP.NET: http://support.microsoft.com/kb/306158

2)      Similar to above: this error message can appear if the user logging in is a domain account from a different, untrusted domain from the SQL Server’s domain.  The next step for this is either to move the client machine into the same domain as the SQL Server and set it up to use a domain account, or to set up mutual trust between the domains.  Setting up mutual trust is a complicated procedure and should be done with a great deal of care and due security considerations.

3)      This error message can appear immediately after a password change for the user account attempting to login.  This occurs because of caching of the client user’s credentials.  The next step here is to log out the application user with the old password, and re-login with the new password before running the application.

4)      If this error message only appears sporadically in an application using Windows Authentication, it may result because the SQL Server cannot contact the Domain Controller to validate the user.  This may be caused by high network load stressing the hardware, or to a faulty piece of networking equipment.  The next step here is to troubleshoot the network hardware between the SQL Server and the Domain Controller by taking network traces and replacing network hardware as necessary.

5)      This error message can appear consistently for local connections using trusted authentication, when SQL Server’s SPN is not interpreted by SSPI as belonging to the local machine.  This can be caused either by a misconfiguration of DNS, or by a machine having multiple names.  If your machine has multiple names, try to work around the need for multiple names and give it a unique name.  If the machine just has one name, then check your DNS configuration.

Dan Benediktson
SQL Server Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Steps to troubleshoot SQL connectivity issues

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 connectivity issue in their applications, but it would be great if you can follow the steps below to eliminate issues one by one and post a question on SQL Server Data Access forum if needed.

Step 1: Network issue

You might be able to make local connection without a working network, but that's a special case. For remote connection, a stable network is required. The first thing to trouble shoot SQL connectivity issues is to make sure the network we rely on is workable and stable. Please run the following commands:

ping -a <your_target_machine>    (use -4 and -6 for IPv4 and IPv6 specifically)

ping -a <Your_remote_IPAddress>

nslookup (type your local and remote machine name and IP address multiple times)

Be careful to see any mismatch on the returned results. If you are not able to ping your target machine, it has high chance that either the network is broken or the target machine is not running. It's possible the target machine is behind a firewall and the firewall blocks the packets sent by ping, though. Windows firewall does not block ping (ECHO) packet by default. The correctness of DNS configuration on the network is vital to SQL connection. Wrong DNS entry could cause of all sorts of connectivity issue later. See this link for example, "Cannot Generate SSPI Context" error message, Poisoned DNS.

Step 2: SQL Server configuration issue

You need to make sure the target SQL Server is running and is listening on appropriate protocols. You can use SQL Server Configuration Manager (SCM) to enable protocols on the server machine. SQL Server supports Shared Memory, Named Pipes, and TCP protocols (and VIA which needs special hardware and is rarely used). For remote connection, NP and/or TCP protocols must be enabled. Once you enabled protocols in SCM, please make sure restart the SQL Server.

You can open errorlog file to see if the server is successfully listening on any of the protocol. The location of errorlog file is usually under:

%ProgramFile%Microsoft SQL Server/MSSQLxx.xxx/MSSQL/Log

If the target SQL instance is a named instance, you also need to make sure SQL Browser is running on the target machine. If you are not able to access the remote SQL Server, please ask your admin to make sure all these happen.

Step 3: Firewall issue

A firewall on the SQL Server machine (or anywhere between client and server) could block SQL connection request. An easy way to isolate if this is a firewall issue is to turn off firewall for a short time if you can. Long term solution is to put exception for SQL Server and SQL Browser.

For NP protocol, please make sure file sharing is in firewall exception list. Both file sharing and NP use SMB protocol underneath.

For TCP protocol, you need put the TCP port on which the SQL Server listens on into exception.

For SQL Browser, please put UDP port 1434 into exception.

Meanwhile, you can put sqlservr.exe and sqlbrowser.exe into exception as well, but this is not recommended. IPSec between machines that we are not trusted could also block some packets. Note that firewall should never be an issue for local connections.

Step 4: Client driver issue

At this stage, you can test your connection using some tools. The tests need to be done on client machine for sure.

First try:

telnet <your_target_machine> <TCP_Port>

You should be able to telnet to the SQL server TCP port if TCP is enabled. Otherwise, go back to check steps 1-3. Then, use OSQL, SQLCMD, and SQL Management Studio to test sql connections. If you don't have those tools, please download SQL Express from Microsoft and you can get those tools for free.

OSQL (the one shipped with SQL Server 2000) uses MDAC.

OSQL (the one shipped with SQL Server 2005 & 2008) uses SNAC ODBC.

SQLCMD (shipped with SQL Server 2005 & 2008) uses SNAC OLEDB.

SQL Management Studio (shipped with SQL Server 2005 & 2008) uses SQLClient.

Possilbe command use be:

osql -E -SYour_target_machine\Your_instance for Windows Auth

osql -Uyour_user -SYour_target_machine\Your_instance for SQL Auth

SQLCMD also applies here. In addition, you can use “-Stcp:Your_target_machine, Tcp_port” for TCP,  “-Snp:Your_target_machine\Your_instance” for NP, and “-Slpc:Your_target_machine\Your_instance” for Shared Memory. You would know if it fails for all protocols or just some specific procotols.

At this stage, you should not see general error message such as error 26 and error 40 anymore. If you are using NP and you still see error 40 (Named Pipes Provider: Could not open a connection to SQL Server), please try the following steps:

a)       Open a file share on your server machine.

b)       Run “net view \\your_target_machine” and “net use \\your_target_machine\your_share”  (You can try Map Network Drive from Windows Explorer as well)

If you get failure in b), it's very likely you have OS/Network configuration issue, which is not SQL Server specific. Please search on internet to resolve this issue first.

You can try connection using both Windows Authentication and SQL Authentication. If the tests with all tools failed, there is a good chance that steps 1-3 were not set correctly, unless the failure is logon-related then you can look at step 6.  

If you succeeds with some of the tools, but fails with other tools, it's probably a driver issue. You can post a question on our forum and give us the details.

You can also use “\windows\system32\odbcad32.exe” (which ships with Windows) to test connection by adding new DSN for various drivers, but that's for ODBC only.

Step 5: Application issue

If you succeed with steps 1-4 but still see failure in your application, it's likely a configuration issue in your application. Think about couple of possible issues here.

a) Is your application running under the same account with the account you did tests in step 4? If not, you might want to try testing in step 4 under that account or change to a workable service account for your application if possible.

b) Which SQL driver does your app use?

c) What's your connection string? Is the connection string compatible to your driver? Please check http://www.connectionstrings.com/ for reference.

Step 6: Authentication and logon issue

This is probably the most difficult part for sql connectivity issues. It's often related to the configuration on your network, your OS and your SQL Server database. There is no simple solution for this, and we have to solve it case by case. There are already several blogs in sql_protocols talking about some special cases and you can check them see if any of them applies to your case. Apart from that, things to keep in mind:

a) If you use SQL auth, mixed authentication must be enabled. Check this page for reference http://msdn.microsoft.com/en-us/library/ms188670.aspx

b) Make sure your login account has access permission on the database you used during login ("Initial Catalog" in OLEDB).

c) Check the eventlog on your system see if there is more information

At last, please post question on our forum. More people could help you over there. When you post question, you can refer to this link and indicate you see failure at which step. The most important things for us to troubleshoot are a) exact error message and b) connection string.

Xinwei Hong, SQL Server Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Could not connect to X64 named instance

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 state.
4) Customer confirmed it's not a firewall issue. (either by disabling firewall on both client and server machine temprarily, or by adding needed firewall exceptions for application or port number)

If you see this happens on your X64 named instance, then the solution below may applies to you. We have confirmed a bug in SQL Server 2005 could cause this problem.

Let's say your X64 instance is INST1. You can find its instance ID from HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL, say it's MSSQL.3. Then the configuration of this instance is under 
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.3
Please check if you have the following entry in registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432\Microsoft\Microsoft SQL Server\MSSQL.3

If you do have it, then it's the root cause of the problem and you should remove it to solve the problem. INST1 is an X64 instance and its configuration should present under Wow hive. This wrong registry might come from a previous instance which you have removed but the registry is not cleaned by the SQL uninstallation for some reason.

A good news is that the issue is resolved in SQL Sever 2008. Browser will response correctly even if there is wrong entry.

Xinwei Hong, SQL Server Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Understanding “Connection forcibly closed by remote host” Errors Caused by TOE/Chimney

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 hardware implementation of the TCP/IP stack.

The following are the symptoms to look for:

·         The client connection is sporadically failing with the message: “TCP Provider: Connection forcibly closed by remote host.” The client connection may, in addition, sometimes fail with the message: “General network error”.

·         There are no corresponding network-related error messages in the SQL Server instance’s ERRORLOGs. Normally, the “Connection forcibly closed by remote host” message on the client indicates that an error occurred on the server which is deemed severe enough to close the connection; in that case, the server would log an error message explaining why the connection was closed. An example error message for this would be Error 17828: “The prelogin packet used to open the connection is structurally invalid; the connection has been closed. Please contact the vendor of the client library.” However, if the issue is in the networking hardware, such as a TOE-related issue, there will be no message in the SQL Server instance’s ERRORLOGs for this connection closure, since the server is not intentionally closing the connection. Therefore, check the SQL Server ERRORLOG for an absence of any corresponding network-related error messages.

·         There is no other client killing the first client’s connection. In addition to potential network hardware causes, the “Connection forcibly closed” message can also appear with no corresponding server ERRORLOG message if the client’s connection is being killed by a different client. Examine the SQL Server ERRORLOG for KILL statements; if there are none, then no other client is killing SQL Server connections.

If all three of these symptoms are appearing, your problem is likely due to a faulty piece of network hardware, possibly due to TOE/Chimney.

To test if TOE/Chimney is the source of your problem, you can disable it and see if the problem goes away. You should do this for BOTH the client and server, since TOE/Chimney on either machine, or both, could be the cause of the issue. To disable Chimney, run this command (if on Windows Vista or Windows Server 2008, run it at an elevated command prompt):

netsh int ip set chimney DISABLED

This command does NOT require a reboot. If you have these symptoms and running this command doesn’t fix the problem, then you likely have an issue with network hardware and should follow up by investigating your network hardware. This kb article should give you some leads on how to begin network troubleshooting: http://support.microsoft.com/kb/325487

Dan Benediktson
SQL Server Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Why Browser is installed under 90\shared directory rathar than 100\Shared?

After installing SQL Server 2008, some users might notice that sqlbrowser.exe is installed under %ProgramFiles%Microsoft SQL Server\90\Shared, rather than %ProgramFiles%Microsoft SQL Server\100\Shared 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).  So, usually shared component of SQL Server 2005 is installed under 90\Shared and shared component of SQL Server 2008 is installed under 100\Shared.

Then, why do we install sqlbrowser.exe under 90\shared, rather than 100\shared during SQL Server 2008 setup? The short answer is that SQL Server Browser is a shared component, not only by all instances of SQL Server, but also by different versions of SQL Server, i.e. It's shared by all SQL Server instances on a machine, no matter the instance comes from which version of SQL Server.

SQL Server Browser service used UDP port 1434 to receive request and send information about SQL Server instances to clients. Only one process can listen on that port. This basically means that there can be only one running SQL Server Browser service on a machine.

In order to support SQL Server 2005 and SQL Server 2008 Side-by-Side(SxS), the binaries (sqlbrowser.exe and its resource files) need to be kept in the same place. Otherwise, we have to uninstall SQL Server 2005 Browser before installing SQL Server 2008, but it is not feasible without some complicated redesign. More importantly, we need to support the case that customer install SQL Server 2005 after SQL Server 2008.

The installation of SQL Server Browser binaries are managed by Windows Installer. The SQL Browser component in SQL Server 2005 and SQL Server 2008 share the same GUID in term of Windows Installer. Windows installer will make sure the binaries on the box is always the newest one. The component will be ref-counted and will only be removed when the refcount decreased to 0.

SQL Serve Browser bacomes a seperate application starting from SQL Server 2005. In SQL Server 2000, Browser service is a thread in sqlservr.exe. So, users can consider Browser compoment was introduced in SQL Server 2005 and that's why it's installed under 90\Shared.

For the similar reason, SQL Server Browser group is kept the same as SQLServer2005SQLBrowserUser$<MachineName> in SQL Server 2008. SQL Server resources (registry and file folders) are ACL'ed to this group and it's needed to keep the same in order to access the resources.

Xinwei Hong, SQL Server Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Service Principal Name (SPN)

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.100).aspx

----
Tres London
SQL Server Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

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

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:<machinename> ...." or your client protocol setting ask for first try Named Pipe connection, if fails, then fallback to TCP connection. For eg, in "Client NetworkUtility", you see the protocol order is "np on top of tcp". 

Secondly, this error is winsock error "WSAECONNREFUSED  - 10061". You'd better go through the following checklist to fix the "connection refused" problem.

1) Are you connecting to the right server? Check out whether your target machine name is valid.

2) Whether the <machinename> you specified in your connection string resolve to the correct ipaddress? do:

  ping <machinename> (if your remote server has ipv6 enabled, it might return ipv6 address). Go to the remote machine to see whether it returned the correct ipaddress.

 Then do ping -a <ipaddress>, can it be resolved to correct target machine name?

3) Are you connecting to default instance or named instance? Please go to the target machine, to check whether the instance started successfully, you can go to "Service Control Manager", find out "MSSQLSERVER" or "MSSQL$<instancename>" whether they are running?

4) Does your target SQL instance enabled TCP and listening on a valid TCP port?

a. If it is default instance, on your client machine, try : "telnet <ipaddress> 1433", remember, SQL Server 2005 default instance always use the reserved port 1433, namely, if you modify it to any other port number, the client app can not connect to the sql instance.

b. If it is named instance, on your target server machine, go to "SQL Server Configuration Manager", open "Protocols for MSSQL$<instancename>", check TCP enabled, and right click TCP, find out the tcp port number under 'IPAll". Then on your client machine, do " telnet <hostname> <portnumber>", see whether it succeeds.

5) Is your browser service running on the target server?

If you connect to named instance, then on your target server machine do "net start sqlbrowser".

6) Are your target server behind firewall?

If so, go to " SQL Server 2005 Surface Area configuration", click "Surface Area configuration for service and connection", then click the instance name, enable its remote connection.

You should be able to see the sql tcp port and udp port(1434 for browser service) is opened in Firewall, which enable you can telnet from client.

7) Does your client app set any alias that cause the connection redirect to an invalid port?

a. If you are using MDAC: on your client machine, click "cliconfig.exe", check out alias, whether it points to some other port that you can not telnet, if so, correct it or remove alias, since you should be able to connect through servername by sqlbrowser service automatcally respond to the correct port which server is listening on.

b. If you are using SNAC/SqlClient,  go to SQL Configuration Manager, check out client protocols, see alias setting.

8) Check out IPSec setting if you still face problem. See whether IPSec specification make dropping any packets.

9) If you still face problem, please use "Evenvwr" on the client machine, try to look up the remote server application log, see any info from SQL Server that indicates the sql instance reject client connection. Under this situation, it probably due to invalid client request. Open SQL Profile also can help you dig out which client data operation make server terminate the connection.

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

Login failed for user <xx>, Msg 18456, Level 14, State 1

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 domain\user 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 [domain\user] from windows", then try -

osql -S -U [domain\user] -P xx or osql -S -U[machineaccount] -P xx.

To explain the reason, first you need to understand two Authentication Modes in SQL Server 2005:

• Windows authentication   Only authenticated windows users can gain access to the SQL instance. You need to add a windows login for each windows user or group that needs access to SQL istance. This is default and recommended mode because you can take advantage of all the centralized security policers of your Active Directory domain.
• Mixed Mode authentication Both windows login and SQL Server logins(neither of which are mapped to an operating system users) can access the SQL instances. You use mixed mode when you need to provide access to non-Windows-users for example when users of another operating system need access to SQL.

The –U and –P options are only for SQL Authentication. The SQL Client will not try to impersonate a windows user, instead it directly send the user/password to SQL Server and try to find a matching SQL login.

The solution is: You need to runas to impersonate the windows user and connecting using -E option in "osq.exe" or specify "Integrated Security=SSPI" or "Trusted_Connection = yes" in your connection string.

SSL Cipher Suites used with SQL Server

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 set of cryptographic algorithms that specifies the algorithm for key exchange, encryption, and message authentication (http://msdn2.microsoft.com/en-us/library/aa374757.aspx).  What the best cipher suite to use is negotiated by SSL/TLS and depends upon the cipher suites supported by the OS on the client and the server.  This negotiation is described in this white paper, http://download.microsoft.com/download/7/8/0/780b2238-1fc4-47f5-aa5b-def979ba558b/SSL%20Whitepaper.DOC, but simplistically it can be described as:

1. Determine the highest level protocol mutually supported by the client and the server.
   The currently recognised protocols are, from highest to lowest: TLS1.1, TLS1.0, SSL3.0, SSL2.0 
2. The client will provide the server with a list of its cipher suites from the negotiated protocol
3. The server will chose the strongest cipher suite that it is able to support from the client's list.

The following links list the cipher suites available for SSL2.0, SSL3.0, and TLS1.0:
http://msdn2.microsoft.com/en-us/library/aa380124.aspx
http://msdn2.microsoft.com/en-us/library/aa380512.aspx

Unfortunately, I have been unable to locate a reference providing a list of cipher sutes for each OS.  When I do find a reference, I will update this posting.

One last thing.  Some of you may be cuious to know if there is a way to influence the cipher suites that are used for channel encryption.  While there is nothing you can do at the SQL Server level, you can do something at the OS level as descrbed in this KB article, http://support.microsoft.com/kb/245030.  I don't expect that this is something that most of you will want or need to do.  If you're concerned about the cipher suites used because of FIPS compliance, then please refer to this article instead: http://support.microsoft.com/kb/920995.

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

Install a self-signed test certificate that can be loaded by SQL Server automatically

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 certificate for your own use. The following command would do the work:

You must replace YOUR_SERVER_FQDN with appropriate stuff. You can get the usage of makecert tool from http://msdn2.microsoft.com/en-us/library/bfsktky3(vs.71).aspx, makecert tool comes with winsdk and .Net SDK.

With this, your SQL Server will automatically load the certificate when the SQL Server restarts (if there are multiple certificates meets the requirements, SQL Server will load the first one it finds from the cert store.).

Note that, if you don’t have a certificate, SQL Server will automatically generate one self-signed certificate for you. However, the difference here is: the certificate generated by SQL Server will change every time the server restarts and the certificate’s subject CN is not your FQDN which is in general a critical part if client choose to authenticate the server. More specifically, if the client forces encryption, the connection attempt may fail because the subject CN does not match the server FQDN (in general) and the certificate is not trusted by the client. With the certificate we just generated, your connection attempt may also fail if your client forces encryption. You may get the following on your client machine:

C:\>osql -E -Syourserver

[SQL Native Client]SSL Provider: The certificate chain was issued by an authority that is not trusted.

[SQL Native Client]Client unable to establish connection

The reason of the failure is obvious, as stated in the error message. You can solve the issue by doing the following steps:

Connecting to SQL Server 2005 on Vista and Longhorn

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 BUILTIN\Administrators are not automatically added to the sysadmin fixed server role when they connect to SQL Server.

SQL Server 2005 SP2 provides an option during the setup process allows you to automatically add the user who is running setup into the SysAdmin Fixed Server Role. This option is turned off by default. To enable it, users can use the Setup UI by checking the checkbox in the Configuration Options page of the Setup UI with the caption 'Add user to the SQL Server Administrator role'. Alternatively, this can be done using the setup command line by specifying the ADDUSERASADMIN=1 option in order to add a login for the user running setup. To not add a login for that user, use ADDUSERASADMIN=0 (the default behavior).

This is discussed in detail at http://blogs.msdn.com/sqlexpress/archive/2006/11/15/sql-express-sp2-and-windows-vista-uac.aspx.

Users can always connect to SQL Server using elevated privileges (run as Administrator), or connect as sa (if Mixed authentication mode was selected during install). Once connected, any user can be granted login privileges or added to the sysadmin fixed server role by executing

EXEC sp_grantlogin 'domain\user'

or

EXEC sp_addsrvrolemember 'domain\user', 'sysadmin'

respectively.

Once this is done, users who have been granted login privileges or added to the sysadmin fixed server role no longer need to run as administrator in order to connect to SQL Server.

Stoyko Kostov, SQL Server Protocols

Named Pipes Provider, error: 40 - Could not open a connection to SQL Server (Microsoft SQL Server, Error: XXX)

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 expired

Basically, 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 access denied" in MDAC. Although the error message say about Named Pipe Provider, the issue does not have to be NP related. The reason is that, by default, the client stack try TCP and NP in order. If the connection attempt could not success with any of them, then NP is the last protocol tried and this is the error message to present to users.

When users see this error message, sometimes xxx is omitted. But actually, xxx is the most important part of this error message. xxx is Windows error code and it gives customer hints about why the connection fails. Here are some error code users often see. I also explain the root cause and possible solutions here.

1) xxx=53
winerr 53 means "The network path was not found". If you got this message, it means the client stack cannot find the target machine. Here are possible reasons for this failure,
 a) typo in the server name, or using "/" rather than "\" between server name and instance name, e.g. "myserver/myinst" is not correct.
 b) name resolution to the server name is not correct, "ping -a yourserver" would tell if that's the case
 c) The server machine is firewall'ed and file sharing is not in the exception list. You need put "File and Printer Sharing" in exception.

2) xxx = 1326
winerr 1326 means "Logon failure: unknown user name or bad password". When you get this error code, it means a) the client stack is able to reach the target machine, and b) the service account of the client application does not have enough credential to make a NP session to the server. NP is based on SMB (file sharing). The logon failure message represented by winerr 1326 is from SMB layer, not SQL Server. You need to make sure you can make a file sharing to the server machine with the same service account. If you cannot make a file sharing between your server and your client, NP in SQL Server would not success. Try "net use" or "Map Network Drive" to check this. This often happens when server and/or client is not on domain.

3) xxx = 2
winerr 2 means "The system cannot find the file specified". This error message means the client can reach the server but there is no Named Pipe listener with specific pipe name on the target machine. It also means the account can have a file sharing session without a problem.Possible reasons are:
 a) typo in instance name or wrong instance name. The instance name is not the one you are targeting. Note that for default instance, you shouldn't use MSSQLSERVER as instance name.
 b) Target SQL Server is not running
 c) Named Pipe is not enabled on the server. In this case, the SQL server is not listenning on the specific pipe name.

4) xxx = 233
winerr 233 means "No process is on the other end of the pipe". If you see this error, it usually means you don't have sufficient credential to connect to the server, e.g, wrong user name and/or password when you are using SQL authentication. The connection is closed by server before an error message is sent to the client.

There are other error code come with this error message, but they are not as often as the ones I just mentioned. When you see a new error code, just use "net helpmsg xxx" to get some information and see if you can figure out any suspecious issue.

As I have mentioned, this error message does not mean you have an issue with NP. You can do something unrelated to NP to eliminate this error message, e.g.
a) make sure target machine is accessible
b) target server is running
c) TCP protocol on the target instance is enabled
d) sqlservr.exe and/or the TCP port that the server listens is on firewall exception if firewall is ON.
By doing this, you may success with TCP or have a failure with an error message related to TCP and/or logon credential. This is because we success or fail with TCP protocol and does not even come to the point of using NP.

Xinwei Hong, SQL Server Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified

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? Because even if we failed at this stage (i.e. error locating server/instance specified), we will continue to try connect using default values, e.g defaul TCP port 1433, default pipe name for Named Pipes. You may see other error message due to failure later, but not this error message.

Every time client makes a connection to SQL Server named instance, we will send a SSRP UDP packet to the server machine UDP port 1434. We need this step to know configuration information of the SQL instance, e.g., protocols enabled, TCP port, pipe name etc. Without these information, client does know how to connect the server and it fails with this specified error message.

In a word, the reason that we get this error message is the client stack could not receive SSRP response UDP packet from SQL Browser. It's easy to isolate the issue. Here are the steps:
1) Make sure your server name is correct, e.g., no typo on the name.
2) Make sure your instance name is correct and there is actually such an instance on your target machine. [Update: Some application converts \\ to \. If you are not sure about your application, please try both Server\Instance and Server\\Instance in your connection string]
3) Make sure the server machine is reachable, e.g, DNS can be resolve correctly, you are able to ping the server (not always true).
4) Make sure SQL Browser service is running on the server.
5) If firewall is enabled on the server, you need to put sqlbrowser.exe and/or UDP port 1434 into exception.

Once you are done the steps, you should not see this error message anymore. You may still fail to connect your SQL server, but error message should be different and you have a different issue now. [Update: If it still fails, you may replace server\instance with tcp:server\instance and/or np:server\instance and see if it succeeds with either TCP or NP protocol. That way, you can isolate the issue a little bit. ]

There is one corner case where you may still fail after you checked step 1)-4). It happens when a) your server is a named instance on cluster or on a multi-homed machine, and b) your client is a Vista machine with Firewall on. I have explained the details at: Unable to connect to a SQL Server named instance on a cluster

As a final note, the error message for the same issue when you use SNAC is:
[SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

If you still see this error, please post questions at MSDN SQL Server Data Access Forum:
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1 
Please mention you already read this post and confirm steps you have taken and the results of each step.(e.g. server\instance,etc)  

Xinwei Hong, SQL Server Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Connecting to SQL Server from a workgroup using Windows Authentication

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 details here.

The first method is simple and has the following steps: 
a) Create a user account with same password on both server and client machines.
b) Add the Windows user account as a login for your SQL Server (either by using TSQL or adding the account to windows administrator group).
c) Run your application under that user account.

The second method is a little tricky and can only work with NP protocol. The steps are:
a) From your client machine, make a "net use" to create a file share to your server machine. E.g, "net use z: \\yourservername\sharename". It will ask you to enter user name and password. Make sure you use an eligible windows account. This account can be a domain account if the server machine is in a domain. The account must be able to logon to your SQL server. Of course, you need make sure your are able to connect to "yourservername" (need correct DNS or netbios). You can replace "yourservername" with "yourserverIP". The "net use" step can be done from Explorer by mapping Network Drive under Tools menu.
b) You can now make a connection to your SQL server using Windows Authentication if you specify "np:yourservername" as your data source in your connection string. For Named Instance, you can use "np:yourservername\yourInst". If you don't specify "np:" at the begining of your server name, you may or may not succeed depends on if the provider have cached connection information which may enable us to use NP directly. If no protocol prefix exists, we try TCP and then Named Pipes by default. If it ends up connecting with TCP, you will see something like the followings in the errorlog file.

2007-05-09 21:20:51.61 Logon       SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 192.168.1.100]
2007-05-09 21:20:51.62 Logon       Error: 18452, Severity: 14, State: 1.
2007-05-09 21:20:51.62 Logon       Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: 192.168.1.100]
2007-05-09 21:23:11.07 Logon       Error: 17806, Severity: 20, State: 2.

You client Application will get the following error message:

Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: 192.168.1.100]

If your server does not listen on TCP, your connection should succeed with setp a) and b) because only NP will be used. Network prefix can be done in some tools by selecting NP protocol as perference. You must do step a) and b) under the same under account.

The last question is: when there are connection caches? Here are some general rules:
1) SQLClient does not use registry and does not have connection cache.
2) MDAC cache any success connections under
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\LastConnect
It includes protocol information to that server during the last successful connection.
3) SNAC does not cache protocols for default instance, it only caches protocols to named instances under
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0\LastConnect
4) If connection string does not contain protocols and there is a cache for that server, cached protocols will be used to speed connection.
5) If we fail to connect to a SQL instance, caches for that instance will be removed.

Xinwei Hong, SQL Server Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Named Pipes Provider, error: 40 - Could not open a connection to SQL Server

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=192622&SiteID=1

https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1287189&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=348662&SiteID=1

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1334187&SiteID=17

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1292357&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=136253&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=322792&SiteID=1

https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=763875&SiteID=1

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 to not a real server in your connection string.

5 Other reasons such as incorrect security context.

Let's go throught the detail one by one:

I.   Incorrect connection string, such as using SqlExpress.

Check out: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=558456&SiteID=17

                https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1245564&SiteID=1

The typical error when dealing with Express includes:

a.  User is not aware of SqlExpress was installed as a named instance, consequently, in his/her connection string, he/she only specify ".","localhost" etc instead of ".\SqlExpress" or "<machinename>\Sqlexpress".

b. Np was disabld by default after installing SqlExpress.

c. If Sqlexpress was installed on the remote box, you need to enable remote connection for Express.

Please read the following blog for best practice of connecting to SqlExpress.

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

II. NP was not enabled on the SQL instance.

Check out: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=136253&SiteID=1

Oppose to SQL 2000 which turn on all protocols, SQL 2005 SKUs turn off NP by default. So, when you see this error, please check:

1) Go to SQL Server Configuration Manager, See Server has NP enabled.

2) %windir%\program files\microsoft sql server\mssql.1\mssql\log, notepad ERRORLOG, see whether Server is listening on NP.  You should see "Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ] or [\\.\pipe\mssql$<InstanceName>\sql\query]"

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

4) If you are using SQL Native Client ODBC/OLEDB provider({SQL Native Client} or SQLNCLI), go to SQL Configuration Manager, click client protocols, make sure NP and TCP are both enabled. Right click properties of NP, make sure client is using the same pipe name as server for connection.

5) If you are using MDAC ODBC/OLEDB({SQL Server} or SQLOLEDB) provider, in command line, launch "cliconfg.exe" and make sure NP enabled and right pipe name specified.

III. Remote connection was not enabled. 

Check out: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=322792&SiteID=1

                 https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=763875&SiteID=1

If you are making a remote connection, namely, your target SQL Server is on the different box as client application, you might need to check whether:

a. "File and Printer Sharing" was opened in Firewall exception list.

b. Please see the blog for enabling remote connection for express and troubleshooting tips of remote connection.

http://blogs.msdn.com/sql_protocols/archive/2005/11/14/492616.aspx

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

IV. Server not started, or point to not a real server in your connection string.

Check out: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=348662&SiteID=1

a. use "sc query mssqlserver" for default instance or "sc query mssql$<instancename>" to make sure SQL Server was started. Sometimes, reseason behind the broken of your client application w/ this error:40 might be SQL server restarted and failed, so, it'd better for you to double check.

b. User specified wrong server in their connection string, as described in the forum discussion, "MSSQLSERVER" is an invalid instance name. Remember, when you connect to default instance, <machinename> could be best representitive for the instance, when you connect to a named instance such as sqlexpress, you should specify <machinename>\<instancename> as data source in your connection string.

 V. Other reasons such as incorrect security context.

Check out: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=192622&SiteID=1

Such error also occured during user operation such as moving database or db mirroring or cluster, any DB OP that might invovle different sql instances, namely, the destination database is located in another sql instance and user is not aware of the state of the destination. I recommend you first isolate whether this fail is during connection stage or data operation stage.

a. During data operation, you are normally asked to type in the destination server name whether it is default to "(local)" or another server "<remotemachinename>". So, remember the exact string that represent the target instance, then when the error repros, open command line, use "sqlcmd -S<representitive> -E" ,see what happens, if the connection fail, please follow up above I - IV troubleshooting lists. otherwise continue.

b. If you can make basic conection, but still face the error, then there must be something that server reject the connection or client close the connection for some reason.

Summary, give checklist:

1. Is your target server started?

2. Is your target server listening on NP? Which Pipe?

3. Has your client enabled NP? Use the same pipe to connect as Server?

4. Are you making local connection? If so, what is the instance, default or remote?  

5. Did you put correct instance name in the connection string? Remember, Sqlexpress is a named instance.

6. Did you enable remote connection? Firewall? IPSec? "File and Printer Sharing" opened? Can access server?

7. Can you make basic connection by using <servername> or <servername>\<instancename>? Use sqlcmd or osql.

8. What is your repro step? What was your client APP doing during this error occuring? Which DB operation, detail?

MING LU