SQL Server connection problems

You may encounter problems when entering the database connection details in the first step of database setup:

 

clip0404

 

Error 1: Establishing connection to the server

 

Error message:

 

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

 

Troubleshooting:

 

1.Make sure the SQL Server name or IP address is correct. In some cases, using one of the following values may help:
- your computer name
- localhost
- 127.0.0.1
- (local)

 

2.Make sure the server has Microsoft SQL Server 2000 or 2005 installed and running.

 

3.Make sure you are using the appropriate instance of the SQL Server in case you are using different instances of SQL Server. The instance name must be entered as myserver\myinstance (please note there's a backslash \).

 

4.If you're using Microsoft SQL Server Express 2005 with default installation settings, the correct server name is .\sqlexpress or computername\sqlexpress .

 

5.Make sure the access to the database server is not blocked by some firewall (the default port number for TCP/IP protocol is 1433).

 

6.If you're using SQL Server 2005 (especially the Express Edition), some protocols are disabled by default. You may need to go to Start menu -> All Programs -> Microsoft SQL Server 2005 -> Configuration Tools on the computer where the SQL Server is installed and start SQL Server Configuration Manager. Then, go to SQL Server 2005 Network Configuration and enable the TCP/IP protocol:
 
clip0405
 
You may also need to enable the TCP/IP protocol in the SQL Native Client Configuration -> Client Protocols section:
 
clip0406

 

 

Error 2: Login failed for user 'xy'
 

Error message:

 

Login failed for user 'xy'

 

Troubleshooting for SQL Server account

 

If you're using SQL Server account with password, make sure you are using a valid user name and password. The login must be created on the server, it must be enabled and granted with permission to connect to the server. You can check the user account in Enterprise Manager/SQL Server Management Studio -> Server -> Security -> Logins:
 
clip0407

 

Also, check the Server Properties -> Security dialog in Enterprise Manager/SQL Server Management Studio and make sure your server supports SQL Server and Windows Authentication mode:

 

SQL Server 2000:
 
clip0408
 
SQL Server 2005:
 
clip0409
 

 

Troubleshooting for Windows Authentication account

 

If you're using Windows Authentication account, the situation may be a little more complex and may require you to contact your network administrator. The ASP.NET applications run under some particular local or domain account. This current account is displayed on the screen:
 
clip0428

 

This account must have its own login with Windows authentication in the SQL Server. You can create the login in Enterprise Manager\SQL Server Management Studio -> Security -> Logins and grant it with appropriate permissions on the server. If you SQL Server is located on a different machine than your web server, you may need to configure your web application so that it runs under some domain account, rather than local account so that you can the login in the remote SQL Server.

 

If you do not succeed to configure Windows authentication, you may want to enable Windows and SQL Server Authentication on your SQL Server and use SQL Server account instead. You can learn more about SQL Server authentication in the Troubleshooting for SQL Server account section earlier in this chapter.