Cannot connect to Database using EM

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Cannot connect to Database using EM

Author

Topic

wkdelaney
Starting Member

17 Posts

Posted - 2006-04-25 : 00:12:58

Greetings,

A newbie needing some guidance. We have SQL running on our Windows 2003 server. When I connect via the Enterprise manager I have two instances of SQL running.

SRV01\ACT7 (Windows NT)
SRV01\WEBAPP (Windows NT)

When I click on the ACT instance I get the following error:
A connection could not be established to SRV01/ACT7

Reason: Login Failed for user xxxx\administrator.
Please verify SQL Server is running and check your SQL Server registration properties.

I only get the message when I try to connect to the SRV01\ACT. The connection to SRV01\WEBAPP works fine.

I checked with SQL server manager and both instances are running and no one has reported any problems connecting to either one of them.

The way it acts, its as if SQL server has lost the ability to authorize a connection via the windows account. As if it has lost its associatoin to the administrator account. So while the instance still runs and is functional, I just can't connect to it and manage it.

I have connected via remote desktop from a WinXP machine and the server console. Both end up with the same error.

Can Someone give me step by step instructions on how to correct this?

Any help please!!!

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-04-25 : 00:57:24

first, verify that your account has access to the first instance.

have the admin make sure that your user account is specified or contained in a group that has been granted permissions to access this instance. He/she can easily do this by comparing the two instances for differences in the security folder of enterprise manager.

-ec

wkdelaney
Starting Member

17 Posts

Posted - 2006-04-25 : 08:12:48

I am logged in as the administrator on the server. shouldn't that grant me access? Remember I have access to SRV01\WEBAPP but not to SRV01\ACT7.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-25 : 08:22:54

quote:
Originally posted by wkdelaney

I am logged in as the administrator on the server. shouldn't that grant me access? Remember I have access to SRV01\WEBAPP but not to SRV01\ACT7.

It should unless somebody has configured in ACT7 such that it deny access to Administrator !

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-25 : 09:32:00

verify the following:
1. was the builtin\administrator removed or denied access
2. is that user still permitted to connect?
3. it looks to me that you are using the local administrator group
xxxx\administrator
4. since these are instances, check your client network utility if they are configured to have: 1 instance=fixed port like 1433, the other dynamically determine port
--also check the service account running the instance services

that's it for now, hope that helps...

--------------------
keeping it simple...

wkdelaney
Starting Member

17 Posts

Posted - 2006-04-25 : 11:07:14

JEN,
I am using remote desktop to access the server logging in as the administraor. the builtin account has not bee removed or denied access. The local admin group has full access. I have no problem connecting to SVR01\WEBAPP in EM. Both SRV01\WEBAPP and SVR01\ACT7 are in the same EM\SQL group. I don't understand why I cant connect to one and not the other. Unless something has happend to the SA for SVR01\ACT7?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-25 : 11:14:26

ok... like this...

go into the srv01.. open EM, use windows authentication to connect to ACT7...

how did it go?

--------------------
keeping it simple...

suhde76
Starting Member

10 Posts

Posted - 2006-04-25 : 15:00:58

Open Query Analyzer, and connect to SRV01\ACT7 using the login "sa" or any user that has sysadmin privileges. Run the following query:

Use Master
Go
Select * from sysxlogins
Go

You should see the xxxx\administrator in the list. If you dont see xxxx\administrator in the list, run the stored proc:

EXEC sp_grantlogin 'xxxx\administrator'
Go
EXEC sp_addsrvrolemember 'xxxx\administrator', 'sysadmin'
Go

Now try connecting, you should be able to connect.

Thanks, Suhas, MS SQL Server Engineer, Microsoft.

wkdelaney
Starting Member

17 Posts

Posted - 2006-04-25 : 17:00:18

suhde76,

Is the stored proc: command ran from the query analyzer also?
thanks,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-25 : 17:40:34

quote:
Originally posted by wkdelaney

suhde76,

Is the stored proc: command ran from the query analyzer also?
thanks,

Yes.

Tara Kizer
aka tduggan

wkdelaney
Starting Member

17 Posts

Posted - 2006-04-25 : 20:53:43

No I cannot connect. I get the following error with Query analyzer:

Unable to connect to server SRV01\ACT7
Server: Msg 18456, Level 16, State 1
Login failed for user ‘XXX\administrator.

Nothing i have tried so far has worked. If I use the EM to log on with "sa" or any user that has sysadmin privileges, it fails and ask me if i still want to modify.

At this point i am not even sure i am using a vaild SA login. Not sure what to do next.

Any help is appreciated!

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-25 : 21:28:49

what is the account that the sql services for act7 is running on?

if it's a domain account, use that account on another machine and try to logon

what happens next?

--------------------
keeping it simple...

wkdelaney
Starting Member

17 Posts

Posted - 2006-04-25 : 21:36:40

The SQL server is running on a domain controler. I have tried to connect to SVR01\ACT7 from another computer and also from the server itself. I belive I have a correct sysadmin username and password but not entirely sure now!

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-25 : 21:41:28

ok, breathe and relax...

did you try to use the domain account that is running the sql services for act7 on another computer?

if yes, did you verify if the password expired?

--------------------
keeping it simple...

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2006-04-26 : 00:27:57

Good last name Delaney. :)

Answer the following questions:

1. What is the exact name of the account the MSSQL$ACT7 service is running under.
2. How did you verify the BUILTIN\Administrator hasn't been removed if the following statement you made is true?

quote:

Nothing i have tried so far has worked. If I use the EM to log on with "sa" or any user that has sysadmin privileges, it fails and ask me if i still want to modify.

Who verified the account status for you? Can they login to the SQL Server? If so, how are they logging in?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

wkdelaney
Starting Member

17 Posts

Posted - 2006-04-26 : 18:41:38

As far as I know the name of the account the MSSQL$ACT7 service is running under is the administrator account. I am not entirely sure if the BUILTIN\Administrator hasn't been removed for SRV01\ACT7. I just assumed it wasn't. But you know what assumptions mean!

I belive the BUILTIN\Administrator is there for SRV1\WEBBAPP because I have no problem connection to it in EM ot Querry.

Every thing that all of you have told me to do leads me to belive it probably has been removed because I can't connect to it. with any method.

Anyhow, I don't know much about SQL to start with and the Admin who was here is gone. ACT is the primary contact application this company uses.
The users do not have any problem opening ACT from their workstation and I have an ADMIN username and password to use once act is open and at the login screen. once in ACT I have the ability to add and remove users. I have tried to add a user with Admin privliges but the new account only works to log into ACT the porgram and not in EM or Query.

Any ideas?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-26 : 19:03:21

quote:

As far as I know the name of the account the MSSQL$ACT7 service is running under is the administrator account.

You can't make a guess. Check it out. Control Panel..Admin Tools..Services..Double click on MSSQL$ACT7..what does it say for Log On..This account?

Tara Kizer
aka tduggan

wkdelaney
Starting Member

17 Posts

Posted - 2006-04-26 : 19:35:55

In Services,MSSQL$ACT7 Properties (local computer). under the Log on tab it says:

Log on as: Local system account

If you click the button next to: This account, there is no name in the box but you can browse.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-26 : 20:48:24

one last try, look at sqlserveragent service, same location as mssqlserver for act7, is it also localsystemaccount or uses a domain account? if yes, use that account, if not go through the list below

you're trying to get into a system that...
1. runs under the localsystemaccount
2. builtin\administrator removed
3. and you only have application admin user privilege on one/more databases in the server itself, not even a sql login
4. nobody now in the company can get into the sql server

hmmm...

1. well... can you do a backup of the database in question using the apps you mentioned? there must be some administrative tasks?
2. then restore that backup on srv01\webapp, if successful, store the backup in a safe location to be retrieved later...
3. then remove the instance act7...
4. then restore the backup file

--------------------
keeping it simple...

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2006-04-26 : 21:28:35

quote:

In Services,MSSQL$ACT7 Properties (local computer). under the Log on tab it says:

Log on as: Local system account

If you click the button next to: This account, there is no name in the box but you can browse.

Switch this service to run under a domain user account. Login to the server as that user. You can then connect through EM. You should then add some accounts that make sense, and that you can use to manage with.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-26 : 22:10:07

but don't you need to add that domain account in the sql server first before defining it as service account?

well, atleast it's worth a try at this point

quote:
Originally posted by derrickleggett

quote:

In Services,MSSQL$ACT7 Properties (local computer). under the Log on tab it says:

Log on as: Local system account

If you click the button next to: This account, there is no name in the box but you can browse.

Switch this service to run under a domain user account. Login to the server as that user. You can then connect through EM. You should then add some accounts that make sense, and that you can use to manage with.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

--------------------
keeping it simple...

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources