Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Other SQL Server Topics (2005)  Ad hoc access denied

Author  Topic  ArviL Starting Member 25 Posts Posted - 2010-09-27 : 08:47:26 Hi I have 2 different servers. On one of them is installed 64bit TS 2005 (referred as TS1), and on another is installed 32bit TS 2005 Express (TS2). TS2 is defined as linked server in TS1. In TS2 I have a database MyDB and in it a stored procedure, which queries data from DBF-format tables. When I execute this procedure directly from TS2, then it works OK. When I try to execute from TS1, I get an error "Ad hoc access to OLE DB provider 'MSDASQL' has been denied. You must access this provider through a linked server." I'd prefer to cope without defining a linked server there! (So long I haven't been able to define such LS with parameters Exclusive=No and Deleted= Yes) Any advice available? Thanks in advance! Arvi Laanemets --- Additional info --- --The linked server defined in TS1: /****** Object: LinkedServer [TS1] Script Date: 09/27/2010 15:11:25 ******/ EXEC master.dbo.sp_addlinkedserver @server = N'TS2', @srvproduct=N'SQL Server' /* For security reasons the linked server remote logins password is changed with ######## */ EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TS2',@useself=N'False',@locallogin=NULL,@rmtuser=N'LinkedServerUser',@rmtpassword='########' GO EXEC master.dbo.sp_serveroption @server=N'TS2', @optname=N'collation compatible', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'TS2', @optname=N'data access', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'TS2', @optname=N'dist', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'TRK-TS2', @optname=N'pub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'TS2', @optname=N'rpc', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'TS2', @optname=N'rpc out', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'TS2', @optname=N'sub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'TS2', @optname=N'connect timeout', @optvalue=N'0' GO EXEC master.dbo.sp_serveroption @server=N'TS2', @optname=N'collation name', @optvalue=null GO EXEC master.dbo.sp_serveroption @server=N'TS2', @optname=N'lazy schema validation', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'TS2', @optname=N'query timeout', @optvalue=N'0' GO EXEC master.dbo.sp_serveroption @server=N'TS2', @optname=N'use remote collation', @optvalue=N'true' --An example of stored procedure code USE [MyDB] DECLARE @Employees TABLE( Empl_id varchar(15), forename varchar(50), lastname varchar(50), department numeric(6,0), appointment varchar(10), empl_start datetime, empl_end datetime ) INSERT INTO @Employees ( empl_id, forename, lastname, department, appointment, empl_start, empl_end ) SELECT * FROM OPENROWSET( 'MSDASQL', 'Driver=Microsoft Visual FoxPro Driver; SourceDB=\\trk-trt13\taavi\wkaader\; SourceType=DBF; -- NB! The next parameter value is essential! Exclusive = No; Collate=Machine; -- NB! The next parameter value is essential! NULL = NO; DELETED = YES; BACKGROUNDFETCH=NO ', 'SELECT Empl_id, forename, lastname, department, appointment, empl_start, empl_end FROM Employees') --Refresh Employees table TRUNCATE TABLE dbo.Employees INSERT INTO dbo.Employees SELECT empl_id, forename, lastname, department, appointment, empl_start, empl_end (Case When empl_start<'01.01.1900' Then Null Else empl_start End) As meile_tool, (Case When empl_end<'01.01.1900' Then Null Else empl_end End) As empl_end, FROM @Employees WHERE empl_id Is Not Null AND empl_id <> '' AND empl_start Is Not Null -- In TS2 is defined a login for LinkedServerUser, and it is mapped as dbo for MyDB -- In database MyDb are given rights to connect, delete, execute, insert, select and update for LinkedServerUser