Cannot remotely connect on SQL Server Instances by name.

Hi all,

On my computer I have installed two instances of SQL Server 2000. The first one is the default instance of SQL Server Personal Edition and the second one is an MSDE instance.

From other PCs on the company LAN I can connect, by name, only to the first one and not to the MSDE instance.

I have tried the combination IP Address, TCP Port and it work fine.

Anyone any ideas?

Thanks in advance,

Stavros Makridis.

did you get this fixed ever?|||

Hi,

the named instance is accessible with MachineName\InstanceName,PortNumber in SQL Server 2000.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Jens K. Suessmeyer wrote:

Hi,

the named instance is accessible with MachineName\InstanceName,PortNumber in SQL Server 2000.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

If tcp port is specified in the connection string, the instance name value is ignored.|||UDP port 1434 must be blocked somehow. SQL Browser needs this port to resolve srv\instance to a valid server:tcp_port.|||He is using SQL 2000, so no SQL Browser.|||

Yes there is. Browser is handled by the first instance of sqlserver internally.

|||

I wasn′t aware of that, do you have some links about this topic and this internal feature ?

|||You can find it in bol and on technet.

Here is an excerpt from bol (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3cc00d3a-487c-4cd9-a155-655f02485fa0.htm):
"Background

Prior to Microsoft SQL Server 2000, only one instance of SQL Server could be installed on a computer. SQL Server listened for incoming requests on port 1433, assigned to SQL Server by the official Internet Assigned Numbers Authority (IANA). Only one instance of SQL Server can use a port, so when SQL Server 2000 introduced support for multiple instances of SQL Server, SQL Server Resolution Protocol (SSRP) was developed to listen on UDP port 1434. This listener service responded to client requests with the names of the installed instances, and the ports or named pipes used by the instance. To resolve limitations of the SSRP system, SQL Server 2005 introduces the SQL Server Browser service as a replacement for SSRP."

Cannot remotely connect on SQL Server Instances by name.

Hi all,

On my computer I have installed two instances of SQL Server 2000. The first one is the default instance of SQL Server Personal Edition and the second one is an MSDE instance.

From other PCs on the company LAN I can connect, by name, only to the first one and not to the MSDE instance.

I have tried the combination IP Address, TCP Port and it work fine.

Anyone any ideas?

Thanks in advance,

Stavros Makridis.

did you get this fixed ever?|||

Hi,

the named instance is accessible with MachineName\InstanceName,PortNumber in SQL Server 2000.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Jens K. Suessmeyer wrote:

Hi,

the named instance is accessible with MachineName\InstanceName,PortNumber in SQL Server 2000.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

If tcp port is specified in the connection string, the instance name value is ignored.|||UDP port 1434 must be blocked somehow. SQL Browser needs this port to resolve srv\instance to a valid server:tcp_port.|||He is using SQL 2000, so no SQL Browser.|||

Yes there is. Browser is handled by the first instance of sqlserver internally.

|||

I wasn′t aware of that, do you have some links about this topic and this internal feature ?

|||You can find it in bol and on technet.

Here is an excerpt from bol (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3cc00d3a-487c-4cd9-a155-655f02485fa0.htm):
"Background

Prior to Microsoft SQL Server 2000, only one instance of SQL Server

could be installed on a computer. SQL Server listened for incoming requests on

port 1433, assigned to SQL Server by the official Internet Assigned Numbers

Authority (IANA). Only one instance of SQL Server can use a port, so when SQL

Server 2000 introduced support for multiple instances of SQL Server, SQL Server

Resolution Protocol (SSRP) was developed to listen on UDP port 1434. This

listener service responded to client requests with the names of the installed

instances, and the ports or named pipes used by the instance. To resolve

limitations of the SSRP system, SQL Server 2005 introduces the SQL Server

Browser service as a replacement for SSRP."

Cannot register SQL Server 2000SP3 from client computer

I have a sql server residing on a win2k3 box. It is a new installation
and there is no data in the server at all besides default data. I have
my workstation that I have installed client tools on. I am attempting
to log into the sql server via the enterprise manager. I decided to
use the sql account sa to login. I figured if any account would get in
it would be that one. However I am mistaken because it seems that no
matter what I try I cannot get it to work. I get the following errror:
SQL Server does not exist or access denied.ConnectionOpne(Connect())
Any help would be greatly appreciated...
Graham Hill
grahamhil@.yahoo.com
Hi,
This error will come due to below reasons:-
1. Network issue - Not able to connect from client to SQL server machine
2. SQL server Service might be down
3. Connecting to wrong machine
Can you try creating a Alias of the SQL server in you client using "CLient
Network utility". USe the TCP/IP protocol , IP and port number of server.
After that use this Alias name to register in enterprise manager.
See the below link.
http://support.microsoft.com/default...;en-us;Q328306
Thanks
Hari
MCDBA
"G-ram" <grahamhil@.yahoo.com> wrote in message
news:55739f20.0407211734.8345d96@.posting.google.co m...
> I have a sql server residing on a win2k3 box. It is a new installation
> and there is no data in the server at all besides default data. I have
> my workstation that I have installed client tools on. I am attempting
> to log into the sql server via the enterprise manager. I decided to
> use the sql account sa to login. I figured if any account would get in
> it would be that one. However I am mistaken because it seems that no
> matter what I try I cannot get it to work. I get the following errror:
> SQL Server does not exist or access denied.ConnectionOpne(Connect())
> Any help would be greatly appreciated...
> Graham Hill
> grahamhil@.yahoo.com
|||Graham,
If you have a firewall on either computer, please disable it for tests
and see what happens.
Also let us know what authentication mode you have set up - either
Windows Authentication mode, Mixed mode, or SQL Server mode. If yiou
selected Windows Authentication then you may not be able to log in
under the SA account.
Lou Arnold
On 21 Jul 2004 18:34:05 -0700, grahamhil@.yahoo.com (G-ram) wrote:

>I have a sql server residing on a win2k3 box. It is a new installation
>and there is no data in the server at all besides default data. I have
>my workstation that I have installed client tools on. I am attempting
>to log into the sql server via the enterprise manager. I decided to
>use the sql account sa to login. I figured if any account would get in
>it would be that one. However I am mistaken because it seems that no
>matter what I try I cannot get it to work. I get the following errror:
>SQL Server does not exist or access denied.ConnectionOpne(Connect())
>Any help would be greatly appreciated...
>Graham Hill
>grahamhil@.yahoo.com

Cannot register SQL Server 2000SP3 from client computer

I have a sql server residing on a win2k3 box. It is a new installation
and there is no data in the server at all besides default data. I have
my workstation that I have installed client tools on. I am attempting
to log into the sql server via the enterprise manager. I decided to
use the sql account sa to login. I figured if any account would get in
it would be that one. However I am mistaken because it seems that no
matter what I try I cannot get it to work. I get the following errror:
SQL Server does not exist or access denied.ConnectionOpne(Connect())
Any help would be greatly appreciated...
Graham Hill
grahamhil@.yahoo.comHi,
This error will come due to below reasons:-
1. Network issue - Not able to connect from client to SQL server machine
2. SQL server Service might be down
3. Connecting to wrong machine
Can you try creating a Alias of the SQL server in you client using "CLient
Network utility". USe the TCP/IP protocol , IP and port number of server.
After that use this Alias name to register in enterprise manager.
See the below link.
http://support.microsoft.com/defaul...b;en-us;Q328306
Thanks
Hari
MCDBA
"G-ram" <grahamhil@.yahoo.com> wrote in message
news:55739f20.0407211734.8345d96@.posting.google.com...
> I have a sql server residing on a win2k3 box. It is a new installation
> and there is no data in the server at all besides default data. I have
> my workstation that I have installed client tools on. I am attempting
> to log into the sql server via the enterprise manager. I decided to
> use the sql account sa to login. I figured if any account would get in
> it would be that one. However I am mistaken because it seems that no
> matter what I try I cannot get it to work. I get the following errror:
> SQL Server does not exist or access denied.ConnectionOpne(Connect())
> Any help would be greatly appreciated...
> Graham Hill
> grahamhil@.yahoo.com|||Graham,
If you have a firewall on either computer, please disable it for tests
and see what happens.
Also let us know what authentication mode you have set up - either
Windows Authentication mode, Mixed mode, or SQL Server mode. If yiou
selected Windows Authentication then you may not be able to log in
under the SA account.
Lou Arnold
--
On 21 Jul 2004 18:34:05 -0700, grahamhil@.yahoo.com (G-ram) wrote:

>I have a sql server residing on a win2k3 box. It is a new installation
>and there is no data in the server at all besides default data. I have
>my workstation that I have installed client tools on. I am attempting
>to log into the sql server via the enterprise manager. I decided to
>use the sql account sa to login. I figured if any account would get in
>it would be that one. However I am mistaken because it seems that no
>matter what I try I cannot get it to work. I get the following errror:
>SQL Server does not exist or access denied.ConnectionOpne(Connect())
>Any help would be greatly appreciated...
>Graham Hill
>grahamhil@.yahoo.com

Cannot register local sql server

I am having a problem registering my local SQL server.
When I try to use Windows authentication an error msg
appears saying "cannot open default db, using master db
instead" but it doesn't register. When I try using SQL
Server authentication (using the sa logon) a msg appears
saying "login failed for sa, not associated with a trusted
sql server connection".
I previously had the server registered ok and then it
suddenly stopped working so I deleted it and now it won't
reregister. I have no problem registering other SQL
Servers through EM on my pc. Any ideas how to fix this and
maybe what caused the problem in the first place?
Thanks
DaveTry changing your default database (which sounds like it has been dropped)
using osql e.g.
osql -E -Sservername -dmaster -Q"exec sp_defaultdb
'domain\username','master'"
replacing servername with the server you are trying to register and
domain\username with your nt login.Once this is done try registering the
server again. BTW you can also use Query Analyzer if that is more
comfortable by starting it from the command line and passing the database
e.g.
isqlw -E -dmaster -Sservername
and then run the command
exec sp_defaultdb 'domain\username','master'
again, you should now be able to register the server
The reason the sa login doesn't work is because the server you are
connecting to is set to Windows Authentication only.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"dave" <anonymous@.discussions.microsoft.com> wrote in message
news:09d301c3b438$d482b550$a001280a@.phx.gbl...
I am having a problem registering my local SQL server.
When I try to use Windows authentication an error msg
appears saying "cannot open default db, using master db
instead" but it doesn't register. When I try using SQL
Server authentication (using the sa logon) a msg appears
saying "login failed for sa, not associated with a trusted
sql server connection".
I previously had the server registered ok and then it
suddenly stopped working so I deleted it and now it won't
reregister. I have no problem registering other SQL
Servers through EM on my pc. Any ideas how to fix this and
maybe what caused the problem in the first place?
Thanks
Dave|||just change the loginmode to 2 in the registry
--
Shaju Thomas
"dave" <anonymous@.discussions.microsoft.com> wrote in message
news:09d301c3b438$d482b550$a001280a@.phx.gbl...
> I am having a problem registering my local SQL server.
> When I try to use Windows authentication an error msg
> appears saying "cannot open default db, using master db
> instead" but it doesn't register. When I try using SQL
> Server authentication (using the sa logon) a msg appears
> saying "login failed for sa, not associated with a trusted
> sql server connection".
> I previously had the server registered ok and then it
> suddenly stopped working so I deleted it and now it won't
> reregister. I have no problem registering other SQL
> Servers through EM on my pc. Any ideas how to fix this and
> maybe what caused the problem in the first place?
> Thanks
> Dave

Cannot open user default database. User login failed.

Hi,

I'm sorry if this is simple, I'm no DBA but have been tasked with solving this problem...

We have a website that connects via ODBC to SQL Server (2k sp1) and at the moment I am getting back about every other time:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open user default database. Login failed.

So this is NOT happening every single time. Now I have seen Microsoft KB - 307864 and I can see that none of the databases are marked as suspect, the database I am trying to connect to does exist and is attached and, I have run the command to switch the database to multi-user mode.

The probable cause of this problem is that a while ago we had a hard-drive failure and I was forced to reattach some old datafiles (mdf,ldf) as the database. This seemed ok and I can view data etc in enterprise manager no problem.

I have checked for orphaned users and the user I am logging in with from the webpage is not listed.

So does anyone have a clue as to why this is happening, and more frustratingly for me, why is it only happening some of the time.

Thanks for your help, appreciated.

James.Edit your odbc connection and check the default database drop down box...it's probably set to master...|||Brett - Thanks for posting.

Have checked the ODBC settings and the database is not set to master as default .|||Originally posted by JDMoore
Brett - Thanks for posting.

Have checked the ODBC settings and the database is not set to master as default .

What ODBC says if you are trying to test data source under this account?
If everything is fine with ODBC - check your application...|||ODBC says that the connection is fine.

The application has not been changed and has worked fine so I'm virtually certainly it's nothing to do with the program code. The code in question is simply opening a connection to the database.

As I say the error is sporadic presumably an ODBC connection string can either be right or wrong?|||Remotely, but possible, that the default database is in single user mode, and if you login successfully, it means that you're the first one to show up, while when you get an error, then someone's already there (that's to attempt to explain the sporadic nature of the error.) But to be proactive, open EM and go to that database (make sure to register the server with sysadmin privileges.) Check the Options tab of the properties to ensure that the database is not in single user mode. Also, go to Security folder, then to Logins and open properties for the account that the app uses to login and set the default database to master. This is to prevent the situation when a different database was renamed to the one that is specified now as default. And finally, speaking about renamed databases, can you check if it happened recently?|||You might also want to check the connection type in the ODBC. There are times when it doesn't matter whether your using Pipes or Ip but it will test just fine then throw sporadic error messages when actually using the application.

I would try going into the client config button (2nd screen in the ODBC) and if it's set to Pipes then make it IP (port 1433 is default but may have to be changed depending on network) or vice versa.|||Run sql profiler to see what logins are attempting to connect - I am curious to see if the problem is that the iuser account is failing to connect ...|||Thanks for all the suggestions. I am not in until Monday now but when I get in, I will try everything and then get back to you all.

Thanks for posting.|||As always it turned out to be pretty simple...

As I looked further through the code it turned out that the program was looking at this database and then looking at a database on a different server.

When I went to the other server it turned out that THIS database was in single-user mode and hence the sporadic nature of the problem was explained.

Thanks very much for everyone's contribution, probably wouldn't have stumbled over this for ages without your suggestions.

James.

Cannot open user default database. Login failed?

Hi All,
I have not been using my SQL server 2000 for a while on this particular
machine. I had my account disabled and just got it re-enabled at work.
Login name is the same and password has changed as it does every 30 days. I
am trying to get into my server but keep getting the following error
message:
"SQL Server registration Failed because of the connection failure displayed
below. Do you wish to Register anyway?"
"Cannot open user default database. Login Failed"
I am at a loss and if anyone could suggest something to me other than
un-installing the instance and re-installing it, I would be appreciative.
Thanks,
JohnSeems like your account doesnt´have access to the default database, just try
to EXEC sp_grantdbaccess
with an administrative account for this user.
HTH, Jens Süßmeyer.
"John Rugo" <jwrugo@.patmedia.net> schrieb im Newsbeitrag
news:ObRFOWIfDHA.2320@.TK2MSFTNGP12.phx.gbl...
> Hi All,
> I have not been using my SQL server 2000 for a while on this particular
> machine. I had my account disabled and just got it re-enabled at work.
> Login name is the same and password has changed as it does every 30 days.
I
> am trying to get into my server but keep getting the following error
> message:
> "SQL Server registration Failed because of the connection failure
displayed
> below. Do you wish to Register anyway?"
> "Cannot open user default database. Login Failed"
> I am at a loss and if anyone could suggest something to me other than
> un-installing the instance and re-installing it, I would be appreciative.
> Thanks,
> John
>|||John,
See if this helps...
'Locked Out of SQL Server - Q. I locked myself out of SQL Server without
being able to log on with a sysadmin login. Can I fix this, or do I need to
reinstall SQL Server'
http://www.microsoft.com/sql/techinfo/tips/administration/May3.asp
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"John Rugo" <jwrugo@.patmedia.net> wrote in message
news:ehaGFkIfDHA.1764@.TK2MSFTNGP09.phx.gbl...
> Unfortunately I can't get in as sa or any other login account using osql
or
> enterpise mgr or sql analyzer. I think I will have to uninstall the
> instance and re-install it. the odd thing is that SA doesn't even work.
> Thanks for you help.
> "Jens Süßmeyer" <jsuessmeyer@.[REJECT_SPAM]web.de> wrote in message
> news:OfsbMfIfDHA.1872@.TK2MSFTNGP09.phx.gbl...
> > Seems like your account doesnt´have access to the default database, just
> try
> > to EXEC sp_grantdbaccess
> > with an administrative account for this user.
> >
> > HTH, Jens Süßmeyer.
> >
> > "John Rugo" <jwrugo@.patmedia.net> schrieb im Newsbeitrag
> > news:ObRFOWIfDHA.2320@.TK2MSFTNGP12.phx.gbl...
> > > Hi All,
> > >
> > > I have not been using my SQL server 2000 for a while on this
particular
> > > machine. I had my account disabled and just got it re-enabled at
work.
> > > Login name is the same and password has changed as it does every 30
> days.
> > I
> > > am trying to get into my server but keep getting the following error
> > > message:
> > >
> > > "SQL Server registration Failed because of the connection failure
> > displayed
> > > below. Do you wish to Register anyway?"
> > >
> > > "Cannot open user default database. Login Failed"
> > >
> > > I am at a loss and if anyone could suggest something to me other than
> > > un-installing the instance and re-installing it, I would be
> appreciative.
> > >
> > > Thanks,
> > > John
> > >
> > >
> >
> >
>

Cannot open user default database. Login failed. Login failed for user 'UserName'. (Microsoft

Hi,

SQL Server 2005 installed on my computer. i was trying to make a login page with VS.2005 pro. But i got some errors on Website Administration Tool. Whatsoever, i registered a database (aspnetdb) . Then, i think i made my default database to aspnetdb. Something went wrong and i decided to delete aspnetdb and start whole procces from the begining and i did it. AAANDD WHOOOLA. I got this error:

Cannot Open User Default Database, Login Failed
Microsoft SQL Server, Error 4064

Is there anyway to change my default database to "master" again? Or simply will i uninstall all the sql server 2005?
(?f you say uninstall, answer this:: Will be any problem on VS 2005? I can't uninstall both :) )

You should determine what is the login you are connecting at, then login as administrator and issue an ALTER LOGIN statement to set the default database for that login to master. See Books Online for additional information on using the ALTER LOGIN statement.

Thanks
Laurentiu|||I don't think that Laurentiu understands the problem. I have run into the same situation, I am an administrator on the server, because the default database no longer exists (I had changed it from master) I can no longer connect. I would like to think that in the absence of my default database, it would revert to the master, but this is not the case. I'm unable to connect using the Administrator login to execute the ALTER LOGIN command.|||

You can resolve this by specifying another database you want to connect to; master, for example. Then you can reset the login's default database. With sqlcmd, you can do the following:

sqlcmd -d master -U sa -P mypassword

and then:

alter login sa with default_database = master

Thanks
Laurentiu

|||I've googled this error message and I get a hundred hits. Shouldn't there be a KB? I have exactly the same problem. I changed the default DB, then dropped it. I have no idea what the sa password is. Do I need to uninstall/reinstall SS05?|||

I already posted an answer above. You just need to specify a different database to use at connection time, then change your default database to an existing one.

I'll check to see if we have a KB article on this and pass your feedback to write one if we don't already have it.

Thanks
Laurentiu

|||

I see now that you mentioned that you don't know sa's password.

sa was just provided as an example, for this operation you don't necessarily need to use sa to connect. You just need to connect with the login for which you've received this error. If you use Windows authentication, then you should connect like this:

sqlcmd -E -d master

then issue the ALTER LOGIN statement.

It's the "-d master" that's the key here - it specifies that the connection should be made to the master database, so the login won't try to connect to the database that no longer exists.

Hope this makes the solution clearer. If you still have problems with resetting the default database, let us know.

Thanks
Laurentiu

|||To me this seems like a bug with Sql Server Management Studio, as you can connect to the database by specifing the database under options. You cannot however change the user's default database as right click -> properties tries to make a new connection using the default database. As far as I can see, it should create the new connection with the same database specified in the options section?|||

You should post to SQL Server Tools General, for Management Studio issues. You can also search for existing bugs on this issue or open a new one at: http://lab.msdn.microsoft.com/productfeedback/.

Thanks
Laurentiu

|||

I got the same error with SQL Server Management Studio and could not resolve it with sa authentication because my config wouldn't let me log in as sa.

Selecting master as the connect database in the login dialog got me in. I then tried to edit my login object to change my default database, but this fails with the same error!

I then tried deleting my login. Failed again with error 4064.

Finally, I tried to right-click on the login object, and selected Script Login As > DROP to > New Query Editor Window, and then hit Execute in the view that appeared. It worked!

Hope this helps someone out there...

|||

I just ran into the same issue after I dropped a database that was mistakenly set to be the default database. I found the information posted here pointed me in the right direction, but it didn't really help much because I couldn't figure out which user should be modified when SQL Server 2005 is configured to use Windows authentication (e.g. trusted connections) exclusively. After a lot of digging through SQL Server Books Online, executing various stored procedures to dump login information, and experimentation, I finally stumbled on the right combination.

C:\> sqlcmd -E -d master

1> ALTER LOGIN [BUILTIN\Administrators] WITH DEFAULT_DATABASE=master

2> GO

I show the prompts above so you can see where to type the commands and the expected results (e.g. SQLCMD does not output any information or error messages).

|||

I detached a database on a network server with the idea of reattaching an updated version from my local db.

Evidently, the database I detached was the default database. I couldn't log in now because of the posts in this thread. I was able to log in using the Master db at the login dialog. However, I've tried everything in this post, and nothing seems to work!

When I try to execute a query against the master db (such as the sql in these posts), it executes fine. In the drop down list of available databases, the only thing it has is master... the rest says "server is not available". I'm able to ping it just fine.

I can't even restore the db I detached, because I get the same error msgs as not being able to log on. I desperately need to attach this database again with master as the default db.

How can I accomplish this? Can someone please help?

|||

The only thing I can do with logging in as master, is simply just to look at objects. I cannot perform any action at all against anything because of the same error message (Cannot open user default database. Login failed for user sa...).

How can I resolve this!!!!

Microsoft, please help..........

|||For what it's worth, the db icazon next to the server is simply a white circle (no activity, etc)....|||

I tried what you're mentioning Chris and it didn't work.

In the query window I have the following:

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'sa')

DROP LOGIN [sa]

The results state the following:

Msg 15405, Level 16, State 1, Line 3

Cannot use the special principal 'sa'.

Cannot open user default database. Login failed. Login failed for user 'UserName'. (Microsoft

Hi,

SQL Server 2005 installed on my computer. i was trying to make a login page with VS.2005 pro. But i got some errors on Website Administration Tool. Whatsoever, i registered a database (aspnetdb) . Then, i think i made my default database to aspnetdb. Something went wrong and i decided to delete aspnetdb and start whole procces from the begining and i did it. AAANDD WHOOOLA. I got this error:

Cannot Open User Default Database, Login Failed
Microsoft SQL Server, Error 4064

Is there anyway to change my default database to "master" again? Or simply will i uninstall all the sql server 2005?
(?f you say uninstall, answer this:: Will be any problem on VS 2005? I can't uninstall both :) )

You should determine what is the login you are connecting at, then login as administrator and issue an ALTER LOGIN statement to set the default database for that login to master. See Books Online for additional information on using the ALTER LOGIN statement.

Thanks
Laurentiu|||I don't think that Laurentiu understands the problem. I have run into the same situation, I am an administrator on the server, because the default database no longer exists (I had changed it from master) I can no longer connect. I would like to think that in the absence of my default database, it would revert to the master, but this is not the case. I'm unable to connect using the Administrator login to execute the ALTER LOGIN command.|||

You can resolve this by specifying another database you want to connect to; master, for example. Then you can reset the login's default database. With sqlcmd, you can do the following:

sqlcmd -d master -U sa -P mypassword

and then:

alter login sa with default_database = master

Thanks
Laurentiu

|||I've googled this error message and I get a hundred hits. Shouldn't there be a KB? I have exactly the same problem. I changed the default DB, then dropped it. I have no idea what the sa password is. Do I need to uninstall/reinstall SS05?|||

I already posted an answer above. You just need to specify a different database to use at connection time, then change your default database to an existing one.

I'll check to see if we have a KB article on this and pass your feedback to write one if we don't already have it.

Thanks
Laurentiu

|||

I see now that you mentioned that you don't know sa's password.

sa was just provided as an example, for this operation you don't necessarily need to use sa to connect. You just need to connect with the login for which you've received this error. If you use Windows authentication, then you should connect like this:

sqlcmd -E -d master

then issue the ALTER LOGIN statement.

It's the "-d master" that's the key here - it specifies that the connection should be made to the master database, so the login won't try to connect to the database that no longer exists.

Hope this makes the solution clearer. If you still have problems with resetting the default database, let us know.

Thanks
Laurentiu

|||To me this seems like a bug with Sql Server Management Studio, as you can connect to the database by specifing the database under options. You cannot however change the user's default database as right click -> properties tries to make a new connection using the default database. As far as I can see, it should create the new connection with the same database specified in the options section?|||

You should post to SQL Server Tools General, for Management Studio issues. You can also search for existing bugs on this issue or open a new one at: http://lab.msdn.microsoft.com/productfeedback/.

Thanks
Laurentiu

|||

I got the same error with SQL Server Management Studio and could not resolve it with sa authentication because my config wouldn't let me log in as sa.

Selecting master as the connect database in the login dialog got me in. I then tried to edit my login object to change my default database, but this fails with the same error!

I then tried deleting my login. Failed again with error 4064.

Finally, I tried to right-click on the login object, and selected Script Login As > DROP to > New Query Editor Window, and then hit Execute in the view that appeared. It worked!

Hope this helps someone out there...

|||

I just ran into the same issue after I dropped a database that was mistakenly set to be the default database. I found the information posted here pointed me in the right direction, but it didn't really help much because I couldn't figure out which user should be modified when SQL Server 2005 is configured to use Windows authentication (e.g. trusted connections) exclusively. After a lot of digging through SQL Server Books Online, executing various stored procedures to dump login information, and experimentation, I finally stumbled on the right combination.

C:\> sqlcmd -E -d master

1> ALTER LOGIN [BUILTIN\Administrators] WITH DEFAULT_DATABASE=master

2> GO

I show the prompts above so you can see where to type the commands and the expected results (e.g. SQLCMD does not output any information or error messages).

|||

I detached a database on a network server with the idea of reattaching an updated version from my local db.

Evidently, the database I detached was the default database. I couldn't log in now because of the posts in this thread. I was able to log in using the Master db at the login dialog. However, I've tried everything in this post, and nothing seems to work!

When I try to execute a query against the master db (such as the sql in these posts), it executes fine. In the drop down list of available databases, the only thing it has is master... the rest says "server is not available". I'm able to ping it just fine.

I can't even restore the db I detached, because I get the same error msgs as not being able to log on. I desperately need to attach this database again with master as the default db.

How can I accomplish this? Can someone please help?

|||

The only thing I can do with logging in as master, is simply just to look at objects. I cannot perform any action at all against anything because of the same error message (Cannot open user default database. Login failed for user sa...).

How can I resolve this!!!!

Microsoft, please help..........

|||For what it's worth, the db icazon next to the server is simply a white circle (no activity, etc)....|||

I tried what you're mentioning Chris and it didn't work.

In the query window I have the following:

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'sa')

DROP LOGIN [sa]

The results state the following:

Msg 15405, Level 16, State 1, Line 3

Cannot use the special principal 'sa'.

sql

Cannot open user default database. Login failed. (only when IDE is open)

I get the below error only when my IDE open. It connects well when it is found closed.

[SqlException (0x80131904): Cannot open user default database. Login failed.
Login failed for user 'JP\ASPNET'.]

I could solve this by giving the logged in windows user to impersonate under IIS window > WEBSITE > ASP.NET tab > EDIT CONFIG > APPLICATION tab

But I wish someone could give me the proper solution.

I almost tried all from giving ASPNET user as a administrator to configuring the same in Express management tool.

Environment: XP pro, VWD and SQL Express

The problem i think is that u-r user that u r using has default database and that dont exist, to correct that u must go in the property of that user in Sql menagment system and set other default databse for that user..

I hope this will help

Sorry for my bad english

|||

I believe that when you have the database opened as "user instance" it can not be opened by more than one application, like Visual Studio and SQL Server Management Express, etc.

|||

Hi jpbiznes,

Could you please explain a little more?

where did you get that error message? From VWD or from IIS ? I guess it is from IIS since if you are using VWD accessing database, the account should be the one which you use to run VWD -- in this case the account won't be ASPNET.

I get the below error only when my IDE open.

There is a possibility that if you have attached your database file from one process, you cannot attach it again from another process. Since one physical database file cannot be attached by two different instances (note we use customer instance in sql2005 express). But in that case the error message should look lik: cannot attach database again since database file is currently in use.

Also, I performed a search through internet and find this KB article which may be helpful to you:

SYMPTOMS

loadTOCNode(1, '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

loadTOCNode(1, '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.

http://support.microsoft.com/?id=307864

Hope my suggestion helps

Cannot open user default database. Login failed.

Hi,

I got this application system having SQLExpress as database (ABCSystem.mdf). I'm connecting to the database thru connection string specifying database path in App.config.

At the same time, I got two paths for the mdf files. The first one is the path for my database project while the second one is the path to conduct testing. (This means that I got identical ABCSystem.mdf in two paths)

1) C:\Program Files\Company ABC\ABCDatabaseProject\

2) C:\Program Files\Company ABC Limited\ABCSystem\Database\

In my development machine, I can run my application where database path is testing path (number 2).

When I tried to copy the ABCSystem.mdf and ABCSystem_log.LDF and my application program files to another PC to test, I got the subject error message. (note that I copy program files under directory "C:\Program Files\Company ABC Limited\ABCSystem\" and mdf files under "C:\Program Files\Company ABC Limited\ABCSystem\Database\")

I try to edit path from database testing path to database project path in app.config, run the application, and notice that it is running OK.

What seems to cause the problem? Coz I need the database in the testing path which is "C:\Program Files\Company ABC Limited\ABCSystem\Database\" AND NOT "C:\Program Files\Company ABC\ABCDatabaseProject\".

Thanks alot for your kind attention.

Try this first, and then check your string accordingly:

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

Buck Woody

|||I'm having this identical issue with an ODBC connection.
After wasting hours, I realized I only get the error when I try to change database name. (e.g. from a development to a production server). Based upon everything I've tried, I determined you CANNOT change server name in a Crystal report programmatically as in the Crystal sample code. It may be a possible to change servers if using a command based report (explicit SQL query), but our reports all use tables so a trial would be irrelevant.

Cannot open user default database. Login failed.

Cannot open user default database. Login failed.
Login failed for user '[machine name]\ASPNET'.

I'm working in VS 2005 with a starter kit. I close VS and open an MDF file in SQL Server 2005 Management Studio to modify a table. I attach the file, change a data type, then detach the file. When I go back to VS and try to run the program, I get this error. Everything runs normally before I bring the file into Management Studio. It is not a permissions issue. Does SQL Server put some kind of lock on the file? This must be a fairly common problem. A developer will sometimes want to modify an .mdf file. How does he do that and then run his program? Thank you for any suggestions I can try.

What authentication is this instance. Post your connection string... this seems to be the problem with ASPNET user. The "ASPNET" user account is created in Windows XP by Microsoft .NET Framework. In the connection string you use a SQL Server Login and try.

From the link mentioned below :-

Applications that use SQL Authentication to connect to a database are not generally affected by using the ASPNET account. Applications that use integrated authentication and impersonation are also not generally affected. However, if an application is not impersonating and is using Integrated Authentication, you must grant access to the database for the ASPNET account.

You cannot use the ASPNET account when you try to authenticate to Microsoft SQL Server by using Integrated Authentication over named pipes. However, you can use the ASPNET account together with Integrated Authentication over the Transmission Control Protocol (TCP) transport.

If an application must use a Microsoft Access database, the ASPNET account must be able to write to the database file. Administrators must change the file permissions to allow this use.

Refer :

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

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

Madhu

|||

Hi

Seen that before.

Someone has modified your login or the default database is unavailable.

You can use the following command to log on using Query Analyser. Run it the in the RUN window.

Code Snippet

isqlw -S servername -E (-U username -P password if using sql login) -d master

This will log you on to master database and then you can check for any problems or reset the default database using sp_defaultdb.

hope that helps

Jag

Cannot open user default database. Login failed.

Hi,

I got this application system having SQLExpress as database (ABCSystem.mdf). I'm connecting to the database thru connection string specifying database path in App.config.

At the same time, I got two paths for the mdf files. The first one is the path for my database project while the second one is the path to conduct testing. (This means that I got identical ABCSystem.mdf in two paths)

1) C:\Program Files\Company ABC\ABCDatabaseProject\

2) C:\Program Files\Company ABC Limited\ABCSystem\Database\

In my development machine, I can run my application where database path is testing path (number 2).

When I tried to copy the ABCSystem.mdf and ABCSystem_log.LDF and my application program files to another PC to test, I got the subject error message. (note that I copy program files under directory "C:\Program Files\Company ABC Limited\ABCSystem\" and mdf files under "C:\Program Files\Company ABC Limited\ABCSystem\Database\")

I try to edit path from database testing path to database project path in app.config, run the application, and notice that it is running OK.

What seems to cause the problem? Coz I need the database in the testing path which is "C:\Program Files\Company ABC Limited\ABCSystem\Database\" AND NOT "C:\Program Files\Company ABC\ABCDatabaseProject\".

Thanks alot for your kind attention.

Try this first, and then check your string accordingly:

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

Buck Woody

|||I'm having this identical issue with an ODBC connection.
After wasting hours, I realized I only get the error when I try to change database name. (e.g. from a development to a production server). Based upon everything I've tried, I determined you CANNOT change server name in a Crystal report programmatically as in the Crystal sample code. It may be a possible to change servers if using a command based report (explicit SQL query), but our reports all use tables so a trial would be irrelevant.

Cannot open user default database. Login failed

Hi,

I have installed SQL Server 2005 Express edition and when i try to run the web site as http://127.0.0.1/SiteName/Login.aspx. I am using default SQL Provider.

Can anyoone please help me? i was struck here from last two days.

System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.

Login failed for user 'XXXXXX\ASPNET'.

Regards,

Pradeep.

hi Pradeep,

please verify the default database for \ASPNET login is available on the target server...

regards

|||See the Security matrix form here:

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

You either can create the ASPNET user on the server, create another one and specify it within the web.config file, or specify in the web.config file, that you want to use impersonation. (Depending on your needs)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi

Thank U verymuch for the reply. I have created the ASPNET User using Microsoft SQL Server Management Studio Express but the result is same.

How can i see the default database for ASPNET user....

Regards,

Pradeep.

|||

Hi

Thank U for the reply. I have created ASPNET user in the database using Microsoft SQL server Management Studio Express and in web config file i have added

<identity impersonate="true"/>

<authentication mode="Forms" />

but still the reult is same as

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.
Login failed for user 'MIS-2\ASPNET'.

Hope this might be bug in SQL Server in SQL Server 2005 Express edition.

Regards,

Pradeep

|||

Hi,

ok we are getting closer. The error is related to the fact that you want to redirect to a database where is not granted any access to. Automatic redirection of Users is done in either of the two ways. You have a connection string where no "Inital Catalog"
keyword is specified, then the user will be redirected to his default database which every user has. If you want to change that, you can use the GUI and set the default database to another one where he is granted access to. (You can also use the sp_defaultdb procedure if you prefer the non-Gui version). if you mentioned the "Initial Catalog" in the catalog you will have to make sure that the connecting user (in your case the
ASPNET user) has access to the database mentioned in as the keyword value.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi,

I am using the default sql provider model.

one more thing is i am having sql server 2000 in (mixed mode authentcation) and sql server 2005 express edition(in windows authentication), is there any problem related to this.

in sql server 2000 i have given the ASPNET user defult database as master and in sql server 2005 i did the same.

so, does these settings has any impact on the default provider model in ASP.NET 2.0?

Regards,

Pradeep

|||

Hi Pradeep,

The error generally comes when the user tried to connect Database for which he don't have access permission so you may use sp_grantdbaccess to grant db access permission and refer below KBs for more

http://support.microsoft.com/kb/321698/en-us

http://support.microsoft.com/kb/316989/en-us

http://support.microsoft.com/kb/824308/en-us

http://support.microsoft.com/kb/821498/en-us

http://support.microsoft.com/kb/308157/en-us

http://support.microsoft.com/kb/301240/en-us

Hemantgiri S. Goswami

|||

An answer provided by monishjain from ASP.net forum

Steps (Windows XP):

Go to Control Panel -> Administrative Tools -> Computer Management -> Local Users and Groups -> Users -> ASPNET

Right Click on ASPNET and select Properties

Go to Member Of tab.

Click on Add -> Advanced -> Find Now

Select SQLServer2005MSSQLUser$<machinename>$SQLExpress

Click on Ok -> Ok - >Apply

Close all the windows and reboot your machine. It should work now.

Cannot open user default database. Login failed

Hi,

I have installed SQL Server 2005 Express edition and when i try to run the web site as http://127.0.0.1/SiteName/Login.aspx. I am using default SQL Provider.

Can anyoone please help me? i was struck here from last two days.

System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.

Login failed for user 'XXXXXX\ASPNET'.

Regards,

Pradeep.

hi Pradeep,

please verify the default database for \ASPNET login is available on the target server...

regards

|||See the Security matrix form here:

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

You either can create the ASPNET user on the server, create another one and specify it within the web.config file, or specify in the web.config file, that you want to use impersonation. (Depending on your needs)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi

Thank U verymuch for the reply. I have created the ASPNET User using Microsoft SQL Server Management Studio Express but the result is same.

How can i see the default database for ASPNET user....

Regards,

Pradeep.

|||

Hi

Thank U for the reply. I have created ASPNET user in the database using Microsoft SQL server Management Studio Express and in web config file i have added

<identity impersonate="true"/>

<authentication mode="Forms" />

but still the reult is same as

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.
Login failed for user 'MIS-2\ASPNET'.

Hope this might be bug in SQL Server in SQL Server 2005 Express edition.

Regards,

Pradeep

|||

Hi,

ok we are getting closer. The error is related to the fact that you want to redirect to a database where is not granted any access to. Automatic redirection of Users is done in either of the two ways. You have a connection string where no "Inital Catalog"
keyword is specified, then the user will be redirected to his default database which every user has. If you want to change that, you can use the GUI and set the default database to another one where he is granted access to. (You can also use the sp_defaultdb procedure if you prefer the non-Gui version). if you mentioned the "Initial Catalog" in the catalog you will have to make sure that the connecting user (in your case the
ASPNET user) has access to the database mentioned in as the keyword value.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

I am using the default sql provider model.

one more thing is i am having sql server 2000 in (mixed mode authentcation) and sql server 2005 express edition(in windows authentication), is there any problem related to this.

in sql server 2000 i have given the ASPNET user defult database as master and in sql server 2005 i did the same.

so, does these settings has any impact on the default provider model in ASP.NET 2.0?

Regards,

Pradeep

|||

Hi Pradeep,

The error generally comes when the user tried to connect Database for which he don't have access permission so you may use sp_grantdbaccess to grant db access permission and refer below KBs for more

http://support.microsoft.com/kb/321698/en-us

http://support.microsoft.com/kb/316989/en-us

http://support.microsoft.com/kb/824308/en-us

http://support.microsoft.com/kb/821498/en-us

http://support.microsoft.com/kb/308157/en-us

http://support.microsoft.com/kb/301240/en-us

Hemantgiri S. Goswami

|||

An answer provided by monishjain from ASP.net forum

Steps (Windows XP):

Go to Control Panel -> Administrative Tools -> Computer Management -> Local Users and Groups -> Users -> ASPNET

Right Click on ASPNET and select Properties

Go to Member Of tab.

Click on Add -> Advanced -> Find Now

Select SQLServer2005MSSQLUser$<machinename>$SQLExpress

Click on Ok -> Ok - >Apply

Close all the windows and reboot your machine. It should work now.

Cannot open user default database. Login failed

Hi,

I have installed SQL Server 2005 Express edition and when i try to run the web site as http://127.0.0.1/SiteName/Login.aspx. I am using default SQL Provider.

Can anyoone please help me? i was struck here from last two days.

System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.

Login failed for user 'XXXXXX\ASPNET'.

Regards,

Pradeep.

hi Pradeep,

please verify the default database for \ASPNET login is available on the target server...

regards

|||See the Security matrix form here:

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

You either can create the ASPNET user on the server, create another one and specify it within the web.config file, or specify in the web.config file, that you want to use impersonation. (Depending on your needs)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi

Thank U verymuch for the reply. I have created the ASPNET User using Microsoft SQL Server Management Studio Express but the result is same.

How can i see the default database for ASPNET user....

Regards,

Pradeep.

|||

Hi

Thank U for the reply. I have created ASPNET user in the database using Microsoft SQL server Management Studio Express and in web config file i have added

<identity impersonate="true"/>

<authentication mode="Forms" />

but still the reult is same as

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.
Login failed for user 'MIS-2\ASPNET'.

Hope this might be bug in SQL Server in SQL Server 2005 Express edition.

Regards,

Pradeep

|||

Hi,

ok we are getting closer. The error is related to the fact that you want to redirect to a database where is not granted any access to. Automatic redirection of Users is done in either of the two ways. You have a connection string where no "Inital Catalog"
keyword is specified, then the user will be redirected to his default database which every user has. If you want to change that, you can use the GUI and set the default database to another one where he is granted access to. (You can also use the sp_defaultdb procedure if you prefer the non-Gui version). if you mentioned the "Initial Catalog" in the catalog you will have to make sure that the connecting user (in your case the
ASPNET user) has access to the database mentioned in as the keyword value.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

I am using the default sql provider model.

one more thing is i am having sql server 2000 in (mixed mode authentcation) and sql server 2005 express edition(in windows authentication), is there any problem related to this.

in sql server 2000 i have given the ASPNET user defult database as master and in sql server 2005 i did the same.

so, does these settings has any impact on the default provider model in ASP.NET 2.0?

Regards,

Pradeep

|||

Hi Pradeep,

The error generally comes when the user tried to connect Database for which he don't have access permission so you may use sp_grantdbaccess to grant db access permission and refer below KBs for more

http://support.microsoft.com/kb/321698/en-us

http://support.microsoft.com/kb/316989/en-us

http://support.microsoft.com/kb/824308/en-us

http://support.microsoft.com/kb/821498/en-us

http://support.microsoft.com/kb/308157/en-us

http://support.microsoft.com/kb/301240/en-us

Hemantgiri S. Goswami

|||

An answer provided by monishjain from ASP.net forum

Steps (Windows XP):

Go to Control Panel -> Administrative Tools -> Computer Management -> Local Users and Groups -> Users -> ASPNET

Right Click on ASPNET and select Properties

Go to Member Of tab.

Click on Add -> Advanced -> Find Now

Select SQLServer2005MSSQLUser$<machinename>$SQLExpress

Click on Ok -> Ok - >Apply

Close all the windows and reboot your machine. It should work now.

Cannot Open User Default Database. Login Failed

Yesterday was not my best day. I disconnected my database to make a copy of it and forgot to reconnect it before closing SQL Server 2000.

Now when I try ot access the database I get message.

Cannot Open User Default Database. Login Failed

Is there a way to attach the database again or a work around for this?

Thanks in advance for your help!You should be able to get an admin to change your default database to pubs, or northwind. Then you would be able to log on, anyway. I am not sure if you would be able to re-attach the database without an admin, though.|||Use the osql.exe utility to run sp_defaultdb and change the default database.sql

Cannot open user default database, login fails

hi:
I got the problem, which lots of people might encounter before.

In my case, I use login control and sqlexpress to build a login page.
when password & user name were entered. It gave me:
"Cannot open user default database, login fails"

The solution given by msdn is:

SQL Server 2005

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:

?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

?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

Note InstanceName is a placeholder for the name of the SQL Server 2005 instance to which you are connecting. SQLLogin is a placeholder for the SQL Server login whose default database has been dropped. Password is a placeholder for the SQL Server login password.3.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.4.At the sqlcmd prompt, type GO, and then press ENTER.

I wonder what are InstanceName, SQLLogin & AvailDBName. It would be nice if someone can give me a specific example, since I am not sure how to type them in a command line. :)

You're in the wrong forum. Try here: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=84&SiteID=1

-Jamie

Cannot open user default Database login failed

Dear all,

I got the error message:

Error 27052 Could not connect to Microsoft SQL Server 'VPC' Cannot open User default Database login failed.

This error is happened when installing one application to Virtual Machine (Machine name: VPC).

Does anyone knows how to solve this problem? I checked from www.microsoft.com but i just got the limited information:

27502

The parameter is not valid.

Not applicable

SSCE_M_CMN_INVALIDPARAMETER

There is no further information.

Thanks & Regards,

Agus Suryadi

When you log in to SQL Server, SQL Server figures out who you are and then puts your session into a specific database. The database it puts you into is either the one you specified on your connection string (if you specified one) or into your default database, the database that your login has as its default.

If you can get into the SQL Server you could check the login you were connecting with to see what its default database is, then check to see if a database with that name currently exists.

Perhaps the application that is being installed expects a certain database to already be created, but apparently it's not there (or the Login/user doesn't have permissions to access it).

Don

Cannot Open User Default Database (error 4064)

Hi,

SQL Server 2005 installed on my computer. i was trying to make a login page with VS.2005 pro. But i got some errors on Website Administration Tool. Whatsoever, i registered a database (aspnetdb) . Then, i think i made my default database to aspnetdb. Something went wrong and i decided to delete aspnetdb and start whole procces from the begining and i did it. AAANDD WHOOOLA. I got this error:

Cannot Open User Default Database, Login Failed
Microsoft SQL Server, Error 4064

Is there anyway to change my default database to "master" again? Or simply will i uninstall all the sql server 2005?
(?f you say uninstall, answer this:: Will be any problem on VS 2005? I can't uninstall both :) )

You should determine what is the login you are connecting at, then login as administrator and issue an ALTER LOGIN statement to set the default database for that login to master. See Books Online for additional information on using the ALTER LOGIN statement.

Thanks
Laurentiu|||I don't think that Laurentiu understands the problem. I have run into the same situation, I am an administrator on the server, because the default database no longer exists (I had changed it from master) I can no longer connect. I would like to think that in the absence of my default database, it would revert to the master, but this is not the case. I'm unable to connect using the Administrator login to execute the ALTER LOGIN command.|||

You can resolve this by specifying another database you want to connect to; master, for example. Then you can reset the login's default database. With sqlcmd, you can do the following:

sqlcmd -d master -U sa -P mypassword

and then:

alter login sa with default_database = master

Thanks
Laurentiu

|||I've googled this error message and I get a hundred hits. Shouldn't there be a KB? I have exactly the same problem. I changed the default DB, then dropped it. I have no idea what the sa password is. Do I need to uninstall/reinstall SS05?|||

I already posted an answer above. You just need to specify a different database to use at connection time, then change your default database to an existing one.

I'll check to see if we have a KB article on this and pass your feedback to write one if we don't already have it.

Thanks
Laurentiu

|||

I see now that you mentioned that you don't know sa's password.

sa was just provided as an example, for this operation you don't necessarily need to use sa to connect. You just need to connect with the login for which you've received this error. If you use Windows authentication, then you should connect like this:

sqlcmd -E -d master

then issue the ALTER LOGIN statement.

It's the "-d master" that's the key here - it specifies that the connection should be made to the master database, so the login won't try to connect to the database that no longer exists.

Hope this makes the solution clearer. If you still have problems with resetting the default database, let us know.

Thanks
Laurentiu

|||To me this seems like a bug with Sql Server Management Studio, as you can connect to the database by specifing the database under options. You cannot however change the user's default database as right click -> properties tries to make a new connection using the default database. As far as I can see, it should create the new connection with the same database specified in the options section?|||

You should post to SQL Server Tools General, for Management Studio issues. You can also search for existing bugs on this issue or open a new one at: http://lab.msdn.microsoft.com/productfeedback/.

Thanks
Laurentiu

|||

I got the same error with SQL Server Management Studio and could not resolve it with sa authentication because my config wouldn't let me log in as sa.

Selecting master as the connect database in the login dialog got me in. I then tried to edit my login object to change my default database, but this fails with the same error!

I then tried deleting my login. Failed again with error 4064.

Finally, I tried to right-click on the login object, and selected Script Login As > DROP to > New Query Editor Window, and then hit Execute in the view that appeared. It worked!

Hope this helps someone out there...

|||

I just ran into the same issue after I dropped a database that was mistakenly set to be the default database. I found the information posted here pointed me in the right direction, but it didn't really help much because I couldn't figure out which user should be modified when SQL Server 2005 is configured to use Windows authentication (e.g. trusted connections) exclusively. After a lot of digging through SQL Server Books Online, executing various stored procedures to dump login information, and experimentation, I finally stumbled on the right combination.

C:\> sqlcmd -E -d master

1> ALTER LOGIN [BUILTIN\Administrators] WITH DEFAULT_DATABASE=master

2> GO

I show the prompts above so you can see where to type the commands and the expected results (e.g. SQLCMD does not output any information or error messages).

|||

I detached a database on a network server with the idea of reattaching an updated version from my local db.

Evidently, the database I detached was the default database. I couldn't log in now because of the posts in this thread. I was able to log in using the Master db at the login dialog. However, I've tried everything in this post, and nothing seems to work!

When I try to execute a query against the master db (such as the sql in these posts), it executes fine. In the drop down list of available databases, the only thing it has is master... the rest says "server is not available". I'm able to ping it just fine.

I can't even restore the db I detached, because I get the same error msgs as not being able to log on. I desperately need to attach this database again with master as the default db.

How can I accomplish this? Can someone please help?

|||

The only thing I can do with logging in as master, is simply just to look at objects. I cannot perform any action at all against anything because of the same error message (Cannot open user default database. Login failed for user sa...).

How can I resolve this!!!!

Microsoft, please help..........

|||For what it's worth, the db icazon next to the server is simply a white circle (no activity, etc)....|||

I tried what you're mentioning Chris and it didn't work.

In the query window I have the following:

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'sa')

DROP LOGIN [sa]

The results state the following:

Msg 15405, Level 16, State 1, Line 3

Cannot use the special principal 'sa'.