Problem! Connection with ADOMD

[MathewLeung]

Hi,

I have an application which is running on a different machine then the SQL Server. Both machines are running within the same workgroup but not domain.

I have MDAC 2.6 installed on client. My application is a Lotus Notes client using ADOMD to access the OLAP cubes on the SQL server.

Security on SQL Server is set to run on Mixed mode, where I want to use the SQL server authentication only. A user account is setup on SQL server as TestUser with pwd as password.

My code is as follows:

Set catalogObject as CreateObject("ADOMD.Catalog&quot
catalogObject.ActiveConnection = "DataSource=Dev2;Initial Catalog=Data Warehouse;Provider=msolap;UID=TestUser;Pwd=password;"

If I logged on to the workgroup as TestUser and entered password as password, then I would be able to connect to the SQL server with access to the cubes

But if I logged on as a different user who does not exist in the login accounts, it will return an error with 'user could not be authenticated'.

Question 1: What can I do if I only want to pass user informatino using the connection string to authenticate user rather then using windows authentication?

Question 2: I tested my connection on the client and it took around 2 mins for SQL server to authenticate and it took no time on the server. Can connection time be improved when running on the client in some way?

Thanks in advance for any help

 

[foxdev]

1) I think the problem is that you need to specify [tt]TRUSTED_CONNECTION=NO[/tt]. I'm not familiar with ADO, but with other forms of ODBC connection strings I've added that phrase and it corrects the problem (IOW, it forces the "standard" security authentication). -----------------
Robert Bradley
use coupon code "TEKTIPS" for 15% off at:

http://www.lowcostprints.com

http://www.foxdev.com

 

[MathewLeung]

I have tried and it still does not use the UID and PWD from the connection string. And someone has told me that MS OLAP only uses NT authentication. Probably thats why it is not working for me.

Thanks anyway Robert.

 

[tlbroadbent]

You may need to upgrade to MDAC 2.6 SP1.

FIX: SQL Server ODBC Driver Ignores Authentication Setting

http://support.microsoft.com/support/kb/articles/Q279/5/26.ASP

Download SP1:

http://www.microsoft.com/data/

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.