Error : 4064

SQL SERVER – Fix : Error: 4064 – Cannot open user default database. Login failed. Login failed for user

I have received following question nearly 10 times in last week though emails. Many users have received following error while connecting to the database. This error happens when database is dropped for which is default for some of the database user. When user try to login and their default database is dropped following error shows up.

Cannot open user default database. Login failed.
Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064)

The fix for this problem is very simple.

Fix/Workaround/Solution:

First click on Option>> Button of “Connect to Server” Prompt.

Now change the connect to database to any existing database on your server like master or msdb.

Now click on connect and it will successfully log you in. Once you login in the system run following statement for your username and it should fix your problem. Make sure to replace [test] with your username and master with yourdatabase name.

ALTER LOGIN [test] WITH DEFAULT_DATABASE = master
GO

Error : 1326

SQL SERVER – FIX : ERROR : (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: )

Regular readers of my blog are aware of the fact that I have written about this subject umpteen times earlier, and every time I have spoken about a new issue related to it. Few days ago, I had redone my local home network. I have LAN setup with wireless router connected with my four computers, two mobile devices, one printer and one VOIP solution. I had also formatted my primary computer and clean installed SQL Server 2008 into it. Yesterday, incidentally, I was sitting in my yard trying to connect SQL Server located in home office and suddenly I stumbled upon the following error. Finding the solution was the most infuriating part as it consumed my precious 10 minutes.

Let us look at few of the common errors received:

An error has occurred while establishing a connection to the server.

(provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 5)

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

I eventually found the resolution to this error on this blog itself; so I was saved from hunting for the solution. I am sure there are a number of developers who had previously fixed this error while installing SQL Server 2008 or SQL Server 2005 but in due course forgot the right solution to this error. This is just a quick refresher. Make sure to bookmark this as you never know when you would need this solution.

Let us check into the steps to resolve this error.

      1)    SQL Server should be up and running.

Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> SQL Server Services, and check if SQL Server service status is “Running”.

In addition, ensure that your remote server is in the same network. Run “sqlcmd -L” in your command prompt to ascertain if your server is included in your network list. You can even find tutorial for the same here SQL SERVER – Find All Servers From Local Network – Using sqlcmd – Detect Installed SQL Server on Network.

I have confronted numerous situations when these nerve-wracking errors crop up, and most of the time when I try to troubleshoot I notice that SQL Server services are neither running nor installed. If SQL Server is not installed as default instance SQL Server Browser should be running together with it; we will explore this further in Topic 5.

      2)    Enable TCP/IP in SQL Server Configuration

When two or more SQL Servers are connected across network they do all communication using TCP/IP. The default port of SQL Server installation is 1433. This port can be changed through SQL Server Configuration Manager. TCP/IP should be enabled for SQL Server to be connected.

Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> Select TCP/IP

Right Click on TCP/IP >> Click on Enable

You must restart SQL Server Services for all the changes to take effect. Right click and go to menu properties to select location where default port of SQL Server can be changed.

       3)    Open Port in Windows Firewall

Windows Firewall is very efficacious in protecting the Operating system from all sorts of malicious attacks. By default, many of the ports and services are refrained from running byfirewall. Time and again, SQL Server ports are not open in firewall as well. All the ports on which SQL Server is running should be added to exception and firewall should filter all the traffic from those ports. As described, by default SQL Server runs on port 1433, but ifdefault port is changed then the new port should be added to exception. If SQL Server has named instance (another instance besides default instance) is installed, SQL Server browsershould also be added to the exception, as described in Step 7.

Go to Control Panel >> Windows Firewall >> Change Settings >> Exceptions  >> Add Port

Make the following entries in popup “Add a Port” and click OK.

Name:Sql
PortNumber:1433
Protocol:  Select TCP

       4)    Enable Remote Connection

Enabling remote connection is another important, yet oft-neglected step that is frequently missed by database administrators while setting up SQL Server. If this feature is turned off SQL Server will function smoothly on local machine, but it will let another server connect to it remotely. By default this feature is ON in SQL Server 2008.

Right click on the server node and select Properties.

Go to Left Tab of Connections and check “Allow remote connections to this server”

5) Enable SQL Server Browser Service

If SQL Server is not installed as default instance but instead installed as named instance and also if there is no specific TCP/IP port configured, it will give rise to the error that is being discussed in this article. If SQL Server Browser service is enabled, it will allow the server to be connected through dynamic TCP/IP port. Enabling this service is a one-time process, as on enabling it once it will apply to all the instances installed on the same server.

Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> SQL Server Browser

Right Click on SQL Server Browser >> Click on Enable

If Click on Enable (Start) is Not allowing to click, then click on Properties >>Service>>

Start-Mode change it to Automatic /Manual >> Apply>>Ok >> Right Click on SQL Server Browser >> Click on Enable.

6) Create exception of sqlbrowser.exe in Firewall

As elucidated in Step 6, sqlbrowser service needs to be enabled for named instance. Windows Firewall may prevent sqlbrowser.exe to execute. So, it is imperative to addexception for the same in windows firewall.

Search for sqlbrowser.exe on your local drive where SQL Server is installed. Copy the path of the sqlbrowser.exe like C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe and create the exception of the file in Firewall, as delineated in Step 3.

7) Recreate Alias

It is getting quite common to create alias of SQL Server and use it in application. This will ensure that in future if any physical SQL Server has to be moved, it will not be required to change any code or connection string. You can simply create alias with the same name pointing to different SQL Server and it will start working instantaneously. I have observed that a couple of times due to internal error while recreating alias this error was fixed.

Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Native Client 10.0 Configuration >> Aliases

Delete the alias that is giving problem and recreate it with identical parameters.

I have tried my best to include all the methods of fixing this error and if I have missed any, please leave a comment and I will be very glad to include them here. I have put in my effort to encompass this issue in one article that needs to be refereed when any connection error comes up.

Errors in Connected to sql server

Errors in Connected to sql server

  Symptoms

Each user has a default database. When you connect to computer that is running Microsoft SQL Server, and you do not specify a login database, the default database is used. However, if the default database is unavailable at the time of the connection, you may not be able to connect. Instead, you receive error message 4062 or error message 4064. The text of the error messages is as follows:

Cannot open user default database

  Cause

The user default database is unavailable at the time of connection. It is possible that the database:

·         Is in suspect mode.

·         No longer exists.

·         Is in single user mode and the only available connection is already being used by someone else or by something else.

·         Has been detached.

·         Has been set to the RESTRICTED_USER state.

·         Is offline.

·         Is set to emergency status.

·         Does not have the login account mapped to a user or the user has been denied access.

·         Is part of a database mirror.

Additionally, the login account may be a member of multiple groups and the default database for one of those groups is unavailable at the time of connection.

  Workaround

To work around this behavior, specify a valid, available database in the connection string. To avoid the error when the user's default database is unavailable, log on as a user who can modify logins. Then, change the user's default database to a database that is currently available for a connection.

SQL Server 2005 and later versions

You can use the sqlcmd utility to change the default database in SQL Server 2005. To do this, follow these steps:

1.       Click Start, click Run, type cmd, and then press ENTER.

2.       Use one of the following methods, depending on the kind of authentication that the SQL Server login uses:

o    If the SQL Server login uses Microsoft Windows authentication to connect to the instance, type the following at the command prompt, and then press ENTER:

sqlcmd –E -S InstanceName –d master

o    If the SQL Server login uses SQL Server authentication to connect to the instance, type the following at the command prompt, and then press ENTER:

sqlcmd -S InstanceName -d master -U SQLLogin -P Password

3.       Note InstanceName is a placeholder for the name of the SQL Server 2005 instance to which you are connecting. SQLLoginis a placeholder for the SQL Server login whose default database has been dropped. Password is a placeholder for the SQL Server login password.

4.       At the sqlcmd prompt, type the following, and then press ENTER:

ALTER LOGIN SQLLogin WITH DEFAULT_DATABASE = AvailDBName

Note AvailDBName is a placeholder for the name of the existing database that can be accessed by the SQL Server login in the instance.

5.       At the sqlcmd prompt, type GO, and then press ENTER.

SQL Server 2000 and SQL Server 7.0

You can use the osql utility to change the default database in SQL Server 2000 and in SQL Server 7.0. To do this, follow these steps:

1.       At a command prompt, type the following and then press ENTER:

C:\>osql -E -d master

2.       At the osql prompt, type the following and then press ENTER:

1>sp_defaultdb 'user's_login', 'master'

3.       At the second prompt, type the following and then press ENTER:

2>go

  Status

This behavior is by design.

  More information

To demonstrate this behavior, try to connect with OSQL and use the -d option with an existing database that is in a normal online status. The connection succeeds without any error message.

Conversely, you receive responses that are similar to the following from various SQL Server client utilities when the database is unavailable:

·         SQLServer Management Studio (SQL Server 2005)

Cannot connect to ServerName
ADDITIONAL INFORMATION:
Cannot open user default database. Login failed. Login failed for user 'UserName'. (Microsoft SQL Server, Error: 4064)

·         Using Sqlcmd utility in SQL Server 2005

Msg 4064, Level 11, State 1, Server ServerName, Line 1
Cannot open user default database. Login failed.
Msg 18456, Level 14, State 1, Server ServerName, Line 1 Login failed for user 'UserName'.

·         SQL Server 2000 Query Analyzer (QA)

Unable to connect to server TESTSERVER:
Server: Msg 4064, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open user default database. Login failed.

·         SQL Server 2000 Server Enterprise Manager (SEM)

A connection could not be established to TESTSERVER - Cannot open user default database 'dbid'. Using master database instead.

Please verify SQL Server is running and check your SQL Server registration properties (by right-clicking on the TESTSERVER node) and try again.

·         A command prompt using SQL Server 2000 OSQL

You receive the following error message when you use the C:\>osql -E command:

Cannot open user default database. Login failed.

·         SQL Server 7.0 Query Analyzer (QA)

Unable to connect to server \\TESTSERVER:
Server: Msg 4062, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open user default database ''. Using master database instead.

·         A command prompt using SQL Server 7.0 OSQL

You receive the following error message when you use the C:\>osql -E command:

Cannot open user default database 'dbid'.
Using master database instead.

The following table provides more information about the products or tools that automatically check for this condition on your instance of SQL Server and on the versions of the SQL Server product against which the rule is evaluated.