Error 7411 not configured for DATA ACCESS 1

[ks1392]

I'm running SQL Server 2000 Pro and have suddenly come across a problem when remote users try to log in and execute a query. They are receiving an error message:

Server: Msg 7411, Level 16, State 1, Line 1
Server 'servername' is not configured for DATA ACCESS.

This is happening both with Windows authenticated users and SQL Server authenticated users. Looking at data through Enterprise Manager works fine, but that's about it. I've looked in BoL, MS support, Google, and here on these forums and haven't been able to find any real info on how to fix this error. Does anyone have any experience with this?

Thanks,
Kerr

 

[mrdenny]

Here's some stuff to check. If you send it to me, I'll see if I can't point you in the correct direction.

Have you changed the name of the server since you installed the SQL Server?

Is @@SERVERNAME the same as your server.

Check the remote server list is correct in Enterprise Manager.

What OS version and SQL Service Pack are you running?

Check the first few entries of the SQL Log, and it should say that it's listening on the port 1433, and the net share path that it's listening on.

Run this and send it back to me

SELECT 'SQL Server '
+ CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - '
+ CAST(SERVERPROPERTY('productlevel') AS VARCHAR) + ' ('
+ CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')', @@SERVERNAME

Denny

 

[ks1392]

Denny,

I believe the name of the server has been changed from blackbox to blackboxdb.

When I select @@SERVERNAME I receive a null value.

We're running NT SP5 and SQL Server 8.00.760 - SP3 (Standard Edition).

It appears I need to change the @@SERVERNAME property. I saw in BoL a blurb about running sp_dropserver and sp_addserver and then stopping and starting the server. I assume this is what I should do to remedy the situation?

Thanks,
Kerr

 

[mrdenny]

correct

sp_dropserver 'blackbox'
go
sp_addserver 'blackboxdb', 'local'
go
--Now restart the SQL Service

That should fix you. You will also need to go into the msdb.dbo.sysjobs table and fix the data in the originating_server field for all your jobs. The field will have the old name, and needs to be updated with the new name.

update msdb.dbo.sysjobs
set originating_server = 'blackboxdb'
where originating_server = 'blackbox'

Denny

 

[ks1392]

Denny - thanks for the help!

 

[mrdenny]

no problem.

Denny