About authentication mode

Hello,

Can I change a user's authentication mode from sql server authentication to windows authentication in Tessitura?

Any help would be greatly appreciated.

I wrote a procedure to update one of impresario tables by an excel file, and everything is ok when I run the procedure in Sql Server 2005. Even in the info maker, it could retrieve  and update data correctly. However, I couldn't run it in Tessitura report, and I would get an error message below

*********************************

I tried two different methods to access Excel from Sql Server 2005,

-SQL Server distributed queries

-SQL Server linked servers

 

They all work perfectly fine in Sql Server 2005, but none of them would work in Tessitura. I don't know the reason why....

 

 

Thank you very much in advance!

*********************************

Error Msg I got at Tessitura when I use Server distributed queries:

Select error: SQL STATE= 42000

Microsoft OLE DB provider for SQL server

Ad hoc access to OLE DB provide”Microsoft.Jet.OLEDB.4.0”has been denied. You must access this provide through a linked server

*********************************

Error Msg I got at Tessitura when I use Server linked servers :

Select Error: SQLSTATE= 01000

Microsoft OLE DB provide for SQL Server

OLE DB provides “Microsoft.Jet. OLEDB 4.0 ”for linked server ”EXCELLINK” returned message “Cannot start your application “. The workgroup information file is missing or opened exclusively by another user

--------------------------------------------

The procedure I use Server linked servers

USE [impresario]

GO

/****** Object:  StoredProcedure [dbo].[LP_UPDATE]    Script Date: 12/07/2009 10:31:49 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/****************************** CREATE_LinkedServer******************************  

IF exists (SELECT * FROM sys.servers WHERE NAME = 'EXCELLINK')

BEGIN

EXEC sp_dropserver 'EXCELLINK',droplogins

END

 

DECLARE @RC int

DECLARE @server nvarchar(128)

DECLARE @srvproduct nvarchar(128)

DECLARE @provider nvarchar(128)

DECLARE @datasrc nvarchar(4000)

DECLARE @location nvarchar(4000)

DECLARE @provstr nvarchar(4000)

DECLARE @catalog nvarchar(128)

-- Set parameter values

SET @server = 'EXCELLINK'

SET @srvproduct = 'Excel'

SET @provider = 'Microsoft.Jet.OLEDB.4.0'

SET @datasrc = '\\user\Share\update.xls'

SET @provstr = 'Excel 8.0' --Excel 8.0 for an Excel 2002, Excel 2000, or Excel 97 workbook

EXEC @RC = [impresario].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider,

@datasrc, @location, @provstr, @catalog

 

******************************  DROP_LinkedServer****************************** 

IF exists (SELECT * FROM sys.servers WHERE NAME = 'EXCELLINK')

BEGIN

EXEC sp_dropserver 'EXCELLINK',droplogins

END

*******************************************************************************/

 

ALTER PROCEDURE [dbo].[LP_UPDATE]

        @updateind char(1)--Y/N

AS

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

      SET NOCOUNT ON;

IF @updateind = 'Y'

BEGIN

--set lock_timeout 1000

--Create logins for linkedserver

      exec sp_addlinkedsrvlogin

 

      @rmtsrvname='EXCELLINK',

 

      @useself='False',

 

      @locallogin='sa', -- Replace this with the SQL Authenticated account

 

      @rmtuser=NULL,

 

      @rmtpassword=NULL

 

      exec sp_configure 'login timeout', 1000

 

      reconfigure;

 

      exec sp_configure 'query timeout (s)', 1000

 

      reconfigure;

--Get data from Excel file

      set lock_timeout 1000 SELECT * into #temp FROM EXCELLINK...Sheet1$

 

      UPDATE

      TEST_PROM

      SET response_dt = GETDATE()

      FROM #temp AS A INNER JOIN TEST_PROM AS B ON A.customer_no = B.customer_no

      where 

      A.customer_no = B.customer_no

      AND A.source_no = B.source_no

 

 

      UPDATE TEST_PROM

      SET response =

      (CASE

            WHEN ( CONVERT(varchar(30),A.response) like '%ABC%'  )

            THEN 20

            WHEN ( CONVERT(varchar(30),A.response)  like '%CDE%')

            THEN 21

            WHEN ( CONVERT(varchar(30),A.response)  like '%EFG%')

            THEN 22

            ELSE  B.response--no update

     END )

      FROM #temp AS A INNER JOIN TEST_PROM AS B ON A.customer_no = B.customer_no

      where

      A.customer_no = B.customer_no

      AND A.source_no = B.source_no

 

      SELECT *

      FROM #temp AS A INNER JOIN TEST_PROM AS B ON A.customer_no = B.customer_no

      where

      A.customer_no = B.customer_no

      AND A.source_no = B.source_no

 

drop table #temp

END

 

ELSE

RETURN

 

END

 

[edited by: Vicky Wenhan Yu at 9:56 AM (GMT -6) on 7 Dec 2009]