How is security to FoxPro managed through Linked Server from SQL

I’ve been working on getting a linked server through SQL 2005 to work with VFP 9.

I get access denied for any and all security set ups on the linked server. I’ve checked the folder-level security settings and see that the user I’m logged in as, and have tried through security settings, and they seem to have access.

What other security settings should I be checking?

Hi Doug,

As you probably already know, there isn't any real security on Fox tables other than Windows permissions to the directory. That means that the account SQL Server uses must have permissions to the directory where the tables are. What account does your SQL Server service run under? Does that account have permissions to the directory?

|||

sp_addlinkedsrvlogin

Creates or updates a mapping between logins on the local instance of SQL Server and remote logins on the linked server.

take note of this option

sp_addlinkedsrvlogin [ @.rmtsrvname = ] 'rmtsrvname'
[ , [ @.useself = ] 'useself' ]
[ , [ @.locallogin = ] 'locallogin' ]
[ , [ @.rmtuser = ] 'rmtuser' ]
[ , [ @.rmtpassword = ] 'rmtpassword' ]

[ @.rmtsrvname = ] 'rmtsrvname'

Is the name of a linked server that the login mapping applies to. rmtsrvname is sysname, with no default.

[ @.useself = ] 'useself'

Determines the name of the login used to connect to the remote server. useself is varchar(8), with a default of TRUE.

A value of true specifies that logins use their own credentials to connect to rmtsrvname, with the rmtuser and rmtpassword arguments being ignored. false specifies that the rmtuser and rmtpassword arguments are used to connect to rmtsrvname for the specified locallogin. If rmtuser and rmtpassword are also set to NULL, no login or password is used to connect to the linked server.

[ @.locallogin = ] 'locallogin'

Is a login on the local server. locallogin is sysname, with a default of NULL. NULL specifies that this entry applies to all local logins that connect to rmtsrvname. If not NULL, locallogin can be a SQL Server login or a Windows login. The Windows login must have been granted access to SQL Server either directly, or through its membership in a Windows group granted access.

[ @.rmtuser = ] 'rmtuser'

Is the user name used to connect to rmtsrvname when useself is false. rmtuser is sysname, with a default of NULL.

[ @.rmtpassword = ] 'rmtpassword'

Is the password associated with rmtuser. rmtpassword is sysname, with a default of NULL.

|||

Yeah, I thought there was no other security other than file level. I'm baffled then. The user of the SQL service does have read rights to the directories and files. I have tried local administrator accounts on the server by designating security through the linked server also. :( Grasping at straws at this point.

|||Thanks for the help screen. I've been trying different combinations. Also trying all possible ways to open up security for various users. What I have gotten is that the SQL Server service user ID can now load through the Linked Server but I can't get any other IDs to load through the Linked Server. Good progress though.