Issue with connecting to SQL Server

[iheartvba]

Hi,

I am trying to connect to an SQL Server Express 2005 Back End with an Access mdb front end. I realise mdb's are not the best to link to a SQL Server.

Reasons for not using an adp file: not able to transfer my queries from the mdb to the adp file.

Attempt to use accdb file: I tried to convert the mdb to an accdb file but the same error (listed below) also occured with the accdb file, so I suspect the issue isn't because I am using an mdb file.

The error is:
Connection Failed
SQL State:'28000'
SQL Server Error:18456
[Microsoft][ODBC Server Driver]login failed for user 'KAMAL\Guest'

the error message box only gives me the option to click 'ok', when I click 'ok' another box opens with the Server Name and a ticked check box called 'use trusted connection' and below it are windows logon details, if I untick the check box I am able to enter the SQL Server Authentication Details,and the error goes away, but then when I close and reopen the database the error happens again.

I have attached file with screenshots that may be usefull (the file is a .zip file contianing a .doc file).

Other Info:
Authentication: SQL Server Authentication
Remote Connections: Enabled
Shared Memory: Enabled
TCPIP: Enabled
Named Pipes:Enabled
VIA : Disabled
Firewall( Client & Server side) : Disabled


Thanks

[NeoPa]

I'm not sure what to tell you.

I know of no way to encode a SQL Server Username & Password into your link (If you find a way then I'll borrow it from you ;)). The only other way that is usually available is Windows Authentication (which clearly isn't set up to work correctly for you). This can only work from a machine on the same or a trusted Windows Domain.

A less used (Great idea but few seem to implement it somehow - even commecially) is Application Security. It was available in the full version of SQL Server 2000, but I don't know about 2005 Express Edition. It may be worth looking at for software access, as that's fundamentally what it was set up for. On the other hand security is there for a reason. What you seem to be trying to do is to circumvent it (I know - probably just for convenience rather than any nefarious purpose). This should be discussed with the DB Server Admin. They will need to set something up for this before you can use it anyway.

[iheartvba]

Well actually, I don't mind using windows authentication, I am only using SQL Authention because otherwise the client app (also on the lan) isn't able to connect to the SQL server.

Another thing which may clarify a few things is that the access front end which is on the local machine hosting the SQL Server doesn't give any issues, but I don't really know what to make of that...

My mind is just boggled, and I need to get this thing up and running on the network :S

I will try the App. Automation you suggested, hopefully it works

Thx

[NeoPa]

@iheartvba
Windows Authentication is no good for you unless it lets through who you want it to let through. There's no good being happy with using it if it doesn't work.
@iheartvba
I suspect the machine the server runs on is already logged in with an account that has the required permissions. That would be usual on the PC itself. Clearly it must be a PC that is a member of the correct domain in this case.
@iheartvba
Sorry. I just reread my post and it did actually say that. i've corrected it now to read Application Security. A feature available in SQL Server 2000 certainly. Sorry for the misdirection.