FM3 error: Could not configure SQL Server for DATA ACCESS

questions
,
1

This came out of the blue today, I suspect DBA has made changes to the server, but don't know where to start looking. User should be a db_owner. As far as I can tell, the required changes were done. I am not sure which file it talks about, but the suspect could be a file where I drop one computed field before performing upgrade.

fm3
fm3583×158 7.75 KB

2

That error is FM3’s M36 — it isn’t about your table. FM3 needs the local server registered as a linked server with DATA ACCESS = TRUE, and if it isn’t, FM3 tries to set it with sp_serveroption. That needs ALTER ANY LINKED SERVER (a server permission), so db_owner isn’t enough — which is why it “came out of the blue” after the DBA touched the server.

Check first:

sql

SELECT @@SERVERNAME;
SELECT name
     , is_linked
     , is_data_access_enabled
  FROM sys.servers;

If the row for @@SERVERNAME is missing, or is_data_access_enabled = 0, have the DBA run once as sysadmin:

sql

EXEC sp_serveroption @server = @@SERVERNAME
                   , @optname = 'DATA ACCESS'
                   , @optvalue = 'TRUE';

(If @@SERVERNAME isn’t in sys.servers at all, the DBA needs sp_addserver '<name>', 'local' and a service restart first — that happens after a rename.)

Once DATA ACCESS is already TRUE, FM3 never needs to set it, so the app login can stay db_owner. The alternative is to grant the login ALTER ANY LINKED SERVER, but most DBAs won’t, and it isn’t needed if the option is set once.

CapeSoft’s own notes on this are in the FM3 SQL docs under “The SQL Database”.

3

Thank you. We have used a login separate from a sysadmin login, so probably exactly what must be changed :slight_smile: