Permissions for network services

I have created a Linked Server to an Access DB on a Novell 6.5 server. The Windows 2003 server with SQL 2005 Server on it is logged in as a Novell user with appropriote rights to the folder where the mdb is stored. The mdb is unsecured.

EXEC sp_addlinkedserver
@.server = 'JetShowData',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = 'k:\groups\sales\showfiles\2007 Show.mdb'

I have successfully created the link and queried the mdb while logged into SQL as Administrator using Windows Autentication. I have set up permissions for another SQL user account (I think) to use the linked server? I have added the linked server login as folllows:

sp_addlinkedsrvlogin 'JetShowData', false, NULL, 'Admin', NULL

This should allow any user to access the linked server. I did try adding a login for a specific SQL user account (sp_addlinkedsrvlogin 'JetShowData', false, 'cbonallo', 'Admin', NULL) but that did not make any difference.

The error I am getting when I try to either test the connection or run a query is the same one I was getting when I had the MSSQLSERVER service running under a local account( I had to change is so that SQLSERVER had network access rights):

TITLE: Microsoft SQL Server Management Studio

"The test connection to the linked server failed."

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "JetShowData" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "JetShowData".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "JetShowData" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.". (Microsoft SQL Server, Error: 7399)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3027&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476

BUTTONS:

OK

This Access db does not have a workgroup file becasue it is unsecured. This error is being reported incorrectly since it is the exact same one when SQL did not have permissions to access the network. I also tried it from another Windows Autenticated user account and got this message:

TITLE: Microsoft SQL Server Management Studio

"The test connection to the linked server failed."

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "JetShowData".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "JetShowData" returned message "Unspecified error". (Microsoft SQL Server, Error: 7303)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3027&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476

BUTTONS:

OK

I'm thinking I need to somehow I need to give the user account network access rights, too? Or some other permissions? Any help will be greatly appreciated!!

Cory Bonallo

Retail Services

Developer

So this is a stumper? There is nothing I can look into. Tech articles? Anything. I did read up on server incompatibilities from this artical http://support.microsoft.com/kb/823659/en-us but don't know enough to evaluate these settings. Any hints would be greatly appreciated!

Cory