unable to access SQL database. Connection could not be established 1

[KevinSwe]

I have SQL 2000 running on W2K. Going into SQL Server Enterprise Manager I try to expand the (local)and get the message "A connection cannot be established to (local. Reason: Cannot open user default database. Login failed.
Please verify SQL Server is running and check your SQL Server registration properties."

Right clicking on (local) doesn't seem to do much. Hesitant to delete the registration and editing it doesn't help.

I am getting a lot of PERC errors in the event logs so I know I have a problem there. Working on a replacement server but running out of time.

My big concern is how to fix the problem of not being able to access the databases? I've search a number of sites to no avail.

Thanks,
Kevin

 

[gmmastros]

Cannot open user default database usually means that the default database setup for the user has been detached.

Open Enterprise Manager
Drill down to the server (local)
Right Click
Edit SQL Server Registration Properties

I suspect that you have 'Use SQL Server Authentication'
with a login and password.

Try changing to Windows Authentication OR changing the user that is logging in.




-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[KevinSwe]

Thanks for responding. I am using Windows authentication.
When I change the authentication I got the message: "SQL Server registration failed because of the connection failure. Do you wish to modify anyway?"

"Login failed for user xxxx."

I tried to go into ODBC and edit the setting but when it comes to the authenticaton part it fails because it cannot authenticate to (default).

The problem seems to be centering around the default database and getting acess.
Thank you,
Kevin

Nothing's so simple you can't complicate it.

 

[SQLBill]

Can you login with the SA account? If so, you need to do so and do the following:

1. check to see that the database is available (attached).

2. check your login to make sure that it exists and has the correct default database.

-SQLBill

Posting advice: FAQ481-4875

 

[KevinSwe]

Thank you! That took care of getting me access. I've saved the databases so I can get over to anew server.

Any ideas what caused it? Do I have to continue to use the sa account?

 

[SQLBill]

You are probably an administrator on the computer (sysadmin). Check to see if the BUILTIN/Administrator login on SQL Server has been deleted or set to DENY.

If your login has administrative permissions on the local server (where SQL Server runs) and/or the domain, then you are accessing SQL Server via the BUILTIN/Administrator login. (Yes, even if you have your own login created). If that login is set for DENY, then it's a case of 'least privileges' or the 'trickle down' permissions. Local system and/or domain admins are DENY'ed access, so therefore you also are denied that access.

It is considered poor security to keep the BUILTIN/Administrator login, since it gives SQL Server admin access to anyone who has a Windows administrator account on that system. Some people make the mistake of just setting the permissions to DENY. You need to delete the login completely.

I make sure my personal account has the default of MASTER. If that database is missing, then I'm having real problems.

-SQLBill

Posting advice: FAQ481-4875

 

[KevinSwe]

I seldom get into SQL and forgot about he least privileges.
Thank you!

 

[SQLBill]

You are welcome. Thanks for the star.


-SQLBill

Posting advice: FAQ481-4875