MS SQL Server 7 won't allow connection in EM

[hawkinpaul]

We recently installed SP4 on our MS SQL 7 server. After installing the update things seemed to be running fine. Our users can connect to the database and do their work but the backup was failing because it couldn't access the database.

When I went into Enterprise Manager and tried to expand the server I got this error;

A connection could not be established to "Server Name" - Access denied
ConnectionOpen (CreateFile())..

Please Verify SQL Server is running and check you SQL Server registration properties (by right clicking on the "Server Name" node) and try again.

I right clicked the server and choose "edit sql server registration properties"
When I double checked the password to make sure it was correct and then hit ok it still gave me the "Access Denied" error.

OK, here is the strange part. If I right click on the server and choose "New SQL Server Registration" the serverdoesn't even show up in the list but the IP address of the server does. When I choose that and try to register the server using the same password it connects with no problem.

So now instead of seeing my server name in Enterprise Manager , I see the IP address. I still can't connect with my backup software and also can't run the KB815495 security patch because it can't connect to the server.

Sorry for the long post but it is a strange problem.

-Paul

 

[JamesLean]

Connect to your server thru Query Analyzer using the IP address and then execute:

SELECT @@SERVERNAME

This should show the name of your SQL Server. Then try and register that.

--James

 

[hawkinpaul]

I know the name of my server. It was working before. That when I try to register the server as that name it fails with an access is denied error.

Paul

 

[hawkinpaul]

I will run it any way and let you know.

Paul

 

[hawkinpaul]

We went ahead and ran the command. It returned that the server name was NULL. What the heck? Is there some whay we can set it back up?

Paul

 

[JamesLean]

Look up sp_addserver in Books Online. Use the LOCAL parameter to set the local server name.

--James

 

[hawkinpaul]

We were able to run the sp_addserver with the LOCAL parameter and add the server name. Then we ran select @@SERVERNAME and it did return the correct server name, However, we are still getting the same result when we try to add the server.

Paul

 

[JamesLean]

Did you restart the server after running this procedure?

--James

 

[hawkinpaul]

Yes we did.

Paul

 

[hawkinpaul]

Do I need to do anything else after rebooting?

Paul

 

[SQLBill]

Try this for me:

Open a command prompt window (Start>RUN type in cmd and click ok). At the prompt type: (replace <servername> with the actual name of the server).

PING <servername>

then try:

PING <serveripaddress> -a

What were the results?

The first should return a REPLY message. If not, then your DNS server is not doing name resolution (not converting the name to the ip address).

The second should also return a REPLY, but it should also return the name of the server. If you get REPLY lines, but no name then it's a DNS error. If you don't get REPLY lines, then there is a bigger problem - something is keeping your computer from 'seeing' the server.

-SQLBill

 

[hawkinpaul]

I don't have any problems seeing the server from a workstation. The problems I am having only deal with the SQL server. Thanks for your help.

-paul

 

[sunila7]

Do you have the latest version of MDAC installed on these machines? What version of MDAC do you have installed on your machines? Try installing MDAC 2.6 and see whether it helps...

Hope this helps.


Sunil

 

[SQLBill]

In all my experiences with this type of problem:

OK, here is the strange part. If I right click on the server and choose &quot;New SQL Server Registration&quot; the serverdoesn't even show up in the list but the IP address of the server does. When I choose that and try to register the server using the same password it connects with no problem.

It's caused by lack of Name Resolution. Your DNS server is not converting the Name to the IP address, therefore you can only use the IP address to connect.

-SQLBill

 

[hawkinpaul]

All of my clients are connecting to the SQL server without a problem. The problem I am having ONLY shows up in Enterprise Manager.
My DNS resoloution is fine. I can ping by name from the local machine and remote machines.

When I try to connect to the server by name in Enterprise Manager it does not give me an error message that say it cannot connect to the server. It gives me anerror message saying that Access is denied.

-paul

 

[hawkinpaul]

Any Ideas?

Paul

 

[hawkinpaul]

I checked to make sure it was not a DNS problem and it isn't. Does anyone have a clue about this one?

Paul