Login failed.... Not associated with a trusted SQL Server connection

[JohnBates]

hello experts,

I've been asked to connect an external Access application to a new SQL Server database (2000).

The SQL Server machine is also a web server (IIS 5) and I think that relates to my problem.

The Access program gives this error when it tries to read the SQL Server table:
"Login failed for user... Not associated with a trusted SQL Server connection"

We've all seen this error before.

The SQL Server is configured for Windows authentication.
I have tried passing both local account names as well as network/domain accounts. But the Access program has never been able to connect.
I also tried the IUSR_ServerName but that didn't work either.

The Access program is able to connect to several other SQL Server machines but none of those are web servers.

Any ideas?

Thanks very much. John

 

[gmmastros]

Are you using a non-standard port (i.e. not port 1433) for the SQL Server instance on your webserver?

Try setting up an alias in cliconfg. This should be set up on the machine that is running the access database.

Click Start->run
Type cliconfg

When setting up the alias, make sure you specify the port.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[JohnBates]

Georgw,

Unless IIS would require SQL Server use something other than port 1433, then we are using 1433. I'm 95 % certain - but when I return to the office tomorrow I will check that.

Thanks. more info later.
John

 

[JohnBates]

... OK. Yes, SQL Server is using port 1433. SQL Server log shows 'Listening on Port 1433'

From the PC that runs the Access program, I can ping the server by server name as well as IP address - both give replies.

I think it will connect successfully, by passing the IUSR_SONYBMG login.

But I don't know what the password for this account is. The guy that configured IIS does not know either.

**** Is there any way to 'extract' the password that IIS assigned for IUSR_SONYBMG ? ****

Thanks, John

 

[JohnBates]

... will I have to switch to Mixed Authentication (Windows AND SQL Server) to enable the Access app to connect ?

All along, the server has been set to Windows Only.

John

 

[JohnBates]

... an update.

Well, it did not connect successfully when I passed the
IUSR_SONYBMG user name.

Apparently that is only used by IIS for web apps. Other apps must supply a regular Windows login ?

Will switching to SQL Server and Windows authentication enable it to connect?

I'm getting nowhere fast.

Thanks. John

 

[JohnBates]

.... this is strange.

(This is SQL Server 2000 SP4 and it uses the default port of 1433).

I wrote a little VB 6 app to connect to this database from my PC.

I CAN connect with the VB app , using my own network login.

But the Access app, with the same connection string as I used in the VB app, running on another PC.... gives error
"Login failed for user. Not associated with a trusted SQL Server connection."

SQL Server is configured for Windows Authentication.

Does anyone have an idea how I can get the Access app to connect to the SQL Server database?

I really appreciate any ideas.
Thanks, John